python sqlite 3 : roll back to save point fails.

Question

def rollback_savepoint(self):
    try:
        self.db.execute("rollback to savepoint pt;")
    except:
        print "roll back to save point failed"
    else:
        print "Roll back to save point. Done"

In above code snippet , It says "roll back to save point failed". What went wrong?

EDIT: I changed the code as shown below and getting error messages

self.db.execute("savepoint pt;")
print "Save point created"

self.cursor.execute("insert into STK values(33)")
self.db.execute("rollback to savepoint pt;")

error

Save point created
Traceback (most recent call last):
  File "open_db.py", line 77, in <module>
    obj1.save_point()
  File "open_db.py", line 63, in save_point
    self.db.execute("rollback to savepoint pt;")
sqlite3.OperationalError: no such savepoint: pt

Answer 1

Don't ever catch exceptions you aren't handling. Let it raise, so you can have useful error messages and tracebacks.

Example:

>>> c.execute('rollback to savepoint pt;')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such savepoint: pt

From the traceback I can know the error is that there's no pt savepoint. I can't know what's wrong with yours because you're hiding the very information that can help you most. Catching all errors and printing some "Failed" message is dumb - the traceback is much more useful and explains the problem better.

---

EDIT: Your code wasn't exactly a easy-to-run testcase, but by reading it I was able to write some code myself to reproduce the issue. I can't explain exactly what is going on yet, but I found a way to make it work - related to how sqlite3 module deals with transactions.

Here's my full, runnable example:

import sqlite3
from tempfile import NamedTemporaryFile as NF
import os

f = NF(suffix='.db', delete=False).name

db = sqlite3.connect(f)

try:
    db.execute('CREATE TABLE foo (id INTEGER PRIMARY KEY, data VARCHAR)')
    db.isolation_level = None

    db.execute('INSERT INTO foo (data) values (?)', ('hello',))
    db.execute('INSERT INTO foo (data) values (?)', ('world',))

    db.execute("savepoint pt;")
    db.execute('INSERT INTO foo (data) values (?)', ('bah',))
    db.execute('INSERT INTO foo (data) values (?)', ('goodbye world',))
    db.execute("rollback to savepoint pt;")

    db.execute('INSERT INTO foo (data) values (?)', ('peace',))

    assert list(db.execute('select * from foo')) == [(1, 'hello'),
                                                     (2, 'world'),
                                                     (3, 'peace')]
finally:
    db.close()
    os.remove(f)

The line that makes it work is db.isolation_level = None. If you comment it out, it breaks just like your error. I've tried using all the documented values “DEFERRED”, “IMMEDIATE” and “EXCLUSIVE”, all ended in error.