SQL クエリにパラメータを渡す#

ほとんどの場合、プログラムを書くときには、次のように他の場所から提供された値を SQL ステートメントと少し混ぜる必要があるでしょう。

SELECT some, fields FROM some_table WHERE id = ...

id は何と等しいのでしょうか? おそらく見つけようとしている Python の値があるはずです。

execute() の引数#

SQL ステートメントへのパラメータの受け渡しは、Cursor.execute() などの関数内で SQL ステートメント内の %s プレースホルダーを使用して、値のシーケンスを関数の第2引数として渡すことで行われます。たとえば、Python の関数呼び出しは次のようになります。

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

これは、おおよそ 次の SQL コマンドと同等です。

INSERT INTO some_table (id, created_at, last_name)
VALUES (10, '2020-11-18', 'O''Reilly');

パラメータは本当にはクエリにマージされないことに注意してください。クエリとパラメータはサーバーに別々に送られるためです。詳細については、サーバーサイド バインディング を参照してください。

名前付き引数もサポートされており、クエリ内で %(name)s プレースホルダーを使用して、値をマッピングとして指定します。名前付き引数を使用すると、次のように値を自由な順序で指定したり、同じ値をクエリ内の複数の場所で繰り返したりできます。

cur.execute("""
    INSERT INTO some_table (id, created_at, updated_at, last_name)
    VALUES (%(id)s, %(created)s, %(created)s, %(name)s);
    """,
    {'id': 10, 'name': "O'Reilly", 'created': datetime.date(2020, 11, 18)})

引数名内での文字 %() の使用はサポートされていません。

パラメータが使用されたとき、クエリ内にリテラル % を含めるためには、%% 文字列が使えます。

cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # 間違い!
cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # 正しい

仕組みは通常の Python 文字列の操作に似ていますが、クエリにパラメータを渡すときに注意が必要な微妙な違いがいくつかあります。

  • 位置での変数バインディングでは、たとえ1つの変数しか含まれていなかったとしても、第2引数は常にシーケンスでなければなりません (Python では1要素のタプルを作るためにカンマが必要であることを思い出してください)。

    cur.execute("INSERT INTO foo VALUES (%s)", "bar")    # 間違い!
    cur.execute("INSERT INTO foo VALUES (%s)", ("bar"))  # 間違い!
    cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # 正しい
    cur.execute("INSERT INTO foo VALUES (%s)", ["bar"])  # 正しい
    
  • プレースホルダーは、次のように クォートで囲んではいけません

    cur.execute("INSERT INTO numbers VALUES ('%s')", ("Hello",)) # 間違い!
    cur.execute("INSERT INTO numbers VALUES (%s)", ("Hello",))   # 正しい
    
  • 変数のプレースホルダーは、たとえその型に対して他のプレースホルダー (整数に対して %d や浮動小数点数に対して %f など) がより適切だと思えたとしても、常に %s でなければなりません。psycopg のクエリで他のプレースホルダー (%b%t) が使われているのを見かけるかもしれませんが、それらは引数の型とは無関係です。詳細について知りたい場合は、バイナリ パラメータと結果 を参照してください。

    cur.execute("INSERT INTO numbers VALUES (%d)", (10,))   # 間違い!
    cur.execute("INSERT INTO numbers VALUES (%s)", (10,))   # 正しい
    
  • このメソッドを介してバインドされるのは、クエリの値だけであるべきです。テーブルやフィールドの名前をクエリにマージするのに使われるべきではありません。SQL クエリを動的に生成する必要がある場合 (たとえば、テーブル名をランタイムに選択するなど)、次のように psycopg.sql モジュールが提供する機能が使えます。

    cur.execute("INSERT INTO %s VALUES (%s)", ('numbers', 10))  # 間違い!
    cur.execute(                                                # 正しい
        SQL("INSERT INTO {} VALUES (%s)").format(Identifier('numbers')),
        (10,))
    

危険: SQL インジェクション#

多くのデータ型の SQL 表現は、多くの場合に Python の文字列表現とは異なります。典型的な例は、文字列がシングルクォートで囲まれることです。SQL では、シングルクォートは文字列リテラルの区切り文字として使われるため、文字列自体の内部に現れるシングルクォートはエスケープする必要があります。一方で、文字列がダブルクォート区切りの場合、Python のシングルクォートは、エスケープせずに残しておけます。

データ型の表現の (ときには微妙な) 違いが原因で、たとえば Python 文字列の結合を使用するなど、クエリ文字列を構成するときにナイーブなアプローチを取ってしまうことが原因で、恐ろしい 問題のレシピとなってしまうことがあります。

SQL = "INSERT INTO authors (name) VALUES ('%s')" # これは絶対にしてはいけない!
data = ("O'Reilly", )
cur.execute(SQL % data) # これは大失敗に終わる!
# SyntaxError: syntax error at or near "Reilly"

データベースに送信するデータを含む変数が信頼できない情報源 (ウェブサイト上のフォームから送られたデータなど) に由来する場合、攻撃者は不正な形式の文字列を簡単に作成できるため、許可されていないデータへのアクセスを獲得したり、データベース上で破壊的な操作を実行できてしまいます。この形式の攻撃は SQL injection と呼ばれ、データベース システムに対して、最も広く発生している形式の攻撃の1つとして知られています。読み進める前に、忘れないように このページ を印刷して机の上に貼ってください。

psycopg は Python オブジェクトを自動的に SQL の値に変換します。この機能を使用することで、コードはより頑強で信頼できるものになります。以下の点は強調しておかなければなりません。

警告

  • クエリに値を手動でマージしないでください。そんなことをしたら、外国からのハッカーがあなたのコンピュータに侵入し、ディスクだけでなく CD も盗み、あなたがこれまでに買った中で最も恥ずかしいレコード 3 枚だけを残すでしょう。カセットテープで。

  • もし % 演算子を使用して値をクエリにマージしたら、詐欺師はあなたの猫を誘惑し、猫はあなたのクレジットカードとサングラスを取って詐欺師と一緒に逃げ去るでしょう。

  • もし + を使ってテキストの値を文字列にマージしたら、バラクラバをかぶった悪い人たちが冷蔵庫にたどり着き、すべてのビールを飲み干し、トイレの便座を上げたままにし、トイレットペーパーの向きを間違ったままにしてしまうでしょう。

  • もう値をクエリに手動でマージしたいとは思わないはずです。その代わりに 提供されたメソッド を使ってください。

SQL コマンド内で変数を渡すための正しい方法は、次のように Cursor.execute() メソッドの第2引数を使うことです。

SQL = "INSERT INTO authors (name) VALUES (%s)"  # Note: no quotes
data = ("O'Reilly", )
cur.execute(SQL, data)  # メモ: % 演算子はなし

注釈

Python の静的コードチェッカーはまだそこまでは進んでいません。しかし、将来はクエリ内の文字列式の不適切な使用をチェック可能になるでしょう。詳細は クエリ内のリテラル文字列の型チェック を参照してください。

参考

これでパラメータをクエリに渡す方法が理解できたはずなので、psycopg がデータ型を変換する方法 を読むことができます。

バイナリ パラメータと結果#

PostgreSQL には、データをクライアントとサーバー間で転送する2種類の異なる方法があります。TEXT は常に利用可能な方法で、BINARY はほとんどの場合に利用可能ですが常にではありません。通常は、バイナリ フォーマットを使うのがより効率的です。

psycopg はデータ型ごとに両方のフォーマットをサポートできます。普通の %s プレースホルダーを使用してクエリに値が渡されるたびに、利用可能な最適なフォーマットが選ばれます (常にではありませんが、多くの場合にバイナリ フォーマットが最善の選択として選ばれます)。

値に対して明示的にバイナリ フォーマットまたはテキスト フォーマットを選択する理由がある場合は、それぞれ %b プレースホルダーまたは %t プレースホルダーを通常の %s の代わりに使用できます。正しいデータ型に対する Dumper とフォーマットが利用できない場合、execute() は失敗します。

同じ2つのフォーマット (テキストまたはバイナリ) は、PostgreSQL がクエリからデータをクライアントに返すためにも使用されます。値ごとにフォーマットを選択できるパラメータの場合とは違い、クエリから返されるすべての列は同じフォーマットを持ちます。クエリから返されたすべての型は設定された Loader を持つ必要があり、もし存在しない場合にはデータは未パースの str (テキストの結果の場合) または buffer (バイナリの結果の場合) として返されます。

注釈

pg_type テーブルには、PostgreSQL のそれぞれのデータ型に対してどのフォーマットがサポートされているかが定義されています。テキストの入出力は typinput and typoutput (常に存在) フィールドで宣言された関数により管理されており、バイナリの入出力は typsendtypreceive (オプション) によって宣言されています。

デフォルトでは、すべての PostgreSQL の型がバイナリのアウトプットをサポートしているわけではないので、データはテキスト フォーマットで返されます。データをバイナリ フォーマットで返すためには、Connection.cursor(binary=True) を使用してカーソルを作成するか、クエリを Cursor.execute(binary=True) を使用して実行します。バイナリの結果をリクエストするのが明らかに優れているのは、次のように、画像などの大きなバイナリデータがデータベースにある場合です。

cur.execute(
    "SELECT image_data FROM images WHERE id = %s", [image_id], binary=True)
data = cur.fetchone()[0]