Python 批量插入给出 TypeError:并非所有参数都在字符串格式化期间转换 [英] Python Bulk Insert gives TypeError: not all arguments converted during string formatting

问题描述

我有以下代码来插入多行；

I have following code to insert multiple rows;

cnx = pymysql.connect(host='xx', user='xx', password='xx', database='xx', autocommit=True)
cursor = cnx.cursor()
query = "INSERT INTO `tableA`('id,'name',...)values(?,? ......)"
cursor.executemany(query, listTosave)

我的 listTosave 包含类似的值；

My listTosave contains values like;

[['AS0001', '1170', 1, '1', 'Unknown', 442, 1, datetime.datetime(2018, 5, 28, 23, 0), datetime.datetime(2018, 3, 15, 11, 15), datetime.datetime(2018, 3, 15, 10, 56), datetime.datetime(2018, 5, 28, 23, 18, 26), datetime.datetime(2018, 5, 28, 23, 59, 22), Decimal('15177.3184'), Decimal('15185.7562'), Decimal('8.4378')],[......],...
....]]

当我尝试插入时出现以下错误；

When I try to insert I get following error;

    cursor.executemany(query, listTosave)
  File "/tmp/pymodules/pymysql/cursors.py", line 194, in executemany
  File "/tmp/pymodules/pymysql/cursors.py", line 194, in <genexpr>
  File "/tmp/pymodules/pymysql/cursors.py", line 163, in execute
  File "/tmp/pymodules/pymysql/cursors.py", line 142, in mogrify
TypeError: not all arguments converted during string formatting

我在这里做错了什么?我想一次插入多行.

What am I doing wrong here? I would like to insert multiple rows at once.

推荐答案

listTosave 应该是元组列表而不是列表列表

listTosave should be an list of tuples rather than list of lists

[('AS0001', '1170', 1, '1', 'Unknown', 442, 1, datetime.datetime(2018, 5, 28, 23, 0), datetime.datetime(2018, 3, 15, 11, 15), datetime.datetime(2018, 3, 15, 10, 56), datetime.datetime(2018, 5, 28, 23, 18, 26), datetime.datetime(2018, 5, 28, 23, 59, 22), Decimal('15177.3184'), Decimal('15185.7562'), Decimal('8.4378')),(......),...]

这篇关于Python 批量插入给出 TypeError:并非所有参数都在字符串格式化期间转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！