SQLAlchemy not rolling back after FlushError

Question

I'm writing some test to a REST API linked to a MySQL db with python+werkzeug+SQLalchemy, one of the test is to try to add a "object" with the primary key missing in the json and verify that it fails and doesn't insert anything in the DB. It used to work fine with sqlite but I switched to MySQLdb and now I get a FlushError (instead of an IntegrityError I used to catch) and when I try to rollback after the error, it doesn't throw any error but the entry is in the database with the primary key set to ''. The code looks like this:

    session = Session()
    try:
        res = func(*args, session=session, **kwargs)
        session.commit()
    except sqlalchemy.exc.SQLAlchemyError as e:
        session.rollback()
        return abort(422)
    else:
        return res
    finally:
        session.close()

And here's the error that I catch during the try/except:

class 'sqlalchemy.orm.exc.FlushError':Instance has a NULL identity key. If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values. Ensure also that this flush() is not occurring at an inappropriate time, such as within a load() event.

I just read the documentation about the SQLalchemy session and rollback feature but don't understand why the rollback doesn't work for me as this is almost textbook example from the documentation.

I use Python 2.7.13, werkzeug '0.12.2', sqlalchemy '1.1.13' and MySQLdb '1.2.3' and mysql Ver 14.14 Distrib 5.1.73 !

Thanks for your help

snoker snoker · Accepted Answer · 2018-03-01T10:16:50

It looks like the problem was MYSQL only: By default, the strict mode isn't activated and allow incorrect insert/update to make changes in the database (wtf?), the solution is to change the sql_mode, either globally: MySQL: Setting sql_mode permanently

Or in SQLalchemy like explained in this blog post: https://www.enricozini.org/blog/2012/tips/sa-sqlmode-traditional/

SQLAlchemy not rolling back after FlushError

1 Answers