sqlite savepoint problem [solved]

[Laszlo Nagy]

> 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