How to efficiently use MySQLDB SScursor?

I am in agreement with Otto Allmendinger's answer, but to make explicit Denis Otkidach's comment, here is how you can iterate over the results without using Otto's fetch() function:

import MySQLdb.cursorsconnection=MySQLdb.connect(    host="thehost",user="theuser",    passwd="thepassword",db="thedb",    cursorclass = MySQLdb.cursors.SSCursor)cursor=connection.cursor()cursor.execute(query)for row in cursor:    print(row)

Definitely use the SSCursor when fetching big result sets. It made a huge difference for me when I had a similar problem. You can use it like this:

import MySQLdbimport MySQLdb.cursorsconnection = MySQLdb.connect(        host=host, port=port, user=username, passwd=password, db=database,         cursorclass=MySQLdb.cursors.SSCursor) # put the cursorclass herecursor = connection.cursor()

Now you can execute your query with cursor.execute() and use the cursor as an iterator.

Edit: removed unnecessary homegrown iterator, thanks Denis!

Alternatively, you can use SSCursor outside the connection object (it is pretty important when you already define connection and dont want all the connection use SSCursor as a cursorclass).

import MySQLdbfrom MySQLdb.cursors import SSCursor # or you can use SSDictCursorconnection = MySQLdb.connect(        host=host, port=port, user=username, passwd=password, db=database)cursor = SSCursor(connection)cursor.execute(query)for row in cursor:    print(row)