Python + MySQLdb executemany [英] Python + MySQLdb executemany

问题描述

我正在使用 Python 及其 MySQLdb 模块将一些测量数据导入 Mysql 数据库.我们拥有的数据量非常大(目前大约有 250 MB 左右的 csv 文件，未来还会有更多).

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

目前我使用 cursor.execute(...) 来导入一些元数据.这没有问题，因为这些条目只有几个.

Currently I use cursor.execute(...) to import some metadata. This isn't problematic as there are only a few entries for these.

问题是当我尝试使用 cursor.executemany() 导入大量实际测量数据时，MySQLdb 引发了一个

The problem is that when I try to use cursor.executemany() to import larger quantities of the actual measurement data, MySQLdb raises a

TypeError: not all arguments converted during string formatting

我当前的代码是

def __insert_values(self, values):
    cursor = self.connection.cursor()
    cursor.executemany("""
        insert into values (ensg, value, sampleid)
        values (%s, %s, %s)""", values)
    cursor.close()

其中 values 是一个元组列表，每个元组包含三个字符串.任何想法这可能有什么问题?

where values is a list of tuples containing three strings each. Any ideas what could be wrong with this?

值由

yield (prefix + row['id'], row['value'], sample_id)

然后一次读入一千个列表，其中 row 和迭代器来自 csv.DictReader.

and then read into a list one thousand at a time where row is and iterator coming from csv.DictReader.

推荐答案

回想起来，这是一个非常愚蠢但很难发现的错误.Values 是 sql 中的关键字，因此表名 values 需要用引号引起来.

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.

def __insert_values(self, values):
    cursor = self.connection.cursor()
    cursor.executemany("""
        insert into `values` (ensg, value, sampleid)
        values (%s, %s, %s)""", values)
    cursor.close()

这篇关于Python + MySQLdb executemany的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！