SQLAlchemy session reconnect

Question

How can I force my engine to reconnect if a query returns an OperationalError like user does not have access to the database or something like that?

engine = create_engine(url, pool_recycle=3600)
Session = sessionmaker(bind=engine)

try:
      sesh = Session()
      sesh.query....
      sesh.close()
except OperationalError:
      # force engine to reconnect here somehow?

Answer 1

If you catch an error that indicates the connection was closed during an operation, SQLAlchemy automatically reconnects on the next access. However, when a database disconnects, your transaction is gone, so SQLAlchemy requires that you emit rollback() on the Session in order to establish within your application that a new transaction is to take place. you then need to start your whole transaction all over again.

Dealing with that issue has a few angles. You should read through the Dealing with Disconnects section of the documentation which illustrates two ways to work with disconnects. Beyond that, if you truly wanted to pick up your transaction from where you left off, you'd need to "replay" the whole thing back, assuming you've done more than one thing in your transaction. This is best suited by application code that packages what it needs to do in a function that can be called again. Note that a future version of SQLAlchemy may introduce an extension called the Transaction Replay Extension that provides another way of doing this, however it will have lots of caveats, as replaying a lost transaction in a generic way is not a trivial affair.

Answer 2

A lot have happened since this question was first answered.

By taking a pessimistic error handling approach you get the most bang for the bug - easy implementation and very effective.

Apply the pool_pre_ping=Truewhen you create the engine, like this:

engine = create_engine("mysql+pymysql://user:pw@host/db", pool_pre_ping=True)

See more: docs.sqlalchemy.org/en/latest/core/pooling.html#pool-disconnects-pessimistic

---

Another approach is to deal with errors in an optimistic way - as they happen. In that case you can wrap the execute statement in a try and except and the invalidate the connection if an exception is raised. Once the connection is invalidated you and re-instantiate it.

See more: docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-optimistic

---

Both approaches works great in situations where your connection otherwise would timeout e.g. overnight / weekend. It also makes it much easier for IT operations to take a database down and not have to worry too much about downstream applications relying on a restart. How ever this is not a silver bullet, it's worth thinking about secure transaction handling (as mentioned by zzzeek) if you deal with very critical transactions.