はじめに

SQLite3は構築が簡単であることや軽量で使い勝手はいいが、一部のSQL文が対応しておらず、苦労する部分がある。例えば、ALTER TABLE系の機能はRENAME COLUMNやADD COLUMNなどしか対応していない。

この仕様に辿り着くのも時間がかかったことと対処法が結構面倒なので、備忘として残しておく。
（やりたかったのはテーブル定義を変更するところなのでそれのやり方を）

やりかた

大まかな手順は以下の通り

元テーブルのCREATE TABLE文を取得する
1で取得したSQLを基に、スキーマ定義を変更した一時テーブルを作成する
2で作成したテーブルに、元テーブルのデータをINSERTする
元テーブルをDROP TABLEする
一時テーブルをRENAME TABLEする

やってること自体はシンプルだが、機械化するには2番目の操作が結構厄介。Pythonで対応してみたのが下の部分。（それでも、スキーマ定義の変更は都度設定が必要。。。。）

SQLite3の操作

import sqlite3

con = sqlite3.connect('test.db')
cur = con.cursor()

create_table_sql = cur.execute("select sql from sqlite_master where name = 'target_table'").fetchone()[0]
create_temp_table_sql = (  # 一時テーブル作成SQLへの変更例
    create_table_sql
    .lower()
    .replace('target_table', 'temp_table')
    .replace(')', ', foreign key (test_fk) references test_table(id) )')  # 外部キーの追加文をいれる
)
cur.execute(create_temp_table_sql)
cur.execute('insert into temp_table select * from target_table')
cur.execute('drop table target_table')
cur.execute('alter table temp_table rename to target_table')

cur.close()
con.close()

これだと複雑な結合関係のテーブルの更新に苦労するので、他にいい手段がないだろうか。。。

参考