为什么在Python MySQLdb中executemany变慢? [英] Why is executemany slow in Python MySQLdb?

问题描述

我正在用Python开发一个程序，该程序使用MySQLdb访问MySQL数据库.在某些情况下，我必须在许多行上运行INSERT或REPLACE命令.我目前正在这样做:

db.execute("REPLACE INTO " + table + " (" + ",".join(cols) + ") VALUES" +
    ",".join(["(" + ",".join(["%s"] * len(cols)) + ")"] * len(data)),
    [row[col] for row in data for col in cols])

它工作正常，但是有点尴尬.我想知道是否可以使它更容易阅读，并且发现了executemany命令.我更改了代码，使其看起来像这样:

db.executemany("REPLACE INTO " + table + " (" + ",".join(cols) + ") " + 
    "VALUES(" + ",".join(["%s"] * len(cols)) + ")",
    [tuple(row[col] for col in cols) for row in data])

它仍然有效，但运行速度慢得多.在我的测试中，对于相对较小的数据集(大约100-200行)，它的运行速度慢了大约6倍.对于大数据集(约13,000行，这是我希望处理的最大数据集)，它的运行速度慢了约50倍.为什么要这样做?

我真的很想简化我的代码，但是我不希望性能大幅度下降.有人知道有什么方法可以使其更快吗?

我正在使用Python 2.7和MySQLdb 1.2.3.我尝试修改setinputsizes函数，但这似乎无济于事.我看了看MySQLdb源代码，看起来它什么也不做.

解决方案

尝试在查询中使用小写的值"一词-这似乎是MySQL-python 1.2.3中的错误/回归.

MySQL-python的executemany()实现将VALUES子句与正则表达式匹配，然后仅克隆每行数据的值列表，因此最终您将执行与第一种方法完全相同的查询.

不幸的是，正则表达式在该版本中丢失了不区分大小写的标志(随后在主干

It works fine, but it is kind of awkward. I was wondering if I could make it easier to read, and I found out about the executemany command. I changed my code to look like this:

db.executemany("REPLACE INTO " + table + " (" + ",".join(cols) + ") " + 
    "VALUES(" + ",".join(["%s"] * len(cols)) + ")",
    [tuple(row[col] for col in cols) for row in data])

It still worked, but it ran a lot slower. In my tests, for relatively small data sets (about 100-200 rows), it ran about 6 times slower. For big data sets (about 13,000 rows, the biggest I am expecting to handle), it ran about 50 times slower. Why is it doing this?

I would really like to simplify my code, but I don't want the big drop in performance. Does anyone know of any way to make it faster?

I am using Python 2.7 and MySQLdb 1.2.3. I tried tinkering with the setinputsizes function, but that didn't seem to do anything. I looked at the MySQLdb source code and it looks like it shouldn't do anything.

解决方案

Try lowercasing the word 'values' in your query - this appears to be a bug/regression in MySQL-python 1.2.3.

MySQL-python's implementation of executemany() matches the VALUES clause with a regular expression and then just clones the list of values for each row of data, so you end up executing exactly the same query as with your first approach.

Unfortunately the regular expression lost its case-insensitive flag in that release (subsequently fixed in trunk r622 but never backported to the 1.2 branch) so it degrades to iterating over the data and firing off a query per row.

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