如何格式化字符串以在 Python 中使用 mysqldb 进行查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2538311/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I format strings to query with mysqldb in Python?
提问by Daniel
How do I do this correctly:
我该如何正确执行此操作：
I want to do a query like this:
我想做这样的查询：
query = """SELECT * FROM sometable
order by %s %s
limit %s, %s;"""
conn = app_globals.pool.connection()
cur = conn.cursor()
cur.execute(query, (sortname, sortorder, limit1, limit2) )
results = cur.fetchall()
All works fine but the order by %s %s is not putting the strings in correctly. It is putting the two substitutions in with quotes around them.
一切正常，但 %s %s 的顺序没有正确放入字符串。它将两个替换放在它们周围的引号中。
So it ends up like:
所以它最终像：
ORDER BY 'somecol' 'DESC'Which is wrong should be:
哪个错误应该是：
ORDER BY somecol DESCAny help greatly appreciated!
非常感谢任何帮助！
回答by SilentGhost
回答by Yaroslav
%s placeholders inside query string are reserved for parameters. %s in 'order by %s %s' are not parameters. You should make query string in 2 steps:
查询字符串中的 %s 占位符是为参数保留的。'order by %s %s' 中的 %s 不是参数。您应该分两步制作查询字符串：
query = """SELECT * FROM sometable order by %s %s limit %%s, %%s;"""
query = query % ('somecol', 'DESC')
conn = app_globals.pool.connection()
cur = conn.cursor()
cur.execute(query, (limit1, limit2) )
results = cur.fetchall()
DO NOT FORGET to filter first substitution to prevent SQL-injection possibilities
不要忘记过滤第一次替换以防止 SQL 注入的可能性
回答by unutbu
Not all parts of an SQL query can be parametrized. The DESC keyword for example is not a parameter. Try
并非 SQL 查询的所有部分都可以参数化。例如，DESC 关键字不是参数。尝试
query = """SELECT * FROM sometable
order by %s """ + sortorder + """
limit %s, %s"""
cur.execute(query, (sortname, limit1, limit2) )
回答by naivnomore
You could try this alternatively...
你也可以试试这个……
query = """SELECT * FROM sometable
order by {0} {1}
limit {2}, {3};"""
sortname = 'somecol'
sortorder = 'DESC'
limit1 = 'limit1'
limit2 = 'limit2'
print(query.format(sortname, sortorder, limit1, limit2))
