SQLite の変数

SQLite SQLには、複数のステートメントにまたがって宣言・代入する手続き型のローカル変数はありません。アプリケーションでは通常、バインドパラメーターを使って値を渡します。1つの接続で複数のステートメントから再利用する値は、一時テーブルにSQLの状態として保持できます。共通テーブル式（CTE）は、1つのステートメント内で値に名前を付けます。それぞれ使える範囲が異なるため、値が必要な期間に合った方法を選びましょう。

SQLiteは変数をサポートしていますか？

SQLiteではプリペアドステートメントでパラメーターを使えますが、その値を渡すのはアプリケーションまたはデータベースドライバーです。パラメーターはステートメント内で値を表すプレースホルダーであり、SQLで宣言する変数ではありません。また、テーブル名や列名の代わりにも使えません。たとえば、SELECT * FROM products WHERE price >= ? の ? に値をバインドします。ドライバーのパラメーターAPIを使って、プレースホルダーに入力値を割り当ててください。

バインドすると、データをSQL構文から分離できます。ユーザー入力、設定値、その他のアプリケーションデータには、まずこの方法を使いましょう。テーブル名や列名を切り替える場合は、アプリケーション側で管理する許可リストから選び、識別子として別に組み立てます。バインド可能な値として扱わないでください。

値にはバインドパラメーターを使う

次のPython例では、標準の sqlite3 モジュールでサンプル行を作成し、価格のしきい値をバインドします。正常に処理できる場合を示した完結した例で、SQLのプレースホルダーには連結した文字列ではなくPythonから値を渡します。実行すると、しきい値以上の製品名と価格が表示されます。製品名はコード内の文字列をそのまま使うため、出力でも英語表記のままです。

次の例を実行します。

このセクションのコードと表示されるラベルや出力は英語のままです。

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE products (name TEXT, price REAL)")
connection.executemany(
    "INSERT INTO products (name, price) VALUES (?, ?)",
    [("Notebook", 4.5), ("Keyboard", 25.0), ("Monitor", 180.0)],
)
minimum_price = 20
rows = connection.execute(
    "SELECT name, price FROM products WHERE price >= ? ORDER BY price",
    (minimum_price,),
).fetchall()
for name, price in rows:
    print(f"{name}: {price:.2f}")
connection.close()

位置指定プレースホルダーには ? を使います。名前付きプレースホルダーには :minimum のような形式を使え、ドライバーには {"minimum": 20} のようなマッピングを渡せます。使用するドライバーが対応するプレースホルダー形式とバインド方法を選び、入力値はすべてSQL文字列に埋め込まずにバインドしてください。

再利用するSQLの状態には一時テーブルを使う

同じ接続上の複数のステートメントで、同じ値の集合を読み書きする場合は一時テーブルが便利です。CREATE TEMP TABLE settings (name TEXT, value INTEGER) でテーブルを作成し、通常の INSERT ステートメントで行を追加すれば、他のテーブルと同じように検索できます。ステートメントの間に行を更新でき、使い終わったら DROP TABLE settings で削除できます。

一時テーブルは、作成した接続に属します。その接続上では、テーブルを削除するか接続を閉じるまで利用できますが、別の接続からは共有されません。トランザクションは変更をコミットするかロールバックするかを制御しますが、一時テーブルの接続スコープはそれとは別です。一時テーブルはSQLの状態を行として表すもので、手続き型の変数宣言ではありません。バインドパラメーターで値を直接渡せる場合に使う必要はありません。

1つのステートメント内の値にはCTEを使う

CTEは、後続のステートメントで使う結果に名前を付けます。たとえば、WITH limits AS (SELECT 20 AS minimum_price) SELECT name FROM products, limits WHERE products.price >= limits.minimum_price; は1つの値を定義し、同じクエリ内で使います。CTEで定義した値を、後続のステートメントから読み取れる変数にすることはできません。クエリの入力値や中間結果を読みやすく整理するにはCTEを使い、同じ接続上の複数のステートメントでデータを使い続ける必要がある場合は一時テーブルを使いましょう。

SQLiteのストレージクラスとCAST

SQLiteの値には、NULL、INTEGER、REAL、TEXT、BLOB の5つのストレージクラスがあります。これらは実行時に格納される値の種類を表し、宣言された変数の型ではありません。列に宣言型を指定すると型アフィニティが与えられ、SQLiteが値を格納・比較する方法に影響することがあります。しかし、SQLiteの型システムは、手続き型の変数宣言を定めるものではありません。

CAST(expression AS type) は、SQLiteの変換規則に従って式を変換します。変換されるのはその式の結果です。パラメーターを変更したり、変数を作成したり、別の場所にある値を恒久的に変更したりするものではありません。たとえば、CAST('42' AS INTEGER) はテキスト値を整数として表します。変換結果は入力値と変換先の型によって異なることがあるため、許容形式が重要な場合は、CASTを入力検証とみなさずにアプリケーション側で入力を検証してください。また、値をCASTしても、文字のASCIIコードを一般的に取得できるわけではありません。

用途に合った方法を選ぶ

アプリケーションから渡す値、特にユーザー入力にはバインドパラメーターを使いましょう。値をSQL文字列から分離でき、追加の状態を管理せずに1つのステートメントで使えます。同じ接続上の複数のステートメントで再利用または変更する行の集合が必要な場合は、一時テーブルを使います。1つのステートメント内だけで値や中間結果を使う場合はCTEを使います。これらは寿命の異なる、互いに補い合う方法であり、永続的なSQLite変数を宣言する3通りの方法ではありません。