Python MySQLdb TypeError：并非所有参数都在字符串格式化期间转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21740359/
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
Python MySQLdb TypeError: not all arguments converted during string formatting
提问by Matthew 'mandatory' Bryant
Upon running this script:
运行此脚本后：
#! /usr/bin/env python
import MySQLdb as mdb
import sys
class Test:
def check(self, search):
try:
con = mdb.connect('localhost', 'root', 'password', 'recordsdb');
cur = con.cursor()
cur.execute( "SELECT * FROM records WHERE email LIKE '%s'", search )
ver = cur.fetchone()
print "Output : %s " % ver
except mdb.Error, e:
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)
finally:
if con:
con.close()
test = Test()
test.check("test")
I get an error of:
我收到以下错误：
./lookup
Traceback (most recent call last):
File "./lookup", line 27, in <module>
test.check("test")
File "./lookup", line 11, in creep
cur.execute( "SELECT * FROM records WHERE email LIKE '%s'", search )
File "/usr/local/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 187, in execute
query = query % tuple([db.literal(item) for item in args])
TypeError: not all arguments converted during string formatting
I have zero idea why. I'm trying to do parameterized querys, but it's been nothing but a pain. I'm somewhat new to Python, so it's probably an obvious problem.
我不知道为什么。我正在尝试进行参数化查询，但这只是一种痛苦。我对 Python 有点陌生，所以这可能是一个明显的问题。
采纳答案by kevinsa5
Instead of this:
取而代之的是：
cur.execute( "SELECT * FROM records WHERE email LIKE '%s'", search )
Try this:
尝试这个：
cur.execute( "SELECT * FROM records WHERE email LIKE %s", [search] )
See the MySQLdb documentation. The reasoning is that execute's second parameter represents a list of the objects to be converted, because you could have an arbitrary number of objects in a parameterized query. In this case, you have only one, but it still needs to be an iterable (a tuple instead of a list would also be fine).
请参阅 MySQLdb文档。原因是 thatexecute的第二个参数表示要转换的对象列表，因为在参数化查询中可以有任意数量的对象。在这种情况下，您只有一个，但它仍然需要是可迭代的（元组而不是列表也可以）。
回答by Mohamed Abd El Raouf
You can try this code:
你可以试试这个代码：
cur.execute( "SELECT * FROM records WHERE email LIKE %s", (search,) )
You can see the documentation
你可以看文档
回答by ksg97031
'%' keyword is so dangerous because it major cause of 'SQL INJECTION ATTACK'.
So you just using this code.
'%' 关键字非常危险，因为它是导致 'SQL INJECTION ATTACK' 的主要原因。
所以你只需使用此代码。
cursor.execute("select * from table where example=%s", (example,))
or
或者
t = (example,)
cursor.execute("select * from table where example=%s", t)
if you want to try insert into table, try this.
如果你想尝试插入表，试试这个。
name = 'ksg'
age = 19
sex = 'male'
t = (name, age, sex)
cursor.execute("insert into table values(%s,%d,%s)", t)
回答by Russell Nelson
I don't understand the first two answers. I think they must be version-dependent. I cannot reproduce them on MySQLdb 1.2.3, which comes with Ubuntu 14.04LTS. Let's try them. First, we verify that MySQL doesn't accept double-apostrophes:
我不明白前两个答案。我认为它们必须依赖于版本。我无法在 Ubuntu 14.04LTS 附带的 MySQLdb 1.2.3 上复制它们。让我们试试看。首先，我们验证 MySQL 不接受双撇号：
mysql> select * from methods limit 1;
+----------+--------------------+------------+
| MethodID | MethodDescription | MethodLink |
+----------+--------------------+------------+
| 32 | Autonomous Sensing | NULL |
+----------+--------------------+------------+
1 row in set (0.01 sec)
mysql> select * from methods where MethodID = ''32'';
ERROR 1064 (42000): 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 '9999'' ' at line 1
Nope. Let's try the example that Mandatory posted using the query constructor inside /usr/lib/python2.7/dist-packages/MySQLdb/cursors.pywhere I opened "con" as a connection to my database.
不。让我们试试 Mandatory 使用查询构造函数发布的示例，/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py其中我打开了“con”作为到我的数据库的连接。
>>> search = "test"
>>> "SELECT * FROM records WHERE email LIKE '%s'" % con.literal(search)
"SELECT * FROM records WHERE email LIKE ''test''"
>>>
Nope, the double apostrophes cause it to fail. Let's try Mike Graham's first comment, where he suggests leaving off the apostrophes quoting the %s:
不，双撇号会导致它失败。让我们试试 Mike Graham 的第一条评论，他建议去掉引用 %s 的撇号：
>>> "SELECT * FROM records WHERE email LIKE %s" % con.literal(search)
"SELECT * FROM records WHERE email LIKE 'test'"
>>>
Yep, that will work, but Mike's second comment and the documentation says that the argument to execute (processed by con.literal) must be a tuple (search,)or a list [search]. You can try them, but you'll find no difference from the output above.
是的，这会起作用，但是 Mike 的第二条评论和文档说要执行的参数（由 con.literal 处理）必须是 tuple(search,)或 list [search]。您可以尝试它们，但您会发现与上面的输出没有区别。
The best answer is ksg97031's.
最好的答案是ksg97031。
回答by bob90937
cur.execute( "SELECT * FROM records WHERE email LIKE %s", (search,) )
I do not why, but this works for me . rather than use '%s'.
我不知道为什么，但这对我有用。而不是使用'%s'.
回答by stillzhl
According PEP8，I prefer to execute SQL in this way:
根据 PEP8，我更喜欢这样执行 SQL：
cur = con.cursor()
# There is no need to add single-quota to the surrounding of `%s`,
# because the MySQLdb precompile the sql according to the scheme type
# of each argument in the arguments list.
sql = "SELECT * FROM records WHERE email LIKE %s;"
args = [search, ]
cur.execute(sql, args)
In this way, you will recognize that the second argument argsof executemethod must be a list of arguments.
通过这种方式，你将认识到，第二个参数args的execute方法必须的参数列表。
May this helps you.
愿这对你有帮助。
回答by user4851
The accepted answer by @kevinsa5 is correct, but you might be thinking "I swear this code usedto work and now it doesn't," and you would be right.
@kevinsa5 接受的答案是正确的，但您可能会想“我发誓这段代码曾经可以工作，但现在不能了”，您是对的。
There was an API change in the MySQLdb library between 1.2.3 and 1.2.5. The 1.2.3 versions supported
MySQLdb 库中的 API 在 1.2.3 和 1.2.5 之间发生了变化。支持的 1.2.3 版本
cursor.execute("SELECT * FROM foo WHERE bar = %s", 'baz')
but the 1.2.5 versions require
但 1.2.5 版本需要
cursor.execute("SELECT * FROM foo WHERE bar = %s", ['baz'])
as the other answers state. I can't find the change in the changelogs, and it's possible the earlier behavior was considered a bug.
正如其他答案所述。我在更改日志中找不到更改，并且可能将较早的行为视为错误。
The Ubuntu 14.04 repository has python-mysqldb 1.2.3, but Ubuntu 16.04 and later have python-mysqldb 1.3.7+.
Ubuntu 14.04 存储库具有 python-mysqldb 1.2.3，但 Ubuntu 16.04 及更高版本具有 python-mysqldb 1.3.7+。
If you're dealing with a legacy codebase that requires the old behavior but your platform is a newish Ubuntu, install MySQLdb from PyPI instead:
如果您正在处理需要旧行为的旧代码库，但您的平台是新的 Ubuntu，请从 PyPI 安装 MySQLdb：
$ pip install MySQL-python==1.2.3
回答by Adel
I encountered this error while executing
SELECT * FROM table;I traced the error to cursor.py line 195.
我在执行时遇到了这个错误，
SELECT * FROM table;我将错误追踪到了 cursor.py 第 195 行。
if args is not None:
if isinstance(args, dict):
nargs = {}
for key, item in args.items():
if isinstance(key, unicode):
key = key.encode(db.encoding)
nargs[key] = db.literal(item)
args = nargs
else:
args = tuple(map(db.literal, args))
try:
query = query % args
except TypeError as m:
raise ProgrammingError(str(m))
Given that I am entering any extra parameters, I got rid of all of "if args ..." branch. Now it works.
鉴于我正在输入任何额外的参数，我摆脱了所有“if args ...”分支。现在它起作用了。
