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))

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. " — David
– David, Commented Feb 8, 2019 at 23:53

Roger Pate · Accepted Answer · 2010-01-10 08:14:36Z

3

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.

answered Jan 10, 2010 at 8:14

Roger Pate

Sign up to request clarification or add additional context in comments.

1 Comment

Shane Holloway Over a year ago

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..

Trenton McKinney · Accepted Answer · 2024-03-29 19:39:10Z

1

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

edited Mar 29, 2024 at 19:39

Trenton McKinney

63.2k41 gold badges170 silver badges213 bronze badges

answered Jan 25, 2013 at 12:18

Gabor Gombas

111 bronze badge

Collectives™ on Stack Overflow

Using savepoints in python sqlite3

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related