Python3 + sqlite3: Where's the bug?

[Hans Mulder]

On 20/12/12 16:20:13, Johannes Bauer wrote:
> 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.

Yes, that's what it looks like.

I think that should be considered a bug in 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.

On my system, fetchmany() defaults to returning only one row.

The documentation says that the default should be the optimal
number of rows per chunk for the underlying database engine.
If the optimum is indeed fetchone one row at a time, then
maybe you could consider using fetchone() as a work-around.

>> 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 (speaking 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?

I think you're right and that fetchmany is broken.

> 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).


Hope this helps,

-- HansM