SQL Expressions

SQL式¶

How do I render SQL expressions as strings, possibly with bound parameters inlined?¶

ORM Query オブジェクトと同様に、SQLAlchemyのCoreステートメントオブジェクトや式の断片の”文字列化”は、大部分の単純なケースでは、組み込み関数の str() を使うのと同じくらい簡単です。 print 関数と一緒に使う場合は次のようになります(Pythonの print 関数も、明示的に使わなければ自動的に str() を呼び出すことに注意してください)。

>>> fromsqlalchemyimporttable,column,select>>> t=table("my_table",column("x"))>>> statement=select(t)>>> print(str(statement))SELECT my_table.x
FROM my_table

ORM Query オブジェクトや、 select(),:func:_expression.insert などの任意の文、そして以下のような任意の式の断片に対して、組み込みコマンドの str() や、それと同等のものを呼び出すことができます。

>>> fromsqlalchemyimportcolumn>>> print(column("x")=="some value")x = :x_1

Stringifying for Specific Databases¶

文字列化しようとしている文や断片に、データベース固有の文字列形式を持つ要素が含まれている場合や、特定の種類のデータベース内でのみ使用可能な要素が含まれている場合には、複雑な問題が発生します。このような場合、対象となるデータベースの正しい構文ではない文字列化された文が返されたり、操作によって UnsupportedCompilationError 例外が発生したりすることがあります。このような場合、対象となるデータベースを表す Engine または Dialect オブジェクトを渡しながら、 ClauseElement.compile() メソッドを使用して文を文字列化する必要があります。次のように、MySQLデータベースエンジンがあれば、MySQLダイアレクトで文を文字列化できます。:

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://scott:tiger@localhost/test")
print(statement.compile(engine))

もっと直接的には、 Engine オブジェクトを構築しなくても、以下のようにPostgreSQLダイアレクトを使用して、 Dialect オブジェクトを直接インスタンス化することができます。:

from sqlalchemy.dialects import postgresql

print(statement.compile(dialect=postgresql.dialect()))

create_engine() 自体にダミーのURLを指定して Engine.dialect 属性にアクセスすることで、例えばpsycopg2のダイアレクトオブジェクトが必要な場合のように、任意のダイアレクトを組み立てることができます:

e = create_engine("postgresql+psycopg2://")
psycopg2_dialect = e.dialect

ORM Query オブジェクトが与えられた場合、 ClauseElement.compile() メソッドを取得するためには、まず Query.statement アクセッサにアクセスする必要があります:

statement = query.statement
print(statement.compile(someengine))

Rendering Bound Parameters Inline¶

Warning

これらのテクニックは、Webフォームやその他のユーザー入力アプリケーションなど、信頼できない入力から受け取った文字列コンテンツには 使用しないでください 。Python値を直接SQL文字列値に強制するSQLAlchemyの機能は、信頼できない入力に対して安全ではなく、渡されるデータのタイプを検証しません**。リレーショナル・データベースに対して非DDL SQL文をプログラムで呼び出す場合は、常にバウンド・パラメータを使用してください。

上記の形式は、SQL文がPython DBAPI に渡されたときにSQL文をレンダリングします。これには、バインドされたパラメータがインラインでレンダリングされないことも含まれています。SQLAlchemyは通常、バインドされたパラメータを文字列化しません。これはPython DBAPIによって適切に処理されるためです。言うまでもなく、バインドされたパラメータをバイパスすることは、おそらく現代のWebアプリケーションで最も広く利用されているセキュリティホールです。SQLAlchemyは、DDLを発行するような特定の状況では、この文字列化を行う機能が制限されています。この機能にアクセスするには、 literal_binds フラグを使用して compile_kwargs に渡すことができます:

from sqlalchemy.sql import table, column, select

t = table("t", column("x"))

s = select(t).where(t.c.x == 5)

# **do not use** with untrusted input!!!
print(s.compile(compile_kwargs={"literal_binds": True}))

# to render for a specific dialect
print(s.compile(dialect=dialect, compile_kwargs={"literal_binds": True}))

# or if you have an Engine, pass as first argument
print(s.compile(some_engine, compile_kwargs={"literal_binds": True}))

この機能は主にログやデバッグの目的で提供されており、問い合わせのsql文字列をそのまま持っていると便利です。

上記のアプローチには、intやstringなどの基本的な型に対してのみサポートされているという警告があります。さらに、事前に設定された値のない bindparam() が直接使用された場合、それも文字列化できません。すべてのパラメータを無条件に文字列化する方法については、以下で詳しく説明します。

Tip

SQLAlchemyがすべてのデータ型の完全な文字列化をサポートしていない理由は3つあります。

これは、DBAPIが通常使用されるときに、使用中のDBAPIによってすでにサポートされている機能です。SQLAlchemyプロジェクトでは、すべてのバックエンドのすべてのデータ型に対してこの機能を複製することはできません。これは冗長な作業であり、大幅なテストと継続的なサポートのオーバーヘッドも発生するためです。

特定のデータベースに対してインライン化されたバインドパラメータを使用して文字列化することは、これらの完全に文字列化されたステートメントを実行のためにデータベースに実際に渡している使用方法を示唆します。これは不必要で安全ではなく、SQLAlchemyはこの使用を奨励しません。

リテラル値をレンダリングする領域は、セキュリティ問題が報告される可能性が最も高い領域です。SQLAlchemyは、各DBAPIの詳細を適切かつ安全に処理できるように、安全なパラメータ文字列化の領域をDBAPIドライバの問題としてできるだけ維持しようとします。

SQLAlchemyは意図的にリテラル値の完全な文字列化をサポートしていないため、特定のデバッグシナリオでこれを行うためのテクニックには次のようなものがあります。例として、PostgreSQL _PostgreSQL.UUID データ型を使用します:

import uuid

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    data = Column(UUID)

stmt = select(A).where(A.data == uuid.uuid4())

上記のモデルと、カラムを単一のUUID値と比較する文の場合、この文をインライン値で文字列化するためのオプションは次のとおりです。

psycopg2などのいくつかのDB APIは、 mogrify() のようなヘルパー関数をサポートしており、リテラル描画機能へのアクセスを提供しています。このような機能を使用するには、SQL文字列を literal_binds を使わずに描画し、SQLCompiler.params アクセッサを介してパラメータを個別に渡します:
```
e = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")

with e.connect() as conn:
    cursor = conn.connection.cursor()
    compiled = stmt.compile(e)

    print(cursor.mogrify(str(compiled), compiled.params))
```
上記のコードは、psycopg2の生のバイト文字列を生成します。
```
b"SELECT a.id, a.data \nFROM a \nWHERE a.data = 'a511b0fc-76da-4c47-a4b4-716a8189b7ac'::uuid"
```

ターゲットDBAPIの適切な paramstyle を使用して、SQLCompiler.params`を直接ステートメントにレンダリングします。例えば、psycopg2 DBAPIは名前付きの ``pyformat` スタイルを使用します。 render_postcompile の意味については次のセクションで説明します。 警告これは安全ではありません。信頼できない入力を使用しないでください
```
e = create_engine("postgresql+psycopg2://")

# will use pyformat style, i.e. %(paramname)s for param
compiled = stmt.compile(e, compile_kwargs={"render_postcompile": True})

print(str(compiled) % compiled.params)
```
これは動作しない文字列を生成しますが、デバッグには適しています。
```
SELECT a.id, a.data
FROM a
WHERE a.data = 9eec1209-50b4-4253-b74b-f82461ed80c1
```
qmark のような位置パラメータスタイルを使用する別の例では、 SQLCompiler.positiontup コレクションを SQLCompiler.params とともに使用して、コンパイルされたステートメントの位置順にパラメータを取得することで、上記のステートメントをSQLiteで表すことができます。:
```
import re

e = create_engine("sqlite+pysqlite://")

# will use qmark style, i.e. ? for param
compiled = stmt.compile(e, compile_kwargs={"render_postcompile": True})

# params in positional order
params = (repr(compiled.params[name]) for name in compiled.positiontup)

print(re.sub(r"\?", lambda m: next(params), str(compiled)))
```
上記のコードは次のように出力されます。
```
SELECT a.id, a.data
FROM a
WHERE a.data = UUID('1bd70375-db17-4d8c-94f1-fc2ef3aada26')
```

Custom SQL Constructs and Compilation Extension 拡張を使用して、ユーザ定義フラグが存在する場合に BindParameter オブジェクトをカスタムな方法でレンダリングします。このフラグは他のフラグと同じように compile_kwargs 辞書を通じて送られます:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import BindParameter

@compiles(BindParameter)
def _render_literal_bindparam(element, compiler, use_my_literal_recipe=False, **kw):
    if not use_my_literal_recipe:
        # use normal bindparam processing
        return compiler.visit_bindparam(element, **kw)

    # if use_my_literal_recipe was passed to compiler_kwargs,
    # render the value directly
    return repr(element.value)

e = create_engine("postgresql+psycopg2://")
print(stmt.compile(e, compile_kwargs={"use_my_literal_recipe": True}))

上記のレシピがプリントされます。

SELECT a.id, a.data
FROM a
WHERE a.data = UUID('47b154cd-36b2-42ae-9718-888629ab9857')

モデルまたはステートメントに組み込まれた型固有の文字列化では、TypeDecorator クラスを使用して、 TypeDecorator.process_literal_param() メソッドを使用して任意のデータ型のカスタム文字列化を提供できます:

from sqlalchemy import TypeDecorator

class UUIDStringify(TypeDecorator):
    impl = UUID

    def process_literal_param(self, value, dialect):
        return repr(value)

上記のデータ型は、モデル内で明示的に使用するか、次のように type_coerce() を使用して文の中でローカルに使用する必要があります。:

from sqlalchemy import type_coerce

stmt = select(A).where(type_coerce(A.data, UUIDStringify) == uuid.uuid4())

print(stmt.compile(e, compile_kwargs={"literal_binds": True}))

Again printing the same form:

SELECT a.id, a.data
FROM a
WHERE a.data = UUID('47b154cd-36b2-42ae-9718-888629ab9857')

Rendering “POSTCOMPILE” Parameters as Bound Parameters¶

SQLAlchemyには、 BindParameter.expanding と呼ばれるバウンドパラメータのバリアントが含まれています。これは、SQL構文がコンパイルされたときに中間状態でレンダリングされる”late evaluated”パラメータであり、実際の既知の値が渡されたときに文の実行時にさらに処理されます。デフォルトでは、 ColumnOperators.in_() 式には”Expanding”パラメータが使用されます。これにより、 ColumnOperators.in_() の特定の呼び出しに渡される実際の値のリストとは無関係に、SQL文字列を安全にキャッシュできます:

>>> stmt = select(A).where(A.id.in_([1, 2, 3]))

実際の束縛パラメータシンボルでIN句を描画するには、 ClauseElement.compile() で render_postcompile=True フラグを使用してください:

>>> e=create_engine("postgresql+psycopg2://")>>> print(stmt.compile(e,compile_kwargs={"render_postcompile":True}))SELECT a.id, a.data
FROM a
WHERE a.id IN (%(id_1_1)s, %(id_1_2)s, %(id_1_3)s)

バインドされたパラメータのレンダリングに関する前のセクションで説明した literal_binds フラグは、自動的に render_postcompile をTrueに設定するので、単純なint/文字列を持つ文の場合、これらを直接文字列化することができます。:

# render_postcompile is implied by literal_binds>>> print(stmt.compile(e,compile_kwargs={"literal_binds":True}))SELECT a.id, a.data
FROM a
WHERE a.id IN (1, 2, 3)

SQLCompiler.params と SQLCompiler.positiontup は render_postcompile とも互換性がありますので、SQLiteのポジショナル形式のような、インラインバウンドパラメータをレンダリングするための以前のレシピは、ここでも同じように動作します。:

>>> u1,u2,u3=uuid.uuid4(),uuid.uuid4(),uuid.uuid4()>>> stmt=select(A).where(A.data.in_([u1,u2,u3]))>>> importre>>> e=create_engine("sqlite+pysqlite://")>>> compiled=stmt.compile(e,compile_kwargs={"render_postcompile":True})>>> params=(repr(compiled.params[name])fornameincompiled.positiontup)>>> print(re.sub(r"\?",lambdam:next(params),str(compiled)))SELECT a.id, a.data
FROM a
WHERE a.data IN (UUID('aa1944d6-9a5a-45d5-b8da-0ba1ef0a4f38'), UUID('a81920e6-15e2-4392-8a3c-d775ffa9ccd2'), UUID('b5574cdb-ff9b-49a3-be52-dbc89f087bfa'))

Warning

リテラル値を文字列化し、データベースに文を送信するときにバインドされたパラメータの使用をバイパスする上記のコードレシピの すべて は、 次の場合にのみ 使用されることに注意してください。

使用は デバッグ目的のみ
文字列 は、本番データベースに渡されません
ローカルで信頼された入力のみ

リテラル値の文字列化のための上記のレシピは、 いかなる方法でも安全ではなく、本番データベースに対して使用すべきではありません 。

Why are percent signs being doubled up when stringifying SQL statements?¶

多くの DBAPI 実装では、構文にパーセント記号を必ず含む pyformat``または ``format paramstyle を使用します。これを行うほとんどのDBAPIでは、他の理由で使用されるパーセント記号が、次のように使用される文の文字列形式で二重に(つまりエスケープされて)使用されることを想定しています。:

.. sourcecode:: sql

SELECT a, b FROM some_table WHERE a = %s AND c = %s AND num %% modulus = 0

SQL文がSQLAlchemyによって基礎となるDBAPIに渡されるとき、バインドされたパラメータの置換はPythonの文字列補間演算子 % と同じように動作し、多くの場合、DBAPIはこの演算子を直接使用します。上の例では、バインドされたパラメータの置換は次のようになります。

SELECT a, b FROM some_table WHERE a = 5 AND c = 10 AND num % modulus = 0

PostgreSQL(デフォルトのDBAPIはpsycopg2)やMySQL(デフォルトのDBAPIはmysqlclient)などのデータベース用のデフォルトコンパイラでは、次のようなパーセント記号のエスケープ動作が行われます。

>>> fromsqlalchemyimporttable,column>>> fromsqlalchemy.dialectsimportpostgresql>>> t=table("my_table",column("value % one"),column("value % two"))>>> print(t.select().compile(dialect=postgresql.dialect()))SELECT my_table."value %% one", my_table."value %% two"
FROM my_table

このようなダイアレクトが使用されている場合、バインドされたパラメータシンボルを含まないDB API以外の文が必要な場合、パーセント記号を簡単に削除する方法の1つは、Pythonの % 演算子を直接使用して、空のパラメータセットを代入することです。

>>> strstmt=str(t.select().compile(dialect=postgresql.dialect()))>>> print(strstmt%())SELECT my_table."value % one", my_table."value % two"
FROM my_table

もう1つは、使用されているダイアレクトに異なるパラメータスタイルを設定することです。すべての Dialect 実装は、そのダイアレクトのコンパイラが指定されたパラメータスタイルを使用するようにするパラメータ paramstyle を受け入れます。以下では、コンパイルに使用されるダイアレクト内で非常に一般的な named パラメータスタイルが設定されているため、コンパイルされた形式のSQLではパーセント記号が意味を持たなくなり、エスケープされなくなります。

>>> print(t.select().compile(dialect=postgresql.dialect(paramstyle="named")))SELECT my_table."value % one", my_table."value % two"
FROM my_table

I’m using op() to generate a custom operator and my parenthesis are not coming out correctly¶

Operators.op() メソッドを使用すると、SQLAlchemyでは認識されないカスタムデータベース演算子を作成できます。

>>> print(column("q").op("->")(column("p")))q -> p

ただし、複合式の右側で使用すると、予期したとおりに括弧が生成されません。

>>> print((column("q1")+column("q2")).op("->")(column("p")))q1 + q2 -> p

上記の場合は、おそらく (q1 + q2) -> p が必要です。

この場合の解決策は、 Operators.op.precedence パラメータを使用して、演算子の優先順位を高い数値に設定することです。ここで、100は最大値で、SQLAlchemy演算子で使用される最大の数値は現在15です。:

>>> print((column("q1")+column("q2")).op("->",precedence=100)(column("p")))(q1 + q2) -> p

ColumnElement.self_group() メソッドを使用して、バイナリ式(たとえば、左/右オペランドと演算子を持つ式)の前後に強制的に括弧を付けることもできます。?

>>> print((column("q1")+column("q2")).self_group().op("->")(column("p")))(q1 + q2) -> p

Why are the parentheses rules like this?¶

括弧が多すぎる場合や、予期しない場所に括弧がある場合には、多くのデータベースが文句を言います。そのため、SQLAlchemyはグループ化に基づいて括弧を生成せず、演算子の優先順位を使用し、演算子が結合性を持つことがわかっている場合には、括弧の生成を最小限に抑えます。それ以外の場合は、次のような式が使用されます。:

column("a") & column("b") & column("c") & column("d")

would produce:

次のようになります。

(((a AND b) AND c) AND d)

これは問題ありませんが、おそらく人々を悩ませるでしょう(そしてバグとして報告されます)。他のケースでは、データベースを混乱させたり、少なくとも読みやすさを低下させたりする可能性が高いものにつながります。例えば:

column("q", ARRAY(Integer, dimensions=2))[5][6]

would produce:

次のようになります。

((q[5])[6])

また、 "(x) = 7" のようなものが得られる極端なケースもありますが、データベースは実際にはそれを好まないのです。そのため、parenthesizationは単純にparenthesizeするのではなく、演算子の優先順位と結合性を使用してグループ化を決定します。

Operators.op() の場合、precedenceの値はデフォルトでゼロになります。

Operators.op.precedence の値をデフォルトの100(最高など)にした場合はどうなりますか?この式では括弧が増えますが、それ以外は問題ありません。つまり、次の2つは等価です。

>>> print((column("q")-column("y")).op("+",precedence=100)(column("z")))(q - y) + z
>>> print((column("q")-column("y")).op("+")(column("z")))q - y + z

でもこの2つは違います

>>> print(column("q")-column("y").op("+",precedence=100)(column("z")))q - y + z
>>> print(column("q")-column("y").op("+")(column("z")))q - (y + z)

今のところ、演算子の優先順位と結合性に基づいて括弧付けを行っている限り、優先順位が指定されていない汎用演算子に対して自動的に括弧付けを行う方法が本当にあるのか、それがすべての場合に機能するのかは明らかではありません。なぜなら、カスタム演算子の優先順位を他の演算子より低くしたい場合もあれば、高くしたい場合もあるからです。

上記の”binary”式が、 op() が呼ばれた時に self_group() メソッドの使用を強制し、左側の複合式が常に括弧で括られても害がないと仮定した可能性があります。おそらく、この変更はある時点で行うことができますが、当面は、括弧の規則を内部でより一貫性のあるものにしておく方が安全なアプローチのようです。

SQLAlchemy 2.0 Documentation - 私家版日本語訳ドキュメント