【Altplus アドベントカレンダー21日目】PythonのSQLAlchemyでupsertで実装されたよ☆

Pocket

さて、PythonのMySQLのデファクトスタンダードであるSQLAlchemyの（ベータ版だけど）で、Upsertが実装されたよ！試してみよう！

下準備

ここらへんを参考に。

あと、MySQLの構築はDockerなりなんなりで。

よし、インストール

とりあえず pip で SQLAlchemyをインストール。β版なのでバージョン指定する必要があり。

pip install SQLAlchemy==1.2.0b3
pip install PyMySQL

いいね、かんたんだね。

データベースを作成

次のようにDBを作成した。

mysql> CREATE DATABASE upsert_test;
mysql> GRANT ALL PRIVILEGES ON upsert_test.* TO  data_user@'%' IDENTIFIED BY 'password';
mysql> USE upsert_test;
mysql> CREATE TABLE data (title TEXT, image VARCHAR(512), url VARCHAR(128) UNIQUE);

urlをユニークにしておく。

さて、実装だ！

さて、実装して行きましょう。とはいえ、ほとんどソースで理解可能かと思われる。
先に説明しておくと、urlが同じデータが入っていた場合に、upsertが掛かる仕組み。

つまり１つめのデータと3つめのデータのurlが「https://example.com/samishii.html」だから、「一人のクリスマスの過ごし方」が「あれ、なんだか目から水が・・・」に、「https://example.com/samishii.jpg」が「https://example.com/watereye.jpg」に変わるはず。

では、以下ソース。

# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, MetaData, Table, Column
from sqlalchemy import VARCHAR, Text
from sqlalchemy.dialects.mysql import insert



DB_NAME = "upsert_test"
DB_HOST = "127.0.0.1"
DB_USER = "data_user"
DB_PASS = "password"
DB_TABLE = "data"

host = 'mysql+pymysql://{0}:{1}@{2}/{3}?charset=utf8'.format(DB_USER, DB_PASS, DB_HOST, DB_NAME)
engine = create_engine(host, echo=True)
metadata = MetaData()
metadata.bind = engine

# upsert_testテーブルの定義
data_table = Table(
  DB_TABLE, metadata,
  Column('title', Text),
  Column('image', VARCHAR),
  Column('url', VARCHAR, unique=True),
)

insert_data = insert(data_table).values([

    {'title': "一人のクリスマスの過ごし方",
     'image':"https://example.com/samishii.jpg",
     'url':"https://example.com/samishii.html"},

    {'title': "コンビニで買うクリスマスケーキを一人で食べる",
     'image': "https://example.com/cake.jpg",
     'url': "https://example.com/cake.html"},

    {'title': "あれ、なんだか目から水が・・・",
     'image':"https://example.com/watereye.jpg",
     'url':"https://example.com/samishii.html"},
])

# クエリを定義
query = insert_data.on_duplicate_key_update(
    title=insert_data.inserted.title,
    image=insert_data.inserted.image,
)

# 接続
connect = engine.connect()

# 実行
connect.execute(query)

実行結果

mysql> SELECT * FROM upsert_test;
+--------------------------------------------------------------------+---------------------------------+----------------------------------+
| title                                                              | image                           | url                              |
+--------------------------------------------------------------------+---------------------------------+----------------------------------+
| あれ、なんだか目から水が・・・                                     | https://example.com/watereye.jpg | https://example.com/samishii.html |
| コンビニで買うクリスマスケーキを一人で食べる                       | https://example.com/cake.jpg     | https://example.com/cake.html     |
+--------------------------------------------------------------------+---------------------------------+----------------------------------+
2 rows in set (0.00 sec)

おお、upsertされてる。