sqlalchemy rollback in INSERTS

Question

I have a small question. I'm inserting a long array of data into a DB, but it can happen that some of the new rows throw an IntegrityError exception. That's fine and I can easily recognize them. The problem is, when I rollback to undo this "fake" insertion, all the previous rows are also deleted.

What I want to do then, is 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()

So, researching a little bit, I found this:

http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html#using-savepoint

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

But I cannot really get it to work, if "records" is an array of the objects to be inserted (in my case instances of Check_Point). I get the following error:

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

I'm close to solve the problem but need some help.

Thanks in advance!

Answer 1

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.