sqlbind

sqlbind 允许在基于文本的原始 SQL 查询中绑定参数。

>>> q = sqlbind.Dialect.default()
>>> email = 'some@domain.com'
>>> sql = f'SELECT * FROM users WHERE email = {q/email}'
>>> sql
'SELECT * FROM users WHERE email = ?'
>>> q
['some@domain.com']
>>> data = connection.execute(sql, q)

支持所有 DBAPI 参数风格。不限于 DBAPI 兼容驱动程序，可用于任何以某种方式接受原始 SQL 查询和参数的场景。例如，sqlbind 可用于 SQLAlchemy 文本查询。或与 clickhouse-driver 的非 DBAPI 接口一起使用。

安装

pip install sqlbind

动机

ORM 很棒，可以有效地用于大量任务。但在使用 SQLAlchemy 多年后，我注意到一些重复出现的模式：

将复杂的 SQLAlchemy 表达式解码回 SQL 确实不是一项容易的任务。特别是当涉及 CTE、子查询、嵌套查询或自引用查询时。它组合得很好，但编写和阅读 SQLAlchemy 查询需要太多努力。对于新手来说，处理起来可能会很困难。
大多数报表查询已经足够大，不需要再bothered with ORM，而直接使用原始 SQL。这种 SQL 通常需要动态构造，并变成字符串操作的contraption。
对于一些任务，ORM 带来的开销太大，唯一的解决方案是降级到原始 DBAPI 连接和原始 SQL。
（个人小抱怨，请忽略）对于某些 ORM（如 Django ORM），你的 SQL 直觉可能无用，需要深入了解 ORM。顺便说一句：sqlalchemy 混合属性，咳咳。

归根结底就是一件事：你时不时需要编写原始 SQL 查询。我可以强调 3 种类型的查询：

固定查询。它们不包含任何参数。例如 SELECT id, name FROM users ORDER BY registered DESC LIMIT 10。通常，固定查询或固定查询部分组合得很好，不需要任何特殊处理。Python 的 f-strings 就足够了。
静态查询。它们包含参数，但结构是预先完全已知的。例如 SELECT id, name FROM users WHERE email = :email LIMIT 1。它们也可以在没有大问题的情况下组合，特别是对于支持命名参数（:param、%(param)s）并接受字典作为参数的连接驱动程序。尽管对于位置连接驱动程序（%s、?），组合需要仔细跟踪参数，查询可能容易改变。
动态查询。查询部分的存在可能取决于参数值或外部条件。例如，要根据输入过滤器提供结果，你必须向查询添加 CTE 和相应的 JOIN。或者只为非 None 输入值添加过滤器。ORM 对组合这种查询很有效。使用原始 SQL 几乎不可能进行抽象，并导致复杂的样板代码。

注意：这里和以下部分我故意使用简单的例子。在实际生活中，不需要为这种查询使用 sqlbind。

注意：这里所说的组合，我指的是能够从可以抽象和重用的部分组装最终查询。

sqlbind 试图解决静态和动态查询类型的问题。它跟踪参数绑定，可以帮助处理动态查询部分。

快速开始

需要考虑的一些事项：

sqlbind 试图为原始 SQL 的简单组合提供 API。大多数操作返回可以插入最终查询的类字符串对象。 sqlbind 做的是琐碎的事情，很容易理解。
有一大组函数/方法来处理动态查询，但你不必在单个查询字符串中内联使用它。你可以使用变量来保存查询部分，并从这些部分拼接生成的 SQL。
本 README 缺少大部分 API。欢迎探索带有完全严格类型提示的 sqlbind 源代码的文档字符串和示例！

一般用例看起来像这样：

# 连接后端使用的方言的全局别名，参见 `sqlbind.Dialect`
QParams = sqlbind.Dialect.some_dialect

def get_my_data(value1, value2):
    # 构造空的新 sqlbind.QueryParams
    q = QParams()

    # 使用 `q` 在 SQL 字符串中绑定参数值。
    sql = f'SELECT * FROM table WHERE field1 = {q/value1} AND field2 > {q/value2}'

    # 将查询和参数传递给连接的 execute。
    return get_connection().execute(sql, q).fetchall()

静态查询

对于结构已知的查询或查询部分，绑定参数最简单的方法是使用绑定运算符 /：

>>> date = "2023-01-01"
>>> q = sqlbind.Dialect.default()
>>> f'SELECT * FROM users WHERE registered > {q/date}'
'SELECT * FROM users WHERE registered > ?'
>>> q
['2023-01-01']

或者对于命名风格参数：

>>> date = "2023-01-01"
>>> q = sqlbind.Dialect.default_named()
>>> f'SELECT * FROM users WHERE registered > {q/date}'
'SELECT * FROM users WHERE registered > :p0'
>>> q
{'p0': '2023-01-01'}

这里没有任何魔法。绑定运算符返回一个包含相应方言占位符的字符串，并将参数的值添加到 q 对象中。就这样。 q 对象继承自 dict 或 list，具体取决于使用的方言。

>>> value = 10
>>> q = sqlbind.Dialect.default()
>>> q/value
'?'
>>> q
[10]

注意：如果你只有静态查询并使用接受命名参数的连接后端，sqlbind 没有太多价值。

动态查询

这里开始有趣的部分。我们不能为动态查询使用简单的绑定。例如，我们有一个返回最近注册用户的函数：

def get_fresh_users(registered_since: datetime):
    q = QParams()  # 构造 sqlbind.QueryParams 实例的某个方言的别名
    sql = f'''\
        SELECT * FROM users
        WHERE registered > {q/registered_since}
        ORDER BY registered
    '''
    return connection.execute(sql, q)

后来，该函数有了新的需求。如果传递相应的参数，它应该只返回启用或只返回禁用的用户。

def get_fresh_users(registered_since: datetime, enabled: Optional[bool] = None):
    q = QParams()

    if enabled is not None:
        enabled_filter = f' AND enabled = {q/enabled}'
    else:
        enabled_filter = ''
这段代码看起来几乎很漂亮。看看 `q/registered` 是如何帮助跟踪额外参数的。
但你可以预见我们接下来要做什么。再增加一两个额外的过滤条件,它就会变得一团糟。注意 `WHERE` 失去了两个过滤条件之间的 `AND`。

### q-模板

实际上,绑定操作符 `/` 是通用 **sqlbind** API 的一个语法糖,用于通过 q-模板绑定参数。

```python
>>> q = sqlbind.Dialect.default()
>>> q('field BETWEEN {} AND {}', 10, 20)
'field BETWEEN ? AND ?'
>>> q
[10, 20]

QueryParams q 对象也是一个可调用对象,接受带有 {} 占位符的模板和后续要替换的参数。q/value 等同于调用 q('{}', value)

>>> q/10
'?'
>>> q('{}', 10)
'?'

你可以使用 q-模板在复杂的 SQL 表达式中绑定参数。

条件语句

q.cond 可以根据某些条件将 q-模板渲染为空字符串。

>>> enabled = True
>>> q.cond(enabled is not None, ' AND enabled = {}', enabled)
' AND enabled = ?'
>>> enabled = None
>>> q.cond(enabled is not None, ' AND enabled = {}', enabled)
''

q.cond 是一个通用形式。为了消除重复(enabled is not None/enabled),当值在条件和参数值中都使用时,有两个常用情况的辅助函数:

q.not_none: 检查值是否不为 None。
q.truthy: 检查值是否为真(bool(value) is True)。not_empty 可以作为 truthy 的别名使用。

>>> enabled = True
>>> q.not_none(' AND enabled = {}', enabled)
' AND enabled = ?'
>>> enabled = None
>>> q.not_none(' AND enabled = {}', enabled)
''

让我们在函数中尝试一下:

def get_fresh_users(registered_since: datetime, enabled: Optional[bool] = None):
    q = QParams()

    enabled_filter = q.not_none(' AND enabled = {}', enabled)

    sql = f'''\
        SELECT * FROM users
        WHERE registered > {q/registered_since} {enabled_filter}
        ORDER BY registered
    '''
    return connection.execute(sql, q)

嗯。但实际上什么都没有改变。你可以用三元运算符 if/else 写出之前的代码,从语义角度来看它们是一样的。也许可以内联使用?

def get_fresh_users(registered_since: datetime, enabled: Optional[bool] = None):
    q = QParams()

    sql = f'''\
        SELECT * FROM users
        WHERE registered > {q/registered_since}
              {q.not_none(' AND enabled = {}', enabled)}
        ORDER BY registered
    '''
    return connection.execute(sql, q)

呃。这简直是个怪物。

AND 在一个晦涩表达式的中间。
q.not_none 和 enabled 相距很远,不明显它们是相关的。
表达式太长且杂乱。

让我们一步步解决这些问题。

`AND_`/`OR_` 前缀

前缀可以用相应的前缀渲染非空输入,否则渲染为空字符串。

>>> AND_('field1 > 1', 'field2 < 1')
'AND field1 > 1 AND field2 < 1'
>>> OR_('field1 > 1', 'field2 < 1')
'OR field1 > 1 OR field2 < 1'
>>> AND_(q.not_none('enabled = {}', True))
'AND enabled = ?'
>>> AND_(q.not_none('enabled = {}', None))
''

使用前缀的函数:

from sqlbind import AND_

def get_fresh_users(registered_since: datetime, enabled: Optional[bool] = None):
    q = QParams()

    sql = f'''\
        SELECT * FROM users
        WHERE registered > {q/registered_since}
              {AND_(q.not_none('enabled = {}', enabled))}
        ORDER BY registered
    '''
    return connection.execute(sql, q)

至少 AND 现在几乎在 SQL 结构中的正确位置了。

条件标记

条件标记 sqlbind.not_none/sqlbind.truthy/sqlbind.cond 允许通过 / 运算符将条件与值绑定:

>>> q('enabled = {}', sqlbind.not_none/10)
'enabled = ?'
>>> q('enabled = {}', sqlbind.not_none/None)
''

条件标记返回值本身或特殊的 UNDEFINED 对象。 UNDEFINED 参数强制表达式被渲染为空字符串。

sqlbind.not_none 如果值为 None 则返回 UNDEFINED:

>>> sqlbind.not_none/10
10
>>> sqlbind.not_none/None is sqlbind.UNDEFINED
True

sqlbind.truthy 或 sqlbind.not_empty 如果 bool(value) != True 则返回 UNDEFINED:

>>> sqlbind.truthy/10
10
>>> sqlbind.not_empty/10
10
>>> sqlbind.truthy/0 is sqlbind.UNDEFINED
True

sqlbind.cond 如果条件为 False 则返回 UNDEFINED:

>>> sqlbind.cond(True)/10
10
>>> sqlbind.cond(False)/10 is sqlbind.UNDEFINED
True

注意: sqlbind.cond 在实际使用中几乎总是不方便内联使用,它的存在主要是为了与 q.cond 保持对称。

重写的函数:

from sqlbind import AND_, not_none

def get_fresh_users(registered_since: datetime, enabled: Optional[bool] = None):
    q = QParams()

    sql = f'''\
        SELECT * FROM users
        WHERE registered > {q/registered_since}
              {AND_(q('enabled = {}', not_none/enabled))}
        ORDER BY registered
    '''
    return connection.execute(sql, q)

差不多了。也许有办法减少 AND_ 内部的引号数量?

q-表达式

q-表达式允许使用中缀运算符生成模板化结果。

对 q 对象的任何未知属性访问都会返回 QExpr,其字符串转换为属性名:

>>> str(q.field)
'field'
>>> str(q.table.field)
'table.field'

q 本身有一些属性,其名称可能与现有的数据库表/列冲突。要解决冲突,你可以使用 q._. (凝视)表达式:

>>> str(q._.cond)
'cond'

实际的数据库表/列可能使用相当特殊的名称。你可以使用 q._ (海盗)表达式从任何字符串构造 QExpr:

>>> str(q._('"weird table"."weird column"'))
'"weird table"."weird column"'

QExpr 对象了解父级 q 对象，并定义了一组中缀运算符，允许绑定右值：

>>> q.field > 10
'field > ?'
>>> q.table.field == 20
'table.field = ?'
>>> q._.table.field == None
'table.field IS NULL'
>>> q._('"my column"') != None
'"my column" IS NOT NULL'
>>> q.field <= not_none/None  # 条件标记也适用！
''
>>> q.field.IN(not_none/[10]) # 顺便说一下，sqlbind 有解决方案处理 SQLite 中的 IN 数组
'field IN ?'

这可能看起来像是一种黑科技，感觉类似 ORM，但实际上没有任何表达式树和树编译过程。q 表达式立即被渲染为字符串，易于理解。

此外，操作集合非常小，仅包括比较和 QExpr.IN。

让我们在函数中使用 q 表达式：

from sqlbind import AND_, not_none

def get_fresh_users(registered_since: datetime, enabled: Optional[bool] = None):
    q = QParams()

    sql = f'''\
        SELECT * FROM users
        WHERE registered > {q/registered_since}
              {AND_(q.enabled == not_none/enabled)}
        ORDER BY registered
    '''
    return connection.execute(sql, q)

我没有其他技巧了。这是最终的内联版本。我无法使其更漂亮或可读。确实，内联表达式看起来有点杂乱，为了使其可管理，尽量提取尽可能多的逻辑，只使用 not_none 条件标记。

我的观点是，与其这样：

>>> now = None
>>> show_only_enabled = True
>>> f'SELECT * FROM users WHERE registered > {q/((now or datetime.utcnow()) - timedelta(days=30))} {AND_(q.enabled == cond(show_only_enabled)/1)}'
'SELECT * FROM users WHERE registered > ? AND enabled = ?'

不如考虑使用：

>>> now = None
>>> show_only_enabled = True
>>> registered_since = (now or datetime.utcnow()) - timedelta(days=30)
>>> enabled = 1 if show_only_enabled else None
>>> f'SELECT * FROM users WHERE registered > {q/registered_since} {AND_(q.enabled == not_none/enabled)}'
'SELECT * FROM users WHERE registered > ? AND enabled = ?'

此外，还可以通过 WHERE 预置器在外部构造过滤器。

WHERE 预置器

使用 sqlbind.WHERE 预置器从 f 字符串中提取过滤器可能会很有用。这有助于提高长复杂过滤器的可读性。

from sqlbind import not_none, WHERE

def get_fresh_users(registered_since: datetime, enabled: Optional[bool] = None):
    q = QParams()

    filters = [
        q.registered > registered_since,
        q.enabled == not_none/enabled,
    ]

    sql = f'SELECT * FROM users {WHERE(*filters)} ORDER BY registered'
    return connection.execute(sql, q)

还有其他预置器：WITH、LIMIT、OFFSET、GROUP_BY、ORDER_BY、SET。它们都会省略空部分，如果所有部分都为空，则渲染为空字符串。

你还可以使用 & 运算符连接过滤器，组装条件表达式而无需使用列表：

>>> filters = (q.registered > '2023-01-01') & (q.enabled == not_none/True)
>>> WHERE(filters)
'WHERE (registered > ? AND enabled = ?)'

— "等一下。这是怎么工作的？你说过没有表达式树和编译！所有操作都返回字符串！"

表达式

好吧，从技术上讲，它们确实是字符串。几乎所有方法和函数都返回 sqlbind.Expr。它是 str 的一个非常浅层的派生类，只重写了 __or__、__and__ 和 __invert__。

>>> q('enabled') & q('registered')
'(enabled AND registered)'
>>> type(q('enabled'))
<class 'sqlbind.Expr'>
>>> type(q.enabled == True)
<class 'sqlbind.Expr'>

所有 Expr 实例都可以用 &、| 和 ~（取反）操作进行组合。遗憾的是，由于 Python 的优先级规则，你必须用额外的括号包裹表达式才能使其正常工作。

结语

这是一个偏好和团队代码约定的问题。就我个人而言，我不认为内联表达式有什么问题。但对其他人来说，这可能是一个巨大的警告信号，这也没问题。sqlbind 提供了使用内联或非内联方法的选择。

但请注意，对于位置方言（如问号样式），非内联渲染有一个主要缺点。你应该注意部分的顺序。绑定和部分使用应该同步。例如：

>>> q = sqlbind.Dialect.default()
>>> filter1 = q.registered > '2023-01-01'
>>> filter2 = q.enabled == 1
>>> f'SELECT * FROM users WHERE {filter2} AND {filter1}'
'SELECT * FROM users WHERE enabled = ? AND registered > ?'
>>> q  # 参数顺序与占位符不匹配
['2023-01-01', 1]