SQLite3でのCSVファイルのインポート

はじめに

SQLiteにCSVファイルをインポートする方法についてはあちこちで書かれていますが、ヘッダ行の扱いで少し迷ったのでメモとして残します。

使用したSQLite3のバージョン: 3.9.2 (on Mac)

SQLiteのCSVインポートの仕様

公式に書いてある通りなのですが、SQLite3をコマンドラインで起動後、

sqlite3

sqlite> .mode csv
sqlite> .import csv_file_path table_name

でインポートできます。
シェルスクリプト内などで直接実行する場合の書き方は

bash

$ sqlite3 -separator , db_file_path ".import csv_file_path table_name"

のようになります。-separatorオプションで区切り文字を変えることができます。

ただし、インポート先のテーブルが既に存在しているかどうかで挙動が違います。

• テーブルが存在しない場合

  • CSVファイルの先頭行がカラム名の定義に使われる。
  • データ型は全てTEXTになる。

• テーブルが存在する場合

  • CSVファイルの先頭行もデータとして扱われる。
  • 先頭行を無視するオプションは存在しないので、先頭行があるCSVファイルの場合は事前に先頭行を削除しておく必要がある。
  • すでにデータが入っている場合は追記される。

おまけ

データベースを直接扱うことが少なくてすぐ忘れるので、作業中に使ったコマンドのメモや、気になったことを記載しておきます。

シェルスクリプトでのSQL実行

単発のSQLならこれでOK。

bash

$ sqlite3 db_file_path 'SELECT * FROM users'

SQLファイルの読み込み

まとまった量のSQL文を実行する場合は別にSQLファイルを作りましょう。

bash

$ sqlite3 db_file_path < sql_file_path

なお、データベースファイルがない状態でもテーブルを作成すると自動的にファイルが作られます。
明示的に空のデータベースファイルを作りたい時は単に

bash

$ touch db_file_path

で空のファイルを作ればOKです。

テーブルとインデックスを作成するSQLファイルの例

カジュアルに使うなら型の指定などは不要と思います。NULL撲滅委員会に怒られそうなテーブル定義ですね。
primary keyには自動的にインデックスが張られるので改めてインデックスを作成する必要はありません。

create-table.sql

-- ユーザーテーブルの作成
DROP TABLE IF EXISTS users;
CREATE TABLE users (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	department_id INTEGER DEFAULT NULL,
	name TEXT DEFAULT NULL,
	mail TEXT DEFAULT NULL,
	created_at TEXT NOT NULL DEFAULT '0000-00-00 00:00:00',
	updated_at TEXT NOT NULL DEFAULT '0000-00-00 00:00:00',
	deleted INTEGER NOT NULL DEFAULT 0
);
-- インデックスの作成
CREATE INDEX IF NOT EXISTS idx_department_id_on_users ON users (department_id);

なお、インポートの際にはCSVで未入力の箇所には空白文字列が入るのでDEFAULT制約で設定した値にはなりません。また、コンマの数が足りない場合は残りがNULLで埋められますが、その時にNOT NULL制約がかかっているとDEFAULT制約を設定していてもエラーになるようです。

primary key の後付けについて

インポートでテーブルを作成した場合はprimary keyを後付けしたくなりますが、SQLite3ではSQLで

SQL

-- **** not work ****
ALTER TABLE table_name ADD PRIMARY KEY(id);

のようにしてprimary keyを後付けすることはできないようです。
なので、あらかじめprimary keyを定義したテーブルを作成してインポートする必要があります。

ただし、DB Browser for SQLiteを使えばModify Tableで簡単に後付けできます。

また、

SQL

CREATE UNIQUE INDEX index_name ON table_name(column_name)

でユニークインデックスを作成するとprimary keyを設定したのとほぼ同等になります。
参考: stack overflow: SQLite add Primary Key

###CSVファイルを変更せずに先頭行をスキップしてインポート
一時ファイルを作成するので、データが大きい時は注意が必要です。

import.sh

#!/bin/sh
db_file=$1
csv_file=$2
table_name=$3
temp_file=$(mktemp)
sed -e '1d' $csv_file > $temp_file # 先頭行を削除
sqlite3 -separator , $db_file ".import ${temp_file} ${table_name}"
rm $temp_file

以下のように使用します。

bash

$ import.sh db_file_path csv_file_path table_name

ワンライナーで書くとこうでしょうか。

bash

$ temp_file=$(mktemp); sed -e '1d' csv_file_path > $temp_file; sqlite3 -separator , db_file_path ".import ${temp_file} users"; rm $temp_file

ディレクトリ中のCSVファイルを全てインポート

ファイル名がそのままテーブル名となります（拡張子は除く）。
ヘッダ行があることが前提なので先頭行は自動的に削除されます。
ヘッダ行のないCSVファイルをインポートする場合は修正が必要です。

import-dir.sh

#!/bin/sh
DB="/path/to/database/file"
IMPORT_DIR="/path/to/import/dir"

cd $(dirname $0)

import_files="${IMPORT_DIR}/*.csv"

for file_path in $import_files; do
	file_name=$(basename $file_path)
	table_name=${file_name%.*}
	temp_file=$(mktemp)
	sed -e '1d' $file_path > $temp_file # 先頭行を削除
	sqlite3 -separator , $DB ".import ${temp_file} ${table_name}"
	rm $temp_file
done

#終わりに
おまけの方が長くなってしまいました。
テストデータをCSVで作ってインポートする場合などの参考になれば幸いです。