桥山之巅,姬水之畔

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

2019.05.05

问题

使用Python MySQLdb批量执行sql时,总是报错TypeError: not all arguments converted during string formatting

错误代码

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()

查看executemany方法源码

	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)

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个数对不上。

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

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()