Python + MySQLdb executemany
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/974702/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python + MySQLdb executemany
提问by lhahne
I'm using Python and its MySQLdb module to import some measurement data into a Mysql database. The amount of data that we have is quite high (currently about ~250 MB of csv files and plenty of more to come).
我正在使用 Python 及其 MySQLdb 模块将一些测量数据导入 Mysql 数据库。我们拥有的数据量非常大（目前大约有 250 MB 的 csv 文件，还有更多）。
Currently I use cursor.execute(...) to import some metadata. This isn't problematic as there are only a few entries for these.
目前我使用 cursor.execute(...) 来导入一些元数据。这没有问题，因为这些条目只有几个。
The problem is that when I try to use cursor.executemany() to import larger quantities of the actual measurement data, MySQLdb raises a
问题是，当我尝试使用 cursor.executemany() 导入大量实际测量数据时，MySQLdb 引发了一个
TypeError: not all arguments converted during string formatting
My current code is
我目前的代码是
def __insert_values(self, values):
cursor = self.connection.cursor()
cursor.executemany("""
insert into values (ensg, value, sampleid)
values (%s, %s, %s)""", values)
cursor.close()
where valuesis a list of tuples containing three strings each. Any ideas what could be wrong with this?
其中values是一个元组列表，每个元组包含三个字符串。任何想法这可能有什么问题？
Edit:
编辑：
The values are generated by
这些值是由
yield (prefix + row['id'], row['value'], sample_id)
and then read into a list one thousand at a time where row is and iterator coming from csv.DictReader.
然后在行和迭代器来自的时间读入一千个列表csv.DictReader。
回答by lhahne
In retrospective this was a really stupid but hard to spot mistake. Values is a keyword in sql so the table name values needs quotes around it.
回想起来，这是一个非常愚蠢但很难发现的错误。Values 是 sql 中的关键字，因此表名 values 需要用引号引起来。
def __insert_values(self, values):
cursor = self.connection.cursor()
cursor.executemany("""
insert into `values` (ensg, value, sampleid)
values (%s, %s, %s)""", values)
cursor.close()
回答by gimel
The message you get indicates that inside the executemany()method, one of the conversions failed. Check your valueslist for a tuple longer than 3.
您收到的消息表明在executemany()方法内部，其中一个转换失败。检查您的values列表中是否有超过 3 的元组。
For a quick verification:
快速验证：
max(map(len, values))
If the result is higher than 3, locate your bad tuple with a filter:
如果结果高于 3，请使用过滤器定位您的坏元组：
[t for t in values if len(t) != 3]
or, if you need the index:
或者，如果您需要索引：
[(i,t) for i,t in enumerate(values) if len(t) != 3]
