Pandas で SQLite の Upsert(車輪の再開発)

PandasでUpsertが待ちきれず、自作してみた

PandasのSql Upsertを試してみた を見て、Upsert なる言い方を知った（SQLiteの"INSERT OR REPLACE"は大昔使っていたけど）

これこれ、と思ったけど、まだリリースされていないいし(21年4月現在)、なんとPandas の to_sqlだと、primary keyやunique keyが作れない。
（SQLTable/SQLiteTableにはkeysで指定できるけど、to_sqlにない。Why?）

とりあえず、下記を参考にして、SQLite専用だけどUpsertを作ってみた。
Python Pandas to_sql, how to create a table with a primary key?

def upsert(frame, name: str, unique_index_label, con):
    pandas_sql = pd.io.sql.pandasSQL_builder(con)
    
    if isinstance(frame, pd.Series):
        frame = frame.to_frame()
    elif not isinstance(frame, pd.DataFrame):
        raise NotImplementedError(
            "'frame' argument should be either a Series or a DataFrame"
        )
    
    # table = pd.io.sql.SQLiteTable(name, pandas_sql, frame=frame, index=False, if_exists='append')
    table = pd.io.sql.SQLiteTable(name, pandas_sql, frame=frame, index=False, if_exists='append', keys=unique_index_label)
    table.create()
    # pandas_sql.execute('CREATE UNIQUE INDEX IF NOT EXISTS "{0}_{1}" ON "{0}" ("{1}");'.format(table.name, unique_index_label))
    
    def _execute_insert(self, conn, keys, data_iter):
        wld = "?"  # wildcard char
        escape = pd.io.sql._get_valid_sqlite_name

        bracketed_names = [escape(str(column)) for column in keys]
        col_names = ",".join(bracketed_names)
        wildcards = ",".join([wld] * len(bracketed_names))
        insert_statement = (
            f"INSERT OR REPLACE INTO {escape(self.name)} ({col_names}) VALUES ({wildcards})"
        )
        data_list = list(data_iter)
        conn.executemany(insert_statement, data_list)
    
    table.insert(method=_execute_insert)

こんな感じで使用

import pandas as pd
import sqlite3
from contextlib import closing

large_category_list = []
middle_category_list = []
for large_category in large_categories:
    large_category_list.append(dict(id=large_category['id'], name=large_category['name']))
    for middle_category in large_category['middle_categories']:
        middle_category_list.append(dict(large_category_id=large_category['id'], id=middle_category['id'], name=middle_category['name']))

large_category_df = pd.DataFrame(large_category_list)
middle_category_df = pd.DataFrame(middle_category_list)

with closing(sqlite3.connect(args.sqlite)) as con:
    upsert(large_category_df, 'large_categories', 'id', con)
    upsert(middle_category_df, 'middle_categories', 'id', con)

ちなみに、sqlite3には、下記を使ってExcelからもアクセス可能。
ExcelのTableの機能と合わせると結構便利かも(試行錯誤中)。
Webから取得したデータはSQLiteで管理(更新されたらupsertで対応)。
分析やデータの加工はExcelのテーブルとかで対応。

SQLite ODBC Driver
32/64bitに注意。Excelは32bitだと思い込んでいてえらい時間くった。
インストールしたけど使えないのが、(ODBC初めてで)使い方ミスったと思って、インストールミスにすぐ気が付かなかった。。。