python - oursql 插入数据速度极慢

Question

我正在尝试将 python 脚本生成的一些数据存储在 MySQL 数据库中。本质上我正在使用命令:

con = oursql.connect(user="user", host="host", passwd="passwd", 
                     db="testdb")
c = con.cursor()                             

c.executemany(insertsimoutput, zippedsimoutput)

con.commit()
c.close()

在哪里，

insertsimoutput = '''insert into simoutput 
                        (repnum, 
                         timepd, 
                         ...) values (?, ?, ...?)'''

插入了大约 30,000 行，大约有 15 列。以上大约需要 7 分钟。如果我使用 MySQLdb 而不是 oursql，大约需要 2 秒。为什么会有如此巨大的差异？这是否应该在 oursql 中以其他方式完成，我们的 oursql 非常慢？如果有更好的方法使用 oursql 插入此数据，请告诉我，我将不胜感激。

谢谢。

Answer 1

区别在于 MySQLdb 会对您的查询进行一些黑客攻击，而 oursql 不会...

拿这个:

cursor.executemany("INSERT INTO sometable VALUES (%s, %s, %s)",
    [[1,2,3],[4,5,6],[7,8,9]])

MySQLdb 在遇到这个之前翻译它:

cursor.execute("INSERT INTO sometable VALUES (1,2,3),(4,5,6),(7,8,9)")

但如果你这样做:

cursor.executemany("INSERT INTO sometable VALUES (?, ?, ?)",
    [[1,2,3],[4,5,6],[7,8,9]])

在我们的 sql 中，它被翻译成类似这样的伪代码:

stmt = prepare("INSERT INTO sometable VALUES (?, ?, ?)")
for params in [[1,2,3],[4,5,6],[7,8,9]]:
    stmt.execute(*params)

因此，如果您想模拟 mysqldb 正在做的事情，但又想从 oursql 的准备好的语句和其他优点中受益，您需要这样做:

from itertools import chain
data = [[1,2,3],[4,5,6],[7,8,9]]
one_val = "({})".format(','.join("?" for i in data[0]))
vals_clause = ','.join(one_val for i in data)
cursor.execute("INSERT INTO sometable VALUES {}".format(vals_clause),
    chain.from_iterable(data))

我敢打赌当你这样做时我们的 sql 会更快 :-)

此外，如果您认为它很丑，那您是对的。但请记住，MySQL 数据库在内部做了一些更丑陋的事情——它使用正则表达式来解析您的 INSERT 语句并中断参数化部分，然后执行我建议您为 oursql 执行的操作。