MySQLdb批量执行sql, TypeError: not all arguments converted during string formatting

错误代码

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
import MySQLdb
if __name__ == '__main__':
dbItem={}
dbItem['host']='127.0.0.1'
dbItem['port']=3306
dbItem['user']='root'
dbItem['password']='123456'
dbItem['database']='test'
dbItem['charset']='utf8'
dbItem['use_unicode']='False'

conn = MySQLdb.connect(host=dbItem.get('host'), port=dbItem.get('port', 3306),
user=dbItem.get('user'), passwd=dbItem.get('password'),
db=dbItem.get('database'), charset=dbItem.get('charset'),
use_unicode=dbItem.get('use_unicode'))
cursor = conn.cursor()

# 初始数据,第一个值为表名的一部分
list = [('classify', 130, 'classify1'),('content',14,'markdownContent')]
insertsql = '''insert into atc_%s(id,data) values(%s, %s)'''

"---这里报错---"
cursor.executemany(insertsql, tuple(list))

conn.close()

错误信息

TypeError: not all arguments converted during string formatting

查看executemany方法源码

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
def executemany(self, query, args):
del self.messages[:]
db = self._get_db()
if not args: return
charset = db.character_set_name()
if isinstance(query, unicode): query = query.encode(charset)
"---!!重点就在这里---"
m = insert_values.search(query)
if not m:
r = 0
for a in args:
r = r + self.execute(query, a)
return r
p = m.start(1)
e = m.end(1)
qv = m.group(1)
try:
q = [ qv % db.literal(a) for a in args ]
except TypeError, msg:
if msg.args[0] in ("not enough arguments for format string",
"not all arguments converted"):
self.errorhandler(self, ProgrammingError, msg.args[0])
else:
self.errorhandler(self, TypeError, msg)
except:
exc, value, tb = sys.exc_info()
del tb
self.errorhandler(self, exc, value)
r = self._query('\n'.join([query[:p], ',\n'.join(q), query[e:]]))
if not self._defer_warnings: self._warning_check()
return r

找到关键点所在,m = insert_values.search(query)

1
2
3
4
5
6
7
8
9
10
11
import re
restr = (r"\svalues\s*"
r"(\(((?<!\\)'[^\)]*?\)[^\)]*(?<!\\)?'"
r"|[^\(\)]|"
r"(?:\([^\)]*\))"
r")+\))")
# 全部变量
insert_values= re.compile(restr)
...
# executemany方法中
m = insert_values.search(query)

这段代码的意思就是:截取出sql语句中values之后的字符串。
我上面的代码截取到的就应该是:values(%s, %s)
以上代码有疑惑的同学:传送门1传送门2

解决方法

问题出在哪里显而易见了,executemany方法中设置参数时没有截取到values之前的字段,导致参数个数与%s个数对不上。

将表名和要插入的数据封装成字典,再进行批量插入即可。修改后的代码:

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
import MySQLdb
if __name__ == '__main__':
dbItem={}
...
conn = MySQLdb.connect(host=dbItem.get('host'), port=dbItem.get('port', 3306),
user=dbItem.get('user'), passwd=dbItem.get('password'),
db=dbItem.get('database'), charset=dbItem.get('charset'),
use_unicode=dbItem.get('use_unicode'))
cursor = conn.cursor()

# 初始数据,第一个值为表名的一部分
list = [('classify', 130, 'classify1'),('content',14,'markdownContent')]

# 封装成字典数据结构
value_dic = {}
for item in list:
valList = []
tbName = item[0]
if value_dic.has_key(tbName):
valList = value_dic.get(tbName)
valList.append(item[1:])
value_dic[tbName] = valList

# 分数据表批量执行
for tbName,valList in value_dic.items():
insertsql = '''insert into atc_{tbName}(id,data) values(%s, %s)'''.format(tbName=tbName)
cursor.executemany(insertsql, tuple(valList))

conn.close()
评论