mysqldb总是将整个查询结果拉成一个块，即使我只是做一个fetchone吗? [英] mysqldb pulls whole query result in one chunk always even if I just do a fetchone?

问题描述

所以，如果我这样做

 import MySQLdb
 conn = MySQLdb.connect(...)
 cur = conn.cursor()
 cur.execute("SELECT * FROM HUGE_TABLE")
 print "hello?"
 print cur.fetchone() 

在我看来，MySQLdb在进入打印"之前先获得了整个巨大的表. 我以前以为它在后台执行了某种游标/状态"懒惰检索， 但在我看来却不一样. 这是正确的吗?如果是这样，是因为必须采用这种方式，还是由于某种限制 线协议的概念?这是否意味着java/hibernate的行为方式相同?

It looks to me that MySQLdb gets the entire huge table before it gets to the "print". I previously assumed it did some sort of "cursor/state" lazy retrieval in the background, but it doesn't look like it to me. Is this right? If so is it because it has to be this way or is this due to a limitation of the MySQL wire protocol? Does this mean that java/hibernate behave the same way?

我想如果要遍历，我需要使用"limit 1" MySQL子句和亲戚 一张大桌子却不把整个东西都拉进去吗?或者没有?预先感谢.

I guess I need to use the "limit 1" MySQL clauses and relatives if I want to walk through a large table without pulling in the whole thing at once? Or no? Thanks in advance.

推荐答案

在 _mysql 模块中，使用以下调用:

In the _mysql module, use the following call:

conn.use_result()

这告诉连接您要一张一行地获取行，将其余部分保留在服务器上(但将游标保持打开状态).

That tells the connection you want to fetch rows one by one, leaving the remainder on the server (but leaving the cursor open).

替代方法(默认)是:

conn.store_result()

这告诉连接在执行查询后获取整个结果集，随后的获取将仅遍历结果集，该结果集现在位于您的Python应用程序的内存中.如果结果集很大，则应考虑使用LIMIT将其限制为可以处理的内容.

This tells the connection to fetch the entire result set after executing the query, and subsequent fetches will just iterate through the result set, which is now in memory in your Python app. If your result set is very large, you should consider using LIMIT to restrict it to something you can handle.

请注意，在从已打开的行中取出所有行之前，MySQL不允许运行其他查询.

Note that MySQL does not allow another query to be run until you have fetched all the rows from the one you have left open.

在 MySQLdb 模块中，等效项是使用来自MySQLdb.cusrors的这两个不同的游标对象之一:

In the MySQLdb module, the equivalent is to use one of these two different cursor objects from MySQLdb.cusrors:

• CursorUseResultMixIn
• CursorStoreResultMixIn

这篇关于mysqldb总是将整个查询结果拉成一个块，即使我只是做一个fetchone吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！