cursor fetchall vs list cursor in Python

Question

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

User · Answer

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

User · Answer

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   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    However  if you use an SSCursor or 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  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   Otherwise  as others have already stated  cursor fetchall   and list cursor  are essentially the same

User · Answer

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

User · Answer

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

User · Answer

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

[python] cursor.fetchall() vs list(cursor) in Python

Examples related to python

Examples related to cursor

Examples related to mysql-python