【詳しく解説】SQLiteにおけるUpsertの書き方(サンプル付き)

当ページのリンクには広告が含まれています。

【総まとめ】UPSERTとは?DBごとの違いは?」では、主要なDBごとの書き方を簡単に説明しました。

今回は、SQLiteでのUpsertに焦点を絞って、さらに細かく解説したいと思います。

参考として、DataFrameからUpsertを生成するPython関数も紹介しますので、必要な方は是非コピぺしてご利用下さい。

目次

SQLiteは Insert into ~ on conflict ~ で Upsertを行う

SQLiteでUpsertを行う場合は、Insert into ~ on confrict ~を使います。

UPSERT文の構造を表す画像

簡単に説明すると、Insert Into ~にインサート文を記述し、 on conflict ~ に重複キーを指定、do update set に重複があった場合の update 文を記述します。

Insert Into の後に value ではなく、次の内容を記述すると、テーブル間でUpsertが行えます。

select * from テーブル名 where 条件式

where 条件式 は必須であり、これが無いと「near “do”:syntax error」という例外が発生します。特に絞り込む必要が無い場合は、 where true と記述しておきしょう。 

do update set count = excluded.count と記述していますが、これで values の 値が参照できます。(詳細は後述)

検証データ

本記事のUpsert文を実際に試してみたい場合は、以下のSQLを実行して下さい。尚、SQLiteには日付型が無いので、order_date は text型にしています。

1行をUpsertする

1レコードのUpsertイメージ図

下記は、サンプルテーブルに (1,’2023/01/01 09:00:00′,10000,15) のデータをInsertし、レコードが既に存在していれば 数量(count)を 15にUpdateするサンプルとなります。

EXCLUDEDを使って、update set を簡略化する

Insert 文で指定した values の値は、EXCLUDEDという一時的なテーブル(のようなもの)に格納されており、これを使って参照が可能です(PostgreSQLと同じ仕様)。

EXCLUDEDのイメージ図

今までの説明では update 文に直接、値を指定していましたが、 EXCLUDED.列名 で更新が行えます。

複数行をまとめてUpsertする

SQLiteでは、1回のInsert文で複数行(複数レコード)をまとめて登録する マルチインサート文が使えますが、マルチインサート文に続けて ON CONFLICT UPDATE SET ~ と記述すれば、複数行まとめてUpsertできます。

ポイントは、do update set ~ の部分です。今回の例では count=excluded.count と記述していますが、EXCLUDED.列名 を使う必要があります。

count = 5 など、直接値を指定するとエラーになりますのでご注意ください。また、1回のUpsertの中で、キーが重複するデータが存在する場合もエラーとなります。

1回のInsert文でキーが重複している例の画像

テーブル間でUpsertする

2つのテーブル同士のUpsertイメージ図

2つのテーブルAとBがあって、Bの内容をAにUpsertしたい場合は、select * from を使います。

insert into テーブル名A select * from テーブル名B where 条件式

下記は old_oders から oders へ Upsertをする例です。全件をupsertしているため、where true を記述しています。

UpsertでInsertだけ行う(Updateしない)

Upsertでなければインサート、あればなのもしない動作のイメージ画像

なければインサート、あれば何もしたくない場合は、do に続けて NOTING を記述します。

Pythonのサンプル

それでは、参考としてDataFrameの内容をもとにUpsert文を生成するPythonのサンプルプログラムをご紹介します。

使い方のサンプル

create_upsert にDataFrame、テーブル名、ユニークキーのリストを指定するだけです。

尚、下記サンプルで生成したUpsert文は、最初に紹介したサンプルテーブルに対して実行が可能です。

関数のソースコード

以下は、関数本体のソースコードです。数値以外のデータ(文字列、日付)はシングルクォートで括る必要がありますが、それを dtype が ‘object’ か否かで判断しています。

また、insert into ~ values ~ の values に渡す列の値は、最終的に ‘nan’⇒NULL に置換しています。仮に列の値に ‘nan’ という文字列が含まれると、NULLに変換されてしまうのでご注意下さい。

まとめ

今回はSQLiteのUpsertに焦点を絞って詳しく解説しました。

SQLiteでUpsertを行うには、Insert into ~ にインサート文を、on conflict ~ に重複を判断するためのユニークキー(又はキー名)を、do ~ に update 文を記述します。

もし、存在した場合に何もしなくない場合は、do nothing と記述します。

また、excluded に これからインサートしようとする値が格納されているので、 excluded.列名で参照できることも紹介しました。

SQLiteでUpsertが必要になった時は、是非この記事を参考にしてください。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次