【プログラミング】SQLite3：データが重複したら更新か何もしない、新規のデータは追加する動作 UPSERT

SQLite3

前回、データベース管理システムSQLite3のPRIMARY KEYとUNIQUEを勉強してみました。

あわせて読みたい

【プログラミング】SQLite3：PRIMARY KEYとUNIQUE SQLite3 前回、データベース管理システムSQLite3のデータの更新と削除、テーブル名の変更とテーブルの削除を勉強してみました。 今回は次回、重複したデータの取り扱い…

今回はこのPRIMARY KEYとUNIQUEを使って、追加しようとしたデータがデータベース内に既にある場合は更新や何もしない、ない場合はそのまま追加するという動作を試してみたいと思います。

このような動作は「UPdate」と「inSERT」を合わせて「UPSERT」と呼ばれるようです。

SQLite3でこのUPSERTの動作を実装するには「REPLACE INTO」というコマンドと「ON CONFLICT」という二つの方法があるようなので、それぞれ試してみましょう。

ということでまずは今回使用するデータベースの作成を行います。

ターミナルを立ち上げ、こちらのコマンドを実行し、テーブルの作成、データの追加を行います。

sqlite3 test6.db
sqlite> .databases
sqlite> CREATE TABLE table1(id INTEGER PRIMARY KEY, name TEXT UNIQUE, age INTEGER);
sqlite> INSERT INTO table1(id, name, age) values(1, 'Sato', 10);

今回はidをPRIMARY KEY、nameをUNIQUEにしてあります。

それでは始めていきましょう。

REPLACE INTO

まずは「REPLACE INTO」から試してみましょう。

「REPLACE INTO」では「REPLACE INTO テーブル名(列名1, 列名2…) values(値1, 値2)…;」というように使用します。

それではそれぞれPRIMARY KEYで重複するデータがない場合とある場合、またUNIQUEで重複するデータがない場合とある場合で試してみましょう。

重複するデータがない場合

PRIMARY KEYでもUNIQUEでも重複しないように idを2、nameをSuzuki、ageを15としてみます。

sqlite> REPLACE INTO table1(id, name, age) values(2, 'Suzuki', 15);
sqlite> SELECT * FROM table1;

実行結果
1|Sato|10
2|Suzuki|15

データが追加されました。

PRIMARY KEYで重複するデータがある場合

PRIMARY KEYだけで重複するように idを1、nameをTakahashi、ageを20としてみます。

sqlite> REPLACE INTO table1(id, name, age) values(1, 'Takahashi', 20);
sqlite> SELECT * FROM table1;

実行結果
1|Takahashi|20
2|Suzuki|15

idが1のSato 10が書き換えられて、Takahashi 20になりました。

UNIQUEで重複するデータがある場合

UNIQUEだけで重複するようにidを3、nameをSuzuki、ageを25としてみます。

sqlite> REPLACE INTO table1(id, name, age) values(3, 'Suzuki', 25);
sqlite> SELECT * FROM table1;

実行結果
1|Takahashi|20
3|Suzuki|25

Suzukiのデータが書き換えられ、idが3、ageが25になりました。

ON CONFLICT

次にもう一つのコマンドである「ON CONFLICT」を試してみましょう。

「ON CONFLICT」の場合は、「INSERT INTO」と合わせて、「INSERT INTO テーブル名(列名1, 列名2…) values(値1, 値2…) ON CONFLICT(PRIMARY KEYかUNIQUEの列名) DO 動作;」となります。

また動作には更新する場合は「UPDATE SET 列名1=値1, 列名2=値2…;」、何もしない場合は「NOTHING」と記述します。

重複するデータがない場合

PRIMARY KEYでもUNIQUEでも重複しないように idを4、nameをTanaka、ageを30としてみます。

また「ON CONFLICT」の「PRIMARY KEYかUNIQUEの列名」はとりあえずidにしておきます。

sqlite> INSERT INTO table1(id, name, age) values(4, 'Tanaka', 30) ON CONFLICT(id) DO UPDATE SET name='Tanaka', age=30;
sqlite> SELECT * FROM table1;      

実行結果                                             
1|Takahashi|20
3|Suzuki|25
4|Tanaka|30

Tanakaのデータが追加されました。

PRIMARY KEYで重複するデータがあった場合でデータを更新する場合

PRIMARY KEYだけで重複するように idを1、nameをIto、ageを40としてみます。

sqlite> INSERT INTO table1(id, name, age) values(1, 'Ito', 40) ON CONFLICT(id) DO UPDATE SET name='Ito', age=40;
sqlite> SELECT * FROM table1;        

実行結果                                           
1|Ito|40
3|Suzuki|25
4|Tanaka|30

idが1のTakahashi 20がIto 40に書き換えられました。

UNIQUEで重複するデータがあった場合で更新する場合

UNIQUEだけで重複するようにidを5、nameをSuzuki、ageを45としてみます。

ここでは「ON CONFLICT」の「PRIMARY KEYかUNIQUEの列名」をnameにします。

sqlite> INSERT INTO table1(id, name, age) values(5, 'Suzuki', 45) ON CONFLICT(name) DO UPDATE SET id=5, age=45;
sqlite> SELECT * FROM table1;                                                   
1|Ito|40
4|Tanaka|30
5|Suzuki|45

Suzukiのデータが書き換えられ、idが5、ageが45になりました。

PRIMARY KEYで重複するデータがあった場合で何もしない場合

PRIMARY KEYだけで重複するように idを1、nameをWatanabe、ageを50としてみます。

また「ON CONFLICT(id) DO NOTHING;」とします。

sqlite> INSERT INTO table1(id, name, age) values(1, 'Watanabe', 50) ON CONFLICT(id) DO NOTHING;
sqlite> SELECT * FROM table1;                                                   

実行結果
1|Ito|40
4|Tanaka|30
5|Suzuki|45

確かに何も変化は起きませんでした。

UNIQUEで重複するデータがあった場合で何もしない場合

UNIQUEだけで重複するようにidを6、nameをIto、ageを55としてみます。

また「ON CONFLICT(name) DO NOTHING;」とします。

sqlite> INSERT INTO table1(id, name, age) values(6, 'Ito', 55) ON CONFLICT(name) DO NOTHING;
sqlite> SELECT * FROM table1;                                                   

実行結果
1|Ito|40
4|Tanaka|30
5|Suzuki|45

こちらも追加も書き換えもありませんでした。

ON CONFLICTのキーは複数設定できるのか？

「ON CONFLICT」を試してきて気になったのは、ON CONFLICT()の括弧に入れるキーは複数設定できるのかということです。

とりあえずやってみました。

sqlite> INSERT INTO table1(id, name, age) values(1, 'Ito', 55) ON CONFLICT(id, name) DO NOTHING;

実行結果
Error: in prepare, ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint (1)

「ON CONFLICT」で指定されたキュー（語句）が「PRIMARY KEY」や「UNIQUE」のどれにも当てはまりませんでしたといいうエラーが出ました。

よく考えてみると複数の条件の設定は無理なのではないでしょうか。

上記の例だとidもnameも重複している場合は「DO NOTHING」となると考えられますが、例えばidだけ重複している場合は、データを追加しようとします。

しかしながらidはPRIMARY KEYで重複が許されないので、そこで矛盾が生じます。

もしかしたら複数の条件を設定する別の方法があるかもしれませんが、今回は「REPLACE INTO」や「ON CONFLICT」をとりあえず使えるようになることが目的なので、ここまでにしておきましょう。

次回からはSQLite3をPythonで使っていくための勉強と練習をしていきます。

あわせて読みたい

【Python基礎】sqlite3：操作の基本 SQLite3 前回、データベース管理システムSQLite3でデータが重複したら更新か何もしない、新規のデータは追加する動作を勉強してみました。 前回まででデータベースやテ…

ではでは今回はこんな感じで。