Re: how not to run out of memory in cursor.execute

amberite wrote:
johnlichtenstein@xxxxxxxxx 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

The MySQLdb solution you give is way more complicated than it needs to be, thereby skewing your opinion towards cx_Oracle unnecessarily.

Look up the .fetchmany() method of cursors in the DB API. There is only any need to execute a single query no matter how large the result set: you simply need to keep calling .fetchmany(N) (where N is whatever you've decided by testing is your optimum chunk size) until it returns less than N rows, at which point you have exhausted the query.

It's very little more effort to wrap this all up as a generator that effectively allows you to use the same solution as you quote for cx_Oracle.

regards
Steve

--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Love me, love my blog http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

.

Relevant Pages

  • Re: how not to run out of memory in cursor.execute
    ... and I find that the cursor.execute method uses a lot of memory that ... help, since execute doesn't give its memory back, after reading enough ... memory back from execute in cx_Oracle and MySQLdb? ... cursor iteration idiom: ...
    (comp.lang.python)
  • Re: how not to run out of memory in cursor.execute
    ... and I find that the cursor.execute method uses a lot of memory that ... help, since execute doesn't give its memory back, after reading enough ... memory back from execute in cx_Oracle and MySQLdb? ... cursor iteration idiom: ...
    (comp.lang.python)
  • Re: how not to run out of memory in cursor.execute
    ... and I find that the cursor.execute method uses a lot of memory that ... For MySQLdb, the SSCursor class, rather than the ... help, since execute doesn't give its memory back, after reading enough ... the trick is to avoid consuming the memory in the first ...
    (comp.lang.python)
  • how not to run out of memory in cursor.execute
    ... 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 ... help, since execute doesn't give its memory back, after reading enough ...
    (comp.lang.python)
  • [UNIX] Linux Kernel binfmt_elf ELF Loader Privilege Escalation
    ... Get your security news from a reliable source. ... or in other words to execute a new program. ... One of the Linux format loaders is the ELF (Executable and Linkable ... of the memory map header in the binary image and the program ...
    (Securiteam)