1.はじめに
pythonからDBへのアクセスは、PEP 249 -- Python Database API Specification v2.0という仕様が定められており、各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」より）
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」より）
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」より）
>>> 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」より）
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 への接続を概念実証する」より）
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」よりコード抜粋）
>>> 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」より
engine = create_engine('postgresql://me@localhost/mydb',pool_size=20, max_overflow=0)
4.参考
PlaySQLAlchemy: SQLAlchemy入門
The Novice’s Guide to the Python 3 DB-API
とりあえず使えそうな SQLAlchemy 入門（※ ORM機能は使いません）