python - Python 中的多个字符串插值

Question

我正在尝试构建一个 SQL 查询，该查询涉及同一字符串的两次单独插入，该字符串本身是通过用逗号连接列表(术语)来构造的。我这样做如下:

format_strings = ','.join(['%s'] * len(terms))
sql = """
SELECT i.id, i.title, i.description, MATCH (i.title, i.description) AGAINST (%s IN NATURAL LANGUAGE MODE) AS score
FROM items i
WHERE MATCH (i.title, i.description) AGAINST (%s IN NATURAL LANGUAGE MODE) AS score""" % format_strings, (terms, terms)

结果是TypeError:格式字符串没有足够的参数

这仅适用于术语的一次使用，但不适用于第二次。我对 Python 很陌生，所以这可能是非常简单的事情。非常感谢任何帮助!

编辑:术语来自于在包含空格的字符串上运行 .split()，因此 ('apples', 'oranges') 将是一个示例值。

更新:

以下结果会导致 SQL 错误，但看起来确实像是字符串没有被替换(SQL 中出现 %s):

terms = term.split()
format_strings = ','.join(['%s'] * len(terms))
sql = """SELECT i.id, i.title, i.description, MATCH (i.title, i.description) AGAINST (%s IN NATURAL LANGUAGE MODE) AS score
FROM items i
WHERE MATCH (i.title, i.description) AGAINST (%s IN NATURAL LANGUAGE MODE)""" % (format_strings, (terms, terms))
cursor.execute(sql)

输出:

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s IN NATURAL LANGUAGE MODE) AS score\tFROM items i\tWHERE MATCH (i.title, i.descr' at line 1")

Answer 1

在格式化查询时，您需要传递 format_strings 的两个实例，或者更好的是，使用 str.format()并引用参数两次:

sql = """
SELECT i.id, i.title, i.description, MATCH (i.title, i.description)
  AGAINST ({0} IN NATURAL LANGUAGE MODE) AS score
FROM items i
WHERE MATCH (i.title, i.description) AGAINST ({0} IN NATURAL LANGUAGE MODE)
  AS score""".format(format_strings)

然后将参数传递给cursor.execute():

cursor.execute(sql, terms*2)