General discussion about the SQLite embedded database.

Jim Wilcoxson | 1 Jul 2009 03:32

Re: Python sqlite binding: commit inside select loop

I did some checking on the pysqlite mailing list.  Apparently the
change to reset all cursors before commit is recent, and the reason is
that pysqlite or sqlite itself would return bogus rows.  There is an
example here:

http://article.gmane.org/gmane.comp.python.db.pysqlite.user/2217

Returning old or new data would be one thing, but in this example,
totally wrong data is returned.  There's no explanation of why it was
happening.

On 6/30/09, Jim Wilcoxson <prirun@...> wrote:
> Thanks for the comments and explanations everyone - much appreciated.
> It seems there are a few alternatives I can check out.
>
> Jim
>
> On 6/30/09, Roger Binns <rogerb@...> wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Jim Wilcoxson wrote:
>>> I guess I am a bit confused.  Igor says it's not possible, but Roger
>>> says my example works.
>>
>> For the exact code you were running the error message came from pysqlite
>> and not from SQLite.  Other posters are not aware of the underlying
>> implementation details in pysqlite and it doing all sorts of transaction
>> related stuff behind the scenes, and so were telling you about SQLite
>> behaviour in general.  In summary the behaviour you see from pysqlite

(Continue reading)

headers

Roger Binns | 1 Jul 2009 05:25

Re: Python sqlite binding: commit inside select loop

Jim Wilcoxson wrote:
> I did some checking on the pysqlite mailing list.  Apparently the
> change to reset all cursors before commit is recent, and the reason is
> that pysqlite or sqlite itself would return bogus rows.

It is pysqlite returning the bogus rows, and again entirely due to
pysqlite doing transaction management behind the scenes.  Use whatever
isolation level means it stops doing that, accept things the way they
are, or use APSW.

> Returning old or new data would be one thing, but in this example,
> totally wrong data is returned.  There's no explanation of why it was
> happening.

Long boring section:

The data is "totally wrong" in the sense that outstanding cursors would
still return data from before rollbacks.  It won't return random rubbish
from thin air.  Duplication arises because of how cursors are
implemented behind the scenes.  They have to be both greedy and passive.
 The statement is initially prepared using sqlite3_prepare but nothing
happens till sqlite3_step is called which will execute the statement to
completion (eg if it was "create table foo(x)") or till a row is
returned (eg if it was "select * from foo").  Calling next()/using the
cursor as an iterator will call sqlite3_step to get the next row (being
passive) but you don't want the user to have to call next() for "create
table foo(x)" to be executed so the initial execute is greedy in case
the statement executes to completion.  The cursor has internal state
tracking (entirely within pysqlite or APSW for that matter) as to

(Continue reading)