Introduction to RDB with sqlalchemy Ⅰ

Database

What is DB

Forget the word RDB and learn the word database first.

A DB (Database) is a collection of a lot of data for later use **. For example, an address book or telephone directory can also be called a DB.

In particular, DB is indispensable for Web services. For example, if you are a search site, you will need a DB that collects websites, and if you are an SNS like twitter, you will need a DB that collects posts.

DB and DBMS

There is a type of software called DBMS (Database Management System) for using DB.

DB alone is just data, but by using DBMS, you can easily perform operations such as adding data to the DB and retrieving data from the DB.

The relationship between DB and DBMS is just like the relationship between xlsx files and Excel. The data is saved in the xlsx file, but you can't view or edit it without Excel. In the same way, a DBMS is indispensable for operating a DB.

Please note that DB and DBMS are often collectively referred to as a database. Unless otherwise noted, this article distinguishes between DB and DBMS.

Data model

The way a database stores data is called a "data model."

There are many different ways to store data, but here are some typical ones.

--"Hierarchical data model" that stores data as a tree structure --"Network type data model" that stores data as a bundle --"Relational model" that stores data as a table

Summary

--DB (Database) is a collection of a lot of data for later use. --DBMS is a general term for software for operating DB, and is indispensable for using DB. --The method by which DB stores data is called "data model", and there are various types.

Relational database

What is RDB

RDB (Relational Database) is a DB based on the Relational model, and the DBMS of RDB is called RDBMS.

The relational model stores the data as a table. If it's hard to imagine, it's okay to recognize that an RDBMS is something like Excel.

As an example, make a table showing the menu.

+----+-----------+------+
| id | name      | kcal |
+----+-----------+------+
|  1 |Curry meshi|  500 |
|  2 |Kiviak|  100 |
+----+-----------+------+

line

First, let's look at the table focusing on the horizontal direction (Row). The first line is extracted as follows. Note that the top line is the heading, not the body of the data.

+----+-----------+------+
| id | name      | kcal |
+----+-----------+------+
|  1 |Curry meshi|  500 |
+----+-----------+------+

As you can see, the row represents a piece of data.

Next, let's look at the table with a focus on the vertical direction (Column). If you extract the name column, it will be as follows.

+-----------+
| name      |
+-----------+
|Curry meshi|
|Kiviak|
+-----------+

By extracting columns in this way, it is possible to compare multiple data by a certain item.

Summary

--RDB is a DB based on a relational model and stores data in the form of a table. --By looking at the rows, you can represent the data. --Focusing on columns, you can compare between multiple data.

What RDBMS can do

Operations on tables

--Create a new table --Delete existing table --Change the structure of the existing table

Modifying the structure of an existing table means adding or removing columns from the existing table.

Manipulation on data

--Data acquisition --Create new data --Data deletion --Data update

Of these, other than new creation, it can be applied after performing the following operations.

--Refine by condition (only e.g.kcal is 200 or less) --Sort (sort in ascending order of e.g. kcal) --Limited number of cases (e.g. 10 cases only)

By combining these, for example, the following operations are possible.

--Delete data with kcal of 200 or less --Sort data with kcal of 200 or less in ascending order of kcal and update the first 10 kcals to 100 --name gets the data of curry meshi --After sorting kcal in ascending order, get 10 items from the 30th item.

This is a very flexible specification.

Summary

--RDBMS can operate on tables and data. --Very flexible specification is possible when acquiring / updating / deleting data.

This summary

This time, I explained that DB / RDB and RDBMS can be done. You don't have to remember everything perfectly, but the content of the "summary" is important, so try to keep it as low as possible.

Next time, I will finally use RDB from Python using sqlalchemy.

Recommended Posts

Introduction to RDB with sqlalchemy Ⅰ
Introduction to RDB with sqlalchemy II
How to update with SQLAlchemy?
How to Alter with SQLAlchemy?
How to Delete with SQLAlchemy?
Connect to multiple databases with SQLAlchemy
How to INNER JOIN with SQLAlchemy
Introduction to MQTT (Introduction)
Introduction to Scrapy (1)
[Introduction to WordCloud] Let's play with scraping ♬
Introduction to Scrapy (3)
Introduction to Supervisor
Introduction to Tkinter 1: Introduction
How to get parent id with sqlalchemy
Introduction to PyQt
Introduction to Scrapy (2)
[Introduction to Python] Let's use foreach with Python
[Python] Introduction to CNN with Pytorch MNIST
[Linux] Introduction to Linux
[Introduction to Pytorch] I played with sinGAN ♬
Introduction to Scrapy (4)
Introduction to discord.py (2)
How to use SQLAlchemy / Connect with aiomysql
Introduction to discord.py
Introduction to Statistical Hypothesis Testing with stats models
[Python] Easy introduction to machine learning with python (SVM)
Introduction to Artificial Intelligence with Python 1 "Genetic Algorithm-Theory-"
Markov Chain Chatbot with Python + Janome (1) Introduction to Janome
Markov Chain Chatbot with Python + Janome (2) Introduction to Markov Chain
Introduction to Artificial Intelligence with Python 2 "Genetic Algorithm-Practice-"
[Introduction to StyleGAN2] Independent learning with 10 anime faces ♬
Introduction to Tornado (1): Python web framework started with Tornado
Introduction to formation flight with Tello edu (Python)
[Introduction to minimize] Data analysis with SEIR model ♬
Introduction to Python with Atom (on the way)
Introduction to Vector Autoregressive Models (VAR) with stats models
Introduction to Generalized Linear Models (GLM) with Python
[Introduction to Udemy Python3 + Application] 9. First, print with print
Convert 202003 to 2020-03 with pandas
Introduction to Web Scraping
Introduction to Nonparametric Bayes
Introduction to Python language
Introduction to TensorFlow-Image Recognition
Introduction to OpenCV (python)-(2)
Introduction to PyQt4 Part 1
Introduction to Dependency Injection
Introduction to Private Chainer
Use Enums with SQLAlchemy
Introduction to machine learning
[Introduction to Python] How to iterate with the range function?
[Introduction to WordCloud] It's easy to use even with Jetson-nano ♬
How to convert a class object to a dictionary with SQLAlchemy
[Chapter 5] Introduction to Python with 100 knocks of language processing
An introduction to Python distributed parallel processing with Ray
Reading Note: An Introduction to Data Analysis with Python
[Chapter 6] Introduction to scikit-learn with 100 knocks of language processing
How to get more than 1000 data with SQLAlchemy + MySQLdb
[Chapter 3] Introduction to Python with 100 knocks of language processing
[Introduction to Pytorch] I tried categorizing Cifar10 with VGG16 ♬
[Chapter 2] Introduction to Python with 100 knocks of language processing
Introduction to Vector Error Correcting Models (VECM) with stats models