Django and SQLAlchemy session transaction errors
Issue experienced here is related to deploying a django application with SQLAlchemy as the ORM not the in-built Django ORM. Its my first time to deploy Django with a different ORM, in this case SQL Alchemy. The error faced here didn’t happen when running on the django dev server but in Multi-threaded environment (Using Gunicorn). My production environment is comprised of Gunicorn and Nginx on an Amazon micro instance running Ubuntu AMI. Reason for using SQLAlchemy is shared common lib between django web application, RabbitMQ workers and other python tools
The transaction is inactive due to a rollback in a subtransaction. On subsequent requests,
This Session’s transaction has been rolled back due to a previous exception during flush.
How I am using the session?
GLOBAL_ENGINE = get_engine() SESSION_MAKER = scoped_session(sessionmaker(bind=GLOBAL_ENGINE)) def get_session(): return SESSION_MAKER()
Why the error?
Quote from the SQLAlchemy site:-
This is an error that occurs when a flush() raises an exception, rolls back the transaction, but further commands upon the Session are called without an explicit call to rollback() or close().
How to fix:
Create a middleware class that commits and closes the session at the end of the request and on any exception
My middle ware class
class SqlAlchemySessionMiddleWare(object): def process_response(self, request, response): try: session = Repository.get_session() session.commit() session.close() except Exception, err: pass return response def process_exception(self, request, exception): try: session = Repository.get_session() session.rollback() session.close() except Exception, err: pass
Register the middleware class in settings
MIDDLEWARE_CLASSES = ( ... 'MiddleWare.SqlAlchemySessionMiddleWare' )
Restart the web server.