当前位置： 文江博客话题详情

Python SQL 查询字符串格式

发布于 2024-10-20 18:20:05 字数 1609 浏览 2 评论 0原文

我正在尝试找到格式化 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'\n从表格中选择字段 1、字段 2、字段 3、字段 4\n_____\n_ ___其中条件1=1 \n_____且条件2=2'

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

选项3

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 字符串时出现额外的空格。

您知道还有其他选择吗？

原文

I'm trying to find the best way to format an sql query string. When I'm debugging
my application I'd like to log to file all the sql query strings, and it is
important that the string is properly formated.

Option 1

def myquery():
    sql = "select field1, field2, field3, field4 from table where condition1=1 and condition2=2"
    con = mymodule.get_connection()
    ...
  • This is good for printing the sql string.
  • It is not a good solution if the string is long and not fits the standard width
    of 80 characters.

Option 2

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

  • Here the code is clear but when you print the sql query string you get all these annoying white spaces.

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

Note: I have replaced white spaces with underscore _, because they are trimmed by the editor

Option 3

def query():
    sql = """select field1, field2, field3, field4
from table
where condition1=1
and condition2=2"""
    con = mymodule.get_connection()
    ...
  • I don't like this option because it breaks the clearness of the well tabulated code.

Option 4

def query():
    sql = "select field1, field2, field3, field4 " \
          "from table " \
          "where condition1=1 " \
          "and condition2=2 "
    con = mymodule.get_connection()    
    ...
  • I don't like this option because all the extra typing in each line
    and is difficult to edit the query also.

For me the best solution would be Option 2 but I don't like the extra whitespaces when I print the sql string.

Do you know of any other options?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（16）

十年九夏 2024-10-27 18:20:06

这是@aandis 答案的稍微修改版本。当涉及原始字符串时，请在字符串前添加前缀“r”字符。例如：

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

当您的查询包含任何特殊字符（例如“\”）需要转义并且像 flake8 这样的 lint 工具将其报告为错误时，建议这样做。

This is slightly modified version of @aandis answer. When it comes to raw string, prefix 'r' character before the string. For example:

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

This is recommended when your query has any special character like '\' which requires escaping and lint tools like flake8 reports it as error.

回复 原文
余生共白头 2024-10-27 18:20:06

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

多行字符串不与其余部分的缩进一起流动
程序。如果需要避免在字符串中嵌入额外的空格，请使用
连接的单行字符串或多行字符串
textwrap.dedent() 删除每行的初始空格：

<前><代码> 是：
导入文本换行

long_string = textwrap.dedent("""\
这也很好，因为 textwrap.dedent()
将折叠每行中的公共前导空格。""")

字符串可以用一对匹配的三引号括起来：""" 或 '' '。
使用三引号时，行尾不需要转义，但它们将包含在字符串中。
通过在行尾添加 \ 可以防止行尾字符。

下面使用一种转义来避免不需要的初始空行。

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

repr(sql)：

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

The Google style guide: 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.""")

Strings can be surrounded in a pair of matching triple-quotes: """ or '''.
End of lines do not need to be escaped when using triple-quotes, but they will be included in the string.
It is possible to prevent the end of line character by adding a \ at the end of the line.

The following uses one escape to avoid an unwanted initial blank line.

example = """\
    SELECT FROM"""

So option 2 modified:

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'
回复 原文
路弥 2024-10-27 18:20:06

如果您的应用程序使用多个 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)

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

参考：

还有另一个库可以提供帮助 - JinjaSQL：

希望有帮助，
谢谢！

If your application uses more than one SQL query, I would also add one more option: templating with Jinja2.

It helps to keep my code clean from SQL strings, but would be an overkill if your application only uses a simple SQL query.

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

The config is a dictionary, that could be derived from a file, e.g. YAML, JSON, etc.

In 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 -%}

For formatting, if you find difficult with Jinja2, you can use the sqlparse library. However, you probably can format only with Jinja2 if you keep tweaking the whitespace control. That said, here is a sqlparse example:

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

References:

There is also another library that could help - JinjaSQL:

Hope that helps,
Thanks!

回复 原文
你的心境我的脸 2024-10-27 18:20:06

textwrap.dedent 不处理嵌套缩进或行尾。这有效：

re.sub(r' ?\n', ' ', re.sub(r' {2,}', '', f'''
    select 
        x, 
        y
    from zzz
    where 
        a = 'a' 
        and (
            b = 'b' 
            or c = 'c' 
        )
    group by 
        d, 
        e;
''')).strip()

删除所有行尾字符以及任何出现的 2 个或更多空格。
结果：

"select x, y from zzz where a = 'a' and ( b = 'b' or c = 'c' ) group by d, e;"

如果你要经常使用它，最好编译正则表达式

textwrap.dedent doesn't handle nested indents nor end of line. This worked:

re.sub(r' ?\n', ' ', re.sub(r' {2,}', '', f'''
    select 
        x, 
        y
    from zzz
    where 
        a = 'a' 
        and (
            b = 'b' 
            or c = 'c' 
        )
    group by 
        d, 
        e;
''')).strip()

Removes all end of line characters and any occurrence of whitespace of 2 or more.
Result:

"select x, y from zzz where a = 'a' and ( b = 'b' or c = 'c' ) group by d, e;"

If you're gonna use it a lot, better compile the regexp though

回复 原文
柠栀 2024-10-27 18:20:06

您可以将字段名称放入数组“fields”中，然后：


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

you could put the field names into an array "fields", and then:


sql = 'select %s from table where condition1=1 and condition2=2' % (
 ', '.join(fields))
回复 原文
番薯 2024-10-27 18:20:06

我建议坚持使用选项 2（我总是使用它来进行比 SELECT * FROM table 更复杂的查询），如果您想以良好的方式打印它，您可以始终使用 单独的模块。

I would suggest sticking to option 2 (I'm always using it for queries any more complex than SELECT * FROM table) and if you want to print it in a nice way you may always use a separate module.

回复 原文
余生一个溪 2024-10-27 18:20:06

对于可以容纳一两行的简短查询，我使用上面投票最高的解决方案中的字符串文字解决方案。对于较长的查询，我将它们分解为 .sql 文件。然后，我使用包装函数来加载文件并执行脚本，例如：

script_cache = {}
def execute_script(cursor,script,*args,**kwargs):
    if not script in script_cache:
        with open(script,'r') as s:
            script_cache[script] = s
    return cursor.execute(script_cache[script],*args,**kwargs)

当然，这通常位于类内部，因此我通常不必显式传递 cursor 。我通常也使用 codecs.open()，但这可以了解总体思路。然后，SQL 脚本完全独立于自己的文件中，并具有自己的语法突出显示。

For short queries that can fit on one or two lines, I use the string literal solution in the top-voted solution above. For longer queries, I break them out to .sql files. I then use a wrapper function to load the file and execute the script, something like:

script_cache = {}
def execute_script(cursor,script,*args,**kwargs):
    if not script in script_cache:
        with open(script,'r') as s:
            script_cache[script] = s
    return cursor.execute(script_cache[script],*args,**kwargs)

Of course this often lives inside a class so I don't usually have to pass cursor explicitly. I also generally use codecs.open(), but this gets the general idea across. Then SQL scripts are completely self-contained in their own files with their own syntax highlighting.

回复 原文
皓月长歌 2024-10-27 18:20:06

为了避免完全格式化，我认为一个很好的解决方案是使用 程序。

调用过程会为您提供您想要放入此过程中的任何查询的结果。您实际上可以在一个过程中处理多个查询。该调用将仅返回调用的最后一个查询。

MYSQLPython

DROP PROCEDURE IF EXISTS example;
 DELIMITER //
 CREATE PROCEDURE example()
   BEGIN
   SELECT 2+222+2222+222+222+2222+2222 AS this_is_a_really_long_string_test;
   END //
 DELIMITER;

#calling the procedure gives you the result of whatever query you want to put in this procedure. You can actually process multiple queries within a procedure. The call just returns the last query result
 call example;

​

sql =('call example;')

To avoid formatting entirely, I think a great solution is to use procedures.

Calling a procedure gives you the result of whatever query you want to put in this procedure. You can actually process multiple queries within a procedure. The call will just return the last query that was called.

MYSQL

DROP PROCEDURE IF EXISTS example;
 DELIMITER //
 CREATE PROCEDURE example()
   BEGIN
   SELECT 2+222+2222+222+222+2222+2222 AS this_is_a_really_long_string_test;
   END //
 DELIMITER;

#calling the procedure gives you the result of whatever query you want to put in this procedure. You can actually process multiple queries within a procedure. The call just returns the last query result
 call example;

Python

sql =('call example;')
回复 原文
梦纸 2024-10-27 18:20:06

只是为基本用户分享字符串格式化的快速实践！
您应该在数据科学项目中使用哪种 Python 字符串格式化方法？

Just sharing a quick hands on string formatting for basic users!
Which Python String Formatting Method Should You Be Using in Your Data Science Project?

回复 原文
生来就爱笑 2024-10-27 18:20:06
sql = """\
select field1, field2, field3, field4
from table
where condition1=1
and condition2=2
"""

[编辑回复评论]
在方法中包含 SQL 字符串并不意味着您必须对其进行“制表”：

>>> class Foo:
...     def fubar(self):
...         sql = """\
... select *
... from frobozz
... where zorkmids > 10
... ;"""
...         print sql
...
>>> Foo().fubar()
select *
from frobozz
where zorkmids > 10
;
>>>

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

[edit in responese to comment]
Having an SQL string inside a method does NOT mean that you have to "tabulate" it:

>>> class Foo:
...     def fubar(self):
...         sql = """\
... select *
... from frobozz
... where zorkmids > 10
... ;"""
...         print sql
...
>>> Foo().fubar()
select *
from frobozz
where zorkmids > 10
;
>>>
回复 原文
听不够的曲调 2024-10-27 18:20:05

很抱歉发帖到这么旧的线程 - 但作为一个也对 pythonic“最佳”充满热情的人，我想我应该分享我们的解决方案。

解决方案是使用 python 的字符串文字连接（http:// docs.python.org/），可以在选项 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 'well制表格式，但不添加无关的空格字符（这会污染日志记录）。
  2. 它避免了选项 4 的反斜杠延续丑陋，这使得添加语句变得困难（更不用说空白盲区）。
  3. 此外，在 VIM 中扩展语句非常简单（只需将光标定位到插入点，然后按 SHIFT-O 打开新行）。

Sorry for posting to such an old thread -- but as someone who also shares a passion for pythonic 'best', I thought I'd share our solution.

The solution is to build SQL statements using python's String Literal Concatenation (http://docs.python.org/), which could be qualified a somewhere between Option 2 and Option 4

Code Sample:

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

Works as well with 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};")

Pros:

  1. It retains the pythonic 'well tabulated' format, but does not add extraneous space characters (which pollutes logging).
  2. It avoids the backslash continuation ugliness of Option 4, which makes it difficult to add statements (not to mention white-space blindness).
  3. And further, it's really simple to expand the statement in VIM (just position the cursor to the insert point, and press SHIFT-O to open a new line).
回复 原文
揽月 2024-10-27 18:20:05

显然，您已经考虑了很多编写 SQL 的方法，以便它可以正常打印，但是如何更改用于调试日志记录的“打印”语句，而不是以您不喜欢的方式编写 SQL？使用上面您最喜欢的选项，像这样的日志记录功能怎么样：

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)

如果行比您期望的长度长，这也将使添加额外的逻辑将记录的字符串拆分为多行变得微不足道。

You've obviously considered lots of ways to write the SQL such that it prints out okay, but how about changing the 'print' statement you use for debug logging, rather than writing your SQL in ways you don't like? Using your favourite option above, how about a logging function such as this:

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)

This would also make it trivial to add additional logic to split the logged string across multiple lines if the line is longer than your desired length.

回复 原文
忆梦 2024-10-27 18:20:05

我遇到的最简洁的方法是受到 sql 样式指南 的启发。

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

本质上，子句开头的关键字应该右对齐，字段名称等应该左对齐。这看起来非常整洁，也更容易调试。

Cleanest way I have come across is inspired by the sql style guide.

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

Essentially, the keywords that begin a clause should be right-aligned and the field names etc, should be left aligned. This looks very neat and is easier to debug as well.

回复 原文
拿命拼未来 2024-10-27 18:20:05

使用“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;

参考：https://pypi.org/project/sqlparse/

Using 'sqlparse' library we can format the sqls.

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

回复 原文
寂寞陪衬 2024-10-27 18:20:05

您可以使用inspect.cleandoc来很好地格式化打印的SQL语句。

这非常适合您的选项 2。

注意：如果不使用 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)

清除文档字符串中的缩进，这些缩进与代码块对齐。

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

You can use inspect.cleandoc to nicely format your printed SQL statement.

This works very well with your option 2.

Note: the print("-"*40) is only to demonstrate the superflous blank lines if you do not use cleandoc.

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

Output:

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

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

From the docs:

inspect.cleandoc(doc)

Clean up indentation from docstrings that are indented to line up with blocks of code.

All leading whitespace is removed from the first line. Any leading whitespace that can be uniformly removed from the second line onwards is removed. Empty lines at the beginning and end are subsequently removed. Also, all tabs are expanded to spaces.

回复 原文
攒眉千度 2024-10-27 18:20:05
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'"

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'

if the value of condition should be a string, you can do like this:

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'"
回复 原文
~没有更多了~

关于作者

三岁铭

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

烙印

文章 0 评论 0

singlesman

文章 0 评论 0

给自己一个微笑

文章 0 评论 0

独孤求败

文章 0 评论 0

晨钟暮鼓

文章 0 评论 0

我是自愿种绣球花的

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文