SQL 表达式¶

如何将 SQL 表达式渲染为字符串，可能需要内联绑定参数？¶

SQLAlchemy Core 语句对象或表达式片段的“字符串化”，以及 ORM Query 对象的字符串化，在大多数简单情况下，只需使用 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

str() 内置函数或等效函数可以应用于 ORM Query 对象，以及任何语句，例如 select()、insert() 等，以及任何表达式片段，例如

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

针对特定数据库进行字符串化¶

当我们正在字符串化的语句或片段包含具有数据库特定字符串格式的元素，或者包含仅在特定类型的数据库中可用的元素时，就会出现一个复杂情况。在这些情况下，我们可能会得到一个针对我们目标数据库的语法不正确的字符串化语句，或者操作可能会引发 UnsupportedCompilationError 异常。在这些情况下，有必要使用 ClauseElement.compile() 方法对语句进行字符串化，同时传递一个表示目标数据库的 Engine 或 Dialect 对象。例如，如果我们有一个 MySQL 数据库引擎，我们可以根据 MySQL 方言对语句进行字符串化

from sqlalchemy import create_engine

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

更直接地，无需构建 Engine 对象，我们可以直接实例化一个 Dialect 对象，如下所示，我们在其中使用 PostgreSQL 方言

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))

渲染内联绑定参数¶

警告

切勿对从不受信任的输入（例如，来自 Web 表单或其他用户输入应用程序）接收的字符串内容使用这些技术。SQLAlchemy 将 Python 值强制转换为直接 SQL 字符串值的功能不安全，无法抵御不受信任的输入，也不会验证正在传递的数据类型。在以编程方式对关系数据库执行非 DDL 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 字符串可能会有用。

以上方法的局限性在于，它只支持基本类型，例如整数和字符串，此外，如果直接使用没有预设值的 bindparam()，它也无法对其进行字符串化。无条件地将所有参数字符串化的方法将在下面详细介绍。

提示

SQLAlchemy 不支持对所有数据类型进行完整字符串化的原因有三方面

当正常使用 DBAPI 时，这是一种由所用 DBAPI 支持的功能。SQLAlchemy 项目无法承担为所有后端的所有数据类型重复此功能的任务，因为这会重复工作，还会带来大量的测试和持续支持开销。
针对特定数据库对绑定参数进行内联字符串化，表明实际上是在将这些完全字符串化的语句传递给数据库以进行执行。这是不必要的，也不安全的，SQLAlchemy 绝不希望鼓励这种用法。
渲染字面值是报告安全问题的最有可能的区域。SQLAlchemy 试图尽可能地将安全参数字符串化问题保留给 DBAPI 驱动程序，以便可以适当地安全地处理每个 DBAPI 的细节。

由于 SQLAlchemy 有意不支持对字面值进行完整字符串化，因此在特定调试场景中进行此操作的技术包括以下几种。例如，我们将使用 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 值，对该语句进行字符串化以包含内联值的选择包括

一些 DBAPI（例如 psycopg2）支持像 mogrify() 这样的辅助函数，这些函数提供了对其字面值渲染功能的访问权限。要使用这些功能，请在不使用 literal_binds 的情况下渲染 SQL 字符串，并通过 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"

将 SQLCompiler.params 直接渲染到语句中，使用目标 DBAPI 的适当 paramstyle。例如，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 来呈现上面的语句，以按编译语句的位置顺序检索参数。

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')

使用自定义 SQL 结构和编译扩展扩展，当存在用户定义的标志时，以自定义方式呈现 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}))

再次输出相同形式：

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

将“POSTCOMPILE”参数呈现为绑定参数¶

SQLAlchemy 包含一种绑定参数的变体，称为 BindParameter.expanding，这是一个“延迟评估”参数，在编译 SQL 结构时以中间状态呈现，然后在执行语句时，当实际已知值被传递时，进一步处理。“Expanding”参数默认用于 ColumnOperators.in_() 表达式，以便 SQL 字符串可以安全地缓存，独立于传递给 ColumnOperators.in_() 的特定调用的实际值列表。

>>> 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，因此对于具有简单整数/字符串的语句，这些可以被直接字符串化。

# 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'))

警告

请记住，所有上述对文字值进行字符串化的代码示例，都绕过了使用绑定参数将语句发送到数据库，只在以下情况下使用：

仅用于调试目的。
该字符串不能传递给实时生产数据库。
仅适用于本地、可信输入。

上述对文字值进行字符串化的示例不安全，决不应针对生产数据库使用。

为什么在对 SQL 语句进行字符串化时百分号会加倍？¶

许多 DBAPI 实现使用 pyformat 或 format paramstyle，这必然涉及它们的语法中的百分号。大多数执行此操作的 DBAPI 期望用于其他原因的百分号在用于语句的字符串形式中加倍（即转义），例如：

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

当使用这种方言时，如果需要不包含绑定参数符号的非 DBAPI 语句，一种快速删除百分号的方法是使用 Python 的 % 运算符直接替换为空参数集。

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

另一种方法是在使用的方言上设置不同的参数样式；所有 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

我使用 op() 生成自定义运算符，但我的括号没有正确显示¶

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

为什么括号规则是这样？¶

许多数据库在存在过多括号或括号位于它们不期望的非寻常位置时会报错，因此 SQLAlchemy 不基于分组生成括号，它使用运算符优先级，并且如果已知运算符是可结合的，则最少生成括号。否则，像这样的表达式：

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

将产生：

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

这很好，但可能会惹恼人们（并被报告为错误）。在其他情况下，它会导致更容易混淆数据库或至少可读性较差的事情，例如：

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

将产生：

((q[5])[6])

还有一些边缘情况，我们会得到类似 "(x) = 7" 的东西，数据库也确实不喜欢这样。因此，括号不会天真地进行括号化，它使用运算符优先级和结合性来确定分组。

对于 Operators.op()，优先级值默认为零。

如果我们将 Operators.op.precedence 的值默认为 100，例如最高值？那么，这个表达式会生成更多的括号，但其他方面是 OK 的，也就是说，这两个是等价的

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

但这两个不相同

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

目前，还不清楚只要我们基于运算符优先级和结合性进行括号化，是否真的有一种方法可以自动为没有给定优先级的通用运算符进行括号化，因为有时你希望自定义运算符的优先级低于其他运算符，有时你希望它更高。

如果上面的“二元”表达式在调用 op() 时强制使用 self_group() 方法，假设左侧的复合表达式总是可以被无害地括起来，那可能就可以了。也许这个更改可以在某个时候进行，但是目前，保持括号规则更加内部一致似乎是更安全的方法。

SQLAlchemy 2.0 文档

SQLAlchemy 2.0 文档

常见问题

项目版本