SqlAlchemy + Tornado: Cannot reconnect until invalid transaction is canceled

Question

I am building a webapp with tornado + sqlalchemy and absolutely by accident, I got this error

File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception exc_info File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 187, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=exc_value) File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 182, in reraise raise value.with_traceback(tb) File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 822, in _execute_context conn = self._revalidate_connection() File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 239, in _revalidate_connection "Can't reconnect until invalid " sqlalchemy.exc.StatementError: Can't reconnect until invalid transaction is rolled back 

I can’t figure out how to solve this. I put all of db.commit in

 try: self.db.commit() except Exception(e): self.db.rollback() 

This is a class app.

 class Application [...] engine = create_engine(options.db_path, convert_unicode=True, echo=options.debug) models.init_db(engine) self.db = scoped_session(sessionmaker(bind=engine)) tornado.web.Application.__init__(self, handlers, **settings) 

but nothing. What is the best way to configure sqlalchemy and tornado for a web application like mysql + php?

Answer 1

I remember that I had the same prescription. There seems to be some weird stuff related to the connection pool. Disabling the pool seems to be fixed. Not a good idea in general, but it worked.

Try passing poolclass=NullPool to create_engine

 ... from sqlalchemy.pool import NullPool ... engine = create_engine(options.db_path, convert_unicode=True, echo=options.debug, poolclass=NullPool) 

Answer 2

My way is to roll back at the finish, add it to your BaseHandler:

 def on_finish(self): if self.get_status() == 500: self.db_session.rollback()