DSV形式ファイル→テーブル。
成果物
.help
.import FILE TABLE Import data from FILE into TABLE
--help
.import --help
Usage: .import FILE TABLE
説明が雑すぎない?
公式ドキュメント
公式ドキュメントには以下のように説明があった。
- CSV Import
- 出力形式を
.mode
で指定可
だが、じつは入力形式も一部指定できる。その説明はない。自前でパターンを試した。
.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
でソートされている。
つまり、最適解は以下。
惜しむらくは、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
TSV
をインポートしてテーブル作成.mode insert
で.sql
ファイル出力- テーブルのレコード全削除
.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
TSV
をインポートしてテーブル作成.mode insert
で.sql
ファイル出力- テーブルのレコード全削除
.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>
これは読んでくれても良さそうなのに。
対象環境
- Raspbierry pi 3 Model B+
- Raspbian stretch 9.0 2018-11-13
- bash 4.4.12(1)-release
- SQLite 3.29.0
- MeCab 0.996ユーザ辞書
$ uname -a Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux
前回まで
- SQLite3学習をはじめよう
- SQLite3学習 SQLiteについて
- SQLite3学習 SQLiteの適切な用途
- SQLite3学習 SQLiteの特徴
- SQLite3学習 SQLiteのクセ
- SQLite3学習 データ型とアフィニティ
- SQLite3学習 演算子の一覧
- SQLite3学習 よくある質問
- SQLite3学習 SQLiteダウンロード&コンパイル
- SQLite3学習 Tclで操作する
- SQLite3学習 ビルドオプション動作確認(SQLITE_ALLOW_URI_AUTHORITY)
- SQLite3学習 面白そうなコンパイルオプション
- SQLite3学習 SQLiteの拡張について
- SQLite3学習 JSON拡張
- SQLite3学習 JSON拡張(json_extract)
- SQLite3学習 JSON拡張(json_each)
- SQLite3学習 JSON拡張(json_tree オブジェクト→行)
- SQLite3学習 JSON拡張(json_tree オブジェクトツリー→行)
- SQLite3学習 JSON拡張(json_tree オブジェクト配列→行)
- SQLite3学習 JSON拡張(json_group_array 行→配列)
- SQLite3学習 JSON拡張(json_group_object 行→オブジェクト)
- SQLite3学習 JSON拡張(json_array_length)
- SQLite3学習 JSON拡張(json_type)
- SQLite3学習 JSON拡張(json_valid)
- SQLite3学習 JSON拡張(json_quote)
- SQLite3学習 JSON拡張(json_array)
- SQLite3学習 JSON拡張(json_object)
- SQLite3学習 JSON拡張(json_patch)
- SQLite3学習 JSON拡張(json_insert)
- SQLite3学習 JSON拡張(json_replace)
- SQLite3学習 JSON拡張(json_set)
- SQLite3学習 JSON拡張(json_remove)
- SQLite3学習 全文検索(FTS5)
- SQLite3学習 全文検索FTSを日本語で使う方法を調べてみた
- 形態素解析MeCabをインストールする
- SQLite3学習 全文検索FTS5のMeCab用トークナイザを実装する
- SQLite3学習 FTS5+MeCabでクエリ構文
- SQLite3学習 FTS5のテーブル作成と初期化
- SQLite3学習 FTS5の補助関数
- SQLite3学習 FTS5のfts5vocab仮想テーブル
- SQLite3学習 再帰クエリ(WITH RECURSIVE)
- SQLite3学習 R-Treeモジュール
- SQLite3学習 ファイル入出力(SQL集計)
- SQLite3学習 拡張関数(generate_series)
- SQLite3謎 主キーの型をintにするとinsertで値を省略したらNULLになってしまう
- SQLite3学習 入出力関数(fsdir, readfile, writefile, edit)
- SQLite3ビルド コンパイルオプションを付与する方法(CFLAGS等))
- SQLite3 コンパイルオプション確認方法(pragma compile_options)
- SQLite3ビルド ICUを有効にする(SQLITE_ENABLE_ICU)
- SQLite3拡張 ICUを動的ロードする
- SQLite3拡張 ICUでcollateする
- SQLite3拡張 ICUで全文検索する(FTS4)
- SQLite3拡張 SQL関数一覧(pragma function_list)
- SQLite3拡張 仮想テーブルモジュール一覧(pragma module_list)
- SQLite3拡張 プラグマ一覧(pragma pragma_list)
- SQLite3謎 values()構文
- SQLite3学習 インタフェース概要
- SQLite3学習 CLI起動引数(-A)Archive
- SQLite3ビルド SQLITE_HAVE_ZLIBコンパイルオプション付与するも確認できず
- SQLite3学習 CLI(-readonly)
- MeCabユーザ辞書の作り方(Wikipediaの題名を名詞とした)
- SQLite3学習 CLI(-zip)
- SQLite3ドットコマンド(.archive)
- SQLite3ドットコマンド(.auth)断念
- SQLite3ドットコマンド(.backup .restore)
- SQLite3ドットコマンド(.read)
- SQLite3ドットコマンド(.dump)
- SQLite3ドットコマンド(.bail)謎
- SQLite3ドットコマンド(.binary)
- SQLite3ドットコマンド(.cd)
- SQLite3ドットコマンド(.changes)
- SQLite3ドットコマンド(.testcase .check)
- SQLite3ドットコマンド(.clone)
- SQLite3ドットコマンド(.databases)
- SQLite3ドットコマンド(.dbconfig)
- SQLite3ドットコマンド(.dbinfo)
- SQLite3ドットコマンド(.echo)
- SQLite3ドットコマンド(.eqp)
- SQLite3ドットコマンド(.excel)
- SQLite3ドットコマンド(.exit)
- SQLite3ドットコマンド(.expert)
- SQLite3ドットコマンド(.filectrl)
- SQLite3ドットコマンド(.fullschema)
- SQLite3ドットコマンド(.headers)
- SQLite3ドットコマンド(.help)