SQLite の一時テーブル

SQLite の一時テーブルを使うと、データベースを操作する間だけ中間データを保持できます。TEMP または TEMPORARY で作成し、通常の SQL で行を照会・変更して、作業後に削除します。一時テーブルは作成したデータベース接続に属するため、その接続とともに存続します。

SQLite で一時テーブルを作成する

一時テーブルは CREATE TEMP TABLE（または同等の CREATE TEMPORARY TABLE）で定義します。次の例では、Python 標準の sqlite3 モジュールを使い、メモリ上で完結する SQLite の処理を実行します。CREATE TABLE AS SELECT 文の SELECT が既存のテーブルを参照する場合に限り、その参照元テーブルが必要です。列や値を指定して一時テーブルを直接定義することもできます。

次の例を実行します。

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

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TEMP TABLE users (user_id INTEGER, name TEXT, age INTEGER)")
connection.executemany(
    "INSERT INTO users (user_id, name, age) VALUES (?, ?, ?)",
    [(1, "Ari", 30), (2, "Bo", 25)],
)

connection.execute(
    "CREATE TEMP TABLE recent_users AS "
    "SELECT user_id, name FROM users WHERE age >= 18"
)
rows = connection.execute("SELECT user_id, name, age FROM users ORDER BY user_id").fetchall()
print(rows)

connection.execute("UPDATE users SET age = 31 WHERE user_id = 1")
connection.execute("DELETE FROM users WHERE user_id = 2")
connection.execute("DROP TABLE IF EXISTS temp.users")
connection.close()

Python の例では、同じ接続上に users がある状態で CREATE TEMP TABLE ... AS SELECT を使い、recent_users を作成します。SQLite はクエリ結果から新しいテーブルの列を決定します。クエリが参照元テーブルを読み取る場合、そのテーブルは文の実行時に存在している必要があります。参照元を使わない場合、あらかじめテーブルを用意する必要はありません。TEMP と TEMPORARY はどちらも使用できるため、表記をどちらかに統一してください。

一時テーブルのスコープと存続期間を理解する

SQLite は接続の一時データベースに一時テーブルを保存します。同じデータベースファイルを使っていても、同じテーブル名を使っていても、別の接続からその一時テーブルを共有することはできません。一時テーブルは、それを所有する接続が閉じられると消えます。これは接続の存続期間に関するもので、アプリケーションのセッションがタイムアウトする時点とは異なります。そのため、接続を再利用するコードでは、接続を閉じるかテーブルを明示的に削除するまで、そのテーブルを使い続けられます。

このスコープにより、一時テーブルはアプリケーションの永続データとして残す必要のない中間結果の保存に適しています。一方、接続を閉じると、必要な一時データも失われます。必要な結果は永続テーブルにコピーするか、接続を閉じる前に取得してください。

一時テーブルを照会する

一時テーブルは通常の SELECT 構文で照会できます。たとえば、SELECT user_id, name FROM users WHERE age >= 18; のように記述します。永続テーブルと名前が重複していなければ、その名前で一時テーブルを参照できます。明示的に指定するには temp.users のようにスキーマ名を付けます。一時データを照会できるのは、それを所有する接続からだけです。

一時データを読み取るクエリには、共通テーブル式を使うこともできます。WITH は文の中で名前付きのクエリ結果を定義します。共通テーブル式を含む文は、SELECT が1つだけの文に限られません。たとえば、WITH adults AS (SELECT user_id, name FROM temp.users WHERE age >= 18) SELECT name FROM adults; は一時テーブルを読み取り、選択した名前を返します。中間クエリに名前を付ける必要がなければ、直接 SELECT を使うと明快です。

一時テーブルの行を挿入・更新・削除する

他のSQLiteテーブルと同様に INSERT、UPDATE、DELETE を使います。その際、各文が一時テーブルを対象にしていることを確認してください。たとえば、INSERT INTO temp.users (user_id, name, age) VALUES (3, 'Cy', 28); は行を追加します。特定の行を変更するには、UPDATE temp.users SET age = 29 WHERE user_id = 3; とします。WHERE 条件が変更対象を絞り込みます。条件を省くと、対象テーブルのすべての行が更新されます。

特定の行を削除するには、DELETE FROM temp.users WHERE user_id = 3; とします。SQLite の構文では、DELETE の後にアスタリスクを付けません。WHERE を省くと、一時テーブル自体は残したまま、すべての行が削除されます。これらの操作で変更されるのは一時データだけで、同じ名前の永続テーブルには影響しません。データベースに永続テーブル users もある場合は、対象のテーブル名とスキーマを明確にしてください。

一時テーブルを安全に削除する

一時テーブルは DROP TABLE temp.users; で明示的に削除できます。temp. を付けると一時スキーマが指定されるため、同名の永続テーブルを誤って削除するのを防ぐのに役立ちます。テーブルがすでに削除されている可能性がある場合は、DROP TABLE IF EXISTS temp.users; を使います。IF EXISTS を付けると、その一時テーブルが存在しなくても文を安全に実行できます。

削除や接続の終了の前に、残しておく必要がある行をコピーするか取得してください。テーブルを削除すると、そのテーブルと行が取り除かれます。所有する接続を閉じた場合も、テーブルの存続期間は終了します。接続を閉じた後もデータを利用できるようにするには、永続テーブルを使ってください。