SQL 表达式¶

如何将 SQL 表达式呈现为字符串，可能内联绑定参数？¶

在大多数简单情况下，SQLAlchemy Core 语句对象或表达式片段以及 ORM 查询对象的“字符串化”就像使用 str（） 内置函数一样简单，如下所示，当它与打印一起使用时函数（请注意，如果我们不显式使用 str（），Python 打印函数也会自动调用 str（））：

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

可以在 ORM 上调用 str（） 内置函数或等效函数 Query 对象以及任何语句（如 select（）、insert（） 等，以及任何表达式片段，例如：

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

为特定数据库字符串化¶

当我们字符串化的语句或片段时，就会出现复杂性包含具有特定于数据库的字符串格式的元素，或者当它包含仅在特定类型的数据库中可用的元素。在这些情况下，我们可能会得到一个不正确的字符串化语句语法，否则该作可能会引发 UnsupportedCompilationError 异常。在这些情况下，它是的 Tim Git，以便我们使用 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 accessor first：

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

内联渲染绑定参数¶

警告

切勿将这些技术用于从不受信任的输入（例如，来自 Web 表单或其他用户输入应用程序）接收的字符串内容。SQLAlchemy 用于将 Python 值强制转换为直接 SQL 字符串值的工具对于不受信任的输入不安全，并且不会验证类型的数据。在以编程方式对关系数据库调用非 DDL SQL 语句时，始终使用绑定参数。

上述表单将在 SQL 语句传递给 Python 时呈现 SQL 语句 DBAPI，其中包括绑定参数不内联呈现。SQLAlchemy 通常不会字符串化绑定参数，因为这由 Python DBAPI 适当处理，更不用说绕过绑定参数可能是现代 Web 应用程序中利用最广泛的安全漏洞。SQLAlchemy 在某些情况下执行此字符串化的能力有限，例如发出 DDL。为了访问此功能，可以使用 literal_binds flag 中，传递给 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 和 strings，此外，如果直接使用没有预设值的 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"

使用适当的 paramstyle 将 SQLCompiler.params 直接渲染到语句中的目标 DBAPI 中。例如，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）的示例，我们可以将我们上面关于 SQLite 的声明也使用了 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')

使用 Custom SQL Constructs and Compilation Extension 扩展来渲染 BindParameter 对象。此标志通过 compile_kwargs dictionary 一样：

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 构造时以中间状态呈现，然后在语句执行时进一步处理，当实际的传递已知值。 “扩展”参数用于 ColumnOperators.in_（） 表达式，以便可以安全地缓存 SQL 字符串，而不受传递给 ColumnOperators.in_（） 的特定调用的实际值列表的影响：

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

要使用实际绑定参数符号呈现 IN 子句，请使用 render_postcompile=True 标志与 ClauseElement.compile（） 一起：

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

# 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参数样式，这些参数在其语法中必然包含百分号。大多数执行此作的 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 语句，则不包含绑定参数符号，一种快速删除百分比的方法 signs 是简单地使用 Python 的 % 运算符：

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

另一种方法是在所使用的方言上设置不同的参数样式;都 Dialect 实现接受一个参数 param样式，这将导致该 dialect 的编译器使用给定的参数样式。下面，非常常见的名为 参数样式在用于编译的 dialect 中设置，以便百分号在 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

我们通常也可以强制将二进制表达式括起来（例如具有 left/right作数和运算符的表达式）使用 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（），precedence 的值默认为零。

如果我们将 Operators.op.precedence 的值默认为 100，例如最高值，该怎么办？那么这个表达式多加括号，但其他方面都没问题，也就是说，这两者是等价的：

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

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

有可能，如果上面的 “binary” 表达式在调用 op（） 时强制使用 self_group（） 方法，从而假设左侧的复合表达式始终可以无害地括起来。也许可以在某个时候进行此更改，但是目前保持括号规则在内部更加一致似乎是更安全的方法。

SQLAlchemy 2.0 文档

SQLAlchemy 2.0 文档

常见问题解答

SQL 表达式¶

如何将 SQL 表达式呈现为字符串，可能内联绑定参数？¶

为特定数据库字符串化¶

内联渲染绑定参数¶

将 “POSTCOMPILE” 参数渲染为绑定参数¶

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

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

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

SQLAlchemy 2.0 文档

SQLAlchemy 2.0 文档

常见问题解答

SQL 表达式¶

如何将 SQL 表达式呈现为字符串，可能内联绑定参数？¶

为特定数据库字符串化¶

内联渲染绑定参数¶

将 “POSTCOMPILE” 参数渲染为绑定参数¶

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

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

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

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