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のカラム数が少ないとエラーになることがある。