如何在Python中逐行获取MySQL ResultSet

Question

MySQL的结果集默认是从服务器一次性全部取回的，这在处理非常大的结果集时就变得没法用了。我希望能逐行从服务器获取数据。

在Java中，按照这里的说明（在“ResultSet”部分），我创建了这样的语句：

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

在Java中这样做效果很好。我的问题是：在Python中有没有类似的方法？

我尝试过的一种方法是限制每次查询返回1000行，像这样：

start_row = 0
while True:
    cursor = conn.cursor()
    cursor.execute("SELECT item FROM items LIMIT %d,1000" % start_row)
    rows = cursor.fetchall()
    if not rows:
        break
    start_row += 1000
    # Do something with rows...

不过，这样做似乎在起始行数越来越高时速度会变得更慢。

而且，使用fetchone()代替fetchall()也没有改变什么。

澄清：

我用来重现这个问题的简单代码大概是这样的：

import MySQLdb

conn = MySQLdb.connect(user="user", passwd="password", db="mydb")
cur = conn.cursor()
print "Executing query"
cur.execute("SELECT * FROM bigtable");

print "Starting loop"
row = cur.fetchone()
while row is not None:
    print ", ".join([str(c) for c in row])
    row = cur.fetchone()

cur.close()
conn.close()

在一个大约70万行的表上，这段代码运行得很快。但在一个大约900万行的表上，它会打印“正在执行查询”，然后就卡住很久。这就是为什么使用fetchone()或fetchall()没有区别的原因。

Answer 1

你试过这个fetchone的版本吗？或者其他什么不同的方式？

row = cursor.fetchone() 
while row is not None:
    # process
    row = cursor.fetchone()

还有，你试过这个吗？

 row = cursor.fetchmany(size=1)
 while row is not None:
     # process
     row = cursor.fetchmany( size=1 )

并不是所有的驱动都支持这些，所以你可能会遇到错误，或者觉得它们太慢了。

---

补充一下。

当执行时卡住了，说明你在等数据库。这不是Python逐行处理的问题，而是MySQL的问题。

MySQL更喜欢一次性获取所有行，这样可以更好地管理它自己的缓存。如果你想关闭这个功能，可以设置fetch_size为Integer.MIN_VALUE（-2147483648L）。

问题是，Python的DBAPI中哪个部分相当于JDBC的fetch_size呢？

我觉得可能是游标的arraysize属性。试试这个：

cursor.arraysize=-2**31

看看这是否能让MySQL以流的方式返回结果集，而不是先缓存起来。

Answer 2

使用 limit/offset 的方法运行起来比较慢，因为 MySQL 需要重新扫描数据行来找到偏移量。正如你所猜测的，默认的游标会把所有的结果都存储在客户端，这样可能会占用很多内存。

相反，你可以使用服务器端游标，这样查询会一直运行，并根据需要获取结果。你可以通过在连接时提供一个默认值，或者每次在游标方法中提供一个类来定制游标类。

from MySQLdb import cursors
cursor = conn.cursor(cursors.SSCursor)

但事情并没有那么简单。除了存储 MySQL 的结果，默认的客户端游标实际上会无论如何都获取每一行。这种行为没有文档说明，非常不幸。这意味着会为所有行创建完整的 Python 对象，这样会消耗比原始 MySQL 结果更多的内存。

在大多数情况下，将结果存储在客户端并包装成一个迭代器，会在速度和内存使用上达到最佳平衡。但如果你想要这样的话，就得自己动手实现了。

Answer 3

我觉得你需要连接时传入 cursorclass = MySQLdb.cursors.SSCursor：

 MySQLdb.connect(user="user", 
                 passwd="password",
                 db="mydb",
                 cursorclass = MySQLdb.cursors.SSCursor
                )

默认情况下，光标会一次性获取所有数据，即使你没有使用 fetchall。

补充说明：SSCursor 或其他任何支持服务器端结果集的光标类 - 可以查看 MySQLdb.cursors 模块的文档。