Re: [sqlite] Python sqlite binding: commit inside select loop

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

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
whether it is fresh (ie just prepared/in the statement cache), done
initial execution, sitting at rows, row consumed, exhausted etc with
various calls/being used as an iterator adjusting that state.  Depending
on the interaction between pysqlite behind your back transaction
management, iteration, state of the cursors and possibly a bug or too,
you could get the same row appear to be returned twice as well as a
result row ignoring rollbacks.  I don't see how something like that
could happen with commit, but then again I wrote my interface layer so
that it works the way SQLite does and does not try to fight SQLite :-)

Look at
http://oss.itsystementwicklung.de/trac/pysqlite/browser/src/cursor.c for
the pysqlite code.  That file is the second largest in the source to
give you an idea of the complexity (the same is true for APSW).

BTW there is even more complexity than I described.  The cursor
execution also has to take into account bindings sequences such as when
executemany was called.  pysqlite chickened out and won't execute
multiple statements (eg cursor.execute("do one thing;do another").  It
does have executescript that will execute them all, but that ignores any
returned data!

This page gives you some idea of the differences between a wrapper that
follows DBAPI and pretends SQLite does too (ie pysqlite) and a wrapper
that slavishly follows how SQLite works even if that is different to
other databases (ie APSW):

  http://apsw.googlecode.com/svn/publish/pysqlite.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkpK1ygACgkQmOOfHg372QRg9wCgvDDrA6lduq+4CilO5NCiuGlR
nAwAoMziOD7TnsrR0xTNiPlX8GK1NsJ0
=OJfe
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python sqlite binding: commit inside select loop

[Jim Wilcoxson]

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  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  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
>> will not necessarily match the behaviour you would see if using the
>> SQLite C api directly to do the same thing.
>>
>> It was issues like this that led me to create APSW in the first place
>> because I wanted the exact SQLite semantics and not some random
>> "standard" that tried to pretend all database apis behave in exactly the
>> same way.  I also document which SQLite apis are called from each Python
>> api and the index lets you work in the reverse direction:
>>
>>   http://apsw.googlecode.com/svn/publish/genindex.html#S
>>
>>> One other point of confusion is that sometimes an interface layer will
>>> grab all or a bunch of the rows after a select, even though it may
>>> hand them to upper layers one row at a time.
>>
>> Note that none of the Python apis do that by default and you have to
>> call a method to grab all the results.  The apis for other databases do
>> do that by default since they use the network and grabbing result rows
>> one at a time would be far slower than batching them.
>>
>>> For my particular app, I'm only adding new rows, so there wouldn't be
>>> too many weird effects.
>>
>> There was a poster (I forget if it was this list or the pysqlite/APSW
>> one) who managed to get an infinite loop.  Effectively he was changing a
>> rowid for each row found (an UPDATE was doing it - rowid wasn't
>> explicitly mentioned in the query) while doing a select over all items.
>>  The constantly updated rowid effectively added the changed row to the
>> end of the select results and so it carried on forever.
>>
>> Although the relevant information is in my APSW docs, I think I need to
>> pull more of it together and make things clearer.
>>
>> Roger
>> -BEGIN PGP SIGNATURE-
>> Version: GnuPG v1.4.9 (GNU/Linux)
>>
>> iEYEARECAAYFAkpKfdYACgkQmOOfHg372QTAjQCeL3zZCBh8Pgnh/GT95RH0JIY0
>> qGgAoKsqr66QwxKDtQNqe6W1jayU90/J
>> =Xs+y
>> -END PGP SIGNATURE-
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> Software first.  Software lasts!
>


-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python sqlite binding: commit inside select loop

[Jim Wilcoxson]

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  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
> will not necessarily match the behaviour you would see if using the
> SQLite C api directly to do the same thing.
>
> It was issues like this that led me to create APSW in the first place
> because I wanted the exact SQLite semantics and not some random
> "standard" that tried to pretend all database apis behave in exactly the
> same way.  I also document which SQLite apis are called from each Python
> api and the index lets you work in the reverse direction:
>
>   http://apsw.googlecode.com/svn/publish/genindex.html#S
>
>> One other point of confusion is that sometimes an interface layer will
>> grab all or a bunch of the rows after a select, even though it may
>> hand them to upper layers one row at a time.
>
> Note that none of the Python apis do that by default and you have to
> call a method to grab all the results.  The apis for other databases do
> do that by default since they use the network and grabbing result rows
> one at a time would be far slower than batching them.
>
>> For my particular app, I'm only adding new rows, so there wouldn't be
>> too many weird effects.
>
> There was a poster (I forget if it was this list or the pysqlite/APSW
> one) who managed to get an infinite loop.  Effectively he was changing a
> rowid for each row found (an UPDATE was doing it - rowid wasn't
> explicitly mentioned in the query) while doing a select over all items.
>  The constantly updated rowid effectively added the changed row to the
> end of the select results and so it carried on forever.
>
> Although the relevant information is in my APSW docs, I think I need to
> pull more of it together and make things clearer.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkpKfdYACgkQmOOfHg372QTAjQCeL3zZCBh8Pgnh/GT95RH0JIY0
> qGgAoKsqr66QwxKDtQNqe6W1jayU90/J
> =Xs+y
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python sqlite binding: commit inside select loop

[Roger Binns]

-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
will not necessarily match the behaviour you would see if using the
SQLite C api directly to do the same thing.

It was issues like this that led me to create APSW in the first place
because I wanted the exact SQLite semantics and not some random
"standard" that tried to pretend all database apis behave in exactly the
same way.  I also document which SQLite apis are called from each Python
api and the index lets you work in the reverse direction:

  http://apsw.googlecode.com/svn/publish/genindex.html#S

> One other point of confusion is that sometimes an interface layer will
> grab all or a bunch of the rows after a select, even though it may
> hand them to upper layers one row at a time.

Note that none of the Python apis do that by default and you have to
call a method to grab all the results.  The apis for other databases do
do that by default since they use the network and grabbing result rows
one at a time would be far slower than batching them.

> For my particular app, I'm only adding new rows, so there wouldn't be
> too many weird effects.

There was a poster (I forget if it was this list or the pysqlite/APSW
one) who managed to get an infinite loop.  Effectively he was changing a
rowid for each row found (an UPDATE was doing it - rowid wasn't
explicitly mentioned in the query) while doing a select over all items.
 The constantly updated rowid effectively added the changed row to the
end of the select results and so it carried on forever.

Although the relevant information is in my APSW docs, I think I need to
pull more of it together and make things clearer.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkpKfdYACgkQmOOfHg372QTAjQCeL3zZCBh8Pgnh/GT95RH0JIY0
qGgAoKsqr66QwxKDtQNqe6W1jayU90/J
=Xs+y
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python sqlite binding: commit inside select loop

[Jim Wilcoxson]

I guess I am a bit confused.  Igor says it's not possible, but Roger
says my example works.

One other point of confusion is that sometimes an interface layer will
grab all or a bunch of the rows after a select, even though it may
hand them to upper layers one row at a time.  For example, with
pysqlite, cur.fetchmany() does this.  When this happens, it appears as
if the transaction & commit occur, but the select & fetch loop have
already finished behind the scenes.

For my particular app, I'm only adding new rows, so there wouldn't be
too many weird effects.

Jim

On 6/30/09, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Jim Wilcoxson wrote:
>> With the Python bindings, an error occurs
>> because a commit resets all pending select statements.
>
> Note that there are some constraints about how SQLite works detailed by
> Igor, but in this particular case you are being stymied by pysqlite.
>
> By default pysqlite tries to parse the SQL you execute and does
> transaction management behind the scenes on your behalf (ie calling
> begin and commit as it deems fit).  This is allegedly something required
> by the Python DBAPI standard although I admit I don't understand it
> myself.  Apparently you use the isolation level parameter with pysqlite
> to control how much of this nonsense it does behind the scenes.
>
> If you use apsw (disclaimer: I am the author) then it doesn't do any
> nonsense behind your back and you get (1,) (2,) (3,) printed out twice
> without any exceptions as you were expecting.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkpKZKgACgkQmOOfHg372QSMJwCgkIoZ8VGqUcpn8rMtZPF7kpoF
> vqIAnj5qIaSCy7VTp5mJsAQ4mBVCk+GD
> =SGGD
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python sqlite binding: commit inside select loop

[Marcus Grimm]

thank you very much Richard! very interesting to
get some info about these rather internal issues. This
gives us a good background to review some potential
dangerous loops. Your hint with the temp table is extremely
useful.

Best wishes

Marcus Grimm

>
> On Jun 30, 2009, at 2:34 PM, Marcus Grimm wrote:
>>
>> I'm not sure what will happend if you for example
>> delete a table row that would be the next row that
>> sqlite3_step would see, but doing so to me sounds
>> a bad approach anyhow.
>
> The official policy is that if you modify a table (via INSERT, UPDATE,
> or DELETE) in the middle of a SELECT, then what you get from the
> remainder of the SELECT is undefined.
>
> Sometimes, modifying a table has no effect on the remainder of the
> SELECT.  For example, suppose you are running
>
> SELECT x FROM table1 WHERE y>10 ORDER BY x;
>
> where there is no index on x.  The way SQLite implements this is to
> load all the rows that have y>10 into a temporary table that is sorted
> by x.  Then it starts returning rows of the temporary table one by
> one.  By the time the first row is returned, everything that will ever
> be read from table1 by this query has already been read, so modifying
> table1 at that point will have no effect on subsequent SELECT output.
>
> On the other hand, if you have a query like this:
>
>SELECT x FROM table1 WHERE y>10;
>
> where there is no index on y, then the query will be implemented as a
> full table scan.  SQLite will read the rows starting at the beginning
> of table1 and working toward the end.  As each row is read, it will be
> tested to see if y>10.  If y>10, then the row is returned.  Otherwise,
> SQLite advances to the next row.  In such a scenario, if you modify
> one of the later rows in the table, the modifications will be seen
> when the scan reaches the corresponding position in the table.
>
> Things can get tricky when you start looking at joins.  Consider:
>
> SELECT t1.x, t2.y FROM t1 JOIN t2;
>
> This will typically be implemented as a nested loop:
>
>  for each row in t1:
>   for each row in t2:
>output t1.x, t2.y
>
> If you delete the current row from t1 while there are still more rows
> to go on t2, what output do you get for t1.x on subsequent rows?  The
> answer is "it depends".  Sometimes you will continue to get the
> original value of t1.x.  Other times you will get a NULL.  Whether you
> get the original value of t1.x or NULL might change from one point
> release to the next.
>
> Bottom line:  It is best not to depend on the behavior of a SELECT
> after any row in the table being SELECTed is modified.  SQLite tries
> to do something "sensible".  But sometimes it is not clear what the
> sensible behavior is.  If you need to modify a table as you are
> reading it from a SELECT, consider storing the results of the SELECT
> in a TEMP table, then reading the TEMP table as you modify the
> original table:
>
>   CREATE TEMP TABLE xyz AS SELECT * FROM table1 WHERE;
>   SELECT * FROM xyz;   -- make changes to table1 as xyz is read
>   DROP TEMP TABLE xyz;
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python sqlite binding: commit inside select loop

[Marcus Grimm]

> Marcus Grimm  wrote:
>> what is possible I guess would be to start a transaction
>> inside the loop, do something, and commit and procced
>> with stepping
>
> No, this is not possible either. By starting the select, you also start
> an implicit transaction. SQLite doesn't support nested transactions, so
> you can't start another one in the middle of the loop.

hm... just read again some docs... you are right I guess,
as usual... but reading the docs
(http://www.sqlite.org/lang_transaction.html):
"..The explicit COMMIT command runs immediately, even if there are pending
SELECT statements..."

is still a bit contraire, it sounds like it should be possible
to do what I claimed since I assume only a reading select
statement pending.

anyway, it would be simple to test with a little test code,
I guess I'll do this the next days just to see what happends.
I never did such loops with an explicit transaction inside so far,
only guessing..

>
>> I'm still curious what would be the error code of sqlite3_step
>> if I would delete a row that would be one of the rows
>> reached by the running statement, but that's another issue...;)
>
> There wouldn't be any errors. The row just wouldn't be returned, as if
> it never existed.

I was expecting it to be more complicate because sqlite is running
a sort of machine code while doing the select stepping and for example
if that is using a temporary internal table that looses now an entry in
a source table, for this sqlite would need to re-build a temporary
internal table during each step... or how about a "SELECT ... LIMIT N"
statement that would now need to run one item further than
originally seen by the prepare function?
anyway, I'm afraid I'm about to highjack this thread... ;)

Marcus

>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python sqlite binding: commit inside select loop

[D. Richard Hipp]

On Jun 30, 2009, at 2:34 PM, Marcus Grimm wrote:
>
> I'm not sure what will happend if you for example
> delete a table row that would be the next row that
> sqlite3_step would see, but doing so to me sounds
> a bad approach anyhow.

The official policy is that if you modify a table (via INSERT, UPDATE,  
or DELETE) in the middle of a SELECT, then what you get from the  
remainder of the SELECT is undefined.

Sometimes, modifying a table has no effect on the remainder of the  
SELECT.  For example, suppose you are running

SELECT x FROM table1 WHERE y>10 ORDER BY x;

where there is no index on x.  The way SQLite implements this is to  
load all the rows that have y>10 into a temporary table that is sorted  
by x.  Then it starts returning rows of the temporary table one by  
one.  By the time the first row is returned, everything that will ever  
be read from table1 by this query has already been read, so modifying  
table1 at that point will have no effect on subsequent SELECT output.

On the other hand, if you have a query like this:

   SELECT x FROM table1 WHERE y>10;

where there is no index on y, then the query will be implemented as a  
full table scan.  SQLite will read the rows starting at the beginning  
of table1 and working toward the end.  As each row is read, it will be  
tested to see if y>10.  If y>10, then the row is returned.  Otherwise,  
SQLite advances to the next row.  In such a scenario, if you modify  
one of the later rows in the table, the modifications will be seen  
when the scan reaches the corresponding position in the table.

Things can get tricky when you start looking at joins.  Consider:

SELECT t1.x, t2.y FROM t1 JOIN t2;

This will typically be implemented as a nested loop:

 for each row in t1:
  for each row in t2:
   output t1.x, t2.y

If you delete the current row from t1 while there are still more rows  
to go on t2, what output do you get for t1.x on subsequent rows?  The  
answer is "it depends".  Sometimes you will continue to get the  
original value of t1.x.  Other times you will get a NULL.  Whether you  
get the original value of t1.x or NULL might change from one point  
release to the next.

Bottom line:  It is best not to depend on the behavior of a SELECT  
after any row in the table being SELECTed is modified.  SQLite tries  
to do something "sensible".  But sometimes it is not clear what the  
sensible behavior is.  If you need to modify a table as you are  
reading it from a SELECT, consider storing the results of the SELECT  
in a TEMP table, then reading the TEMP table as you modify the  
original table:

  CREATE TEMP TABLE xyz AS SELECT * FROM table1 WHERE;
  SELECT * FROM xyz;   -- make changes to table1 as xyz is read
  DROP TEMP TABLE xyz;


D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python sqlite binding: commit inside select loop

[Igor Tandetnik]

Marcus Grimm  wrote:
> what is possible I guess would be to start a transaction
> inside the loop, do something, and commit and procced
> with stepping

No, this is not possible either. By starting the select, you also start 
an implicit transaction. SQLite doesn't support nested transactions, so 
you can't start another one in the middle of the loop.

> I'm still curious what would be the error code of sqlite3_step
> if I would delete a row that would be one of the rows
> reached by the running statement, but that's another issue...;)

There wouldn't be any errors. The row just wouldn't be returned, as if 
it never existed.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python sqlite binding: commit inside select loop

[Marcus Grimm]

> Marcus Grimm  wrote:
>> I'm a bit confused about Igors replies because
>> you can very well do a select, step throu the
>> results and even delete or update the table row that you
>> are currently inspecting and procceed with stepping.
>
> ... but you cannot COMMIT your changes while still stepping through the
> select. This is the crux of the OP's problem.

ahh, okay..yes. I missed that this is the intension of the OP:
to start a transaction outside the select loop and COMMIT
sometime inside and procced with stepping.
what is possible I guess would be to start a transaction
inside the loop, do something, and commit and procced
with stepping (actually sqlite does this anyhow internally).
I'm still curious what would be the error code of sqlite3_step
if I would delete a row that would be one of the rows
reached by the running statement, but that's another issue...;)

Best

Marcus

>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python sqlite binding: commit inside select loop

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jim Wilcoxson wrote:
> With the Python bindings, an error occurs
> because a commit resets all pending select statements.

Note that there are some constraints about how SQLite works detailed by
Igor, but in this particular case you are being stymied by pysqlite.

By default pysqlite tries to parse the SQL you execute and does
transaction management behind the scenes on your behalf (ie calling
begin and commit as it deems fit).  This is allegedly something required
by the Python DBAPI standard although I admit I don't understand it
myself.  Apparently you use the isolation level parameter with pysqlite
to control how much of this nonsense it does behind the scenes.

If you use apsw (disclaimer: I am the author) then it doesn't do any
nonsense behind your back and you get (1,) (2,) (3,) printed out twice
without any exceptions as you were expecting.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkpKZKgACgkQmOOfHg372QSMJwCgkIoZ8VGqUcpn8rMtZPF7kpoF
vqIAnj5qIaSCy7VTp5mJsAQ4mBVCk+GD
=SGGD
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python sqlite binding: commit inside select loop

[Igor Tandetnik]

Marcus Grimm  wrote:
> I'm a bit confused about Igors replies because
> you can very well do a select, step throu the
> results and even delete or update the table row that you
> are currently inspecting and procceed with stepping.

... but you cannot COMMIT your changes while still stepping through the 
select. This is the crux of the OP's problem.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python sqlite binding: commit inside select loop

[Marcus Grimm]

I'm a bit confused about Igors replies because
you can very well do a select, step throu the
results and even delete or update the table row that you
are currently inspecting and procceed with stepping.
As long as you use the same database connection for this.

I'm not sure what will happend if you for example
delete a table row that would be the next row that
sqlite3_step would see, but doing so to me sounds
a bad approach anyhow.
I do this directly using the C api, don't know why the
python stuff is different here...

Marcus

> Jim Wilcoxson  wrote:
>> I'm using the Python sqlite3 (pysqlite) bindings.  I'd like to be able
>> to do a select, and while fetching those rows and inserting new ones,
>> periodically do a commit.
>
> You can't do this with SQLite. Commit fails if there are outstanding
> statements.
>
>> So I have a couple of questions/observations:
>>
>> 1. Is the thing about resetting cursors an SQLite requirement, or
>> something the Python binding is doing to protect people from the
>> confusion of seeing the updated rows.
>
> It is SQLite requirement that all statements be reset or finalized
> before a transaction can commit. SQLite doesn't automatically reset
> those statements - it fails the COMMIT operation instead. Apparently,
> Python binding is trying to be helpful and resetting all the statements
> for you. One way or the other, your design ain't gonna fly.
>
>> 2. If I were deleting rows, I can see where that would be a problem:
>> what if some of the rows satisfying the query were deleted?  Once the
>> commit happens, the rows are gone unless there is a delete/add with
>> the same rowid.
>
> None of that is the problem. Realize that your select already sees
> changes made on the same connection, even before they are committed.
>
> The issue has to do with locking. COMMIT needs to release all locks the
> connection holds - but it can't if you still have outstanding
> statements.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python sqlite binding: commit inside select loop

[Igor Tandetnik]

Jim Wilcoxson  wrote:
> I'm using the Python sqlite3 (pysqlite) bindings.  I'd like to be able
> to do a select, and while fetching those rows and inserting new ones,
> periodically do a commit.

You can't do this with SQLite. Commit fails if there are outstanding 
statements.

> So I have a couple of questions/observations:
>
> 1. Is the thing about resetting cursors an SQLite requirement, or
> something the Python binding is doing to protect people from the
> confusion of seeing the updated rows.

It is SQLite requirement that all statements be reset or finalized 
before a transaction can commit. SQLite doesn't automatically reset 
those statements - it fails the COMMIT operation instead. Apparently, 
Python binding is trying to be helpful and resetting all the statements 
for you. One way or the other, your design ain't gonna fly.

> 2. If I were deleting rows, I can see where that would be a problem:
> what if some of the rows satisfying the query were deleted?  Once the
> commit happens, the rows are gone unless there is a delete/add with
> the same rowid.

None of that is the problem. Realize that your select already sees 
changes made on the same connection, even before they are committed.

The issue has to do with locking. COMMIT needs to release all locks the 
connection holds - but it can't if you still have outstanding 
statements.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Python sqlite binding: commit inside select loop

[Jim Wilcoxson]

I'm using the Python sqlite3 (pysqlite) bindings.  I'd like to be able
to do a select, and while fetching those rows and inserting new ones,
periodically do a commit.  With the Python bindings, an error occurs
because a commit resets all pending select statements.  Here is an
example showing the error:

import sqlite3

con = sqlite3.connect('testdb')
con.execute('drop table t')
con.execute('create table t(f)')
con.execute('insert into t (f) values (1)')
con.execute('insert into t (f) values (2)')
con.execute('insert into t (f) values (3)')

print '1st select:'
cur = con.execute('select * from t')
for row in cur:
print row

print '2nd select:'
cur = con.execute('select * from t')
for row in cur:
print row
con.execute('update t set f=2 where f=1')
con.commit()

Running it shows:

1st select:
(1,)
(2,)
(3,)
2nd select:
(1,)
Traceback (most recent call last):
  File "dbbug.py", line 17, in 
for row in cur:
sqlite3.InterfaceError: Cursor needed to be reset because of
commit/rollback and can no longer be fetched from.

I can understand that there is some potential weirdness here in the
case where rows are being deleted or rowid's are getting changed
inside a select, and I think deciding I want to see those updates in
the select results is an isolation level setting - not sure of the
details.  Right now, I don't think it matters whether I see the old or
new values, because I'm mostly just adding rows inside the
transaction, updating some stuff where the old or new value would be
okay, and not doing any deletes.

So I have a couple of questions/observations:

1. Is the thing about resetting cursors an SQLite requirement, or
something the Python binding is doing to protect people from the
confusion of seeing the updated rows.

2. If I were deleting rows, I can see where that would be a problem:
what if some of the rows satisfying the query were deleted?  Once the
commit happens, the rows are gone unless there is a delete/add with
the same rowid.

I'm thinking about removing this reset from pysqlite, but don't want
to trash my database either.

Thanks for any insight.
Jim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users