Introduction to RDB with sqlalchemy II

Last time studied the basics of DB and RDB.

This time, let's finally utilize DB from Python.

Advance preparation

Install sqlalchemy

$ pip install sqlalchemy

Database preparation

Connection with database

Create db.py and write the following contents.

db.py


from sqlalchemy import create_engine, MetaData

engine = create_engine('sqlite:///db.sqlite3', echo=True)
metadata = MetaData()
metadata.bind = engine

I will explain it line by line.

engine = create_engine('sqlite:///db.sqlite3', echo=True)

Create an object to connect to the DB.

In the first argument of create_engine, specify the URL of the connection destination DB. I won't elaborate on what the DB URL is, but for sqlite it's okay to specify sqlite: /// filename.

Also, by specifying ʻecho = True for create_engine`, the log will be output. It is good to specify it because it is useful for debugging.

As a general rule, it is necessary to create the connection destination DB in advance, but in sqlite, if the connection destination DB does not exist, it will be created automatically.

matadata = MetaData()

Create the metadata.

The schema of the table is stored in the metadata, but if you don't understand it, it's okay to recognize that "I don't know, but the DB data is included".

metadata.bind = engine

Connect the metadata with the DB.

Table definition

Here we create a menus table with a name column and a kcal column.

Edit db.py as follows.

db.py


#Import additional classes for table definition
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String

engine = create_engine('sqlite:///db.sqlite3', echo=True)
metadata = MetaData()
metadata.bind = engine

#menu table definition
menus = Table(
  'menus', metadata,
  Column('id', Integer, primary_key=True),
  Column('name', String),
  Column('kcal', Integer)
)

Specify the table name in the first argument of Table and the metadata in the second argument. After that, you can freely specify the column.

Also, set the column name in the first argument of Column and the column type in the second argument. Also, the ʻid` column is set as the primary key.

Creating a table

At this point, the table definition is complete, but the table has not been created yet.

Launch an interactive shell and create a table like this:

>>> import db
>>> db.metadata.create_all()

Evolution: Significance of metadata

At this point, the significance of metadata that could not be explained so much can finally be seen. Since metadata has both the structure of the table and ʻengine`, it is possible to create a table.

If metadata and ʻengine` are not linked at this point, you need to explicitly specify as follows.

>>> import db
>>> db.metadata.create_all(engine)

Also, it is recommended to connect metadata and ʻengine` in advance because it will be a little troublesome not only to create the table but also to operate the DB in the future.

Summary

--In order to use DB, it is necessary to connect with DB, define a table, and create a table. --Meta data retains table information.

It may have been a little difficult, but now you are ready to use the DB. Even if you don't understand the contents so far, there is no immediate problem with the following contents. However, it is something that you should understand, so please read it again after a while.

Utilization of DB

Data creation

The operation of creating data is called ** insert ** in RDBMS. Note that it is an insert, not a create. This may be easier to remember if you think of creating new data as inserting a new row into the table.

In sqlalchemy, ʻinsert` is executed as follows.

>>> import db
>>> db.menus.insert().execute(name='Curry meshi', kcal=500)

Note that after calling ʻinsert, we also call ʻexecute. No operation is performed until you call ʻexecute`.

This also applies to other operations. As a general rule, operations on the DB are not executed until ʻexecute` is called.

Data acquisition

Use select to get the data.

First, try running the code below.

>>> import db
>>> db.menus.select().execute().fetchall()
[(1, 'Curry meshi', 500)]

As with ʻinsert, no data is retrieved until you call ʻexecute. Also note that we are calling further fetchall on the result of ʻexecute`.

Note that the return value looks like a list of tuples, but it's not. You can treat it like a tuple like res [0] [0], but you can also get it by column name for res [0] ['id'].

Also, not only get all the data, but also use where to narrow down the data to get, use ʻorder_by to sort, and use limit` to limit the number of data to get. You can also.

For example, the following code allows you to "get up to 5 menus with less than 100 calories and sort them by calories".

>>> import db
>>> db.menus.select().where(db.menus.c.kcal < 100).order_by('kcal').limit(5).execute().fetchall()

This kind of refinement is the same for updating and deleting data.

Data update

Use ʻupdate. The basics are the same as ʻinsert, but please note that all data will be targeted if you do not narrow down.

The following code updates kcal to 1000 for data with ʻid` of 1.

>>> import db
>>> db.menus.update().where(db.menus.c.id == 1).execute(kcal=1000)

Delete data

Use delete. The basics are the same as ʻinsert and ʻupdate.

The code below removes all data whose kcal is greater than 1000.

>>> import db
>>> db.menus.delete().where(db.menus.c.kcal > 1000).execute()

Summary

--Operations on the DB will not be executed until ʻexecute` is called. --Select, update, delete can narrow down the data. If you do not narrow down, all data will be targeted.

Continuing from the first half, the content was a little difficult. It doesn't matter if you don't understand everything. It can also be used as a simple reference, so if you get lost, check it again.

This summary

This time, it was more difficult than last time. It doesn't matter if you don't understand it all at once. Also, even if you think you understand it, you may not know how to write it when you actually try to write the code, so in such a case, read it again.

Recommended Posts

Introduction to RDB with sqlalchemy II
Introduction to RDB with sqlalchemy Ⅰ
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 WordCloud] Let's play with scraping ♬
Introduction to Python Image Inflating Image inflating with ImageDataGenerator
[Python] Introduction to CNN with Pytorch MNIST
[Introduction to Pytorch] I played with sinGAN ♬
How to use SQLAlchemy / Connect with aiomysql
Introduction to MQTT (Introduction)
Introduction to Scrapy (1)
Introduction to Scrapy (3)
Introduction to Supervisor
Introduction to Tkinter 1: Introduction
Introduction to PyQt
Introduction to Scrapy (2)
[Linux] Introduction to Linux
Introduction to Scrapy (4)
Introduction to discord.py (2)
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 (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
[Introduction to Python] How to iterate with the range function?
[Introduction to WordCloud] It's easy to use even with Jetson-nano ♬
Introduction to Lightning pytorch
Introduction to Web Scraping
Introduction to Nonparametric Bayes
Introduction to EV3 / MicroPython
An introduction to Python distributed parallel processing with Ray
Introduction to Mathematics Starting with Python Study Memo Vol.1
Reading Note: An Introduction to Data Analysis with Python
Introduction to Python language
[Chapter 6] Introduction to scikit-learn with 100 knocks of language processing
Introduction to TensorFlow-Image Recognition
Introduction to OpenCV (python)-(2)
[Chapter 3] Introduction to Python with 100 knocks of language processing
Introduction to PyQt4 Part 1
[Introduction to Pytorch] I tried categorizing Cifar10 with VGG16 ♬
[Chapter 2] Introduction to Python with 100 knocks of language processing
Introduction to Dependency Injection
Introduction to Private Chainer
Introduction to Vector Error Correcting Models (VECM) with stats models
Use Enums with SQLAlchemy
Introduction to machine learning
[Introduction to AWS] I tried playing with voice-text conversion ♪
[Chapter 4] Introduction to Python with 100 knocks of language processing
Easy introduction to home hack with Raspberry Pi and discord.py