8.3 リレーショナルデータベース

詳細は前の記事を。

「RDBMSとSQLについて」
https://qiita.com/Taka20200105/items/c58ac4e610954c6d263b

8.3.1 SQL

SQLは二つにのカテゴリに分類される。
- DDL(データ定義言語):テーブルデータベースの作成、削除、制約、許可。
- DML(データ操作言語):データの挿入、選択、更新、削除などの処理

8.3.2 DB-API

APIとはサービスにアクセスするために呼び出す関数を集めたもの。APIを開設するとは、一つの窓を作って外のソフトウェアと連携させることと理解。鎖国中の出島みたいなもの。
DB-APIはRDBMSにアクセスするためのPythonの標準のAPI。
DB-APIの主要関数まとめ

関数	意味
connect()	DBへの接続を開設する。引数指定可能。
cursor()	クエリーを管理するカーソルオブジェクトを作成
execute()とexecutemany()	DBに対して一つまたは複数の複数のSQLコマンドを送る。
fechone()、fetchmany()、fetch all()	DBに対して一つまたは複数の複数のSQLコマンドを送る。

8.3.3 SQLite

SQLiteは通常のファイルにDBを格納する。
非常に移植性が高いRDBMS。


>>> import sqlite3
# enterprise.dbという名前のDBを作る。
>>> conn=sqlite3.connect("enterprise.db")
>>> curs=conn.cursor()
# zooというテーブルの作成。
# critterという可変長文字列のプライマリーキー列
# countという数字の列
# damagesという浮動小数点列
>>> curs.execute("""CREATE TABLE zoo (critter VARCHAR(20)PRIMARY KEY,count INT,damages FLOAT)""")
<sqlite3.Cursor object at 0x10b215f80>

# execute以下の文字列がDMLコマンド
# INSERT INTO zoo VALUES(〜)で行の追加。
>>> curs.execute("""INSERT INTO zoo VALUES("duck",5,0.0)""")
<sqlite3.Cursor object at 0x10b23d030>
>>> curs.execute("""INSERT INTO zoo VALUES("bear",2,1000.0)""")
<sqlite3.Cursor object at 0x10b23d030>

# 以下のような追加方法でも追加可能。
# 3個の疑問符は挿入予定を示す。
>>> ins="""INSERT INTO zoo (critter,count,damages) VALUES(?,?,?)"""
>>> curs.execute(ins, ("weasel",1,2000.0))
<sqlite3.Cursor object at 0x10b23d030>

# "SELECT * FROM テーブル名"で全ての行と列の選択
>>> curs.execute("SELECT * FROM zoo")
<sqlite3.Cursor object at 0x10b23d030>
# 結果の取得
>>> rows=curs.fetchall()
>>> print(rows)
[('duck', 5, 0.0), ('bear', 2, 1000.0), ('weasel', 1, 2000.0)]

# count順にソートする。
>>> curs.execute("SELECT * FROM zoo ORDER BY count")
<sqlite3.Cursor object at 0x10b23d030>
>>> curs.fetchall()
[('weasel', 1, 2000.0), ('bear', 2, 1000.0), ('duck', 5, 0.0)]

# count順でかつ降順で。
>>> curs.execute("SELECT * FROM zoo ORDER BY count DESC")
<sqlite3.Cursor object at 0x10b23d030>
>>> curs.fetchall()
[('duck', 5, 0.0), ('bear', 2, 1000.0), ('weasel', 1, 2000.0)]

# 最もdamagesが最大のものの選択。
>>> curs.execute("""SELECT * FROM zoo WHERE damages=(SELECT MAX(damages) FROM zoo)""")<sqlite3.Cursor object at 0x10b23d030>
>>> curs.fetchall()
[('weasel', 1, 2000.0)]

# 接続とカーソルを開いたら使い終わったら閉じなければならない。
>>> curs.close
<built-in method close of sqlite3.Cursor object at 0x10b23d030>
>>> conn.close
<built-in method close of sqlite3.Connection object at 0x10b1d4ab0>

8.3.4 MySQL

MySQLはSQLiteとは異なり、本物は本物のサーバー。

8.3.5 PostgreSQL

PostgreSQLはMySQLよりも高度な本格的なオープンソースのRDBMSである。

8.3.6 SQLAIchemyライブラリ

SQLは全てのRDBMSで同じというわけじゃない。
DB-APIは共通APIレベルまでしか対応していい。→個々のDBの違いを埋めようとするライブラリの中で、クロスデータベースPythonライブラリであるSQLAIchemyは多くの支持がある。
特徴

DB接続のプール、SQLコマンドの実行、結果のリターン処理。
SQLの文をPythonの式として表現するSQL表現言語である。
ORMレイヤ。このレイヤはSQL表現言語を使ってアプリケーションコードとRDBMS構造を結びつける。

dialect + driver ://user:password@host:port /dbnameという接続文字列形式だ。
dialect:DBのタイプ
driver:そのDBに対して使いたいと思っているドライバ。
userとpassword:DB認証文字列
hostとport:DBサーバーの位置
dbname:最初に接続するサーバー上のDB。

8.3.6.1 エンジンレイヤ

必要なドライバはSQLAIchemyが接続文字列から判断してくれる。
接続文字列を変更するだけで他のタイプのDBに移植可能。


>>> import sqlalchemy as sa
# DBを開設し、記憶領域を作る。
# ResultProxyというオブジェクトが返される。
# dbnameを省略するとDBのファイルをメモリ内に格納する。
# SQliteの文字列ではhost, port,user,password不要。
>>> conn=sa.create_engine("sqlite://")
# 3つの列を持つzooテーブルを作る。
>>> conn.execute("""CREATE TABLE zoo (critter VARCHAR(20) PRIMARY KEY,count INT,damages FLOAT)""")
<sqlalchemy.engine.result.ResultProxy object at 0x108ee3210>
# f3個のデータを挿入する。
>>> ins="""INSERT INTO zoo (critter,count,damages) VALUES(?,?,?)"""
>>> conn.execute(ins,"duck",10,0.0)
<sqlalchemy.engine.result.ResultProxy object at 0x108ee3290>
>>> conn.execute(ins,"bear",2,1000.0)
<sqlalchemy.engine.result.ResultProxy object at 0x108ee3350>
>>> conn.execute(ins,"weasel",1,2000.0)
<sqlalchemy.engine.result.ResultProxy object at 0x108ee32d0>
# zooの全ての情報をDBに要求する。
>>> rows=conn.execute("SELECT*FROM zoo")
>>> print(rows)
<sqlalchemy.engine.result.ResultProxy object at 0x108ee3110>
>>> for row in rows:
...     print(row)
... 
('duck', 10, 0.0)
('bear', 2, 1000.0)
('weasel', 1, 2000.0)

感想

最初勉強した時はMySQLやPostgteSQLはSQLの種類かと思っていたが、今回の復習ではそれが違うことに気づいた。

上記はRDBMSであり、言語ではない。
そして多くのDBにはそれぞれの方言が実装されており、その方言の違いを埋めようと出ててきたのがSQLAIchemyライブラリ。
DB毎にドライバをインストールせず、接続文字列で判断してくれるとは賢いですな。

参考文献

「Bill Lubanovic著『入門 Python3』(オライリージャパン発行)」