cursor.fetchall() vs list(cursor) in Python

by

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.

More Related Contents:

  1. pip install mysql-python fails with EnvironmentError: mysql_config not found
  2. “Cannot open include file: ‘config-win.h’: No such file or directory” while installing mysql-python
  3. Python mysqldb: Library not loaded: libmysqlclient.18.dylib
  4. Database does not update automatically with MySQL and Python
  5. mysql-python install error: Cannot open include file ‘config-win.h’
  6. How to insert pandas dataframe via mysqldb into database?
  7. Replacing Pandas or Numpy Nan with a None to use with MysqlDB
  8. Install mysql-python (Windows)
  9. Output pyodbc cursor results as python dictionary
  10. Installing mysqlclient in Python 3.6 in windows
  11. Python MYSQL update statement
  12. Python 3.4.0 with MySQL database
  13. How can I use executemany to insert into MySQL a list of dictionaries in Python
  14. Python MySQLdb execute table variable [duplicate]
  15. Python and MySQLdb: substitution of table resulting in syntax error
  16. MySQL: Get column name or alias from query
  17. How to efficiently use MySQLDB SScursor?
  18. ImportError: No module named MySQLdb
  19. Print the actual query MySQLdb runs?
  20. Can’t install mysql-python (newer versions) in Windows
  21. Python MySQLdb: connection.close() VS. cursor.close()
  22. What’s the difference between MySQLdb, mysqlclient and MySQL connector/Python?
  23. Python MySQLDB: Get the result of fetchall in a list
  24. How to use python mysqldb to insert many rows at once
  25. About MySQLdb conn.autocommit(True)
  26. Why do you need to create a cursor when querying a sqlite database?
  27. ‘pip install MySQL-python’ fails with ‘IndexError’
  28. ModuleNotFoundError: No module named ‘MySQLdb’
  29. How to disable query cache with mysql.connector
  30. What’s the most efficient way to convert a MySQL result set to a NumPy array?

Leave a Comment