Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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()
share|improve this question
 
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 2 days ago
 
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 2 days ago
 
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 2 days ago
 
You should answer your own question. –  CrazyCasta 2 days ago
add comment

1 Answer

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()
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.