pythonsqlitesqlalchemytransactionsrollback

How do I "ROLLBACK TO" a "SAVEPOINT"?


I'm inserting an array of data into an SQLite database, but sometimes some rows throw an IntegrityError exception. When I rollback, all previous rows are also deleted. I want to rollback to the immediate previous status and continue with the next insert.

import sqlalchemy as alc

def insert(self, specs):
    #do stuff to transfor specs in data_db

    entry = Check_Point(data_db)
    session.add(entry)

    try:
        session.flush()

    except alc.exc.IntegrityError:
        print 'int Error'
        session.rollback()

I found this.

for record in records:
    try:
        with session.begin_nested():
            session.merge(record)
    except:
        print "Skipped record %s" % record
session.commit()

I cannot get it to work if records is an array of objects (instances of Check_Point):

This Session's transaction has been rolled back by a nested rollback() call. To begin a new transaction, issue Session.rollback() first


Solution

  • Currently SAVEPOINT transactions won't work correctly with SQLAlchemy and SQLite3 due to a PySQLite bug. See this SQLAlchemy Google group post for Michael Bayer's detailed explanation about the bug and the complexity of working around it in SQLAlchemy.