Geneator/Iterator Nesting Problem - Any Ideas? 2.4

[ChaosKCW]

Hi

Using Python 2.4 I am trying to procduce a generator which will return
the results of dbi SQL statement using fetchmany for performance.

So instead of fetching one record for each call, I will fetch batches
of X (eg 100) and yeild each record in turn.

For reasons of pure asthetics and my own learning I wanted it to look
like this:

</B>
def resultsetbatchg en(cursor, size=100):
for results in (recordbatch for recordbatch in
cursor.fetchman y(size)):
for rec in results:
yield rec
</B>

Problem is this this gives spurious results. To understand the problem
if I excute the following in the console I get the correct results:

</B>

cur.execute(<QU ERY WITH MOER THAN 1000 records>)
recs = (recordbatch for recordbatch in cur.fetchmany(1 000))
sum(map(lambda x: 1, (rec for rec in recs))) 1000
</B>

This is PERFECT! 1000 is what I expected. but now lets nest this inside
another for construct like so

</B> cur.execute(<QU ERY WITH MOER THAN 1000 records>)
for results in (recordbatch for recordbatch in

cur.fetchmany(1 000)):
.... print sum(map(lambda x: 1, (rec for rec in results)))
76
76
76
76
76
76
76
76
............
</B>

Now it thinks each batch size is 76 ... ? This completly wrong, and
baffling.

The commands are exactly the same as far as I can tell, the only
difference is that it is now nested wihtin another for loop?

Any help would be greatly aprpeciated.

PS a working but far less elegant version of this below but I would
like to understand why teh above doesnt work and doesnt work
consitantly:

def resultsetbatch( cursor, size=100):
results = cursor.fetchman y(size)
while results <> []:
for rec in results:
yield rec
results = cursor.fetchman y(size)

[Steven Bethard]

ChaosKCW wrote:

For reasons of pure asthetics and my own learning I wanted it to look
like this:

</B>
def resultsetbatchg en(cursor, size=100):
for results in (recordbatch for recordbatch in
cursor.fetchman y(size)):
for rec in results:
yield rec
</B>

Note that this is equivalent to:

def resultsetbatchg en(cursor, size=100):
for results in cursor.fetchman y(size):
for rec in results:
yield rec

That is, your generator expression isn't really doing anything.

cur.execute (<QUERY WITH MOER THAN 1000 records>)
recs = (recordbatch for recordbatch in cur.fetchmany(1 000))
sum(map(lam bda x: 1, (rec for rec in recs))) 1000

This should be equivalent to
....

recs = iter(cur.fetchm any(1000))
len(list(recs)) cur.execute (<QUERY WITH MOER THAN 1000 records>)
for results in (recordbatch for recordbatch in cur.fetchmany(1 000)): ... print sum(map(lambda x: 1, (rec for rec in results)))

This is now equivalent to:

cur.execute(<QU ERY WITH MOER THAN 1000 records>)
for rec in cur.fetchmany(1 000):

.... print len(list(result s))

Note that you're counting the number of elements in record, which I'm
guessing is 76?

I'm thinking you want to do something like:

def resultsetbatchg en(cursor, size=100):
while True:
results = cursor.fetchman y(size)
if not results:
break
for rec in results:
yield rec

I don't actually know how you tell when there isn't anything more to
fetch, but if it returns an empty list, you could also write this like:

def resultsetbatchg en(cursor, size=100):
for results in iter(lambda: cursor.fetchman y(size), [])
for rec in results:
yield rec
HTH,

STeVe

[ChaosKCW]

Hi

Thanks this was very helpfull.

Your final solution seems like the best (most elegant). I was trying to
avoid the ugly while loops with breaks and this final one certainly
does that.

Thanks for your help .

[ChaosKCW]

For those that are interested I ran a performance comparison of various
functions for wrapping sql results in a interables and generators. The
results are below and the code is at the very bottom.

Surprisinly (in a happy way) the functional version of the batch
retrieve comes in a very close second beating out two other common
iterative approaches. However the winner by a small margin is still an
iterative routine. The elegance of the more functional approach (thanks
to Steve) and its excellent performance makes it a clear winner in my
mind.

The other intersting conclusion is that batch fetching results gives a
fairly significant and real performance boost. Its not just academic.

The Winner:

def resultset_funct ional_batchgene rator(cursor, size=100):
for results in iter(lambda: cursor.fetchman y(size), []):
for rec in results:
yield rec
Test Results (P2.4 IBM T41 Thinkpad):
Ordered by: cumulative time
List reduced from 57 to 7 due to restriction <'test_'>

ncalls tottime percall cumtime percall filename:lineno (function)
1 2.140 2.140 54.002 54.002
PerformanceTest Generators.py:1 02(test_results et_functional_g enerator)
1 1.957 1.957 45.484 45.484
PerformanceTest Generators.py:9 8(test_resultse t_iterative_gen erator)
1 2.433 2.433 41.844 41.844
PerformanceTest Generators.py:9 4(test_resultse t_iterator)
1 1.930 1.930 39.793 39.793
PerformanceTest Generators.py:1 10(test_results et_iterative_ba tchgenerator_2)
1 1.734 1.734 35.561 35.561
PerformanceTest Generators.py:1 14(test_results et_iterative_ba tchgenerator_3)
1 1.980 1.980 34.579 34.579
PerformanceTest Generators.py:1 18(test_results et_functional_b atchgenerator)
1 1.780 1.780 31.696 31.696
PerformanceTest Generators.py:1 06(test_results et_iterative_ba tchgenerator_1)

Code:

import unittest
import odbc
import profile
import pstats

class resultset_itera tor:
"Iterate over the recordset and frees the cursor afterwards."
def __init__(self, cursor):
self.cursor = cursor
def __iter__(self): return self
def next(self):
rec = self.cursor.fet chone()
if not rec:
raise StopIteration
return rec

def resultset_itera tive_generator( cursor):
rec = cursor.fetchone ();
while rec:
yield rec
rec = cursor.fetchone ();

def resultset_funct ional_generator (cursor):
for rec in iter(lambda: cursor.fetchone (), None):
yield rec

def resultset_itera tive_batchgener ator_1(cursor, size=100):
results = cursor.fetchman y(size)
while results:
for rec in results:
yield rec
results = cursor.fetchman y(size)

def resultset_itera tive_batchgener ator_2(cursor, arraysize=100):
'An iterator that uses fetchmany to keep memory usage down'
done = False
while not done:
results = cursor.fetchman y(arraysize)
if results == []:
done = True
for result in results:
yield result

def resultset_itera tive_batchgener ator_3(cursor, size=100):
while True:
results = cursor.fetchman y(size)
if not results:
break
for rec in results:
yield rec

def resultset_funct ional_batchgene rator(cursor, size=100):
for results in iter(lambda: cursor.fetchman y(size), []):
for rec in results:
yield rec

class testResultSetGe nerators(unitte st.TestCase):

connectstring = "*REMOVED*"
sql = "*REMOVED*"

def setUp(self):
self.con = odbc.odbc(self. connectstring)
self.cur = self.con.cursor ()
self.cur.execut e(self.sql)

def tearDown(self):
self.cur.close( )
self.con.close( )

def test_resultset_ iterator(self):
for row in resultset_itera tor(self.cur):
pass

def test_resultset_ iterative_gener ator(self):
for row in resultset_itera tive_generator( self.cur):
pass

def test_resultset_ functional_gene rator(self):
for row in resultset_funct ional_generator (self.cur):
pass

def test_resultset_ iterative_batch generator_1(sel f):
for row in resultset_itera tive_batchgener ator_1(self.cur ):
pass

def test_resultset_ iterative_batch generator_2(sel f):
for row in resultset_itera tive_batchgener ator_2(self.cur ):
pass

def test_resultset_ iterative_batch generator_3(sel f):
for row in resultset_itera tive_batchgener ator_3(self.cur ):
pass

def test_resultset_ functional_batc hgenerator(self ):
for row in resultset_funct ional_batchgene rator(self.cur) :
pass

if __name__ == '__main__':
suite = unittest.makeSu ite(testResultS etGenerators)
profile.run('un ittest.TextTest Runner(verbosit y=2).run(suite) ',
'c:\\temp\\prof ile')

p = pstats.Stats('c :\\temp\\profil e')
p.strip_dirs(). sort_stats('cum ulative').print _stats('test_')

[Peter Otten]

ChaosKCW wrote:

def resultset_funct ional_generator (cursor):
for*rec*in*iter (lambda:*cursor .fetchone(),*No ne):
yield*rec

This can be simplified to

def resultset_funct ional_generator (cursor):
return iter(cursor.fet chone, None)

It should be a bit faster, too.

Peter