やってみる

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

📌 ？
トップ > SQLite3 > SQLite3ドットコマンド（.import）

SQLite3ドットコマンド（.import）

SQLite3

　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行目もレコードとして認識される
  • idもTEXT型であり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

前回まで