python3 sqlite3のデータベースに制約付きの列を追加するためにINSERT文で全データを引っ越す

やりたいこと

いまあるデータベースに制約付きの新しい列を追加したくなったのだが、なかなか良い方法が見つからなかった。ALTER TABLEを用いれば、一応列の追加はできる。

ALTER TABLE [テーブル名] ADD COLUMN [列名] [データ型]; 

しかし、どうやら外部キー制約などはつけれないようである。いろいろ調べた結果、まっさらな新データベースを作ってそこに旧データベースのデータをINSERTすればいいんじゃね？ということで、今あるデータベースの情報をもとにINSERT文を構成して、旧データベースのデータを新データベースに引っ越す。

テストデータベースの作成

とりあえずテスト用のデータベースを作成。parent_id列が追加したい制約付きの列である。

import sqlite3

bconn = sqlite3.connect("test_before.db")
bcursor = bconn.cursor()
bcursor.execute("CREATE TABLE hoge(\
                id INTEGER PRIMARY KEY,\
                name TEXT)")
bcursor.execute("INSERT INTO hoge(name) VALUES ('test1')")
bcursor.execute("INSERT INTO hoge(name) VALUES ('test2')")

aconn = sqlite3.connect("test_after.db")
acursor = aconn.cursor()
acursor.execute("CREATE TABLE hoge(\
                id INTEGER PRIMARY KEY,\
                name TEXT,\
                parent_id INTEGER,\
                FOREIGN KEY(parent_id) \
                    REFERENCES hoge(id))")

データベースに存在するテーブル名一覧を取得する

データベースのテーブル一覧はsqlite_masterを参照することで得ることができる。

table_names = bcursor.execute("SELECT * FROM sqlite_master WHERE type='table'")
print(table_names.fetchall())

これを実行することで以下のような結果が得られる。

[('table', 'hoge', 'hoge', 2, 'CREATE TABLE hoge(                id INTEGER PRIMARY KEY,                name TEXT)')]

テーブルの一覧をリストで得ることができる。
タプルの中身は以下のようになっている
1. タイプ
2. 名前
3. テーブル名
4. ルートページの番号
5. 生成した時のSQL文
2.名前 と3.テーブル名 はまったく同じものが入っているようである。なぜ2つあるのか詳しいことは気にしない。そこらへんが気になる方はこのページを参照していただきたい。

今欲しい情報はテーブル名なので、データベースのテーブル名を取得していく。

table_names_list = []
for i in table_names:
    table_names_list.append(i[2])

これでデータベースに存在するテーブル名の一覧を取得することができた。

テーブルの列名一覧を取得する

列名を取得するにはcursorオブジェクトのdescription属性にアクセスすれば得ることができる。このdescription属性は、最後に叩いたテーブルの列名を取得することができる。よって、あらかじめ、列名を取得したいテーブルでSELECT文を実行し、そのあとにcursorのdescription属性にアクセスすれば、ほしいテーブルの列を得ることができる。

    bcursor.execute("SELECT * FROM [テーブル名]")
    print(bcursor.description)

これの結果は

(('id', None, None, None, None, None, None), ('name', None, None, None, None, None, None))

列名の後ろにNoneがたくさんならんでいるが、これはPython DB APIとの互換性を保つためらしい。使わないので気にしない。ただ、タプルの中のタプルなので、列名を取り出すのにリスト内包表記を使う。

column_names = [s[0] for s in bcursor.description]

これで列名を出すことができた。
今回は、引っ越すためにデータも同時に取得する必要がある。そのため、辞書を使ってJSONの様な形式で、列名は"key"に、データは"value"に保存していく。

table_names_dic={}
for i in table_names_list:
    table_names_dic[i]={}
    bcursor.execute("SELECT * FROM {}".format(i))
    table_names_dic[i]["key"] = [s[0] for s in bcursor.description]
    table_names_dic[i]["value"] = bcursor.fetchall()

INSERT文の生成

SQLのINSERT文の構文は

INSERT INTO [テーブル名] ([列名1],[列名2]) VALUES ([列名1の値],[列名2の値])

である。これをpythonのstr方で自動生成していく。VALUES以降の値を指定する部分を直接strで追加していくと、データの型ごといろいろやらないといけなくて非常に面倒。そこで、今回はそこらへんをやってくれるプレースホルダーを用いる。

INSERT INTO [テーブル名]([列名1],[列名2]) VALUES (:param1,:param2)

プレースホルダーは変数名の前に:を付けることで定義できる。値の指定は{"param1":[列名1の値],"param2":[列名2の値]}をexecuteに投げることで、:param1、:param2だった部分がうま～く置き換えられる。これを用いて自動的にINSERT文を生成する。

for table_name,datas in table_names_dic.items():
    #SQL生成
    sql = "INSERT INTO {}({}".format(table_name,datas["key"][0])
    for column in datas["key"][1:]:
        sql += ","+str(column)
    sql += ") VALUES (:{}".format(datas["key"][0])
    for column in datas["key"][1:]:
        sql += ",:"+str(column)
    sql += ")"
    #データの辞書生成
    for values in datas["value"]:
        value_dic = {}
        for i,j in zip(datas["key"],values):
            value_dic[i] = j
        acursor.execute(sql,value_dic)

SQL生成部分で以下のような、列名をプレースホルダーとするSQLを生成している。

INSERT INTO hoge(id,name) VALUES (:id,:name)

データの辞書生成部分では、列名をkey、データをvalueとした辞書を生成し、SQL文と、この辞書をexecuteに投げることで、INSERT文を実行している。

{'id': 1, 'name': 'test1'}

これでテーブルに含まれるすべての値をINSERT文に変換し、あたらしいデータベースに引っ越すことができる。

まとめ

INSERT文を使って引っ越しはできる！！

以下にプログラムの全体を示します。
使用は自己責任でお願いします。

import sqlite3

bconn = sqlite3.connect("test_before.db")
bcursor = bconn.cursor()
bcursor.execute("CREATE TABLE hoge(\
                id INTEGER PRIMARY KEY,\
                name TEXT)")
bcursor.execute("INSERT INTO hoge(name) VALUES ('test1')")
bcursor.execute("INSERT INTO hoge(name) VALUES ('test2')")

aconn = sqlite3.connect("test_after.db")
acursor = aconn.cursor()
acursor.execute("CREATE TABLE hoge(\
                id INTEGER PRIMARY KEY,\
                name TEXT,\
                parent_id INTEGER,\
                FOREIGN KEY(parent_id) \
                    REFERENCES hoge(id))")

table_names_list = []
table_names = bcursor.execute("SELECT * FROM sqlite_master WHERE type='table'")

for i in table_names:
    table_names_list.append(i[2])

table_names_dic={}
for i in table_names_list:
    table_names_dic[i]={}
    bcursor.execute("SELECT * FROM {}".format(i))
    table_names_dic[i]["key"] = [s[0] for s in bcursor.description]
    table_names_dic[i]["value"] = bcursor.fetchall()

for table_name,datas in table_names_dic.items():
    #SQL生成
    sql = "INSERT INTO {}({}".format(table_name,datas["key"][0])
    for column in datas["key"][1:]:
        sql += ","+str(column)
    sql += ") VALUES (:{}".format(datas["key"][0])
    for column in datas["key"][1:]:
        sql += ",:"+str(column)
    sql += ")"
    #データの辞書生成
    for values in datas["value"]:
        value_dic = {}
        for i,j in zip(datas["key"],values):
            value_dic[i] = j
        acursor.execute(sql,value_dic)