Python psycopg2 TypeError：并非所有参数都在字符串格式化期间转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21524482/
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
psycopg2 TypeError: not all arguments converted during string formatting
提问by konart
I'm trying execute a simple query, but getting this error no matter how I pass the parameters.
我正在尝试执行一个简单的查询，但无论我如何传递参数都会收到此错误。
Here is the query (I'm using Trac db object to connect to a DB):
这是查询（我使用 Trac db 对象连接到数据库）：
cursor.execute("""SELECT name FROM "%s".customer WHERE firm_id='%s'""" % (schema, each['id']))
schema and each['id'] both are simple strings
schema 和 each['id'] 都是简单的字符串
print("""SELECT name FROM "%s".customer WHERE firm_id='%s'""" % (schema, each['id']))
Result:
SELECT name FROM "Planing".customer WHERE firm_id='135'
结果：
SELECT name FROM "Planing".customer WHERE firm_id='135'
There is on error is a remove quote after firm_id=, but that way parameter is treated a an integer and ::textleads to the very same error.
错误是在 之后删除引号firm_id=，但这种方式参数被视为整数并::text导致完全相同的错误。
采纳答案by RjOllos
You should not use string interpolation for passing variables in database queries, but using string interpolation to set the table name is fine as long as it's not an external input or you restrict the allowed value. Try:
您不应该使用字符串插值在数据库查询中传递变量，但是使用字符串插值来设置表名就可以了，只要它不是外部输入或者您限制了允许的值。尝试：
cursor.execute("""SELECT name FROM %s.customer WHERE firm_id=%%s""" % schema, each['id'])
Rules for DB API usageprovides guidance for programming against the database.
DB API 使用规则提供了针对数据库进行编程的指导。
回答by ndpu
The correct way to pass variables in a SQL command is using the second argument of the execute()method. And i think you should remove single quotes from second parameter, read about it here - http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters.
在 SQL 命令中传递变量的正确方法是使用该execute()方法的第二个参数。我认为您应该从第二个参数中删除单引号，请在此处阅读 - http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters。
Note that you cant pass table name as parameter to executeand it considered as bad practice but there is some workarounds:
Passing table name as a parameter in psycopg2
psycopg2 cursor.execute() with SQL query parameter causes syntax error
请注意，您不能将表名作为参数传递给execute它，这被认为是不好的做法，但有一些解决方法：
在 psycopg2 psycopg2 cursor.execute()中将表名作为参数传递给
SQL 查询参数会导致语法错误
To pass table name try this:
要传递表名试试这个：
cursor.execute("""SELECT name FROM "%s".customer WHERE firm_id=%s""" % (schema, '%s'), (each['id'],))
回答by Clodoaldo Neto
Use AsIs
用 AsIs
from psycopg2.extensions import AsIs
cursor.execute("""
select name
from %s.customer
where firm_id = %s
""",
(AsIs(schema), each['id'])
)
回答by rsacc
I got this same error and couldn't for the life of me work out how to fix, in the end it was my stupid mistake because I didn't have enough parameters matching the number of elements in the tuple:
我遇到了同样的错误，并且终其一生都无法弄清楚如何修复，最终这是我的愚蠢错误，因为我没有足够的参数与元组中的元素数量匹配：
con.execute("INSERT INTO table VALUES (%s,%s,%s,%s,%s)",(1,2,3,4,5,6))
Note that I have 5 elements in the values to be inserted into the table, but 6 in the tuple.
请注意，要插入表中的值中有 5 个元素，但元组中有 6 个元素。
回答by Spencer Sutton
In my case I didn't realize that you had to pass a tuple to cursor.execute. I had this:
就我而言，我没有意识到您必须将元组传递给 cursor.execute。我有这个：
cursor.execute(query, (id))
But I needed to pass a tuple instead
但我需要传递一个元组
cursor.execute(query, (id,))
