SQLite で躓いたこと（import csv）

普段 DB に触れることがないから全然進まない。試行錯誤したこと備忘録。
一人で作業をしていると、何をやっているのか／やってきたのかだんだんわからなくなってくる。

サマリ

• とある CSV 形式のデータを作成したテーブルにインポートしたかったが、当初想定した import コマンドで取り込む方法では取り込めなかった。
• ID の自動採番や DEFAULT 値の設定が効かなさそう。（試した限りは無理そうだった）
• 代替策として、CSV インポートではなく INSERT 文を作成・実行した。
• INSERT 文は、ID のカラムと DEFAULT 値を設定したいカラムは除外した。
• 当初は CSV 取り込めばいいじゃん ♪ というノリだったが、はまってしまった。結果論、初歩的なことを学んだのでよかったが。

やりたかったこと

• CSV 形式のデータを作成済みのテーブルにインポートする。
• 作成済みのテーブルは、CSV データのカラムに加えて、先頭にID（int / PK）、末尾に最終更新日時の列を追加している。

確認・実施したこと

※結果論、あまり本質的な点ではなかった。

• CSV データのヘッダを除く（作成済みのテーブルにインポートする場合、先頭行が見出しではなくデータとみなされるため）
• CSV データの EOF を正しい位置にする（データの最終行の末尾に EOF があること）
• CSV データのカラム数と作成したテーブルのカラム数をそろえる（CSV を加工）
• CSV データの各値をダブルクォーテーションで囲む（シングルクォーテーションだとそれがついた値で格納されるため NG）

作成したテーブル（サンプル）

-- SQLite
CREATE TABLE test2(
user_id integer PRIMARY KEY , 
user_name text,
user_role text DEFAULT '0',
updated_at timestamp DEFAULT (datetime(CURRENT_TIMESTAMP, 'localtime'))
);

CSV データ（test.csv）

"1","a","",""
"2","b","",""
,"","",""
"","","",""

① import コマンドで CSV をインポートした場合 → NG

import コマンド

> sqlite3 panco.db
sqlite> .mode csv
sqlite> .import test.csv test2

実行結果

1行目、2行目のみが INSERT された。しかし、DEFAULT 値が空文字。CSVの通りなので当然ではある。

3行目、4行目は user_id の型がテーブル定義と CSV とで異なるため INSERT 失敗。（たぶん）

② SQL で INSERT した場合（user_name のみ設定）→ 期待通り

INSERT 文

-- SQLite
INSERT INTO test2 (user_name)
VALUES ('a'),
('b');

実行結果

全カラムに期待通りのレコードが入った。

• user_id は INTEGER かつ PK のため自動採番された
• user_name は SQL の通り
• user_role, updated_at は DEFAULT 値が付与された