Last time studied the basics of DB and RDB.
This time, let's finally utilize DB from Python.
Install sqlalchemy
$ pip install sqlalchemy
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.
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.
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()
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.
--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.
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.
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.
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)
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()
--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 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