sqlite savepoint problem [solved]

Laszlo Nagy gandalf at shopzeus.com
Fri Mar 12 09:35:27 CET 2010


> No it doesn't.  The problem is that using a connection as a context
> manager doesn't do what you think. 
>
> It does *not* start a new transaction on __enter__ and commit it on
> __exit__.  As far as I can tell it does nothing on __enter__ and calls
> con.commit() or con.rollback() on exit.  With isolation_level=None,
> these are no-ops.
>   
Thank you Ryan! You are abolutely right, and thank you for reading the 
source.  Now everything works as I imagined.

The way the context manager and isolation_level works looks very very 
strange to me. Here is a demonstration:

import sqlite3
def getconn():
    conn = sqlite3.connect(':memory:')
    conn.isolation_level = None
    return conn
def main():
    with getconn() as conn:
        conn.execute("create table a ( i integer ) ")
    try:
        conn.execute("insert into a values (1)")
        with conn:
            conn.execute("insert into a values (2)")
            raise Exception
    except:
        print "There was an error"
    for row in conn.execute("select * from a"):
        print row
main()


Output:

There was an error
(1,)
(2,)


Looks like the context manager did not roll back anything. If I remove 
isolation_level=None then I get this:

There was an error

E.g. the context manager rolled back something that was executed outside 
the context. I cannot argue with the implementation - it is that way. 
But this is not what I would expect. I believe I'm not alone with this.

Using your connection manager, everything is perfect:

There was an error
(1,)


The only thing I have left is to implement a connection manager that 
emulates nested transactions, using a stack of savepoints. :-)

Suggestions:

Just for clarity, we should put a comment at the end of the 
documentation here:

http://docs.python.org/library/sqlite3.html#sqlite3-controlling-transactions

I would add at least these things:

#1. By using isolation_level = None, connection objects (used as a 
context manager) WON'T automatically commit or rollback transactions.
#2. Using any isolation level, connection objects WON'T automatically 
begin a transaction.
#3. Possibly, include your connection manager class code, to show how to 
do it "the expected" way.

Also one should clarify in the documentation, what isolation_level does. 
Looks like setting isolation_level to None is not really an "auto commit 
mode". It is not even part of sqlite itself. It is part of the python 
extension.

Thank you again.

   Laszlo




More information about the Python-list mailing list