Python 3 から MySQL を触る

Python 3 から MySQL を操作する方法を調べると Python 2 の情報や古い情報が多くヒットしてわかりづらかったのでまとめる。

環境は Python 3.6 を利用している。

パッケージ

mysqlclient というパッケージもあるが、MySQL 公式の mysql-connector-python-rf というパッケージがあるので今回はこちらを使う。

[2018-12-03 追記] mysql-connector-python-rf は更新が止まっており、同じく MySQL 公式の mysql-connector-python というパッケージがあるらしいので注意。詳しくはコメント欄を参照。

ふたつのパッケージのメリデメはあまりわかっていないが、mysqlclient でクエリの結果をディクショナリとして扱う方法を見つけられなかったので個人的には mysql-connector-python-rf の方が使い勝手が良さそう。

普通に pip からインストールできる。

$ pip install mysql-connector-python-rf

接続

PostgreSQL のコネクタ (psycopg2) は URL 文字列を投げるだけで接続できたが、mysql-connector-python-rf ではできない模様。仕方がないので urllib でパースする。

from urllib.parse import urlparse
import mysql.connector

url = urlparse('mysql://user:pass@localhost:3306/dbname')

conn = mysql.connector.connect(
    host = url.hostname or 'localhost',
    port = url.port or 3306,
    user = url.username or 'root',
    password = url.password or '',
    database = url.path[1:],
)

接続できているか確認したい場合は is_connected() を使う。

conn.is_connected()  #=> Bool

コネクションを張りっぱなしにしていると、いつの間にか接続が切れてしまうことがある。
これを回避するためには定期的に ping を送り、接続できていなければ再接続するようにしておく。

conn.ping(reconnect=True)

クエリ実行

標準の SQLite コネクタ などと同様に、カーソルオブジェクトを作成し execute メソッドでクエリを実行する。

cur = conn.cursor()

取得 (SELECT)

クエリを実行した後に fetchone / fetchmany / fetchall メソッドでレコードを取得できる。

cur.execute('SELECT * FROM users')

cur.fetchall()  #=> [(1, 'foo'), (2, 'bar')]

プリペアードステートメントも利用できる。

cur.execute('SELECT * FROM users WHERE id = %s', [1])

cur.statement  #=> 'SELECT * FROM users WHERE id = 1'

cur.fetchone()  #=> (1, 'foo')

更新 (INSERT / UPDATE / DELETE)

クエリ実行後に conn.commit() でコミットされる。

try:
    cur.execute('INSERT INTO users (name) VALUES (%s)', ['foo'])
    conn.commit()
except:
    conn.rollback()
    raise

結果をディクショナリとして取得

カーソルオブジェクト作成時に dictionary オプションを渡すとクエリ実行結果をディクショナリとして取得できる。

cur = conn.cursor(dictionary=True)

cur.fetchall()  #=> [{'id': 1, 'name': 'foo'}, {'id': 2, 'name': 'bar'}]

その他のカーソルオブジェクトに渡せるオプションは以下を参照。

MySQL :: MySQL Connector/Python Developer Guide :: 10.6 Subclasses cursor.MySQLCursor