Python講座 / sqlite3で保存しパラメーター付きSQLを使う
sqlite3は別のデータベースサーバーなしで、SQLのデータをファイルへ保存できます。入力値をSQL文字列へ埋め込まずパラメーターで渡し、成功時の確定、失敗時の取り消し、接続終了を明示します。
トランザクションの方針を選ぶ
SQLiteは組み込み型のデータベースで、ローカルファイルに便利ですが、同時アクセスなどの条件はサーバー型と異なります。接続しただけで、その後のすべての変更が保存確定されるわけではありません。
この例はPython 3.12以降を使い、autocommit=Falseを明示します。接続のwithは成功時にcommit、失敗時にrollbackを行いますが、接続を閉じません。そのためfinallyでcloseを別に行います。
失敗の回復とコマンドライン保存は、try・except・else・finally・raiseで例外を扱うで扱う規則とつながります。
挿入と照会を全体例で実行する
新しい練習フォルダーを使い、ledger-demo.dbをこの実習専用にします。sqlite_demo.pyを保存し、python sqlite_demo.pyを実行します。
import sqlite3
MAX_AMOUNT = 1_000_000_000_000
amount = 8000
if type(amount) is not int or not 0 <= amount <= MAX_AMOUNT:
raise ValueError("amount outside practice range")
con = sqlite3.connect("ledger-demo.db", autocommit=False)
try:
with con:
con.execute("CREATE TABLE IF NOT EXISTS expenses (id TEXT PRIMARY KEY, name TEXT NOT NULL, amount INTEGER NOT NULL CHECK(amount BETWEEN 0 AND 1000000000000))")
con.execute("INSERT INTO expenses VALUES (?, ?, ?) ON CONFLICT(id) DO UPDATE SET name=excluded.name, amount=excluded.amount", ("e1", "lunch", amount))
print(con.execute("SELECT name, amount FROM expenses ORDER BY id").fetchall())
finally:
con.close()
予想結果です。
[('lunch', 8000)]
値とSQLの構造を分ける
疑問符は値の置き場所で、executeの第2引数のタプルが値を渡します。一つの値でも(value,)のようにカンマが必要です。テーブル名や並べ替え式はSQLの構造なので、変更する必要がある場合は許可した候補から選びます。
PRIMARY KEY、NOT NULL、CHECKは保存側にも規則を設けます。別の経路からの書き込みがあっても規則を守るには、Python側と保存側の両方が重要です。ON CONFLICTは同じIDを再実行時に更新し、同じ支出のコピーを増やしません。
再実行・拒否・終了を検証する
二回実行して行数が一つのままであることを確かめます。金額を負にしてバインド前のValueErrorを確認し、以前の正しい値が残っているか照会します。Python検証を通らない直接SQLにもCHECK制約が適用されます。別のIDを追加したときは明示的なORDER BYの結果を確認し、最後に元へ戻します。
ORDER BYなしの順序を保証として扱わないでください。ロックのエラーでは長いトランザクションや閉じていない接続を調べます。再実行のためだけに、未知のデータベースへDROP TABLEを加えないでください。最終支出管理アプリでは初期化と操作コマンドを分けて使います。
Pythonのintはメモリーの範囲で大きな整数を扱えますが、SQLite INTEGERは符号付き64ビット整数です。この練習では0から1,000,000,000,000の整数に制限し、バインド前に検証します。トランザクションは書き込みをまとめて確定・取り消す境界で、接続を閉じる処理とは別です。CREATE TABLE IF NOT EXISTSでは既存のCHECK制約は変わりません。新しい練習フォルダーを使い、実データにはバックアップと移行が必要です。
動作と適用条件の公式説明：SQLite: storage classes and datatypes
