Pythonから各種DBへ接続する方法（PEP 249）とSQLAlchemyについて

# 1.はじめに
pythonからDBへのアクセスは、[PEP 249 -- Python Database API Specification v2.0](https://www.python.org/dev/peps/pep-0249/)という仕様が定められており、各DBがこの仕様に合わせて実装を行っています。（各DBが用意しているこの実装をDBAPIと呼ぶことにします。）

その思想は、

~~~
　接続先のDBを意識することなく、同一のコードでDBへの接続とSQLの実行、その結果の取得を実現できる
~~~
ということだと思います。では、実際どうなのか。確認していきたいと思います。

# 2.各DBごとに用意されているDBAPIについて
各DBの代表的なDBAPIは以下の一覧の通りとなります。

| No | DB | モジュール名 | 備考 |
| --- | --- | --- | --- |
| 1 | MySQL | mysql、pymysqlなど | |
| 2 | MariaDB | mariadb | mysql用のパッケージも代用可能 |
| 3 | PostgreSQL | psycopg2など | |
| 4 | Oracle | cx-Oracle | |
| 5 | Microsoft SQL Server| pymssql、PyODBCなど |  |

各DBの代表的なDBAPIについて、一つずつ見ていきましょう。

## 2-1.MySQL


■mysqlモジュールを用いた実装例（「[5.4 Querying Data Using Connector/Python](https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html)」より）

~~~python
import datetime
import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()

query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")

hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)

cursor.execute(query, (hire_start, hire_end))

for (first_name, last_name, hire_date) in cursor:
  print("{}, {} was hired on {:%d %b %Y}".format(
    last_name, first_name, hire_date))

cursor.close()
cnx.close()
~~~


## 2-2.MariaDB

■mariadbモジュールを用いた実装例（「[How to connect Python programs to MariaDB](https://mariadb.com/ja/resources/blog/how-to-connect-python-programs-to-mariadb/)」より）

~~~python
import mariadb 

conn = mariadb.connect(
    user="db_user",
    password="db_user_passwd",
    host="localhost",
    database="employees")
cur = conn.cursor() 

#retrieving information 
some_name = "Georgi" 
cur.execute("SELECT first_name,last_name FROM employees WHERE first_name=?", (some_name,)) 

for first_name, last_name in cur: 
    print(f"First name: {first_name}, Last name: {last_name}")
    
#insert information 
try: 
    cur.execute("INSERT INTO employees (first_name,last_name) VALUES (?, ?)", ("Maria","DB")) 
except mariadb.Error as e: 
    print(f"Error: {e}")

conn.commit() 
print(f"Last Inserted ID: {cur.lastrowid}")
    
conn.close()
~~~


## 2-3.PostgreSQL

■psycopg2モジュールを用いた実装例（「[Basic module usage](https://www.psycopg.org/docs/usage.html)」より）

~~~python

>>> import psycopg2

# Connect to an existing database
>>> conn = psycopg2.connect("dbname=test user=postgres")

# Open a cursor to perform database operations
>>> cur = conn.cursor()

# Execute a command: this creates a new table
>>> cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")

# Pass data to fill a query placeholders and let Psycopg perform
# the correct conversion (no more SQL injections!)
>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",
...      (100, "abc'def"))

# Query the database and obtain data as Python objects
>>> cur.execute("SELECT * FROM test;")
>>> cur.fetchone()
(1, 100, "abc'def")

# Make the changes to the database persistent
>>> conn.commit()

# Close communication with the database
>>> cur.close()
>>> conn.close()
~~~


# 2-4.Oracle

■psycopg2モジュールを用いた実装例（「[Python and Oracle Database Tutorial: Scripting for the Future](https://oracle.github.io/python-cx_Oracle/samples/tutorial/Python-and-Oracle-Database-Scripting-for-the-Future.html)」より）

~~~python
import cx_Oracle
import db_config

con = cx_Oracle.connect(db_config.user, db_config.pw, db_config.dsn)

cur = con.cursor()
cur.execute("select * from dept order by deptno")
res = cur.fetchall()
for row in res:
    print(row)

cur.close()
con.close()
~~~

## 2-5.Microsoft SQL Server

■pymssqlモジュールを用いた実装例（「[手順 3:pymssql を使用した SQL への接続を概念実証する](https://docs.microsoft.com/ja-jp/sql/connect/python/pymssql/step-3-proof-of-concept-connecting-to-sql-using-pymssql?view=sql-server-ver15)」より）

~~~python
import pymssql  
conn = pymssql.connect(server='yourserver.database.windows.net', user='yourusername@yourserver', password='yourpassword', database='AdventureWorks')  
cursor = conn.cursor()  
cursor.execute('SELECT c.CustomerID, c.CompanyName,COUNT(soh.SalesOrderID) AS OrderCount FROM SalesLT.Customer AS c LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID GROUP BY c.CustomerID, c.CompanyName ORDER BY OrderCount DESC;')  
row = cursor.fetchone()  
while row:  
    print str(row[0]) + " " + str(row[1]) + " " + str(row[2])     
    row = cursor.fetchone()  
~~~


## 2-6. 各DBのDBAPIの差異
　上述のサンプルコードをよく見ると、どのDBAPIにおいても、

　　①Connectionインスタンスを生成する
　　②Cursorインスタンスを生成
　　③生成したCursorインスタンスを使ってSQLを実行する
　　④SQLの実行結果をFetchする

という形がとられていると思います。若干の書きっぷりの違いはありますが、一つのDBAPIの書き方さえ覚えてしまえば、苦も無く別のDBを利用することも可能と思います。こうなると、直接各DBのDBAPIを使えば良さそうなのですが、DBAPIの標準ではConnection Poolの仕様が残念ながら含まれていません。各DBAPIがそれぞれ独自の実装を行ってるケースもあるため、開発するアプリケーションに組み込むことは可能ですが、使用するDBによって実装はバラバラになります。


# 3.SQLAlchemy
　ORMとして知られるSQLAlchemyですが、各種DBの各種DBAPIを取り揃えており、且つConnction Poolを実装しているDBAPIの拡張モジュールのような存在です。

ORMを使うのは正直億劫で、DB依存の書き方になってしまうとはいえ直接SQLを書きたいと私は常日頃考えてます。
そのような我儘な私に対しても、SQLAlchemyはORMの利用を押し付けてくるわけではなく、直接SQLを実行する手段を用意してくれているのは、非常にありがたいと思っています。もし、DBへの接続をDBAPIにて行おうと考えているのであるならば、ぜひSQLAlchemyの利用を検討してみてください。

なお、DBAPIではないので、実装方法が多少変わるのですが、[2-6. 各DBのDBAPIの差異]に記載した流れとほぼ同じような流れでDBへの操作を行うことができます。

■sqlalchemyモジュールを用いた実装例（「[SQL Expression Language Tutorial](https://docs.sqlalchemy.org/en/13/core/tutorial.html#using-textual-sql)」よりコード抜粋）

~~~python
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)
>>> conn = engine.connect()
>>> from sqlalchemy.sql import text
>>> s = text(
...     "SELECT users.fullname || ', ' || addresses.email_address AS title "
...         "FROM users, addresses "
...         "WHERE users.id = addresses.user_id "
...         "AND users.name BETWEEN :x AND :y "
...         "AND (addresses.email_address LIKE :e1 "
...             "OR addresses.email_address LIKE :e2)")
>>> conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall()
~~~

Connection Poolを使いたい場合は、create_engineの部分を以下のように記載します。

■「[Connection Pooling](https://docs.sqlalchemy.org/en/13/core/pooling.html)」より

~~~python
engine = create_engine('postgresql://me@localhost/mydb',pool_size=20, max_overflow=0)
~~~




# 4.参考

[PlaySQLAlchemy: SQLAlchemy入門](https://www.slideshare.net/YasushiMasuda/playsqlalchemy-sqlalchemy)
[The Novice’s Guide to the Python 3 DB-API](https://philvarner.github.io/pages/novice-python3-db-api.html)
[とりあえず使えそうな SQLAlchemy 入門（※ ORM機能は使いません）](https://www.m3tech.blog/entry/sqlalchemy-tutorial)