やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

SQLite3ドットコマンド(.import)

 DSV形式ファイル→テーブル。

成果物

.help

.import FILE TABLE       Import data from FILE into TABLE

--help

.import --help
Usage: .import FILE TABLE

 説明が雑すぎない?

公式ドキュメント

 公式ドキュメントには以下のように説明があった。

 だが、じつは入力形式も一部指定できる。その説明はない。自前でパターンを試した。

.mode + .import パターン一覧

.mode .import ファイル形式
csv CSV
tabs TSV
list DSV(.separatorでデリミタ指定)
insert
html

  • insert.readで取り込める

 実験の結果、.importはDSV形式(CSV,TSV等)のみ対応しているらしい。

 他は以下の理由で.import実験しない。

.mode .import実験しない理由
ascii 列デリミタが文字化けする
column おそらく表示専用だろう。.width次第でデータが欠ける
line おそらく表示専用だろう。ヘッダが重複しまくりウザい
quote CSV的なので無視
tcl CSV的なので無視

CLI .import

 公式ドキュメントによると、以下でCSV形式ファイルからテーブルへインポートできるらしい。

sqlite> .mode csv
sqlite> .import C:/work/somedata.csv tab1

 というわけでCLIコマンドにして試してみた。

.mode csv + .import

 まずはCSVファイル作成。

echo "id,name,class
1,Yamada,A
2,Suzuki,B
3,Tanaka,A" > users.csv

 次にCSVインポート&確認。

sqlite3 :memory: \
".mode csv" \
".import users.csv users" \
".tables" \
"select sql from sqlite_master;" \
".headers on" \
".mode column" \
"select * from users;"

 出力結果は以下。OK。

users
"CREATE TABLE users(
  ""id"" TEXT,
  ""name"" TEXT,
  ""class"" TEXT
)"
id          name        class     
----------  ----------  ----------
1           Yamada      A         
2           Suzuki      B         
3           Tanaka      A         

テーブル名が未存なら

  • CSVの1行目を列名とする
  • テーブルを新規作成する
    • 列の型はすべてTEXT

テーブル名が既存なら

  • CSVの1行目をレコードとする
  • テーブルに挿入する

 やってみる。.importを2回繰り返すことで。

sqlite3 :memory: \
".mode csv" \
".import users.csv users" \
".tables" \
"select sql from sqlite_master;" \
".headers on" \
".mode column" \
"select * from users;"
".import users.csv users" \
"select * from users;"

 出力結果のポイントは以下。OK。

id          name        class     
----------  ----------  ----------
1           Yamada      A         
2           Suzuki      B         
3           Tanaka      A         
id          name        class     
1           Yamada      A         
2           Suzuki      B         
3           Tanaka      A         
  • 2回目のインポートは1行目もレコードとして認識される
  • idTEXT型でありprimary key制約もないため、重複してもエラーにならない

問題

  • 1行目の扱いが異なる(テーブルが既存/未存で)
  • 型や制約がつけられない

型がTEXTである問題

 ソート順が期待どおりにならない。

echo "id,name,class
1,Yamada,A
222,Suzuki,B
3,Tanaka,A" > users.type.csv
sqlite3 :memory: \
".mode csv" \
".import users.type.csv users" \
".tables" \
"select sql from sqlite_master;" \
".headers on" \
".mode column" \
"select * from users order by id;"
users
"CREATE TABLE users(
  ""id"" TEXT,
  ""name"" TEXT,
  ""class"" TEXT
)"
id          name        class     
----------  ----------  ----------
1           Yamada      A         
222         Suzuki      B         
3           Tanaka      A         

 ポイントはorder by id句。idでソートしていること。その順序が1,222,3の順になっている。

 もしidが数値型なら以下を期待していたはず。もちろん普通idは数値型である。

1           Yamada      A         
3           Tanaka      A         
222         Suzuki      B         

 .importするとTEXT型になってしまうせいでソートが期待通りにならない。

解法: テーブルを予め作っておく

sqlite3 :memory: \
"create table users(id integer primary key, name text not null, class text not null)" \
".mode csv" \
".import users.type.csv users" \
".tables" \
"select sql from sqlite_master;" \
".headers on" \
".mode column" \
"select * from users order by id;"
users.type.csv:1: INSERT failed: datatype mismatch
users
"CREATE TABLE users(id integer primary key, name text not null, class text not null)"
id          name        class     
----------  ----------  ----------
1           Yamada      A         
3           Tanaka      A         
222         Suzuki      B         

 create table文を最初に追加した。その後、そのusersテーブルへ.importしている。

 以下はCSVファイルの1行目でエラーになっている。CSVの1行目はid,name,classであるが、テーブルのスキーマ側は1列目がintegerである。1行目をinsertするときidは文字列であり数値化できないため、以下エラーとなる。

users.type.csv:1: INSERT failed: datatype mismatch

 だが、1,3,222は成功している。このことから、数値化できる文字列なら問題なく取り込んでくれるようだ。

 そして最後のソート結果。期待通りに数値の昇順1,3,222でソートされている。

 つまり、最適解は以下。

  1. スキーマcreate table <TableName>(...)を実行する
  2. CSVファイルを.importする(ヘッダなし)

 惜しむらくは、create文とCSVファイルを1つのファイルにまとめることができない点。

.mode

 .modeコマンドと組合せることで様々な形式のファイルからインポートできる。

.mode --help
Error: mode should be one of: ascii column csv html insert line list quote tabs tcl
.mode 出力結果
ascii (文字化けした)
column 列揃え。.widthと併用
csv 列デリミタ,
html 行:<tr>
セル:<td>
ヘッダ項目:<th>(.headers on時のみ)
insert insert into "テーブル名"(列名,...) values(...);
line 列名 = 値で1行ずつ。レコード間には空行が1つ入る。
list デフォルト。列1の値<sep>列2の値<sep>...<sep>.separatorで設定。
quote '値1','値2',....mode csvの全シングルクォート版。
tabs TSV版。.separator "\t"と同じ。
tcl "値1" "値2"。値はダブルクォート。1レコードずつ改行。

 主に使うのは以下だろう。

  • インポート&エクスポート用
    • csv: .csv
    • tabs: .tsv
    • insert: .sql
  • 表示用
    • list
    • column
    • html

.mode tabs + .import

 TSVファイル作成。

echo -e "id\tname\tclass
1\tYamada\tA
2\tSuzuki\tB
3\tTanaka\tA" > users.tsv

 TSVファイルのインポート&確認。

sqlite3 :memory: \
".mode tabs" \
".import users.tsv users" \
".tables" \
"select sql from sqlite_master;" \
".headers on" \
".separator ' '" \
".mode column" \
"select * from users;"
users
CREATE TABLE users(
  "id" TEXT,
  "name" TEXT,
  "class" TEXT
)
id          name        class     
----------  ----------  ----------
1           Yamada      A         
2           Suzuki      B         
3           Tanaka      A         

.mode insert + .import(不可)

 .mode insertなら*.sqlファイルから.importできると思ったが、できなかった。

 TSVファイル作成。

echo -e "id\tname\tclass
1\tYamada\tA
2\tSuzuki\tB
3\tTanaka\tA" > users.tsv
  1. TSVをインポートしてテーブル作成
  2. .mode insert.sqlファイル出力
  3. テーブルのレコード全削除
  4. .mode insert.import *.sql
sqlite3 :memory: \
".mode tabs" \
".import users.tsv users" \
".mode insert" \
".output users_insert.sql" \
"select * from users;" \
"delete from users;" \
".output stdout" \
".mode insert" \
".import users_insert.sql users" \
".headers on" \
".separator ' '" \
".mode column" \
"select sql from sqlite_master;" \
"select * from users;"
users_insert.sql:1: expected 3 columns but found 1 - filling the rest with NULL
users_insert.sql:2: expected 3 columns but found 1 - filling the rest with NULL
users_insert.sql:3: expected 3 columns but found 1 - filling the rest with NULL
sql                                                             
----------------------------------------------------------------
CREATE TABLE users(
  "id" TEXT,
  "name" TEXT,
  "class" TEXT
)
id                                             name        class     
---------------------------------------------  ----------  ----------
INSERT INTO "table" VALUES('1','Yamada','A');                        
INSERT INTO "table" VALUES('2','Suzuki','B');                        
INSERT INTO "table" VALUES('3','Tanaka','A');                        

 なぜかid列にinsert文がセットされてしまった。

 insert文の出力を確認。テーブル名が"table"になっている。期待値はusers

$ cat users_insert.sql 
INSERT INTO "table" VALUES('1','Yamada','A');
INSERT INTO "table" VALUES('2','Suzuki','B');
INSERT INTO "table" VALUES('3','Tanaka','A');

 でも、.importしたときはusersテーブルが対象になっている。第二引数で指定しているから? もっとも、すべてid列にセットされてしまっているが。

 つまり、.mode insertは出力用であって、.importには使えない。では、insert文からインポートするにはどうすればいいのか?

.mode insert.importできない検証

.mode insertのテーブル名が"table"になってしまう

sqlite3 :memory: \
".mode tabs" \
".import users.tsv users" \
".mode insert" \
"select * from users;"
INSERT INTO "table" VALUES('1','Yamada','A');
INSERT INTO "table" VALUES('2','Suzuki','B');
INSERT INTO "table" VALUES('3','Tanaka','A');

.readで取り込もうとするも失敗

 *.sqlファイルの.importをやめて、.readにする。

sqlite3 :memory: \
".mode tabs" \
".import users.tsv users" \
".mode insert" \
".output users_insert.sql" \
"select * from users;" \
"delete from users;" \
".output stdout" \
".read users_insert.sql" \
".headers on" \
".separator ' '" \
".mode column" \
"select sql from sqlite_master;" \
"select * from users;"
Error: near line 1: no such table: table
Error: near line 2: no such table: table
Error: near line 3: no such table: table

 ですよねー。"table"などという名前のテーブル名など存在しませんもの。

 やはり.mode insertで出力したinsert文が使い物にならない。テーブル名がおかしい。

cat users_insert.sql 
INSERT INTO "table" VALUES('1','Yamada','A');
INSERT INTO "table" VALUES('2','Suzuki','B');
INSERT INTO "table" VALUES('3','Tanaka','A');

事前にcreate tableして.mode insertしてみる

sqlite3 :memory: \
"create table users(id integer primary key, name text not null, class text not null)" \
".mode tabs" \
".import users.tsv users" \
".mode insert" \
"select * from users;"
users.tsv:1: INSERT failed: datatype mismatch
INSERT INTO "table" VALUES(1,'Yamada','A');
INSERT INTO "table" VALUES(2,'Suzuki','B');
INSERT INTO "table" VALUES(3,'Tanaka','A');

 やはりテーブル名が"table"になってしまう。

事前にcreate table, insertして.mode insertしてみる

sqlite3 :memory: \
"create table users(id integer primary key, name text not null, class text not null)" \
"insert into users(name,class) values('AAA','A');" \
".mode insert" \
"select * from users;"
INSERT INTO "table" VALUES(1,'AAA','A');

 やはりテーブル名が"table"になってしまう。

.mode insertはテーブル名が"table"になってしまう?

 バグ? 仕様? 使い方まちがってる? 説明求む。

 ググってみた。

引数にテーブル名を渡せばOK

sqlite3 :memory: \
"create table users(id integer primary key, name text not null, class text not null)" \
"insert into users(name,class) values('AAA','A');" \
".mode insert users" \
"select * from users;"
INSERT INTO users VALUES(1,'AAA','A');

 ヘルプ……たのむから仕事してくれ。以下には書いてあった。

.mode insert.importできなかった

 .mode insertでテーブル名を指定してもダメだった。

 TSVファイル作成。

echo -e "id\tname\tclass
1\tYamada\tA
2\tSuzuki\tB
3\tTanaka\tA" > users.tsv
  1. TSVをインポートしてテーブル作成
  2. .mode insert.sqlファイル出力
  3. テーブルのレコード全削除
  4. .mode insert.import *.sql
sqlite3 :memory: \
".mode tabs" \
".import users.tsv users" \
".mode insert users" \
".output users_insert.sql" \
"select * from users;" \
"delete from users;" \
".output stdout" \
".mode insert users" \
".import users_insert.sql users" \
".headers on" \
".separator ' '" \
".mode column" \
"select sql from sqlite_master;" \
"select * from users;"
users_insert.sql:1: expected 3 columns but found 1 - filling the rest with NULL
users_insert.sql:2: expected 3 columns but found 1 - filling the rest with NULL
users_insert.sql:3: expected 3 columns but found 1 - filling the rest with NULL
sql                                                             
----------------------------------------------------------------
CREATE TABLE users(
  "id" TEXT,
  "name" TEXT,
  "class" TEXT
)
id                                           name        class     
-------------------------------------------  ----------  ----------
INSERT INTO users VALUES('1','Yamada','A');                        
INSERT INTO users VALUES('2','Suzuki','B');                        
INSERT INTO users VALUES('3','Tanaka','A');                        

 またid列にinsert文が入ってる……。

 insert文のテーブル名はちゃんとusersが入った。

cat users_insert.sql
INSERT INTO users VALUES('1','Yamada','A');
INSERT INTO users VALUES('2','Suzuki','B');
INSERT INTO users VALUES('3','Tanaka','A');

 ということは、.import.mode insertにしても.sqlファイルのinsert文から取り込むことはできないってことか。

.mode insert + .read

 *.sqlファイルの.importをやめて、.readにする。

sqlite3 :memory: \
".mode tabs" \
".import users.tsv users" \
".mode insert users" \
".output users_insert.sql" \
"select * from users;" \
"delete from users;" \
".output stdout" \
".read users_insert.sql" \
".headers on" \
".separator ' '" \
".mode column" \
"select sql from sqlite_master;" \
"select * from users;"
sql                                                             
----------------------------------------------------------------
CREATE TABLE users(
  "id" TEXT,
  "name" TEXT,
  "class" TEXT
)
id          name        class     
----------  ----------  ----------
1           Yamada      A         
2           Suzuki      B         
3           Tanaka      A         

 できた。つまり.importコマンドはinsert文が書いてあるSQLファイルを取り込めない……。

 だったらどの形式が.importできるか説明してくれない? 公式ドキュメントにはCSV形式ファイルを取り込める説明はある。でも.mode tabsを使えばTSVファイルも取り込めた。これは公式に説明なし。

 つまり説明がなくとも.mode次第では取り込める形式があるということ。なら.modeのどれが取り込めて、どれが取り込めないの? これを自分で調査するしかないとか……。

  • .mode insert + .import不可
  • .mode insertは出力専用

.mode list + .import

sqlite3 :memory: \
".mode tabs" \
".import users.tsv users" \
".mode list" \
".output users.list" \
"select * from users;" \
".output stdout" \
".import users.list users" \
".headers on" \
".mode column" \
"select sql from sqlite_master;" \
"select * from users;"
sql                                                             
----------------------------------------------------------------
CREATE TABLE users(
  "id" TEXT,
  "name" TEXT,
  "class" TEXT
)
id          name        class     
----------  ----------  ----------
1           Yamada      A         
2           Suzuki      B         
3           Tanaka      A         
1           Yamada      A         
2           Suzuki      B         
3           Tanaka      A         

 .mode list形式を.importできた。

 一応、users.listファイルを確認。

cat users.list
1|Yamada|A
2|Suzuki|B
3|Tanaka|A

 .mode list.separatorでデリミタを設定できる。変更しても成功すると思われる。

.separator COL ?ROW?     Change the column and row separators

 だが行デリミタは\nが一般的だし、列デリミタも,\tで十分なので試さない。もしLinux環境変数なら;が行デリミタなので、それを指定するとうまく処理できそう。

.mode html + .import(不可)

 HTML形式からインポートはできなかった。

sqlite3 :memory: \
".mode tabs" \
".import users.tsv users" \
".mode html" \
".output users.html" \
"select * from users;" \
".output stdout" \
".import users.html users" \
".headers on" \
".mode column" \
"select sql from sqlite_master;" \
"select * from users;"
users.html:1: expected 3 columns but found 1 - filling the rest with NULL
users.html:2: expected 3 columns but found 1 - filling the rest with NULL
users.html:3: expected 3 columns but found 1 - filling the rest with NULL
users.html:4: expected 3 columns but found 1 - filling the rest with NULL
users.html:5: expected 3 columns but found 1 - filling the rest with NULL
users.html:6: expected 3 columns but found 1 - filling the rest with NULL
users.html:7: expected 3 columns but found 1 - filling the rest with NULL
users.html:8: expected 3 columns but found 1 - filling the rest with NULL
users.html:9: expected 3 columns but found 1 - filling the rest with NULL
users.html:10: expected 3 columns but found 1 - filling the rest with NULL
users.html:11: expected 3 columns but found 1 - filling the rest with NULL
users.html:12: expected 3 columns but found 1 - filling the rest with NULL
sql                                                             
----------------------------------------------------------------
CREATE TABLE users(
  "id" TEXT,
  "name" TEXT,
  "class" TEXT
)
id          name        class     
----------  ----------  ----------
1           Yamada      A         
2           Suzuki      B         
3           Tanaka      A         
<TR><TD>1<                        
<TD>Yamada                        
<TD>A</TD>                        
</TR>                             
<TR><TD>2<                        
<TD>Suzuki                        
<TD>B</TD>                        
</TR>                             
<TR><TD>3<                        
<TD>Tanaka                        
<TD>A</TD>                        
</TR>                                   

 残念。id列にすべて代入されてしまっている……。

 一応、users.htmlファイルを確認。

cat users.html
<TR><TD>1</TD>
<TD>Yamada</TD>
<TD>A</TD>
</TR>
<TR><TD>2</TD>
<TD>Suzuki</TD>
<TD>B</TD>
</TR>
<TR><TD>3</TD>
<TD>Tanaka</TD>
<TD>A</TD>
</TR>

 これは読んでくれても良さそうなのに。

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで