how not to run out of memory in cursor.execute

[amberite]

johnlichtenstein at gmail.com wrote:
> I am using cx_Oracle and MySQLdb to pull a lot of data from some tables
> and I find that the cursor.execute method uses a lot of memory that
> never gets garbage collected. Using fetchmany instead of fetchall does
> not seem to make any difference, since it's the execute that uses
> memory. Breaking the query down to build lots of small tables doesn't
> help, since execute doesn't give its memory back, after reading enough
> small tables execute returns a memory error. What is the trick to get
> memory back from execute in cx_Oracle and MySQLdb?

cx_Oracle and MySQLdb must be handled differently, due to the fact that
MySQL does not actually have cursors (MySQLdb fakes them for you).

To handle large resultsets efficiently in cx_Oracle simply use the
cursor iteration idiom:

for row in cursor:
    # do stuff with the row

cx_Oracle takes care of the fetching for you, and your memory usage
should remain fairly constant when using this idiom.

To handle large resultsets in MySQLdb, you have to resort to multiple
queries:

l = 1000
o = 0

cursor.execute('SELECT foo FROM Bar LIMIT %d OFFSET %d', (l, o))
rows = cursor.fetchall()
while len(rows) > 0:
    # process the fetched rows
    o += l
    cursor.execute('SELECT foo FROM Bar LIMIT %d OFFSET %d', (l, o))
    rows = cursor.fetchall()

cursor.close()

As you can see, the MySQLdb version is more involved, due to the lack
of real cursor support in the MySQL database.  Any database with good
cursor support will likely have good cursor iteration support in the
corresponding DBAPI driver.

Hope this helps,

L. Daniel Burr