SQL构造器

一种编程方法（和辅助函数），用于从Python 3.6及更高版本中编程构建和保持格式良好的SQL。

动机和哲学

与所有编程语言一样，良好的格式和布局使编写可维护的SQL变得更容易，从而减轻了程序员的认知负荷。例如，使用静态格式化工具进行完全静态的SQL是可能的，但在编程构建SQL时则更难实现。很容易在生成Python代码和生成的SQL中都丢失格式。哎呀。

此包旨在通过特定的编程模式和少量实用函数来解决这个问题。

具体目标包括

• 使SQL在源Python代码中看起来尽可能像SQL，同时仍然使用明显的Python来生成它。这涉及到权衡。
• 使生成的SQL看起来就像可以直接编写，以便尽可能容易地阅读和理解。
• 能够用SQL而不是从其他语言尝试编写最优的SQL，并在心理上思考目标SQL。

应该清楚的是，SQL构造器不是一个ORM。没有抽象。事实上，正好相反。

SQL变体

该库已被用于生成适用于PostgreSQL的SQL。然而，它应该能够与其它数据库引擎/语言变体以很少或没有变化的方式工作。同样，它对SQL代码样式规范不敏感，因为这由程序员控制。

实际上，整个想法根本不是关于SQL，而是关于管理文本。

示例

一个简单的示例将说明这种方法。从SQL的角度来看，子查询是不必要的；它纯粹用来展示组合。

import sqlcon

sq = sqlcon.single_quote
dq = sqlcon.double_quote


def select_columns(variables):
    yield sqlcon.joinwith(dq(v) for v in variables)


def subquery():
    yield """
        SELECT
            *
        FROM
            some_table
        LEFT JOIN
            some_other_table
        USING
            some_table.id = some_other_table.key
    """, -1


def where_clauses(variables, conditions):
    for condition in conditions:
        variable, comparator, constant = condition
        assert variable in variables, f"Unknown variable: {variable}"
        assert comparator in ("=", "~"), f"Unknown comparator: {comparator}"
        yield f"{dq(variable)} {comparator} {sq(constant)}"


def example(variables, conditions):
    yield """
        SELECT
    """
    yield 1, select_columns(variables), -1
    yield """
        FROM
            (
    """
    yield 1, subquery(), -1
    yield """
            ) AS tmp
        WHERE
    """
    yield sqlcon.indented_joinwith(
        where_clauses(variables, conditions), separator=" AND "
    )


if __name__ == "__main__":
    sql = example(
        ["name", "age", "address"],
        [("name", "=", "tim"), ("address", "~", "England")],
    )
    print(sqlcon.process(sql))

运行时，将产生

SELECT
    "name",
    "age",
    "address"
FROM
    (
        SELECT
            *
        FROM
            some_table
        LEFT JOIN
            some_other_table
        USING
            some_table.id = some_other_table.key
    ) AS tmp
WHERE
    "name" = 'tim' AND
    "address" ~ 'England'

处理过程需要字符串（实际的SQL）、整数（手动缩进更改）以及列表/元组/生成器（用于上述组合）。显然，这个示例相当退化。它还混合了几种风格，这是不一致的，但说明了几个不同的方法。

注意一些相对微妙的事情是如何自动发生的

1. 正在去除常见的缩进，以便将生成的SQL的底部对齐。
2. 正在智能地去除空白行。例如，三重引号字符串的开始和结束。
3. 正在跟踪缩进级别。例如，子查询在输出中被缩进，但在subquery()函数中没有被缩进。因此，嵌套层（如视图内的视图或视图内的PostgreSQL函数）可以整洁地编写，无需担心其包含作用域的缩进。

API

SQL构造函数 API由少数几个函数组成。主要函数是process；其他的是辅助的。

• process将SQL字符串、缩进整数以及作为可迭代对象的组合转换为输出SQL。
• single_quote将为PostgreSQL正确地引号化字面字符串。
• double_quote将为PostgreSQL正确地引号化标识符。
• joinwith将列表用逗号连接（例如，用于列标识符列表或"IN"子句）或用"AND"连接条件（例如，用于WHERE子句中的合取）。可以在单行或跨多行使用，同时保持缩进。
• indented_joinwith提供了一个简写，将缩进前缀和缩进后缀添加到joinwith。

请参阅源代码文档字符串以获取详细信息。

测试

请参阅tests/目录中的单元测试。

替代方案

在决定创建SQL构造函数之前，我在一些真实的项目中尝试了各种方法。最值得注意的是

• 使用纯Jinja2或为与SQL一起使用而设计的变体（如JinjaSQL）来模板化SQL。维护格式很困难，现在你需要在两个文件（Python和模板）和三种语言（模板化、Python和SQL）中工作。

• 使用ORM如SQLAlchemy。虽然表面上看起来很干净（“全是Python”），但任何比最简单的事情都需要精神体操，即思考SQL但在编写Python中，并且需要退出Python API以创建单独的SQL支持函数和视图等，以便充分利用数据库引擎。

这些方法对我来说都不奏效。

贡献

请分享您尝试过的其他方法。也许有更好的方法？！同样，欢迎pull-requests、错误报告等。

发布清单

• 运行：black .
• 运行：isort -y
• 运行：flake8 .
• 运行：nose2 -v --with-coverage tests
• 运行：poetry export -f requirements.txt >requirements.txt（用于snyk扫描）
• 确保git tag、软件包版本（通过poetry version）和sqlcon.__version__都是相等的！