[pysqlite] Savepoint support

jason kirtland <[email protected]> Tue, 2 Jun 2009 07:02:27 -0700
Newsgroups gmane.comp.python.db.pysqlite.user
Message-ID <[email protected]>
I'm having trouble getting the new sqlite nested transactions working
through pysqlite- savepoints seem to get lost. Am I doing something
wrong below?

Thanks,
Jason

"""
SQLite version 3.6.14.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> BEGIN;
sqlite> CREATE TABLE x (y INT);
sqlite> SAVEPOINT a;
sqlite> INSERT INTO x VALUES (1);
sqlite> SAVEPOINT b;
sqlite> INSERT INTO x VALUES (2);
sqlite> RELEASE SAVEPOINT a;
sqlite> INSERT INTO x VALUES (3);
sqlite> COMMIT;
sqlite> SELECT COUNT(*) FROM x;
3
"""
import pysqlite2.dbapi2 as dbapi
assert dbapi.version_info == (2, 5, 5)
assert dbapi.sqlite_version_info >= (3, 6, 8)

cx = dbapi.connect(':memory:')
r = cx.cursor()
r.execute('BEGIN')
r.execute('CREATE TABLE x (y INT)')
r.execute('SAVEPOINT a')
r.execute('INSERT INTO x VALUES (1)')
r.execute('SAVEPOINT b')
r.execute('INSERT INTO x VALUES (2)')
r.execute('RELEASE SAVEPOINT a')
r.execute('INSERT INTO x VALUES (3)')
cx.commit()
print cr.execute('SELECT COUNT(*) FROM x').fetchall()

# Traceback (most recent call last):
#   File "savepoints.py", line 29, in <module>
#     r.execute('RELEASE SAVEPOINT a')
# pysqlite2.dbapi2.OperationalError: no such savepoint: a