Faster Python MySQL

Question

I use mysql.connector (MySQLdb Python implementation?) to access MySQL. The transfer from a select statement from a cursor isn't that fast.

Is there a way to speed up the code?

Maybe another library? Which? (I have Windows and Python 3.1) Maybe a row retrieval different from iterating over the cursor?

Answer 1

The default MySQLdb cursor fetches the entire query result at once from the server. Conversion of this data to a Python list of tuples can consume a lot of memory and time.

Use MySQLdb.cursors.SSCursor when you want to make a huge query and pull results from the server one at a time. Note, however, that when using SSCursor, no other query can be made on the connection until the entire result set has been fetched.

import MySQLdb
import MySQLdb.cursors as cursors
connection = MySQLdb.connect(
    ...
    cursorclass = cursors.SSCursor)
cursor = connection.cursor()
cursor.execute(query)
for row in cursor:
    ...

Or, use oursql, an alternative Python driver for MySQL. One of the features of oursql is that it fetchs rows lazily.