-
1.错误代码
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()
-
2.错误信息:
TypeError: not all arguments converted during string formatting
-
3.查看
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 -
4.解决方法
问题出在哪里显而易见了,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()
MySQLdb批量执行sql, TypeError: not all arguments converted during string formatting
最新推荐文章于 2024-04-27 19:14:59 发布