Q&A
前提・実現したいこと
Pythonのpandas.DataFrameをPOSTGRESにUPSERTしたいですが調べてもわかりませんでした。SQLは初心者です。
データについて
- カラム数が600程あります。
- updateする場合は、特定カラムだけではなく、1行すべてupdate
- conflictの判定には２つのカラム（ID,登録日時）を使いたい
- POSTGRESに格納済のデータ行数は1000万行ほど、一度にUPSERTするのは10万行程
案１：psycopg2またはsqlalchemyから直接UPSERT
案２：tmpテーブルを作成してpandas.to_sqlで上げて、tmpから正式テーブルにupsert
案１、案２どちらでも（または別の手段でも）よいと考えています。高速に処理できるほうがうれしい。
発生している問題・エラーメッセージ
postgresのupsertはsetで１つ１つのカラム名を定義する必要がありそうで、カラム数が600もあると記述が大変。
案１の参考
valuesの定義を600も書かなくてはならないのか？すべてのカラム指定ができないのか？ソースは参考のページのコピーです。
python
1from sqlalchemy import create_engine, MetaData 2from sqlalchemy.dialects.postgresql import insert # on_conflict_do_update が使える 3 4 5# テーブルの取得 6engine = create_engine(db_url) 7meta = MetaData(bind=engine) 8meta.reflect() 9table = meta.tables["test_conflict"] 10 11table.select().execute().fetchall() 12# [(2, 'msg2'), (1, 'msg1_updated')] 13 14# on_conflict_do_update の挙動を設定する 15# set_ で定義しているのが、PostgreSQL での 'do update set ' 以降の部分にあたる 16insert_stmt = insert(table) 17set_ = dict(id=insert_stmt.excluded.id, message=insert_stmt.excluded.message) 18insert_stmt = insert_stmt.on_conflict_do_update(index_elements=['id'], 19 set_=set_) 20# upsert の実行 21with engine.connect() as conn: 22 values = [dict(id=1, message='msg1_updated2'), 23 dict(id=3, message='msg3'), 24 dict(id=3, message='msg3_updated')] 25 conn.execute(insert_stmt, values) 26 27table.select().execute().fetchall() 28# [(2, 'msg2'), (1, 'msg1_updated2'), (3, 'msg3_updated')] 29
案２についても調べたところ、同様にupdateする場合はカラムをすべて定義する必要があるのでは？と思っています。
簡単に記述できる方法はありますでしょうか？
補足情報（FW/ツールのバージョンなど）
postgres 9.6
python3.6（anaconda）
以上、よろしくお願いします。
