SQLAlchemy で MySQL の upsert を試す

概要

Python のデファクトスタンダード O/R マッパーである SQLAlchemy。最新のバージョン 1.2 から MySQL の upsert 機能である INSERT...ON DUPLICATE KEY UPDATE構文が使えるようになった。使えるようになるまでにいろいろはまったのでメモ。

環境

Python 3 を想定。

執筆時点での SQLAlchemy 最新版は 1.2.0b3 であった（[全バージョンはこちら]
(https://pypi.python.org/pypi/SQLAlchemy/))。ベータ版のせいか、バージョン指定しないと入らないので、

$ pip install SQLAlchemy==1.2.0b3

としてインストール。

$ pip install PyMySQL

も合わせて入れておく。

サンプルコード

こちらからコードのひな形をお借りする。そして、MySQL に対応させるために、こちらの記事を参考にさせていただいた。

あらかじめ MySQL の test_db データベース上で

create table menus(id int primary key, name varchar(100), kcal int);

と menus テーブルを作っておく。

insert into menus values(1, 'apple', 100);

として1件だけデータをいれておく。

公式ドキュメントを参考に次のようなコードを実行すると、

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
from sqlalchemy.dialects.mysql import insert

url = 'mysql+pymysql://root:@localhost/test_db?charset=utf8'
engine = create_engine(url, echo=True)
metadata = MetaData()
metadata.bind = engine

# menuテーブルの定義
menus = Table(
  'menus', metadata,
  Column('id', Integer, primary_key=True),
  Column('name', String),
  Column('kcal', Integer)
)

insert_stmt = insert(menus).values([
    {'id': 1, 'name': 'apple2', 'kcal': 200},
    {'id': 2, 'name': 'orange', 'kcal': 300}]
)
on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
    name=insert_stmt.inserted.name,
    kcal=insert_stmt.inserted.kcal
)

conn = engine.connect()
conn.execute(on_duplicate_key_stmt)

結果は以下の通り。

mysql> select * from menus;
+----+--------+------+
| id | name   | kcal |
+----+--------+------+
|  1 | apple2 |  200 |
|  2 | orange |  300 |
+----+--------+------+
2 rows in set (0.00 sec)

既存の id = 1 のレコードは更新され、一方で name == 'orange' な新しいレコードが挿入された。めでたしめでたし。