「【総まとめ】UPSERTとは？DBごとの違いは？」では、主要なDBごとの書き方を簡単に説明しました。
今回は、SQLiteでのUpsertに焦点を絞って、さらに細かく解説したいと思います。
参考として、DataFrameからUpsertを生成するPython関数も紹介しますので、必要な方は是非コピぺしてご利用下さい。
SQLiteは Insert into ～ on conflict ～ で Upsertを行う
SQLiteでUpsertを行う場合は、Insert into ～ on confrict ～を使います。
簡単に説明すると、Insert Into ～にインサート文を記述し、 on conflict ～ に重複キーを指定、do update set に重複があった場合の update 文を記述します。
Insert Into の後に value ではなく、次の内容を記述すると、テーブル間でUpsertが行えます。
select * from テーブル名 where 条件式
where 条件式 は必須であり、これが無いと「near "do":syntax error」という例外が発生します。特に絞り込む必要が無い場合は、 where true と記述しておきしょう。
-- table_A に1行 Upsert する場合
insert into table_A
values(1,'2023/01/01 09:00:00',10000,15)
on conflict (shop_no,order_date,product_id)
do update set count=excluded.count
-- table_B からtable_A に Upsert する場合
insert into table_A
select * from table_B where true
on conflict (shop_no,order_date,product_id)
do update set count=excluded.countdo update set count = excluded.count と記述していますが、これで values の 値が参照できます。（詳細は後述）
検証データ
本記事のUpsert文を実際に試してみたい場合は、以下のSQLを実行して下さい。尚、SQLiteには日付型が無いので、order_date は text型にしています。
--サンプルテーブルの作成とテストデータ登録
create table oders
(
shop_no integer not null,
order_date text not null,
product_id integer not null,
count integer not null,
constraint oders_p_key primary key (shop_no,order_date,product_id)
);
alter table oders add constraint oders_uniquekeys unique(shop_no,order_date,product_id)
insert into oders values(1,'2023/01/01 09:00:00',10000,5);
insert into oders values(1,'2023/01/01 09:10:00',10000,15);
insert into oders values(1,'2023/01/01 09:20:00',10000,25);
--下記はテーブル間のUpsert確認用(odersを丸ごとコピー）
create table old_oders as select * from oders1行をUpsertする
下記は、サンプルテーブルに (1,'2023/01/01 09:00:00',10000,15) のデータをInsertし、レコードが既に存在していれば 数量(count)を 15にUpdateするサンプルとなります。
insert into oders values(1,'2023/01/01 09:00:00',10000,15)
on conflict (shop_no,order_date,product_id)
do update set count=15EXCLUDEDを使って、update set を簡略化する
Insert 文で指定した values の値は、EXCLUDEDという一時的なテーブル（のようなもの）に格納されており、これを使って参照が可能です（PostgreSQLと同じ仕様）。
今までの説明では update 文に直接、値を指定していましたが、 EXCLUDED.列名 で更新が行えます。
insert into oders values(1,'2023/01/01 09:00:00',10000,15)
on conflict (shop_no,order_date,product_id)
do update set count=excluded.count複数行をまとめてUpsertする
SQLiteでは、1回のInsert文で複数行（複数レコード）をまとめて登録する マルチインサート文が使えますが、マルチインサート文に続けて ON CONFLICT UPDATE SET ～ と記述すれば、複数行まとめてUpsertできます。
insert into oders values
(1,'2023/01/01 09:01:00',10000,15),
(1,'2023/01/01 09:02:00',10100,12),
(1,'2023/01/01 09:03:00',10200,18)
on conflict (shop_no,order_date,product_id)
do update set count=excluded.countポイントは、do update set ～ の部分です。今回の例では count=excluded.count と記述していますが、EXCLUDED.列名 を使う必要があります。
count = 5 など、直接値を指定するとエラーになりますのでご注意ください。また、1回のUpsertの中で、キーが重複するデータが存在する場合もエラーとなります。
テーブル間でUpsertする
２つのテーブルAとBがあって、Bの内容をAにUpsertしたい場合は、select * from を使います。
insert into テーブル名A select * from テーブル名B where 条件式
下記は old_oders から oders へ Upsertをする例です。全件をupsertしているため、where true を記述しています。
insert into oders select * from old_oders where true
on conflict (shop_no,order_date,product_id)
do update set count=excluded.countUpsertでInsertだけ行う（Updateしない）
なければインサート、あれば何もしたくない場合は、do に続けて NOTING を記述します。
-- oders に向けてインサートを行うが、既に存在する場合は何もしない
insert into oders values(1,'2023/01/01 09:00:00',10000,15)
on conflict (shop_no,order_date,product_id)
do nothing
-- old_odersから oders に向けてインサートを行うが、既に存在するレコードには何もしない
insert into oders
select * from old_oders where true
on conflict (shop_no,order_date,product_id)
do nothingPythonのサンプル
それでは、参考としてDataFrameの内容をもとにUpsert文を生成するPythonのサンプルプログラムをご紹介します。
使い方のサンプル
create_upsert にDataFrame、テーブル名、ユニークキーのリストを指定するだけです。
尚、下記サンプルで生成したUpsert文は、最初に紹介したサンプルテーブルに対して実行が可能です。
datas = [
[1,'2023/01/01 09:00:00',10000,55],
[1,'2023/01/01 09:10:00',10000,65],
[1,'2023/01/01 09:20:00',10000,75]
]
df = pd.DataFrame(datas,columns=['shop_no','order_date','product_id','count'])
sqls = create_upsert(df,'oders',['shop_no','order_date','product_id'])
for sql in sqls:
print(sql)関数のソースコード
以下は、関数本体のソースコードです。数値以外のデータ（文字列、日付）はシングルクォートで括る必要がありますが、それを dtype が 'object' か否かで判断しています。
また、insert into ～ values ～ の values に渡す列の値は、最終的に 'nan'⇒NULL に置換しています。仮に列の値に 'nan' という文字列が含まれると、NULLに変換されてしまうのでご注意下さい。
import pandas as pd
def create_upsert(df,table,key_columns):
'''
DataFrameの内容からUpser文を作成する
Parameters
----------
df : DataFrame
データを格納したDataFrame
table : str
Upsert対象のテーブル名
key_columns : list of str
ユニークキーのカラム
例:['shop_no','order_date',・・・]"
Returns
----------
res:str
生成されたUpsertインサート文
'''
# df から全てのカラム名を取り出す
columns = ",".join([i for i in df.columns])
# シングルクォート格納用のリストを作り、カラムの型がobjectの場合のみシングルクォートをセット
quote = ["'" if str(dtype) == 'object' else '' for dtype in df.dtypes]
sqls = []
# 全ての行を取り出す
for row in df.itertuples():
# 0カラム名（行インデックス）を飛ばし、必要に応じて各カラム値をシングルクォートで括る。
items = [quote[i] + str(row[i+1]) + quote[i] for i in range(len(df.columns))]
# Insert句の作成
values = ','.join(items).replace("'nan'", "NULL")
insert = f'insert into {table}({columns}) values({values}) '
# conflict句の作成
keys = ','.join(key_columns)
conflict = f'on conflict ({keys}) '
# update句の作成
set_list = [f'{x}=excluded.{x}' for x in df.columns if x not in key_columns]
update = f"do update set " + ','.join(set_list)
# insert、conflict、updateを結合
sqls.append(insert + conflict + update)
return sqlsまとめ
今回はSQLiteのUpsertに焦点を絞って詳しく解説しました。
SQLiteでUpsertを行うには、Insert into ～ にインサート文を、on conflict ～ に重複を判断するためのユニークキー（又はキー名）を、do ～ に update 文を記述します。
もし、存在した場合に何もしなくない場合は、do nothing と記述します。
また、excluded に これからインサートしようとする値が格納されているので、 excluded.列名で参照できることも紹介しました。
SQLiteでUpsertが必要になった時は、是非この記事を参考にしてください。
コメント