2019年12月16日月曜日

Embulk で sqlite 3 にバルクロードしようとすると失敗することがある

概要

大量のデータを sqlite3 にバルクロードするのに Embulk を使うのは不向きっぽい。

環境

  • Windows 10 64bit
  • Embulk ver.0.9.20
  • SQLite ver.3.30.1
  • embulk-output-sqlite3 (0.0.1)
  • jdbc-sqlite3 (3.28.0)

実行

少量のデータなら問題なさそう。
大量のデータをバルクロードすると、以下のエラーメッセージが出力される。 


Error: java.lang.RuntimeException: org.sqlite.SQLiteException: [SQLITE_BUSY]  The database file is locked (database is locked)

DBがロックされて、処理出来なくなったとのこと。
きちんと調べたわけではないが、以下の特性があるので 仕方がないのかとも思う。

  • Embulk はバルクロードを並列処理している。
  • SQLite は並列処理が苦手。

対応策

SQLite にデータをロードするならば コマンドラインツールの「.import」コマンドを使った方がいい。
取り込みたいファイルの形式が「.import」コマンドに対応していないのであれば、Embulkで変換してやればいい。

Posted by at
Labels: ,

2019年12月4日水曜日

SQLite 3 コマンドの実行結果の出力先を変更する「.excel」について

概要

SQLite3 のコマンドラインツールでコマンドやSQLの実行結果をCSVエディタで表示する方法。 CSVエディタはOSでCSVファイルに関連付けられたアプリケーションが起動する。

「.once -x」とほぼ同じ。

環境
  • Windows 10 64bit
  • SQLite3 ver.3.29.0

ヘルプの内容


sqlite> .help .excel
.excel         Display the output of next command in a spreadsheet

CSVエディタが起動して表示される。
そこから編集したり保存しなおしたりできる。
sqliteの文字コードがutf-8のため、Microsoft Excelでは文字化けしてしまう。 


sqlite> .excel
sqlite> select * from sample_table;

参考URL

Posted by at
Labels: , ,

2019年11月7日木曜日

SQLite 3 コマンドの実行結果の出力先を変更する「.output」について

概要

SQLite3 のコマンドラインツールでコマンドやSQLの実行結果の 出力先を切り替える方法について。
似たコマンドに 「 .once 」がある。

環境

  • Windows 10 64bit
  • SQLite3 (3.29.0) Command-Line Shell

ヘルプの内容

以下の内容しか表示されないが、「.once」 と同じように「-x」や「-e」のオプションが使える。 


sqlite> .help .output
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
     If FILE begins with '|' then open it as a pipe.

テキストファイルに出力する

以下の例ではsample_tableとsampletable2の内容をsample.txtに出力している。
4行目の引数なしで「.output」を実行することでファイル出力を止める。
(出力先をファイルからコンソールに切り替えている) 


sqlite> .output sample.txt
sqlite> select * from sample_table;
sqlite> select * from sample_table2;
sqlite> .output

テキストエディタで表示する

テキストエディタが起動して表示される。
起動するのは出力先を切り替えたタイミング。(3行目)
そこから編集したり保存しなおしたりできる。 


sqlite> .output -e
sqlite> select * from sample_table;
sqlite> .output

スプレッドシートとして表示する

CSVエディタが起動して表示される。
起動するのは出力先を切り替えたタイミング。(3行目)
そこから編集したり保存しなおしたりできる。
sqliteの文字コードがutf-8のため、Microsoft Excelでは文字化けしてしまう。


sqlite> .output -x
sqlite> select * from sample_table;
sqlite> .output

Select文以外も出力できる

例えば、ヘルプをテキストファイルに保存しておきたい場合は・・・ 


sqlite> .output help.txt
sqlite> .help
sqlite> .output

パイプを使って別のコマンドに渡す

コマンドプロンプトやbashでおなじみのパイプ「|」で 出力結果を他のコマンドに渡すことができる。
こちらも文字コードの都合上、文字化けするのことがある。 


sqlite> select * from sample_table
1|test001
2|sample002
sqlite> .output '| findstr test'
sqlite> select * from sample_table
sqlite> .output
1|test001

参考URL

Posted by at
Labels: ,

2019年10月30日水曜日

SQLite3 コマンドの実行結果の出力先を変更する「.once」について

概要

SQLite3 のコマンドラインツールでコマンドやSQLの実行結果を ファイルに出力する方法について。
似たコマンドに 「 .output 」がある。

環境

  • Windows 10 64bit
  • SQLite3 ver.3.29.0

ヘルプの内容


sqlite> .help .once
.once (-e|-x|FILE)       Output for the next SQL command only to FILE
If FILE begins with '|' then open as a pipe
Other options:
    -e    Invoke system text editor
    -x    Open in a spreadsheet

テキストファイルに出力する


sqlite> .once sample.txt
sqlite> select * from sample_table;
sqlite>

テキストエディタで表示する

テキストエディタが起動して表示される。
そこから編集したり保存しなおしたりできる。 


sqlite> .once -e
sqlite> select * from sample_table;
sqlite>

スプレッドシートとして表示する

CSVエディタが起動して表示される。
そこから編集したり保存しなおしたりできる。
「.excel」コマンドとほぼ同じ。
sqliteの文字コードがutf-8のため、Microsoft Excelでは文字化けしてしまう。


sqlite> .once -x
sqlite> select * from sample_table;
sqlite>

Select文以外も出力できる

例えば、ヘルプをテキストファイルに保存しておきたい場合は・・・ 


sqlite> .once help.txt
sqlite> .help
sqlite>

パイプを使って別のコマンドに渡す

コマンドプロンプトやbashでおなじみのパイプ「|」で 出力結果を他のコマンドに渡すことができる。
こちらも文字コードの都合上、文字化けするのことがある。 


sqlite> select * from sample_table
1|test001
2|sample002
sqlite> .once '| findstr test'
sqlite> select * from sample_table
1|test001

参考URL

Posted by at
Labels: ,

2019年10月18日金曜日

SQLite3 「.once」と「.output」について

概要

SQLite3のコマンドラインツールで、データをファイルに出力する際に使う 「.once」と「.output」の違いについて。

結論として、select文の結果をテキストに出力するだけなら「.once」だけでいい。 「.output」を使う機会はそんなに多くないかも・・・

違いについて

「.once」と「.output」の違いは以下の通り。

.once
  • 引数(出力先の指定)は必須。
  • 次に実行されるコマンドを1回だけファイルに出力する。
.output
  • 引数は任意。(引数を指定しない場合、出力先がコンソールになる)
  • 次以降に実行されるコマンド全ての出力先を変更する。

このくらいしかない。
-x や -e のオプションやパイプを使った値渡しは両方で使える。

参考URL

Posted by at
Labels: , ,

2019年10月2日水曜日

SQLite3 TSVデータを取り込む、TSVデータを出力する

概要

テキストデータであるTSVファイルをSQLite3に入力/出力させる方法。
コマンドラインツールから利用する。
自作のスクリプトでテキストを読込/出力、DBに接続してinsert/selectをやるよりはるかに簡単。
取り込む処理はインポート・バルクロード、出力する処理をエクスポートと言ったりする。

TSVのフォーマットについて

IANAによってMIMEタイプ tsv/tab-separated-values として定義されている。
http://www.iana.org/assignments/media-types/text/tab-separated-values

  • フィールド(列)はTABで区切る
  • レコード(行)は改行で区切る
  • 各行のTABの数は同じでなければならない
といったところ。

構文

TSVファイルのインポート

.mode tab
.import file_path table_name

もしくは


.mode tab
.import file_path
TSVファイルのエクスポート

.mode tab
.once file_path
select...

TSVファイルを読み込む

インポート先として指定した名前のテーブルが存在しているかどうかで挙動が異なる。

既にテーブルがある
  • TSVの一行目もデータとして登録される。
    一行目が不要なら、あらかじめ削除しておく必要がある。
  • テーブルのカラム数・データ型にTSVも合わせる必要がある。
    合っていない場合、エラーになる。
テーブルがない
  • 指定したテーブル名で新しくテーブルが作られる。
  • TSVの一行目がカラム名になり、二行目以降がデータとして挿入される。
  • カラムのデータ型は全てTEXT型になる。
環境
  • Windows 10 64bit
  • SQLite3 ver.3.29.0
実行例

sqlite> -- # 1.
sqlite> .mode tabs
sqlite> -- # 2.
sqlite> .import sample.tsv sample_table
  1. tabs(タブ区切り)モードに切り替え
  2. sample.tsv ファイルを sample_tableに挿入

TSVファイルを書き出す

  • エスケープ処理等はないので、データにTABが含まれていないかあらかじめ確認しておく。
  • 出力先に同名のファイルが存在していても、上書き確認はしてくれない。
実行例

sqlite> -- # 1.
sqlite> .mode tabs
sqlite> -- # 2.
sqlite> .once result.tsv
sqlite> -- # 3.
sqlite> select * from sample_table;
  1. tabs(タブ区切り)モードに切り替え
  2. 「.once」 コマンドで次のコマンドの結果をresult.tsvに出力
  3. 出力したい内容のコマンドを実行

参考URL

Posted by at
Labels: ,

2019年9月13日金曜日

SQLite3 CSVデータを取り込む、CSVデータを出力する(～ver 3.31.1)

概要

テキストデータであるCSVファイルをSQLite3に入力/出力させる方法。
コマンドラインツールから利用する。
自作のスクリプトでテキストを読込/出力したりDBに接続してinsert/selectをやるよりはるかに簡単。
取り込む処理をインポート/バルクロード、出力する処理をエクスポートと言ったりする。

Update!

SQLiteのver 3.32.0 からは「.import」コマンドにオプションが追加され、CSVファイルが取り込みやすくなった。 (「.mode」コマンドでCSVモードに変更しなくてもいい)

CSVのフォーマットについて

CSVはRFCでフォーマットが定義されている。
RFCにはダブルクォートの取り扱いや改行コードなどについて記載されているので、一度は目を通しておくべき。
アプリケーションによって、対応していなかったり拡張されていたりするのでややこしい。

環境
  • Windows 10 64bit
  • SQLite3 ver.3.29.0

CSVファイルを取り込む

CSVファイルをインポートするに当たっては以下のことに注意する。

  • CSVファイルは上記RFCに従って記述しておくのがベスト
  • インポート先として指定した名前のテーブルが存在しているかどうかで挙動が異なる。
既にテーブルがある
  • CSVの一行目はデータとして登録される。
    一行目が不要ならあらかじめ削除しておくこと。
  • テーブルのカラム数とデータ型にCSVを合わせる必要がある。
    合っていない場合、エラーになる。
テーブルがない
  • 指定したテーブル名で新しくテーブルが作られる。
  • CSVの一行目がカラム名になり、二行目以降がデータとして挿入される。
  • カラムのデータ型は全てTEXT型になる。
実行例
  1. CSVモードに切り替え
  2. sample.csv ファイルを sample_tableに挿入

sqlite> -- # 1.
sqlite> .mode csv
sqlite> -- # 2.
sqlite> .import sample.csv sample_table

CSVファイルを書き出す

手順は以下の実行例を参照のこと。 SQLiteコマンドラインツールのCSVをエクスポート機能には、以下の特徴がある。

  • RFCに従ってエスケープ処理をやってくれる。
  • 出力先に同名のファイルが存在していても、上書き確認はしてくれない。
  • 「.mode csv」 を利用し「.separator ,」は利用しないこと。
    「.separator ,」ではカンマやダブルクォーテーションがエスケープ処理されない。
実行例
  1. CSVモードに切り替え
  2. 次に打ち込むコマンドの結果をresult.csvに出力
  3. 出力したいコマンドを実行する

sqlite> -- # 1. 
sqlite> .mode CSV
sqlite> -- # 2. 
sqlite> .once result.csv
sqlite> -- # 3.
sqlite> select * from sample_table;

参考URL

Posted by at
Labels: , ,

2019年9月4日水曜日

サクラエディタでログレベルごとにログファイルを色分け表示

概要

ログファイルをサクラエディタで見た時、 ERRORやINFOといったログレベルに応じて行の色分け表示をする設定。

雑な設定なのでうまく表示できないことがあるかも。
私の場合、エディタでログファイルを見るのは開発環境くらいで、 本番環境は専用のツールを使うので、この程度の機能でも十分。

環境

  • Windows 10 (64bit)
  • サクラエディタ Ver 2.3.2.0

手順

file_copy サクラエディタ設定ファイル
  1. 設定
  2. タイプ別設定一覧
  3. インポート(I)
  1. 設定ファイルを選択して[OK]
  2. [新規追加]と[そのままインポート」を選択して[OK]
  3. [↑(U)]で優先度を上げる

説明

以下の単語が含まれる行を正規表現で検知し、文字の色を変更します。
単語はLog4JやLog4netで使われているログレベルです。

FATAL
ERROR
WARN
INFO
DEBUG
TRACE
Posted by at
Labels:

2019年8月28日水曜日

サクラエディタでCSV, TSVファイルを開く

概要

CSV, TSVファイルをサクラエディタで見やすく表示させるための設定。

CSV対応

CSV用の設定を作成する。

  1. 設定
  2. タイプ別設定一覧
  3. 追加(A)
  4. 設定変更
「スクリーン」タブ
設定の名前(N) CSV
ファイル拡張子(X) csv
折り返し方法(<) 折り返さない
表示モード
(デフォルトが「通常」となっているコンボボックス) 		CSV
「カラー」タブ
カーソル位置縦線 チェックをつける
偶数行の背景色 チェックをつける
半角空白 チェックをつける

TSV対応

TSV用の設定を作成する。
上記 CSV用の設定を流用します。

  1. 設定
  2. タイプ別設定一覧
  3. CSVの設定を選択して「複製(C)」
  4. 設定変更
設定の名前 TSV
ファイル拡張子 tsv
表示モード
(デフォルトが「通常」となっているコンボボックス) 		TSV

あとは必要に応じて拡張子をサクラエディタに関連付ければいい。

矩形モードを利用する

Altキー + 矢印キー、もしくは　Altキー + マウスでドラッグ することで テキストを矩形選択することが出来る。 これでカラム単位で編集が可能になるが、カンマやタブを削除しないよう注意すること。

操作してみるとわかるが、サクラエディタのCSV,TSVモードは「見やすくなる」程度の機能。
ダブルクォーテーションの取り扱いやエスケープ文字など非対応な要素がいくつかある。

CSV, TSVを編集するなら

純粋なCSVエディタとしては「cassava editor」が個人的には使いやすい。
余計な表計算式などを処理せず、純粋にテキストデータを編集できる。

次点でLibreOfficeのcalc。

Microsoft Office Excel は色々余計な事をしてくれるのでCSVエディタとしては使いにくい。

参考URL

Posted by at
Labels: , , ,

2019年8月19日月曜日

SQLite コマンドラインツール導入手順 (Windows 10)

概要

SQLite をコマンドプロンプト または Powershell で利用するための準備

環境

  • Windows 10 1809 (64bit)
  • SQLite 3.29.0

手順

公式サイト
https://www.sqlite.org/index.html
Latest Release から 最新版をダウンロード
  1. Latest Release download
  2. sqlite-tools-win32-x86-3290000.zip
ダウンロードしたファイルを適当なフォルダに展開
展開したフォルダにパスを通す
  1. スタートメニュー
  2. Windows システムツール
  3. コントロール パネル
  4. システム
  5. システムの詳細設定
  6. 環境変数
  7. 「Path」を選択し「編集」
  8. 新規
  9. フォルダのフルパスを登録する
コマンドラインで sqlite3 -version としてバージョンが表示されればOK
  1. スタートメニュー
  2. Windows システムツール
  3. コマンドプロンプト

> sqlite3 -version
3.29.0 2019-07-10 17:32:03 fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6
Posted by at
Labels: ,

2019年8月1日木曜日

SQLite3 SelectしたデータでUpdateする(～ver 3.29.0)

概要

select したデータを使って update するためのSQL。 select と update を別々に実行するのではなく、同時に実行できる。

2020.10.16 追記

バージョン 3.33.0 で「update～from」構文がサポートされた。 より簡単にselectしたデータでupdateすることが可能になっている。

詳しくは以下のURLより。

SQLite3 Selectした結果でUpdateする (update from)
UPDATE FROM (英語) 公式のUPDATE～FROMに関するドキュメント

以下はver 3.33.0 未満で作業したい場合のコマンド例

環境

  • Windows 10 64bit
  • SQLite3 (3.24.0) Command-Line Shell

insert ～ select ～ on conflict

selectした結果を使ってupsertする手順のうち、updateのみを行う。
SQLite3 でレコードがあれば更新、なければ新規挿入する (on conflict)

以下の点、注意。

  • 文法の都合上「where true」が必要
  • 主キーなどが重複した場合、select の結果はexcludedという一時テーブルに格納される。
    update する際はexcludedのデータを利用する。
  • 主キーが重複しないレコードは新規挿入になってしまうため、 updateだけを行いたい場合はselect文で調整する。 


inert into
    table_01
select
    *
from
    table_02
where true
on conflict(column01)
do update
    set
        column_02 = excluded.colum_02
        , column_03 = excluded.column_03
;

update ～ select

サブクエリでselect したデータを使って update する。
ポイントは

  • サブクエリのWhere句で二つのテーブルのキーを一致させる
  • メインクエリのWhere句で対象のレコードを指定する


update
    table_01
set
    column_02 = (
        select
            table_02.column05
        from
            table_02
        where
            table_02.column04 = table_01.column_01
    )
where
    table_01.column_01 = XXXX
;

参考URL

Posted by at
Labels: , , , ,

2019年7月31日水曜日

fciv(File Checksum Integrity Verifier) は使わない方がいい

環境

  • windows 10 1809 (64bit)
  • fciv 2.05 (互換モードは使用しない)

File Checksum Integrity Verifier ユーティリティの概要と入手方法
https://support.microsoft.com/ja-jp/help/841290/availability-and-description-of-the-file-checksum-integrity-verifier-u

概要

fcivはWindowsでファイルのハッシュ値を計算するためのコマンド。

環境のせいか、動作に怪しいところがある。
Windows XP時代のプログラムのなので、仕方がない気もする。

怪しいのは 「-exc」 オプション。
ヘルプを見ると以下のように書いてある。 


-exc file: list of directories that should not be computed.

exc はおそらく exclude の略で、ハッシュ値を計算しないファイルを指定するオプション。
これが効かない、というか2バイト文字を含むファイルを弾くだけになってしまう。

サンプル

「Document」 フォルダの中に2つのファイルがあるとする。


>dir Document

2019/07/26  20:19    <DIR>          .
2019/07/26  20:19    <DIR>          ..
2019/07/26  19:33                 6 test.txt
2019/07/26  19:34                 5 テスト.txt

まず 何も指定せずにfcivを実行。
2ファイルのハッシュ値が出力されることが確認できる。 


>fciv Document
//
// File Checksum Integrity Verifier version 2.05.
//
27c01ca43abd2e05ca08cf95d1a549cd document\test.txt
0cdaa4e38f4475534f739edd5bee4b2c document\テスト.txt

次に 「-exc」で テスト.txt 指定して実行すると、 テスト.txtが除外された結果が出力される。
これは上手くいっているように見える。 


>fciv Document -exc document\テスト.txt
//
// File Checksum Integrity Verifier version 2.05.
//
27c01ca43abd2e05ca08cf95d1a549cd document\test.txt
(※ここでファイル名のみだとエラーになるので、相対パスを指定する。) 

>fciv Document -exc テスト.txt
//
// File Checksum Integrity Verifier version 2.05.
//

Error opening Exception file テスト.txt
  Error msg  : 指定されたファイルが見つかりません。
  Error code : 2

27c01ca43abd2e05ca08cf95d1a549cd document\test.txt
0cdaa4e38f4475534f739edd5bee4b2c document\テスト.txt

次に test.txtを指定してみる。
すると結果は上記と同じになってしまい、「-exc」が機能していないように見える。 


>fciv Document -exc document\test.txt
//
// File Checksum Integrity Verifier version 2.05.
//
27c01ca43abd2e05ca08cf95d1a549cd document\test.txt

上記のような状況なので、Windows 10 で fciv は使わない方がいいと思われる。
ハッシュ値を計算する代替手段としては Powershell の GetHashCode() 等がある。

外部参考サイト

Posted by at
Labels:

2019年7月25日木曜日

SQLite3 Selectしたデータを他のテーブルに挿入(insert ～ select, replace ～ select)

概要

selectしたデータを使ってinsert , replaceするためのSQL。 selectとinsertを別々に実行するのではなく1文で済ませられる。 タイトルには「他のテーブルに」と入れたけど、同じテーブルでもOK。 (そんな機会はほぼないはず・・・)

以下のような機会に使う。

  • CSVファイルを一時テーブルにインポートした後、 本番テーブルに取り込む
  • マテリアライズドビューのようなものを作る
  • テーブル間でデータをコピーする

insert ～ select

selectした結果をinsertする。
selectするカラム・型は挿入するテーブルのカラム・型と揃えること。

例. 「table_02」のデータを「table_01」に全てコピーする

insert into
    table_01
select
    *
from
    table_02
;
例. カラムや条件を指定してselectした結果をinsertする

insert into
    table_01 (
        column_01
        , column_02
        , column_03
    )
select
    column_05
    , column_06
    , column_07
from
    table_02
where
    column_05 = XXXX
;

ユニークキーが重複している場合はエラーになるので、 必要があればあらかじめチェックしておくこと。

重複していない行のみをinsertしたい場合は以下参照のこと。
SQLite 3 INSERT時にキーが重複していたら挿入しないようにする

replace ～ select

selectした結果でreplaceする。
ユニークキーが重複している場合は置換される。

例.「table02」のデータを「table01」にマージする

replace into
    table_01
select
    *
from
    table_02
;
例. カラムや条件を指定してselectした結果でreplaceする

replace into
    table_01 (
        column_01
        , column_02
        , column_03
    )
select
    column_05
    , column_06
    , column_07
from
    table_02
where
    column_05 = XXXX
;

replace構文については以下を参照のこと。
SQLite3 でレコードがあれば置換、なければ新規挿入する (replace)

selectしたデータでupdateをしたい場合は以下を参照のこと。
SQLite3 Select した結果でUpdateする(update from)

外部参考サイト

Posted by at
Labels: , , ,

2019年7月2日火曜日

SQLite3でUpsertを実行する

概要

SQLite 3 でキーか重複するレコードがあれば更新、なければ新規挿入する方法。

具体的には テーブルにレコードを新規挿入しようとして Primary key や Unique が重複するレコードが既に存在していた場合、 新規挿入をやめて update に切り替えるための構文。

以下のような処理分岐をしてくれる。

重複するレコードがある場合 既存のレコードを「更新(update)」する、もしくは何もしない(nothing)
重複するレコードがない場合 そのまま 「新規挿入(insert)」

似たような処理にreplaceがあるが、 replaceはレコード(行)を丸ごと入れ替える(delete/insert)のに対し、 upsertはカラムを指定してレコードを更新する。

公式 によると PostgreSQL の構文を利用しているとのこと。
Microsoft SQLServer や Oracle の「merge(マージ)」構文に近い使い方だが
mergeは重複の判定にUniqueカラム以外のものを指定できたりする。

実行例

「upsert」というコマンドがあるわけではなく、 on conflict文を使い キーが重複した場合の処理を指定することができる。

on conflict文は sqlite のバージョン3.24.0 から利用可能。

環境
  • Windows 10
  • sqlite3 (3.24.0) Command-Line Shell
サンプルテーブル(product)
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 120
3 pumpkin 50 (Comment...)

重複していたら特定のカラムを更新する

以下は「id」カラムが重複していたら新規挿入をやめて、 別の値で「quantity」カラムを更新する例。 


insert into 
    product (
        id
        , name
        , quantity
        , remark
    )
values (
        2
        , 'potato'
        , 150
        , ''
    )
on conflict(id)
do update
    set
        quantity = '200'
;
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 200
3 pumpkin 50 (Comment...)

excluded テーブルを利用する

挿入しようとして、ユニークキーの重複エラーになった行は 「excluded」という一時テーブルに登録される。 update の際にこのexcludedを利用できる。

以下は「id」カラムが重複していたら 新規挿入をやめて、 挿入しようとした値で「quantity」カラムを更新する例 


insert into
    product (
        id
        , name     
        , quantity
        , remark
    )
values (
        2
        , 'potato'
        , 150
        , ''
)
on conflict(id)
do update
    set
        quantity = excluded.quantity
;
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 150
3 pumpkin 50 (Comment...)

重複していなければ、新規登録される

以下は「id」カラムが重複していないので、 そのまま新規挿入される例。 


insert into
    product (
        id
        , name
        , quantity
        , remark
    )
values (
        4
        , 'potato'
        , 150
        , ''
    )
on conflict(id)
do update
    set
        quantity = '200'
;
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 120
3 pumpkin 50 (Comment...)
4 potato 150

重複していた場合は何もしない、ということもできる

on conflictの後にdo nothingを記述すれば 重複していた場合は何もしない、という処理に出来る。 


insert into
    product (
        id
        , name
        , quantity
        , remark
    )
values (
        2
        , 'potato'
        , 150
        , ''
    )
on conflict(id)
do nothing
;
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 120
3 pumpkin 50 (Comment...)
行を丸ごと入れ替えるだけであれば replace を使う。

参考URL

Posted by at
Labels: , , ,

2019年6月14日金曜日

SQLite3 でレコードがあれば置換、なければ新規挿入する (replace)

概要

replace (insert or replace) 構文を使う方法のメモ。

replace を使うと Primary key や Unique で 重複するレコードを挿入しようとした場合、 以下のような処理分岐をしてくれる。

重複するレコードがある場合 既存のレコードを「削除(delete)」 してから 「新規挿入(insert)」
重複するレコードがない場合 そのまま 「新規挿入(insert)」

注意

replaceで重複したキーがあった場合の処理は「削除(delete)」「新規挿入(insert)」であり、 「更新(update)」ではない。 カラムを指定しなければデフォルト値が入るし、NOT NULL制約にもひっかかる。

カラムを指定して「更新(update)」したい場合は replaceではなくupsertを使用する。
SQLite3 で upsert する

環境

  • Windows 10
  • SQLite3 (3.24.0) Command-Line Shell

replace 構文の例

サンプルテーブル(product)
id
primary key
autoincrement 		name
not null 		price
default 0 		category
1 tomato 100
2 potato 120
3 pumpkin 50 (Comment...)

replace(insert or replace) の実行例

以下はidが重複するデータと重複しないデータを挿入するSQL。 「insert or replace」の部分は「replace」だけでもOK。 


insert or replace into product (
    id
    , name
    , quantity
    , remark
)
values (
    2
    , 'potato'
    , 150
    , ''
),(
    4
    , 'carrot'
    , 100      
    , 'Prefecture: XX'
);
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 150
3 pumpkin 50 (Comment...)
4 carrot 100 (Prefecture : XX)

カラムを指定しないとデフォルト値になる

delete > insert であり、updateではないことを留意する。 


replace into product (
    id
    , name
)
values (
    2
    , 'potato'
);
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 0
3 pumpkin 50 (Comment...)

not null 制約にも引っかかる


replace into product (
    id
    , quantity
)
values (
    2
    , 200
);

結果 


Error: NOT NULL constraint failed: product.name
カラムを指定してUPDATEする場合はUPSERTを利用する。
SQLite 3 で upsert する

参考・関連 URL

Posted by at
Labels: , , ,
登録: 投稿 (Atom)