Using savepoints in python sqlite3

python, savepoints, sqlite

Solution

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.

Problem

Original source

Related problems

• Nested transactions with SQLAlchemy and sqlite