【Sqlite3：まとめ】PythonでSqlite3を操作する

今回の記事は、前回の記事

のまとめ編です。内容としては紹介した構文を一括で使用するものなので、後で見返す際に使用するなどのものですかね。
コードの流れとしては以下のようになります。

• データベースと接続
• テーブルの作成
• テーブルの名前を変更
• テーブルのカラムを追加
• テーブルにレコードを追加
• テーブルのレコードの値を変更
• テーブルのレコードを削除
• テーブルのカラムを削除
• テーブルの削除
• データベース接続の解除

細かい点は省きますが、カラムの削除前後で面倒になってSELECT文をVER2で追加しています。
それ以外のクエリは前回の記事にあったものです。（記述ミスがなければ）

構文のまとめ

コードを載せていきます。説明はこちらを参照のこと。

import sqlite3

# クエリ文
ALTER_TABLE_ADD_COLUMN = "ALTER TABLE example ADD COLUMN t6 TEXT;"
ALTER_TABLE_DROP_COLUMN = "ALTER TABLE example DROP COLUMN t6;"
ALTER_TABLE_RENAME = "ALTER TABLE rename_target RENAME TO example;"
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS rename_target (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
DELETE_ALL_RECORDS = "DELETE FROM example;"
DELETE_RECORD_BY_COLUMN_VALUE = "DELETE FROM example WHERE t3 = ?;"
DROP_TABLE = "DROP TABLE IF EXISTS example;"
INSERT_RECORDS = "INSERT INTO example VALUES (?, ?, ?, ?, ?);"
INSERT_RECORDS_ADD = "INSERT INTO example VALUES (?, ?, ?, ?, ?, ?);"
SELECT_MASTER_TABLE = "SELECT name FROM sqlite_master;"
SELECT_MASTER_TABLE_EXIST = "SELECT name FROM sqlite_master WHERE TYPE = 'table' AND name = ?;"
SELECT_TYPE_OF_COLUMNS = "SELECT typeof(t1), typeof(t2),typeof(t3),typeof(t4),typeof(t5) FROM example;"
SELECT_TYPE_OF_COLUMNS_VER2 = "SELECT typeof(t1), typeof(t2),typeof(t3),typeof(t4),typeof(t5), typeof(t6) FROM example;"
SELECT_VALUE_OF_COLUMNS = "SELECT * FROM example;"
UPDATE_RECORDS_BY_COLUMN_VALUE = "UPDATE example SET t1 = ? WHERE t2 = ?;"

# パラメータ
INSERT_DATAS = [
    ('500.0', '500.0', '500.0', '500.0', '500.0'), # str
    (500.0, 500.0, 500.0, 500.0, 500.0), # float
    (500, 500, 500, 500, 500), # int
    (b'0500', b'0500', b'0500', b'0500', b'0500'), # bytes
    (None, None, None, None, None), # None
]

INSERT_DATAS_ADD = [
    ('500.0', '500.0', '500.0', '500.0', '500.0', '1000.0'),
    (500.0, 500.0, 500.0, 500.0, 500.0, 1000.0),
    (None,None,None,None,None,None),
]


# 雑多な関数たち
def records_dict(types: list, values: list):
    records = []
    for idx in range(len(values)):
        record = []
        for key, value in zip(values[idx], types[idx]):
            record.append(f"{key}: {value}")
        records.append(record)
    return records


def retrieve_records_list():
    type_list = cur.execute(SELECT_TYPE_OF_COLUMNS).fetchall() # データタイプリストのコピーを作成
    value_list = cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall() # データ値リストのコピーを作成
    return records_dict(type_list, value_list) # dict = [[value: type], [value: type], ...]


def retrieve_records_list_v2():
    type_list = cur.execute(SELECT_TYPE_OF_COLUMNS_VER2).fetchall() # データタイプリストのコピーを作成
    value_list = cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall() # データ値リストのコピーを作成
    return records_dict(type_list, value_list) # dict = [[value: type], [value: type], ...]


def rename_table_if_not_exists(rename_name: str):
    res = cur.execute(SELECT_MASTER_TABLE_EXIST, (rename_name, )).fetchone()

    if res is None:
        cur.execute(ALTER_TABLE_RENAME)
        print(f"Success: Change table name to {rename_name}...")
    else:
        print(f"Fail: {rename_name} is already existed...")


# データベースに接続（存在しない場合、新たに作成）
con = sqlite3.connect("Examples.db")
# データベースカーソルオブジェクトの生成
cur = con.cursor()


# クエリ実行

# `rename_target`テーブルの作成
cur.execute(CREATE_TABLE)
res = list(cur.execute(SELECT_MASTER_TABLE).fetchall())
print(res)

# `example`テーブルに名前を変更
rename_table_if_not_exists('example') 
res = cur.execute(SELECT_MASTER_TABLE).fetchall()
print(res)

# `example`テーブルにレコードを追加
cur.executemany(INSERT_RECORDS, INSERT_DATAS)

print("After records inserted...")
records = retrieve_records_list() 
for record in records:
    print(record) # 挿入したレコードを表示

# `example`テーブルのデータの値を変更
cur.execute(UPDATE_RECORDS_BY_COLUMN_VALUE, ("Changed", 500, ))

print("After column values changed...")
records = retrieve_records_list()
for record in records:
    print(record) # 挿入したレコードを表示


# `example`テーブルにカラムを追加およびレコードの追加
cur.execute(ALTER_TABLE_ADD_COLUMN)
cur.executemany(INSERT_RECORDS_ADD, INSERT_DATAS_ADD)

print("After column added and records inserted...")
records = retrieve_records_list_v2()
for record in records:
    print(record) # 挿入したレコードを表示

# `example`テーブルに追加したカラムを削除
cur.execute(ALTER_TABLE_DROP_COLUMN)

print("After column deleted...")
records = retrieve_records_list()
for record in records:
    print(record)

# `example`テーブルのレコードを削除
cur.execute(DELETE_ALL_RECORDS)
print("After records deleted...")
records = retrieve_records_list()
if records:
    print("Records exist...")
else:
    print("Record has gone...")

# テーブル`example`を削除
cur.execute(DROP_TABLE) 
res = cur.execute(SELECT_MASTER_TABLE).fetchall()

print("After example table deleted...")
if res:
    print("Table exists...")
else:
    print("Table has gone...")

# データベースへの変更を確定させる
con.commit()
# データベース接続を閉じる
con.close()

まとめ

構文ハイライトが変な気がしますが、とりあえずまとめられたかと思います。
個人的にはALTER TABLEなどのバインドがうまくいっていない点があるのが気がかりですが、あまり頻繁に使用するものではないのでまあええか（という心境です）。