如何避免使用MySQLdb和Python进行多次查询往返？

Question

我正在读取一些原始文件，并把它们插入到数据库中。这涉及到数百万条记录，而且每条记录我还要插入到多个表里。当我在本地测试的时候，速度很快，但对于整个数据集，我需要使用一个远程数据库。这样做速度非常慢，我猜是因为每次删除或插入都要通过网络传输。

我正在使用MySQLdb模块（Python），目前的代码大概是这样的：

# setup connection
con = mdb.connect('remote.host', 'database_user', '123456789', 'database_name');

... read files, loop through records, etc...

# clear out data related to current record
cur.execute("DELETE FROM articles WHERE article_id = %s", article.id)
cur.execute("DELETE FROM authors WHERE article_id = %s", article.id)
cur.execute("DELETE FROM addresses WHERE article_id = %s", article.id)
cur.execute("DELETE FROM citation_references WHERE article_id = %s", article.id)
cur.execute("DELETE FROM citation_patents WHERE article_id = %s", article.id)

# insert the article
cur.execute("INSERT INTO articles (article_id, doctype, keywords, language, title) VALUES (%s, %s, %s, %s, %s, %s)" , (article.id, article.doctype, ';'.join(article.keywords), article.language, article.title))

# insert all the authors
for au in article.authors:
    cur.execute("INSERT INTO isi_authors (article_id, name_first, name_last, email) VALUES (%s, %s, %s, %s)", (article.id, au.first_name, au.last_name, au.email))

... other loops like the authors to insert 10-20 citations per article, multiple addresses, etc ...

根据我的观察，MySQLdb不允许我一次发送多个查询。我想一定有办法可以避免网络延迟。有什么建议吗？

Answer 1

mySQL的INSERT语法是允许这样做的。你可以对比一下1)和2)

1. INSERT INTO tbl_name (a,b,c) VALUES(1,2,3);
2. INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

在第二种情况下，你是一次性插入了三行数据。

http://dev.mysql.com/doc/refman/5.5/en/insert.html

希望这能给你一些启发。

PS：这是一种与编程语言无关的方法。

Answer 2

至少在 MySQLdb 1.2.3 版本中，它似乎可以直接支持多个查询。你只需要调用 cursor.nextset() 就能遍历返回的结果集。

db = conn.cursor()
db.execute('SELECT 1; SELECT 2;')

more = True
while more:
    print db.fetchall()
    more = db.nextset()

如果你想确保这个功能是开启的，或者想关闭这个功能，可以使用类似下面的代码：

MYSQL_OPTION_MULTI_STATEMENTS_ON = 0
MYSQL_OPTION_MULTI_STATEMENTS_OFF = 1

conn.set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_ON)
# Multiple statement execution here...
conn.set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_OFF)

如果在执行某个查询时出现错误，mysql 就不会执行之后的查询了。如果错误是由第一个查询引起的，db.execute() 会抛出异常；如果是其他查询出错，适当的 db.nextset() 会处理这个异常。这样，你可以在遇到异常之前，先获取成功查询的结果集。

Answer 3

使用 executemany 方法。这里有一个来自 手册 的例子：

c.executemany(
      """INSERT INTO breakfast (name, spam, eggs, sausage, price)
      VALUES (%s, %s, %s, %s, %s)""",
      [
      ("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),
      ("Not So Much Spam Plate", 3, 2, 0, 3.95 ),
      ("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )
      ] )

在你的情况下，它看起来会像这样：

sql = "INSERT INTO isi_authors (article_id, name_first, name_last, email) VALUES (%s, %s, %s, %s)"
params = [(article.id, au.first_name, au.last_name, au.email) for au in article.authors]
cur.executemany(sql, params)

关于 executemany 的文档说明：

这个方法在插入多行数据时能提高性能。如果不使用这个方法，就相当于用 execute() 方法一个一个地处理参数。