kuroの覚え書き

96の個人的覚え書き

SQLite3のtable作成覚書

FlaskでSQLAlchemyを使ってSQLiteのデータベースを運用している。
前提

  • model.pyでtableの定義をおこない、データベース上にテーブルが作成されるように設定しているが、データ自体はあとからtsvから読み込む。
  • primary keyとしてIDをふるが、それはデータベースに登録時に自動的に割り振ることにする。
  • SQLiteにデータを登録した時点で、IDの次のカラムに、ある計算式で算出したデータを追加する。(tableの定義にはカラムとして用意されているが読み込むtsvには項目なし)
  • tsvはexcelで加工しない。
  • tsvのカラム数より、tableに定義したカラム数のほうが多い。(tsvに入っていない項目がある)

手順
1.まずFlaskアプリを起動してテーブルを生成後、一旦アプリ閉じる
2.SQLite3を起動する

.separator "\t"
.import input.tsv target_table

これでtsvが流し込まれ、IDが付与される

.mode tabs
.output output1.tsv
SELECT * FROM target_table;

これでIDが付与されたtsvがexportされる。

.output output2.tsv
SELECT XXXXX
DROP TABLE target_table

これである計算式で算出されたカラムがexportされ、一旦テーブルが消される。ここでSQLite3を閉じる。
本当は算出されたカラムを使ってテーブルをUPDATEしたいのだが、テクが足らずできてないので、tsvのテキスト処理で合体させる。
3.シェルコマンドラインでtsvの合体

$ cut -f 1-n output1.tsv > output3.tsv
$ paste output3.tsv output2.tsv > input1.tsv

nはIDの入ったカラムの番号
4.Flaskアプリを起動してテーブルをもう一度生成後、またアプリ閉じる
5.SQLite3を起動する

.separator "\t"
.import input1.tsv target_table

これでIDと計算結果カラムが入ったテーブルが出来上がり。
計算結果カラムでUPDATEできればこんなややこしいことはしなくてSQLite3の操作だけで完了するのだが。
ついでにいうとFlaskをちゃんと使いこなせばそもそもSQLite3を直接起動しなくても操作ができるはずなんだが。
SQLite3のバージョンによってはテーブル定義よりtsvのカラム数が少ないとエラーになることがある。