Problem with sqlite3 cursor and imbricated for loop

[Gerhard Häring]

Charles V. wrote:
> Hi,
> 
> Thank for replying.
> 
>> Either use a second cursor OR ensure you fetch all the data from the
>> first .execute() first:
> 
> Are these really the only solutions ? 

Yes.

> I was expecting the same behavior than 
> MySQLdb module, which is, as sqlite3, DB-API 2.0 compatible.

Both may be standard compliant, but if you're depending on 
implementation details, you may still get different behaviour.
I'm pretty sure that MySQLdb always fetches the entire resultset from 
the server. The sqlite3 module uses what would have been called 
"server-side cursors" in real databases, i. e. it only fetches rows on 
demand. To fetch everything in one go with the sqlite3 module, you have 
to call fetchall() explicitly.

> It means a program written for MySQLdb won't be compatible with sqlite3 (even 
> if I am using standard SQL). In fact I don't really understand why the 
> iterator isn't in some way "encapsulated". [...]

I feel with you. The fact that cursors, and not connection objects have 
the executeXXX methods is totally braindead.

That's why pysqlite (sqlite3) has alternative nonstandard executeXXX 
methods in the connection object that return cursors.

-- Gerhard