Logo
Project Icon

sqlbind

是一个Python库,用于简化原始SQL查询中的参数绑定

sqlbindSQL查询参数绑定Python库数据库操作Github开源项目

sqlbind是一个Python库,用于简化原始SQL查询中的参数绑定。它支持所有DBAPI参数样式,兼容各种SQL工具。该库提供了简洁的API用于组合SQL,处理静态和动态查询,并通过条件标记、q-表达式等功能优化复杂查询构建。sqlbind作为一个轻量级解决方案,适用于需要编写原始SQL的场景,有效提高了查询构建的效率和灵活性。

访问官网访问官网GithubGithub文档文档
介绍相关项目

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 种类型的查询：

  1. 固定查询。它们不包含任何参数。例如 SELECT id, name FROM users ORDER BY registered DESC LIMIT 10。 通常，固定查询或固定查询部分组合得很好，不需要任何特殊处理。Python 的 f-strings 就足够了。

  2. 静态查询。它们包含参数，但结构是预先完全已知的。 例如 SELECT id, name FROM users WHERE email = :email LIMIT 1。它们也可以在没有大问题的情况下组合， 特别是对于支持命名参数（:param、%(param)s）并接受字典作为参数的连接驱动程序。 尽管对于位置连接驱动程序（%s、?），组合需要仔细跟踪参数，查询可能容易改变。

  3. 动态查询。查询部分的存在可能取决于参数值或外部条件。例如，要根据输入过滤器提供结果，你必须向查询添加 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]

这是一个很大程度上人为的例子，但对于由多个部分组成的复杂查询，这可能是一个问题。为了减少这种情况发生的可能性，你可以抽象组合部分，以一种方式包含绑定和 SQL 构造，使其完全同步。

顺便说一下，你可能已经注意到，动态查询和条件中 get_fresh_users 的非内联变体存在相同的顺序错误：内联和非内联方法混合使用效果很差。如果你的连接后端允许，请始终使用命名样式方言。

相关项目
项目侧边栏1项目侧边栏2
推荐项目
Project Cover

豆包MarsCode

豆包 MarsCode 是一款革命性的编程助手，通过AI技术提供代码补全、单测生成、代码解释和智能问答等功能，支持100+编程语言，与主流编辑器无缝集成，显著提升开发效率和代码质量。

Project Cover

AI写歌

Suno AI是一个革命性的AI音乐创作平台，能在短短30秒内帮助用户创作出一首完整的歌曲。无论是寻找创作灵感还是需要快速制作音乐，Suno AI都是音乐爱好者和专业人士的理想选择。

Project Cover

有言AI

有言平台提供一站式AIGC视频创作解决方案，通过智能技术简化视频制作流程。无论是企业宣传还是个人分享，有言都能帮助用户快速、轻松地制作出专业级别的视频内容。

Project Cover

Kimi

Kimi AI助手提供多语言对话支持，能够阅读和理解用户上传的文件内容，解析网页信息，并结合搜索结果为用户提供详尽的答案。无论是日常咨询还是专业问题，Kimi都能以友好、专业的方式提供帮助。

Project Cover

阿里绘蛙

绘蛙是阿里巴巴集团推出的革命性AI电商营销平台。利用尖端人工智能技术，为商家提供一键生成商品图和营销文案的服务，显著提升内容创作效率和营销效果。适用于淘宝、天猫等电商平台，让商品第一时间被种草。

Project Cover

吐司

探索Tensor.Art平台的独特AI模型，免费访问各种图像生成与AI训练工具，从Stable Diffusion等基础模型开始，轻松实现创新图像生成。体验前沿的AI技术，推动个人和企业的创新发展。

Project Cover

SubCat字幕猫

SubCat字幕猫APP是一款创新的视频播放器，它将改变您观看视频的方式！SubCat结合了先进的人工智能技术，为您提供即时视频字幕翻译，无论是本地视频还是网络流媒体，让您轻松享受各种语言的内容。

Project Cover

美间AI

美间AI创意设计平台，利用前沿AI技术，为设计师和营销人员提供一站式设计解决方案。从智能海报到3D效果图，再到文案生成，美间让创意设计更简单、更高效。

Project Cover

AIWritePaper论文写作

AIWritePaper论文写作是一站式AI论文写作辅助工具，简化了选题、文献检索至论文撰写的整个过程。通过简单设定，平台可快速生成高质量论文大纲和全文，配合图表、参考文献等一应俱全，同时提供开题报告和答辩PPT等增值服务，保障数据安全，有效提升写作效率和论文质量。

使用协议隐私政策广告服务
投诉举报邮箱: service@vectorlightyear.com
@2024 懂AI·鲁ICP备2024100362号-6·鲁公网安备37021002001498号