#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』(オライリージャパン発行)」