Sometimes I’ll want to run a query against a MySQL database from Python that returns a large result set. So large in fact that Python hits a memory error.

One way around this is to have the Python MySQL library (MySQLdb) store the results from a query on the database server and bring them back to you one at a time as you request them.

Here’s how you set that up:

import MySQLdb
import MySQLdb.cursors #Make sure to import this seperately

#build your connection object normally but pass it a cursorclass keyword
db=MySQLdb.connect(host=HOSTNAME,user=USERNAME,passwd=PASSWORD,db=DATABASE,
            cursorclass=MySQLdb.cursors.SSCursor)

#Usage

cursor=db.cursor()
cursor.execute('select huge_column from huge_table')
#iterate over the results pulling them down from the server on each iteration
for row in cursor:
    print row
cursor.close()

Posted by Greg Pinero (Primary Searcher) in Python