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

# やりたいこと
いまあるデータベースに制約付きの新しい列を追加したくなったのだが、なかなか良い方法が見つからなかった。ALTER TABLEを用いれば、一応列の追加はできる。
```
ALTER TABLE [テーブル名] ADD COLUMN [列名] [データ型]; 
```
しかし、どうやら外部キー制約などはつけれないようである。いろいろ調べた結果、まっさらな新データベースを作ってそこに旧データベースのデータをINSERTすればいいんじゃね？ということで、今あるデータベースの情報をもとにINSERT文を構成して、旧データベースのデータを新データベースに引っ越す。


# テストデータベースの作成
とりあえずテスト用のデータベースを作成。parent_id列が追加したい制約付きの列である。
``` python
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を参照することで得ることができる。
```python
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つあるのか詳しいことは気にしない。そこらへんが気になる方は[このページ](https://www.sqlite.org/schematab.html)を参照していただきたい。

今欲しい情報はテーブル名なので、データベースのテーブル名を取得していく。
```
table_names_list = []
for i in table_names:
    table_names_list.append(i[2])
```
これでデータベースに存在するテーブル名の一覧を取得することができた。

# テーブルの列名一覧を取得する
列名を取得するにはcursorオブジェクトのdescription属性にアクセスすれば得ることができる。このdescription属性は、最後に叩いたテーブルの列名を取得することができる。よって、あらかじめ、列名を取得したいテーブルでSELECT文を実行し、そのあとにcursorのdescription属性にアクセスすれば、ほしいテーブルの列を得ることができる。
```python
    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との互換性を保つためらしい。使わないので気にしない。ただ、タプルの中のタプルなので、列名を取り出すのにリスト内包表記を使う。
```python
column_names = [s[0] for s in bcursor.description]
```
これで列名を出すことができた。
今回は、引っ越すためにデータも同時に取得する必要がある。そのため、辞書を使ってJSONの様な形式で、列名は"key"に、データは"value"に保存していく。
```python
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文を生成する。
```python
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文を使って引っ越しはできる！！

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

```python
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)
```
:::