今天把django从1.5.5升级到了1.6.2,结果使用mysql-python查询数据库时候就报了这个错误:“not all arguments converted during string formatting”

貌似这个问题应该和django没什么关系,正好今天就看看mysql-python的源码吧。想看如何解决的请直接跳到最后。

查询部分简化后如下:

1
2
3
posistion = "top"
order = r"select id,p_name,p_explain,p_href from pictures where p_position = %s and p_show = 1 order by -id"
self.cur.execute(order, position)

mysql-python中execute函数:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
def execute(self, query, args=None):
"""Execute a query.
query -- string, query to execute on server
args -- optional sequence or mapping, parameters to use with query.
Note: If args is a sequence, then %s must be used as the
parameter placeholder in the query. If a mapping is used,
%(key)s must be used as the placeholder.
Returns long integer rows affected, if any
"""
del self.messages[:]
db = self._get_db()
if isinstance(query, unicode):
query = query.encode(db.unicode_literal.charset)
if args is not None:
if isinstance(args, dict):
query = query % dict((key, db.literal(item))
for key, item in args.iteritems())
else:
query = query % tuple([db.literal(item) for item in args]) ##########################这里!!
try:
r = None
r = self._query(query)
except TypeError, m:
if m.args[0] in ("not enough arguments for format string",
"not all arguments converted"):
self.messages.append((ProgrammingError, m.args[0]))
self.errorhandler(self, ProgrammingError, m.args[0])
else:
self.messages.append((TypeError, m))
self.errorhandler(self, TypeError, m)
except (SystemExit, KeyboardInterrupt):
raise
except:
exc, value, tb = sys.exc_info()
del tb
self.messages.append((exc, value))
self.errorhandler(self, exc, value)
self._executed = query
if not self._defer_warnings: self._warning_check()
return r

错误地方就是后面加了#的语句。

可以看到,因为字符串也是可序列化的,所以一个字符串被拆分成了单个的字符进行db.literal()处理。这个db是什么呢?

往上找:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
def __init__(self, connection):
from weakref import proxy
self.connection = proxy(connection)
self.description = None
self.description_flags = None
self.rowcount = -1
self.arraysize = 1
self._executed = None
self.lastrowid = None
self.messages = []
self.errorhandler = connection.errorhandler
self._result = None
self._warnings = 0
self._info = None
self.rownumber = None
def _get_db(self):
if not self.connection:
self.errorhandler(self, ProgrammingError, "cursor closed")
return self.connection
```
可以看出,这个db就是connection的一个弱引用。再进一步,这个connnection哪来的呢?

```python
def open(self):
""" 返回一个默认的数据库连接游标 """
try:
self.conn = MySQLdb.Connection(
self.db_ip,
self.db_un,
self.db_pd,
self.db_db,
charset=self.db_ch)
self.cur = self.conn.cursor()
except MySQLdb.Error as e:
print e
`

mysqldb-python的connection.py中:

1
2
3
class Connection(_mysql.connection):
"""MySQL Database Connection Object"""
default_cursor = cursors.Cursor

那这个Cursor又是什么呢?

1
2
3
4
class Cursor(CursorStoreResultMixIn, CursorTupleRowsMixIn,
BaseCursor):
"""This is the standard Cursor class that returns rows as tuples
and stores the result set in the client."""

这个Cursor这些类的子类,其中最上面出错函数就是BaseCursor的一个方法。

有点跑偏,先看看那literal函数吧:

1
2
3
4
5
6
7
8
9
10
11
def literal(self, o):
print o,type(o)
print self.encoders
"""
If o is a single object, returns an SQL literal as a string.
If o is a non-string sequence, the items of the sequence are
converted and returned as a sequence.
Non-standard. For internal use; do not use this in your
applications.
"""
return self.escape(o, self.encoders)

print是我添加的,输出如下:

1
2
3
4
5
6
t <type 'str'>
{<type 'instance'>: <function Instance2Str at 0x17b3aa0>, <type 'set'>: <function Set2Str at 0x17b3cf8>, <type 'long'>: <function Thing2Str at 0x17b3de8>, <type 'bool'>: <function Bool2Str at 0x17b3ed8>, <type 'unicode'>: <function unicode_literal at 0x7f12e42c4230>, <type 'str'>: <function string_literal at 0x7f12e42be2a8>, <type 'float'>: <function Float2Str at 0x17b3c08>, <type 'list'>: <function quote_tuple at 0x17b3938>, <type 'tuple'>: <function quote_tuple at 0x17b3938>, <type 'array.array'>: <function array2Str at 0x17b39b0>, <type 'NoneType'>: <function None2NULL at 0x17b3b90>, <type 'datetime.datetime'>: <function DateTime2literal at 0x17b30c8>, <type 'dict'>: <built-in function escape_dict>, <type 'object'>: <function Instance2Str at 0x17b3aa0>, <type 'int'>: <function Thing2Str at 0x17b3de8>, <type 'datetime.timedelta'>: <function DateTimeDelta2literal at 0x17b3140>}
o <type 'str'>
{<type 'instance'>: <function Instance2Str at 0x17b3aa0>, <type 'set'>: <function Set2Str at 0x17b3cf8>, <type 'long'>: <function Thing2Str at 0x17b3de8>, <type 'bool'>: <function Bool2Str at 0x17b3ed8>, <type 'unicode'>: <function unicode_literal at 0x7f12e42c4230>, <type 'str'>: <function string_literal at 0x7f12e42be2a8>, <type 'float'>: <function Float2Str at 0x17b3c08>, <type 'list'>: <function quote_tuple at 0x17b3938>, <type 'tuple'>: <function quote_tuple at 0x17b3938>, <type 'array.array'>: <function array2Str at 0x17b39b0>, <type 'NoneType'>: <function None2NULL at 0x17b3b90>, <type 'datetime.datetime'>: <function DateTime2literal at 0x17b30c8>, <type 'dict'>: <built-in function escape_dict>, <type 'object'>: <function Instance2Str at 0x17b3aa0>, <type 'int'>: <function Thing2Str at 0x17b3de8>, <type 'datetime.timedelta'>: <function DateTimeDelta2literal at 0x17b3140>}
p <type 'str'>
{<type 'instance'>: <function Instance2Str at 0x17b3aa0>, <type 'set'>: <function Set2Str at 0x17b3cf8>, <type 'long'>: <function Thing2Str at 0x17b3de8>, <type 'bool'>: <function Bool2Str at 0x17b3ed8>, <type 'unicode'>: <function unicode_literal at 0x7f12e42c4230>, <type 'str'>: <function string_literal at 0x7f12e42be2a8>, <type 'float'>: <function Float2Str at 0x17b3c08>, <type 'list'>: <function quote_tuple at 0x17b3938>, <type 'tuple'>: <function quote_tuple at 0x17b3938>, <type 'array.array'>: <function array2Str at 0x17b39b0>, <type 'NoneType'>: <function None2NULL at 0x17b3b90>, <type 'datetime.datetime'>: <function DateTime2literal at 0x17b30c8>, <type 'dict'>: <built-in function escape_dict>, <type 'object'>: <function Instance2Str at 0x17b3aa0>, <type 'int'>: <function Thing2Str at 0x17b3de8>, <type 'datetime.timedelta'>: <function DateTimeDelta2literal at 0x17b3140>}

就像上面说的,”top”被拆分成了3个,可是我们语句中的占位符%s只有一个!所以报错 not all arguments converted during string formatting。最初这里我理解成了是哪里转化出了错误,最后醒悟这意思是因为占位符个数和传入参数个数不一样导致!英语不过关阿~不过以前怎么没报错呢??奇怪……

解决办法有2个,简单的方法就是执行语句修改如下:self.cur.execute(order, (position,)) #注意逗号。

更推荐的方法就是使用字典传值:

1
2
3
4
5
if show:
order = r"select id,p_name,p_explain,p_href from pictures where p_position = %(position)s and p_show = 1 order by -id"
else:
order = r"select id,p_name,p_explain,p_href,p_uploaddate,p_show from pictures where p_position = %(position)s"
self.cur.execute(order, {'position': position})

这样虽然多打了几个字母,不过却是一种最稳妥的方式,不用担心由于参数顺序造成错误了。

mysql-python源码写的狠帅阿!