SQLAlchemy session reconnect
python, sqlalchemy
Solution
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.
Problem
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? ```