Python SQL 查询字符串格式 [英] Python SQL query string formatting

问题描述

我正在尝试找到格式化 sql 查询字符串的最佳方法.我调试的时候我的应用程序我想记录所有的 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.

选项 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 查询字符串时，您会得到所有这些烦人的空格.

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

    u'\n选择 field1、field2、field3、field4\n_____从表中\n____where condition1=1 \n_____and condition2=2'

    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

  选项 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 字符串时的额外空格.

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

  你知道其他选择吗?

  推荐答案

  很抱歉在这么老的帖子上发帖——但作为一个对 Pythonic 'best' 也有热情的人，我想我会分享我们的解决方案.

  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.

  解决方案是使用 python 的 String Literal Concatenation (http://docs.python.org/)，可以限定在选项 2 和选项 4 之间的某个位置

  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

  代码示例:

  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 tabulated"格式，但不添加多余的空格字符(这会污染日志记录).
  2. 它避免了选项 4 的反斜杠延续丑陋，这使得添加语句变得困难(更不用说空白盲点了).
  3. 此外，在 VIM 中扩展语句非常简单(只需将光标定位到插入点，然后按 SHIFT-O 以打开新行).
  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).

  这篇关于Python SQL 查询字符串格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！