[Python3 入門 17日目]8章 データの行き先（8.3〜8.3.6.1）


#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。

```py

>>> 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は多くの支持がある。 
- 特徴
 1. DB接続のプール、SQLコマンドの実行、結果のリターン処理。
 2. SQLの文をPythonの式として表現するSQL表現言語である。
 3. 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に移植可能。

```py

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