Python SQL查询字符串格式化

Question

我正在尝试找到最佳的方法来格式化SQL查询字符串。当我调试应用程序时，我想将所有SQL查询字符串记录到文件中，并且重要的是该字符串被正确地格式化。

选项1

def myquery():
    sql = "select field1, field2, field3, field4 from table where condition1=1 and condition2=2"
    con = mymodule.get_connection()
    ...

• 这很适合打印SQL字符串。
• 如果字符串很长且不符合80个字符的标准宽度，则这不是一个好的解决方案。

选项2

def query():
    sql = """
        select field1, field2, field3, field4
        from table
        where condition1=1
        and condition2=2"""
    con = mymodule.get_connection()
    ...

• 代码很清晰，但是当你打印SQL查询字符串时，会出现许多烦人的空格。

  u'\nselect field1, field2, field3, field4\n_____from table\n____where condition1=1 \n_____and condition2=2'

注意：我已经将空格替换为下划线_，因为它们被编辑器修剪掉了。

选项三

def query():
    sql = """select field1, field2, field3, field4
from table
where condition1=1
and condition2=2"""
    con = mymodule.get_connection()
    ...

• 我不喜欢这个选项，因为它会破坏代码的清晰度。

选项4

def query():
    sql = "select field1, field2, field3, field4 " \
          "from table " \
          "where condition1=1 " \
          "and condition2=2 "
    con = mymodule.get_connection()    
    ...

• 我不喜欢这个选项，因为每一行都需要额外输入，而且编辑查询也很困难。

对我来说，最好的解决方案是选项2，但我不喜欢在打印SQL字符串时出现额外的空格。

你知道其他的选择吗？

- ssoler

---

Psycopg的开发者称这是一种天真的查询字符串组合方式，例如使用字符串拼接(http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters)。相反，应该使用查询参数来避免SQL注入攻击，并自动将Python对象转换为SQL文字值，反之亦然(https://dev59.com/gXA75IYBdhLWcg3wubun?rq=1#comment24606225_3134691)。 - Matthew Cornell

这个问题实际上不仅限于SQL查询，而是通常适用于在Python中格式化多行字符串。应该删除SQL标签。 - cstork

选项2 - 三引号和使用内置的 textwrap.dedent 来输出到 SQL 文件或调试日志。 - Davos

Answer 1

很抱歉在这个旧帖子上发布 -- 但作为一个同样热衷于Pythonic编程的人，我想分享我们的解决方案。

解决方案是使用Python的字符串文本串联(http://docs.python.org/)来构建SQL语句，这可以被归类为选项2和选项4之间的某个地方。

代码示例：

sql = ("SELECT field1, field2, field3, field4 "
       "FROM table "
       "WHERE condition1=1 "
       "AND condition2=2;")

使用 f-strings 同样有效：

fields = "field1, field2, field3, field4"
table = "table"
conditions = "condition1=1 AND condition2=2"

sql = (f"SELECT {fields} "
       f"FROM {table} "
       f"WHERE {conditions};")

优点：

1. 保留了Pythonic的“良好表格”格式，但不会添加多余的空格字符（这会污染日志记录）。
2. 避免了选项4中倒斜杠续行的丑陋，这使得添加语句变得困难（更不用说对空格的盲目处理）。
3. 而且，在VIM中扩展该语句非常简单（只需将光标定位到插入点，然后按下SHIFT-O即可打开新行）。

- user590028

---

如果这是用于打印的，我认为更好的选择是使用"""编写多行字符串，并在输出之前使用textwrap.dedent()。 - salezica

我尝试过使用该选项，但它也使日志输出变成了多行。当跟踪一个频繁访问数据库的应用程序时，这会导致大量输出。 - user590028

这是一个旧的讨论串，但我一直将其作为最佳实践进行使用。然而，在查询较长时，这种格式会变得繁琐。 - Jabda

为避免与标准使用单引号的 SQL 字符串混淆，我们是否应该始终使用双引号 "sql query"？ - tpvasconcelos

@Jabda 我同意。我写了很多长查询并将它们存储在一个单独的文件夹中（以防我在文件之间重复使用主要结构）。你是否找到了处理更长查询的解决方法？或者你只是在函数内部编写整个SQL语句？ - mitchell-reynolds

如何格式化源代码是一个有效的问题，但在SQL上下文中，应该明确提到正确转义变量的重要性。使用字符串格式化准备SQL语句是不安全的，这一点应该特别强调。 - schlimmchen

Answer 2

你显然考虑了很多写SQL的方式使其正确输出，但是换一种方式，改变你用于调试日志记录的“print”语句怎么样？ 使用上面提到的您喜欢的选项，可以使用以下记录函数：

def debugLogSQL(sql):
     print ' '.join([line.strip() for line in sql.splitlines()]).strip()

sql = """
    select field1, field2, field3, field4
    from table"""
if debug:
    debugLogSQL(sql)

如果这样做的话，如果行长超过你所需的长度，也很容易添加额外的逻辑来将记录的字符串拆分成多行。

- cdlk

Answer 3

我找到的最干净的方法受到了SQL风格指南的启发。

sql = """
    SELECT field1, field2, field3, field4
      FROM table
     WHERE condition1 = 1
       AND condition2 = 2;
"""

基本上，作为一个从句开头的关键词应该右对齐，而字段名称等则应左对齐。这样看起来非常整洁，而且更容易调试。

- aandis

---

我建议在原始 SQL 字符串前加上 'r' 前缀。请参见下面我的回答中的详细信息。 - akhi

Answer 4

通过使用 'sqlparse' 库，我们可以格式化 SQL。

>>> import sqlparse
>>> raw = 'select * from foo; select * from bar;'
>>> print(sqlparse.format(raw, reindent=True, keyword_case='upper'))
SELECT *
FROM foo;

SELECT *
FROM bar;

Ref: https://pypi.org/project/sqlparse/

- Fazal S

Answer 5

你可以使用 inspect.cleandoc 来美化打印的 SQL 语句。

这个方法和你的第二个选项配合得非常好。

注意：如果不使用 cleandoc，则 print("-"*40) 可能会产生多余的空行。

from inspect import cleandoc
def query():
    sql = """
        select field1, field2, field3, field4
        from table
        where condition1=1
        and condition2=2
    """

    print("-"*40)
    print(sql)
    print("-"*40)
    print(cleandoc(sql))
    print("-"*40)

query()

输出：

----------------------------------------

        select field1, field2, field3, field4
        from table
        where condition1=1
        and condition2=2

----------------------------------------
select field1, field2, field3, field4
from table
where condition1=1
and condition2=2
----------------------------------------

来自文档：

inspect.cleandoc(doc)

清除缩进：将与代码块对齐的文档字符串中的缩进进行清理。

第一行的所有前导空格都将被删除。可以从第二行开始统一删除的所有前导空格都将被删除。随后，开头和结尾的所有空行也将被删除。此外，所有制表符都会扩展为空格。

- Mike Scotty

Answer 6

sql = ("select field1, field2, field3, field4 "
       "from table "
       "where condition1={} "
       "and condition2={}").format(1, 2)

Output: 'select field1, field2, field3, field4 from table 
         where condition1=1 and condition2=2'

如果条件的值应该是一个字符串，你可以这样做：

sql = ("select field1, field2, field3, field4 "
       "from table "
       "where condition1='{0}' "
       "and condition2='{1}'").format('2016-10-12', '2017-10-12')

Output: "select field1, field2, field3, field4 from table where
         condition1='2016-10-12' and condition2='2017-10-12'"

- pangpang

---

绝对不要这样做。这被称为SQL注入，非常危险。几乎每个Python数据库库都提供了使用参数的功能。如果你发现自己在使用format()与SQL字符串一起时，请注意它是一个严重的代码问题。 - mattmc3

我认为我们不能不使用它，你必须在使用之前验证参数，并且你应该知道你传递了什么。 - pangpang

验证比仅使用“where condition1=:field1”然后将值作为参数传递更容易出错。如果您正在使用.format()，那么就会有一种方法可以将';DROP TABLE Users插入到您的SQL中。请参阅PEP-249，了解如何正确使用参数。https://www.python.org/dev/peps/pep-0249/#paramstyle - mattmc3

Answer 7

这是@aandis答案的稍微修改版本。对于原始字符串，需要在字符串前加上前缀'r'字符。例如：

sql = r"""
    SELECT field1, field2, field3, field4
      FROM table
     WHERE condition1 = 1
       AND condition2 = 2;
"""

当您的查询包含特殊字符（如'\'）需要转义时，建议使用此方法，并且像flake8这样的lint工具会将其报告为错误。

- akhi

Answer 8

Google风格指南：https://google.github.io/styleguide/pyguide#310-strings

Multi-line strings do not flow with the indentation of the rest of the program. If you need to avoid embedding extra space in the string, use either concatenated single-line strings or a multi-line string with textwrap.dedent() to remove the initial space on each line:

  Yes:
  import textwrap

  long_string = textwrap.dedent("""\
      This is also fine, because textwrap.dedent()
      will collapse common leading spaces in each line.""")

字符串可以用匹配的三引号对括起来："""或'''。 使用三引号时，行尾不需要转义，但会包含在字符串中。 可以通过在行末添加\来避免换行符。 以下示例使用一个转义符来避免不必要的初始空行。

example = """\
    SELECT FROM"""

选项2修改后：

import textwrap

def query():
    sql = textwrap.dedent("""\
        SELECT field1, field2, field3, field4
        FROM table
        WHERE condition1=1
        AND condition2=2""")
    con = mymodule.get_connection()
    ...

The repr(sql):

'SELECT field1, field2, field3, field4\nFROM table\nWHERE condition1=1\nAND condition2=2'

- The Demz

Answer 9

如果您的应用程序使用多个SQL查询，我还会添加一个选项：使用Jinja2进行模板化。

这有助于使我的代码不受SQL字符串的污染，但如果您的应用程序只使用简单的SQL查询，则可能过于复杂。

在Python中：

import jinja2
jinja_env = jinja2.Environment(loader=jinja2.FileSystemLoader("templates_folder_path/"), trim_blocks=True, lstrip_blocks=True)
template = jinja_env.get_template("sql_template.jinja2")
config = {"cols_keep": ["col1", "col2", "col3"], "from": "datasource", "where": {"equal_to": {'col1': 'value1', 'col2': 'value2'}}
query = template.render(config=config)

< p > config 是一个字典，可以从文件中派生出来，例如 YAML、JSON 等。

在 sql_template.jinja2 中

SELECT
{% for col in config.cols_keep -%}
    {{ col }}
    {%- if not loop.last -%}
        , 
    {% endif %}
{% endfor +%}
FROM {{ config.from }}
{# EQUAL #}
{% if config.where.equal_to %}
WHERE
    {% for col, value in config.where.equal_to.items() %}
        {% if value is string %}
            {{ col }} = "{{ value }}"
        {% else %}
            {{ col }} = {{ value }}
        {% endif %}
        {% if not loop.last %} AND {% endif %}
    {% endfor -%}
{% endif -%}

对于格式化，如果你在使用Jinja2时遇到困难，可以使用sqlparse库。然而，如果你不断调整空格控制，很可能只能使用Jinja2进行格式化。话虽如此，这里有一个sqlparse的示例：

import sqlparse
query_parsed = sqlparse.format(query, reindent=True, keyword_case='upper')

参考资料：

Jinja2 空格控制: https://jinja.palletsprojects.com/en/3.0.x/templates/#whitespace-control https://towardsdatascience.com/a-simple-approach-to-templated-sql-queries-in-python-adc4f0dc511 https://towardsdatascience.com/jinja-sql-️-7e4dff8d8778 https://medium.com/analytics-and-data/jinja-the-sql-way-of-the-ninja-9a64fc815564 https://pypi.org/project/sqlparse/ 还有另一个可以帮助的库 - JinjaSQL:

• https://github.com/sripathikrishnan/jinjasql

希望能有所帮助， 谢谢！

- vcmorini

Answer 10

你可以把字段名放进一个叫做“fields”的数组里，然后：

sql = 'select %s from table where condition1=1 and condition2=2' % (
 ', '.join(fields))

- jcomeau_ictx

---

如果你的条件列表增加了，你可以使用 ' and '.join(conditions) 来做同样的事情。 - jcomeau_ictx

使用您的解决方案，查询将比Option_4更难编辑，并且阅读起来也很困难。 - ssoler

@ssoler，这取决于一个人如何做事。我在我的程序中声明了很少的变量，并使用字符串数组代替，这使得像上面这样的方法非常有用且易于维护，至少对我来说是这样。 - jcomeau_ictx