Re: modify cycles

Eric S. Johansson wrote:
> how does one implement an atomic modify cycle in pysqlite??  I need to 
>   modify records without worrying about another thread/process changing 
> it first.  I understand I can't use an explicit TRANSACTION but I'm not 
> seeing how to group a SELECT, my modify code, MODIFY, and a COMMIT(??) 
> into one atomic operation.

Use a transaction.

I suspect you are falling foul of pysqlite's habit of starting 
transactions for you. You can control this by setting a connection 
variable called isolation_level to None (it defaults to '') as the 
Python 2.5 session below shows.

 >>> import sqlite3
 >>> C=sqlite3.Connection(":memory:")
 >>> C.isolation_level=None
 >>> c=C.cursor()
 >>> C.execute("begin")
<sqlite3.Cursor object at 0x00D8AF50>

 >>> c.execute("insert into t values(1,2,3)")
<sqlite3.Cursor object at 0x00D8AEF0>

 >>> c.execute("insert into t values(1,2,3)")
<sqlite3.Cursor object at 0x00D8AEF0>

 >>> c.execute("update t set b=99 where oid=2")
<sqlite3.Cursor object at 0x00D8AEF0>

 >>> c.execute("end")
<sqlite3.Cursor object at 0x00D8AEF0>

 >>> c.execute("select * from t").fetchall()
[(1, 2, 3), (1, 99, 3), (1, 2, 3)]

Unless you set isolation_level to None pysqlite will start a transaction 
when it sees an insert so if you've explicitly started a transaction the 
SQLite library (which does not support nested transactions) will report 
an error about nested transactions. Owing to a bug in pysqlite this will 
be reported as "SQL logic error or missing database".

 >>> C=sqlite3.Connection(":memory:")
 >>> c=C.cursor()
 >>> C.execute("begin")
<sqlite3.Cursor object at 0x00D82BC0>

 >>> c.execute("create table t(a,b,c)")
<sqlite3.Cursor object at 0x00D9BE30>

 >>> c.execute("insert into t values(1,2,3)")
Traceback (most recent call last):
   File "<interactive input>", line 1, in <module>
OperationalError: SQL logic error or missing database
 >>>

Martin