当我尝试在参数化的SQL查询中使用字符串时，为什么会出现“TypeError：not all arguments converted during string formatting”错误？

Question

我有这段代码：

#! /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")

但是我收到了一个错误，类似于：

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

出了什么问题，我该如何修复？

---

_{在sqlitem中出现了相同的问题，但报告方式不同；有关详细信息，请参见sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied。}

- Matthew 'mandatory' Bryant

---

不需要/不想引用您的查询参数。 - Mike Graham

cur.execute("SELECT * FROM records WHERE email LIKE %s", [search]) 请执行以下代码：cur.execute("SELECT * FROM records WHERE email LIKE %s", [search]) - Mike Graham

我在使用Python2.7时遇到了这个问题，但是在使用Python2.6时却没有问题。你知道原因吗？ - Matt

在我们的服务器上将Ubuntu从14.x升级到16.x后，我也遇到了这个问题。有人能解释一下版本依赖关系吗？那会非常有帮助。 - Ashish Singh

Answer 1

不要这样做：

cur.execute( "SELECT * FROM records WHERE email LIKE '%s'", search )

试一试：

cur.execute( "SELECT * FROM records WHERE email LIKE %s", [search] )

请查看 MySQLdb 的文档。原因是 execute 的第二个参数表示要转换的对象列表，因为您可能在参数化查询中有任意数量的对象。在这种情况下，您只有一个对象，但它仍然需要是可迭代的（元组而不是列表也可以）。

- kevinsa5

---

我明白了，所以必须传递一个列表才能使它工作？ - Matthew 'mandatory' Bryant

有趣的是，我仍然可以在输入中传递百分号%，它不会被转义，而是像通配符一样使用。 - Matthew 'mandatory' Bryant

@MandatoryProgrammer 不要使用 % 格式化查询字符串。请使用上述方法，并根据文档说明“DB API 要求您将任何参数作为序列传递”。 - Bibhas Debnath

我并没有这样做，我是在引用传递的搜索 =“％test％”，这是有效的（因此用户可能会通过注入百分号符号作为输入来枚举我的数据库）。 - Matthew 'mandatory' Bryant

在执行之前，您应该将查询转换为文本。我已经使用相同的方法解决了我的问题。>>> from sqlalchemy.sql import text >>> s = text( ... "SELECT users.fullname || ', ' || addresses.email_address AS title " ... "FROM users, addresses " ... "WHERE users.id = addresses.user_id " ... "AND users.name BETWEEN :x AND :y " ... "AND (addresses.email_address LIKE :e1 " ... "OR addresses.email_address LIKE :e2)") SQL>>> conn.execute(s, x='m', y='z', e1='[e-mail]', e2='[e-mail]').fetchall() - Suresh Kumar

Answer 2

您可以尝试这段代码：

cur.execute( "SELECT * FROM records WHERE email LIKE %s", (search,) )

您可以查看文档

- Mohamed Abd El Raouf

---

在元组末尾添加逗号正是我所需要的。 - User

@User 感谢您指出这一点，真的很有帮助！如果其他人想知道的话；这是因为仅仅在变量名周围添加()并不能使它成为元组。需要添加逗号。 - Daksh Shah

我必须添加括号和逗号才能使其正常工作。 - peterretief

参见：如何创建仅有一个元素的“单例”元组。 - Karl Knechtel

Answer 3

'%'关键字非常危险，因为它是“SQL注入攻击”的主要原因。
所以你只需要使用这段代码。

cursor.execute("select * from table where example=%s", (example,))

或者

t = (example,)
cursor.execute("select * from table where example=%s", t)

如果你想尝试插入表格，可以试试这个方法。

name = 'ksg'
age = 19
sex = 'male'
t  = (name, age, sex)
cursor.execute("insert into table values(%s,%d,%s)", t)

- ksg97031

---

尽管如此，开头的评论并没有解释问题所在。OP已经正确地尝试让库完成查询；问题只是输入没有被正确地包装在单例元组中。这个答案并没有涵盖2014年就已经提供的答案中没有覆盖的任何内容，并且没有清晰地解释。 - Karl Knechtel

Answer 4

cur.execute( "SELECT * FROM records WHERE email LIKE %s", (search,) )

我不知道为什么，但这对我有效。与使用'%s'相比。

- bob90937

---

之前的回答已经详细解释了这一点。这里没有有用的解释，而且代码仍然使用%s。 - Karl Knechtel

Answer 5

@kevinsa5提供的答案是正确的，但你可能会想：“我发誓这段代码以前可以运行，现在不行了”，你是对的。

在MySQLdb库的1.2.3和1.2.5之间进行了API更改。 1.2.3版本支持

cursor.execute("SELECT * FROM foo WHERE bar = %s", 'baz')

但是1.2.5版本需要。

cursor.execute("SELECT * FROM foo WHERE bar = %s", ['baz'])

作为其他答案所述，我无法在更改日志中找到此更改，并且早期行为可能被视为错误。

Ubuntu 14.04存储库具有python-mysqldb 1.2.3，但Ubuntu 16.04及更高版本具有python-mysqldb 1.3.7+。

如果您正在处理需要旧行为的遗留代码库，但您的平台是较新的Ubuntu，请从PyPI安装MySQLdb。

$ pip install MySQL-python==1.2.3

- user4851

Answer 6

我不理解前两个答案。我认为它们必须与版本有关。我无法在MySQLdb 1.2.3上复现它们，这是Ubuntu 14.04LTS附带的版本。让我们尝试一下。首先，我们验证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

不行。让我们尝试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''"
>>> 

不行，双撇号会导致它失败。让我们尝试Mike Graham的第一条评论，他建议在引用%s时省略撇号：

>>> "SELECT * FROM records WHERE email LIKE %s" % con.literal(search)
"SELECT * FROM records WHERE email LIKE 'test'"
>>> 

没错，那个方法可以用，但是Mike的第二个评论和文档说，execute的参数（由con.literal处理）必须是一个元组(search,)或者一个列表[search]。你可以尝试它们，但是你会发现与上面的输出没有区别。

最好的答案是ksg97031的。

- Russell Nelson

---

ksg97031 也使用了元组。这与 MySQLdb 库有关，而不是 MySQL 本身。 - adonese

Answer 7

根据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)

以此方式，您将认识到execute方法的第二个参数args必须是一个参数列表。

希望这可以帮助您。

- stillzhl

Answer 8

在执行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))

考虑到我输入了任何额外的参数，我摆脱了所有的“如果args ...”分支。现在它可以工作。

- Adel