Exception Handler Not Working: Commiting to SQL Database with SQLAlchemy in Python

Question

I am trying to commit entries to a MySQL database using SQLAlchemy in Python. The function below was meant to allow me to commit a batch of entries, even if one or two of them contained errors that the database would not accept. However, when it gets to an entry that it cannot commit it does not skip it and go to the next one, it tries to commit the same entry over and over and over again, getting the same error message for that entry a thousand times. Why doesn't the trycatch in my function work?

def commitentry(database, enginetext, verbose = False):
    """
    Takes a database object and text string that defines the SQL
    engine and adds all entries in the database list to the SQL
    database.
    """

    engine = create_engine(enginetext)
    Session = sessionmaker()
    Session.configure(bind=engine)
    session = Session()
    counter = 0
    for entry in database:
        try:
            session.add(entry)
            session.commit()

        except Exception, e:
            print("Commit Error")

            if verbose:
                print(e)

        finally:
            counter += 1
            if verbose:
                print(counter, counter/float(len(database)))

    if verbose:
        print("Entries saved!")
    session.close()

What does the exception say when it fails? It seems like this is a problem with the iteration over database, your exception handler really doesn't have anything to do with it. — CrazyCasta
– CrazyCasta, Commented Jan 17, 2014 at 22:43
This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (OperationalError) (1366, "Incorrect integer value: 'R' for column 'district' at row 1")... with ... being all the rest of the info about the entry. Basically it's saying you can't put text in an integer column. — Michael
– Michael, Commented Jan 17, 2014 at 22:45
Ok, so I added session.rollback() to the except block and now it works. It seems that a commit that fails leaves the failed object right where it is to fail again on the next commit. — Michael
– Michael, Commented Jan 17, 2014 at 23:10

Michael · Accepted Answer · 2014-01-17 23:29:33Z

When a commit fails it does not delete the object from memory like a successful commit. So when the next object is added to the session it is not alone, the object that just failed is still there. Adding session.rollback() to the except block will remove the object that cannot be saved to the database from the session, allowing the next object to be saved.

def commitentry(database, enginetext, verbose = False):
    """
    Takes a database object and text string that defines the SQL
    engine and adds all entries in the database list to the SQL
    database.
    """

    engine = create_engine(enginetext)
    Session = sessionmaker()
    Session.configure(bind=engine)
    session = Session()
    counter = 0
    for entry in database:
        try:
            session.add(entry)
            session.commit()

        except Exception, e:
            print("Commit Error")
            session.rollback()


            if verbose:
                print(e)

        finally:
            counter += 1
            if verbose:
                print(counter, counter/float(len(database)))

    if verbose:
        print("Entries saved!")
    session.close()

Collectives™ on Stack Overflow

Exception Handler Not Working: Commiting to SQL Database with SQLAlchemy in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related