我有一个很长的疑问。我想用Python将它分成几行。在JavaScript中实现这一点的一种方法是使用几个句子并用+运算符将它们连接起来(我知道,也许这不是最有效的方法,但我并不真正关心这个阶段的性能,只是代码的可读性)。例子:

var long_string = 'some text not important. just garbage to' +
                      'illustrate my example';

我尝试在Python中做类似的事情,但没有成功,所以我使用了\来拆分长字符串。然而,我不确定这是否是唯一/最好/最蟒蛇的做法。看起来很尴尬。实际代码:

query = 'SELECT action.descr as "action", '\
    'role.id as role_id,'\
    'role.descr as role'\
    'FROM '\
    'public.role_action_def,'\
    'public.role,'\
    'public.record_def, '\
    'public.action'\
    'WHERE role.id = role_action_def.role_id AND'\
    'record_def.id = role_action_def.def_id AND'\
    'action.id = role_action_def.action_id AND'\
    'role_action_def.account_id = ' + account_id + ' AND'\
    'record_def.account_id=' + account_id + ' AND'\
    'def_id=' + def_id

当前回答

我个人认为,以下是用Python编写原始SQL查询的最佳(简单、安全和Python化)方法,尤其是在使用Python的sqlite3模块时:

query = '''
    SELECT
        action.descr as action,
        role.id as role_id,
        role.descr as role
    FROM
        public.role_action_def,
        public.role,
        public.record_def,
        public.action
    WHERE
        role.id = role_action_def.role_id
        AND record_def.id = role_action_def.def_id
        AND action.id = role_action_def.action_id
        AND role_action_def.account_id = ?
        AND record_def.account_id = ?
        AND def_id = ?
'''
vars = (account_id, account_id, def_id)   # a tuple of query variables
cursor.execute(query, vars)   # using Python's sqlite3 module

Pros

整洁而简单的代码(Pythonic!)避免SQL注入与Python 2和Python 3兼容(毕竟是Pythonic)不需要字符串串联无需确保每行最右边的字符是空格

Cons

由于查询中的变量被替换为?占位符,它可能会变得有点难以跟踪哪个?当查询中有很多Python变量时,将由哪个Python变量替换。

其他回答

嗯。

我知道这个问题发布已经很久了。但我刚刚找到了我想用来为项目中的变量分配长字符串和多行字符串的样式。这需要一点额外的运行时间,但仍然保留了代码的美感,即使我将字符串分配给的变量缩进很大。

    # Suppose the following code is heavily indented
    line3header = "Third"

    variable = fr"""

First line.
Second line.
{line3header} line.
{{}} line.
...
The last line.

    """.strip()
    """A variable whose name is Variable.

    You can even add a docstring here.
    """

    variable = variable.format("Fourth")
    print(variable)
    variable += "\n"
    print(variable, end="")

就这样。

我使用递归函数来构建复杂的SQL查询。此技术通常可用于构建大型字符串,同时保持代码可读性。

# Utility function to recursively resolve SQL statements.
# CAUTION: Use this function carefully, Pass correct SQL parameters {},
# TODO: This should never happen but check for infinite loops
def resolveSQL(sql_seed, sqlparams):
    sql = sql_seed % (sqlparams)
    if sql == sql_seed:
        return ' '.join([x.strip() for x in sql.split()])
    else:
        return resolveSQL(sql, sqlparams)

P.S.:看看很棒的python-sqlparse库,如果需要,可以打印SQL查询。

结合以下观点:

Levon或Jesse、Faheel和ddrscott

根据我的格式建议,您可以将查询写成:

query = ('SELECT'
             ' action.descr as "action"'
             ',role.id as role_id'
             ',role.descr as role'
         ' FROM'
             ' public.role_action_def'
             ',public.role'
             ',public.record_def'
             ',public.action'
         ' WHERE'
             ' role.id = role_action_def.role_id'
             ' AND'
             ' record_def.id = role_action_def.def_id'
             ' AND'
             ' action.id = role_action_def.action_id'
             ' AND'
             ' role_action_def.account_id = ?' # account_id
             ' AND'
             ' record_def.account_id = ?'      # account_id
             ' AND'
             ' def_id = ?'                     # def_id
         )

 vars = (account_id, account_id, def_id)     # A tuple of the query variables
 cursor.execute(query, vars)                 # Using Python's sqlite3 module

或类似:

vars = []
query = ('SELECT'
             ' action.descr as "action"'
             ',role.id as role_id'
             ',role.descr as role'
         ' FROM'
             ' public.role_action_def'
             ',public.role'
             ',public.record_def'
             ',public.action'
         ' WHERE'
             ' role.id = role_action_def.role_id'
             ' AND'
             ' record_def.id = role_action_def.def_id'
             ' AND'
             ' action.id = role_action_def.action_id'
             ' AND'
             ' role_action_def.account_id = '
                 vars.append(account_id) or '?'
             ' AND'
             ' record_def.account_id = '
                 vars.append(account_id) or '?'
             ' AND'
             ' def_id = '
                 vars.append(def_id) or '?'
         )

 cursor.execute(query, tuple(vars))  # Using Python's sqlite3 module

与“IN”和“vars.extend(options)”或“n_options(len(option))”一起使用可能很有趣,其中:

def n_options(count):
    return '(' + ','.join(count*'?') + ')'

或者从darkcaline那里得到的提示是,您可能仍然会在使用前导空格和分隔符以及命名占位符时出错:

SPACE_SEP = ' '
COMMA_SEP = ', '
AND_SEP   = ' AND '

query = SPACE_SEP.join((
    'SELECT',
        COMMA_SEP.join((
        'action.descr as "action"',
        'role.id as role_id',
        'role.descr as role',
        )),
    'FROM',
        COMMA_SEP.join((
        'public.role_action_def',
        'public.role',
        'public.record_def',
        'public.action',
        )),
    'WHERE',
        AND_SEP.join((
        'role.id = role_action_def.role_id',
        'record_def.id = role_action_def.def_id',
        'action.id = role_action_def.action_id',
        'role_action_def.account_id = :account_id',
        'record_def.account_id = :account_id',
        'def_id = :def_id',
        )),
    ))

vars = {'account_id':account_id,'def_id':def_id}  # A dictionary of the query variables
cursor.execute(query, vars)                       # Using Python's sqlite3 module

请参阅Cursor.execute-function的文档。

“这是[最Python]的方式!”-。。。

例如:

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

你说的是多行字符串吗?简单,使用三引号开始和结束它们。

s = """ this is a very
        long string if I had the
        energy to type more and more ..."""

您也可以使用单引号(当然,在开始和结束处有3个引号),并像对待任何其他字符串一样对待生成的字符串。

注意:与任何字符串一样,起始引号和结束引号之间的任何内容都将成为字符串的一部分,因此本示例有一个前导空格(如@root45所指出的)。此字符串还将包含空格和换行符。

即。,:

' this is a very\n        long string if I had the\n        energy to type more and more ...'

最后,您还可以在Python中构造如下的长行:

 s = ("this is a very"
      "long string too"
      "for sure ..."
     )

这将不包括任何额外的空格或换行符(这是一个故意的示例,显示跳过空格的效果):

'this is a verylong string toofor sure ...'

不需要逗号,只需将要连接在一起的字符串放入一对括号中,并确保考虑到任何需要的空格和换行符。