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?
This question is related to
python
cursor
mysql-python
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.
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)
works because a cursor is an iterable; you can also use cursor
in a loop:
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.
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.
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:
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).
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]
Source: Stackoverflow.com