Python3 + sqlite3: Where's the bug?

[Johannes Bauer]

On 20.12.2012 16:05, Chris Angelico wrote:
> On Fri, Dec 21, 2012 at 1:52 AM, Johannes Bauer <dfnsonfsduifb at gmx.de> wrote:
>> def fetchmanychks(cursor):
>>         cursor.execute("SELECT id FROM foo;")
>>         while True:
>>                 result = cursor.fetchmany()
>>                 if len(result) == 0:
>>                         break
>>                 for x in result:
>>                         yield x
> 
> I'm not familiar with sqlite, but from working with other databases,
> I'm wondering if possibly your commits are breaking the fetchmany.

Hmm, but this:

def fetchmanychks(cursor):
	cursor.execute("SELECT id FROM foo;")
	while True:
		result = cursor.fetchone()
		if result is not None:
			yield result
		else:
			break

Works nicely -- only the fetchmany() makes the example break.

> Would it spoil your performance improvements to do all the fetchmany
> calls before yielding anything?

Well this would effectively then be a fetchall() call -- this is
problematic since the source data is LARGE (spekaing of gigabytes of
data here).

> Alternatively, can you separate the
> two by opening a separate database connection for the foo-reading (so
> it isn't affected by the commit)?

At that point in the code I don't actually have a filename anymore,
merely the connection. But shouldn't the cursor actually be the
"correct" solution? I.e. in theory, should the example work at all or am
I thinking wrong?

Because if I'm approaching this from the wrong angle, I'll have no
choice but to change all that code to open separate connections to the
same file (something that currently are no provisions for).

Best regards,
Johannes

-- 
>> Wo hattest Du das Beben nochmal GENAU vorhergesagt?
> Zumindest nicht öffentlich!
Ah, der neueste und bis heute genialste Streich unsere großen
Kosmologen: Die Geheim-Vorhersage.
 - Karl Kaos über Rüdiger Thomas in dsa <hidbv3$om2$1 at speranza.aioe.org>