Using savepoints in python sqlite3

Question

I'm attempting to use savepoints with the sqlite3 module built into python 2.6. Every time I try to release or rollback a savepoint, I always recieve an OperationalError: no such savepoint. What am I missing?

python version: 2.6.4 (r264:75821M, Oct 27 2009, 19:48:32) 
[GCC 4.0.1 (Apple Inc. build 5493)]
PySQLite version: 2.4.1
sqlite3 version: 3.6.11

Traceback (most recent call last):
  File "spDemo.py", line 21, in <module>
    conn.execute("release savepoint spTest;")
sqlite3.OperationalError: no such savepoint: spTest

from this code:

import sys
import sqlite3

print 'python version:', sys.version
print 'PySQLite version:', sqlite3.version
print 'sqlite3 version:', sqlite3.sqlite_version
print

conn = sqlite3.connect('db_spDemo.db')
conn.isolation_level = "DEFERRED"

with conn:
    conn.execute("create table example (A, B);")

with conn:
    conn.execute("insert into example values (?, ?);", (0,200))

    conn.execute("savepoint spTest;")
    conn.execute("insert into example values (?, ?);", (1,201))
    conn.execute("insert into example values (?, ?);", (2,202))
    conn.execute("release savepoint spTest;")

    conn.execute("insert into example values (?, ?);", (5,205))

Evetteevey

Related: stackoverflow.com/questions/1654857 – Diecious

I know this is an old question but according to the docs, you cannot mix begin/commit transactions with savepoint's sqlite.org/lang_transaction.html Seek/search for the word "nest" or the paragraph starting with "Transactions created using BEGIN...COMMIT do not nest. " – Gnotobiotics

Answer 1

This appears to be a result of how the sqlite3 module behaves with that isolation level.

This works, notice the two changes:

import sys
import sqlite3

print 'python version:', sys.version
print 'PySQLite version:', sqlite3.version
print 'sqlite3 version:', sqlite3.sqlite_version
print

conn = sqlite3.connect('shane.sqlite')
conn.isolation_level = None  # CHANGED

with conn:
    conn.execute("create table example (A, B);")

with conn:
    conn.execute("insert into example values (?, ?);", (0,200))

    conn.execute("savepoint spTest;")
    conn.execute("insert into example values (?, ?);", (1,201))
    conn.execute("insert into example values (?, ?);", (2,202))
    conn.execute("rollback to savepoint spTest;")  # CHANGED

    conn.execute("insert into example values (?, ?);", (5,205))

Output:

$ python shane-sqlite3.py && sqlite3 shane.sqlite 'select * from example;'
python version: 2.6.2 (release26-maint, Apr 19 2009, 01:56:41) 
[GCC 4.3.3]
PySQLite version: 2.4.1
sqlite3 version: 3.6.10

0|200
5|205

This is an unsatisfactory answer, and I didn't see anything relevant in the sqlite3 module docs (nor did I try to take a look at the source). But I hope it helps you find the right direction.

Diecious

I never considered trying it without any outer transaction level, as the SQL docs indicated that the savepoints can be used in conjunction with the transactions. This hints to me that maybe I need a certain pragma to make it all work together! Thanks for giving me a good direction for more investigation.. – Evetteevey

Answer 2

This is a bug in pysqlite, see the pysql issue tracker and the python issue tracker.

Undeniable