如何在python中获取一行一行的MySQL ResultSet [英] How to get a row-by-row MySQL ResultSet in python

问题描述

默认情况下，MySQL ResultSets会从服务器上完全检索出来，然后才能进行任何工作.在巨大的结果集的情况下，这变得不可用.相反，我实际上想从服务器中逐行检索行.

MySQL ResultSets are by default retrieved completely from the server before any work can be done. In cases of huge result sets this becomes unusable. I would like instead to actually retrieve the rows one by one from the server.

在Java中，按照说明在这里(在"ResultSet"下)，我创建如下语句:

In Java, following the instructions here (under "ResultSet"), I create a statement like this:

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

这在Java中效果很好.我的问题是:有没有办法在python中做同样的事情?

This works nicely in Java. My question is: is there a way to do the same in python?

我尝试做的一件事是将查询一次限制为1000行，如下所示:

One thing I tried is to limit the query to a 1000 rows at a time, like this:

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

但是，start_row越高，速度似乎越慢.

However, this seems to get slower the higher start_row is.

不，使用fetchone()代替fetchall()不会更改任何内容.

And no, using fetchone() instead of fetchall() doesn't change anything.

我用来重现此问题的朴素代码看起来像这样:

The naive code I use to reproduce this problem looks like this:

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

在约700,000行的表上，此代码快速运行.但是在大约9,000,000行的表上，它会显示正在执行查询"，然后挂起很长时间.这就是为什么我使用fetchone()或fetchall()没什么区别的原因.

On a ~700,000 rows table, this code runs quickly. But on a ~9,000,000 rows table it prints "Executing Query" and then hangs for a long long time. That is why it makes no difference if I use fetchone() or fetchall().

推荐答案

我认为您必须通过cursorclass = MySQLdb.cursors.SSCursor进行连接:

I think you have to connect passing cursorclass = MySQLdb.cursors.SSCursor:

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

即使您不使用fetchall，默认光标也会一次获取所有数据.

The default cursor fetches all the data at once, even if you don't use fetchall.

SSCursor或任何其他支持服务器端结果集的游标类-检查MySQLdb.cursors上的模块文档.

SSCursor or any other cursor class that supports server side resultsets - check the module docs on MySQLdb.cursors.

这篇关于如何在python中获取一行一行的MySQL ResultSet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！