[PYTHON] 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.