Use generators for fetching large db record sets (Python recipe) by Christopher Prinos

ActiveState Code (http://code.activestate.com/recipes/137270/)

Use of True and False requires Python 2.2.1. The code is stated to require Python 2.2 or later, but the use of True and False means that Python 2.2.1 will actually be required.

True/False and 2.2.1. Right you are, I updated the comment.

2.2 users can of course just replace True/False with 1/0

code cleanup. Why not instead this? It's a bit cleaner, and certainly easier on the eyes...

def ResultIter(cursor, arraysize=1000):
    'An iterator that uses fetchmany to keep memory usage down'
    while True:
        results = cursor.fetchmany(arraysize)
        if not results:
            break
        for result in results:
            yield result

previous comment fixes problem. MySQLdb returns results as tuples, so the comparison ( results == [] ) fails. The

while True:
  if not results:
    break

approach works correctly.

I am using python 2.7 with pyodbc and freeTDS (and pymongo) to query an mssql database.

I can successfully execute a query that returns 9,400 results, both with and without the python generator recipe. I continue to get the following error for queries returning > 10,000 rows:

Traceback (most recent call last):
  File "/Users/dmoney/eclipse/workspace/mongodb/pygeneratorattempt.py", line 77, in <module>
    for tup in ResultIter(cursor):
  File "/Users/dmoney/eclipse/workspace/mongodb/pygeneratorattempt.py", line 21, in ResultIter
   results = cursor.fetchmany(arraysize)
MemoryError

My script:

from __future__ import generators    #needs to be at the top of your module
def ResultIter(cursor, arraysize=1000):
'An iterator that uses fetchmany to keep memory usage down'
while True:
    results = cursor.fetchmany(arraysize)
    if not results:
        break
    for tup in results:
        yield tup

import pyodbc
url = 'DSN=myserver;UID=myuserid;PWD=mypswrd;PORT=1433;DATABASE=mydb
pyodbcconn = pyodbc.connect(url); cursor = pyodbcconn.cursor(); 
from pymongo import Connection; mongoconn = Connection('localhost') 
from pymongo.collection import Collection
mongodb = mongoconn.t4xquery; 

 for tup in ResultIter(cursor):
     dosomethingwith(tup)

i am a newbie to everything - python, pymongo, sql, etc. I am guessing that the generator is failing to chunk the data, since the generator works with smaller sets, but still gives memory errors with the larger queries?

Thanks for help, -d

In Django, once I had to join to unrelated models-tables to select valid rows, for this I written raw Postgresql and utilized django connection object but I have not write ResultIter kind-of code. In Django, you can find cursor_iter that works well. My code was something like this:

from django.db import connection
from django.db.models.sql.compiler import cursor_iter

def get_table_name(model):
    return model._meta.db_table

def select_pan_invalidname_validname(...):
    query = """
    SELECT ...
    FROM (
      SELECT...
        FROM {table1}
        WHERE ...
      UNION...
      SELECT...
        FROM {table1}
        WHERE ...
    ) viewtable, {table3}
    WHERE ...
    ....
    """.format(
        table1=get_table_name(model1),
        table2=get_table_name(model2),
        table3=get_table_name(model3),        
        arg1=value1,
        .
        .
    )
    cursor = connection.cursor()
    try:
        cursor.execute(query)
    except Exception:
        cursor.close()
        raise
    for rows in cursor_iter(cursor, connection.features.empty_fetchmany_value):
        for row in rows:
            yield row