cursor.fetchall（）vs Python中的list（游标）

Both methods return a list of the returned items of the query, did I miss something here?
Or they have identical usages indeed?
Any differences performance-wise?

两种方法都返回查询返回项的列表,我在这里错过了什么吗?或者他们确实有相同的用法?性能方面的任何差异?

4 个解决方案

#1

If you are using the default cursor, a MySQLdb.cursors.Cursor, the entire result set will be stored on the client side (i.e. in a Python list) by the time the cursor.execute() is completed.

如果您使用的是默认游标MySQLdb.cursors.Cursor,则整个结果集将在cursor.execute()完成时存储在客户端(即Python列表中)。

Therefore, even if you use

因此,即使你使用

for row in cursor:

you will not be getting any reduction in memory footprint. The entire result set has already been stored in a list (See self._rows in MySQLdb/cursors.py).

你不会减少内存占用。整个结果集已经存储在一个列表中(参见MySQLdb / cursors.py中的self._rows)。

However, if you use an SSCursor or SSDictCursor:

但是,如果您使用SSCursor或SSDictCursor:

import MySQLdb
import MySQLdb.cursors as cursors

conn = MySQLdb.connect(..., cursorclass=cursors.SSCursor)

then the result set is stored in the server, mysqld. Now you can write

然后结果集存储在服务器mysqld中。现在你可以写了

cursor = conn.cursor()
cursor.execute('SELECT * FROM HUGETABLE')
for row in cursor:
    print(row)

and the rows will be fetched one-by-one from the server, thus not requiring Python to build a huge list of tuples first, and thus saving on memory.

并且将从服务器逐个获取行,因此不需要Python首先构建庞大的元组列表,从而节省内存。

Otherwise, as others have already stated, cursor.fetchall() and list(cursor) are essentially the same.

否则,正如其他人已经说过的那样,cursor.fetchall()和list(cursor)基本相同。

#2

cursor.fetchall() and list(cursor) are essentially the same. The different option is to not retrieve a list, and instead just loop over the bare cursor object:

cursor.fetchall()和list(cursor)基本相同。不同的选择是不检索列表,而只是循环遍历裸光标对象:

for result in cursor:

This can be more efficient if the result set is large, as it doesn't have to fetch the entire result set and keep it all in memory; it can just incrementally get each item (or batch them in smaller batches).

如果结果集很大,这可能会更有效,因为它不必获取整个结果集并将其全部保存在内存中;它可以逐步获取每个项目(或以较小的批次批量处理)。

#3

A (MySQLdb/PyMySQL-specific) difference worth noting when using a DictCursor is that list(cursor) will always give you a list, while cursor.fetchall() gives you a list unless the result set is empty, in which case it gives you an empty tuple. This was the case in MySQLdb and remains the case in the newer PyMySQL, where it will not be fixed for backwards-compatibility reasons. While this isn't a violation of Python Database API Specification, it's still surprising and can easily lead to a type error caused by wrongly assuming that the result is a list, rather than just a sequence.

使用DictCursor时值得注意的(MySQLdb / PyMySQL特定的)差异是list(cursor)总是给你一个列表,而cursor.fetchall()给你一个列表,除非结果集是空的,在这种情况下它给出你是一个空元组。这是MySQLdb中的情况,并且在较新的PyMySQL中仍然存在这种情况,由于向后兼容性的原因,它不会被修复。虽然这不违反Python数据库API规范,但它仍然令人惊讶,并且很容易导致由于错误地假设结果是列表而不仅仅是序列而导致的类型错误。

Given the above, I suggest always favouring list(cursor) over cursor.fetchall(), to avoid ever getting caught out by a mysterious type error in the edge case where your result set is empty.

鉴于上述情况,我建议始终支持list(cursor)over cursor.fetchall(),以避免在结果集为空的边缘情况下被神秘类型错误捕获。

#4

list(cursor) works because a cursor is an iterable; you can also use cursor in a loop:

list(cursor)有效,因为游标是可迭代的;你也可以在循环中使用游标:

for row in cursor:
    # ...

A good database adapter implementation will fetch rows in batches from the server, saving on the memory footprint required as it will not need to hold the full result set in memory. cursor.fetchall() has to return the full list instead.

一个好的数据库适配器实现将从服务器批量获取行,节省了所需的内存占用,因为它不需要在内存中保存完整的结果集。 cursor.fetchall()必须返回完整列表。

There is little point in using list(cursor) over cursor.fetchall(); the end effect is then indeed the same, but you wasted an opportunity to stream results instead.

使用list(cursor)而不是cursor.fetchall();那么最终效果确实是相同的,但是你浪费了一个改变结果的机会。

#1