Python 中的 cursor.fetchall() 与 list(cursor)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17861152/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
cursor.fetchall() vs list(cursor) in Python
提问by NZal
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?
这两种方法都返回查询返回项目的列表，我在这里错过了什么吗？
或者它们确实有相同的用法？
在性能方面有什么不同吗？
采纳答案by unutbu
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.
如果您使用默认游标 a MySQLdb.cursors.Cursor，则在完成时整个结果集将存储在客户端（即在 Python 列表中）cursor.execute()。
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._rowsin MySQLdb/cursors.py).
您不会减少内存占用。整个结果集已经存储在一个列表中（参见self._rowsMySQLdb/cursors.py）。
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)基本上是相同的。
回答by Martijn Pieters
list(cursor)works because a cursor is an iterable; you can also use cursorin a loop:
list(cursor)之所以有效，是因为游标是可迭代的；您也可以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 fullresult set in memory. cursor.fetchall()hasto 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)over没有什么意义cursor.fetchall()；最终效果确实是一样的，但你浪费了一个机会来传输结果。
回答by Amber
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).
如果结果集很大，这会更有效，因为它不必获取整个结果集并将其全部保存在内存中；它可以只是增量地获取每个项目（或以较小的批次对它们进行批处理）。
回答by Mark Amery
A (MySQLdb/PyMySQL-specific) difference worth noting when using a DictCursoris that list(cursor)will always give you a list, while cursor.fetchall()gives you a list unlessthe 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 fixedfor 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.
使用 a 时值得注意的（特定于 MySQLdb/PyMySQL 的）差异DictCursor是，它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)于cursor.fetchall()，以避免在结果集为空的边缘情况下被神秘的类型错误捕获。
回答by Cesar Flores
You could use list comprehensions to bring the item in your tuple into a list:
您可以使用列表推导将元组中的项目放入列表中：
conn = mysql.connector.connect()
cursor = conn.cursor()
sql = "SELECT column_name FROM db.table_name;"
cursor.execute(sql)
results = cursor.fetchall()
# bring the first item of the tuple in your results here
item_0_in_result = [_[0] for _ in results]
