CODEWITHSUNDEEP
pythonsqlalchemy
bwbrowning
bwbrowning

Reputation: 6530

SQLAlchemy session reconnect

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?

Upvotes: 27

Views: 42409

Answers (3)

chjortlund
chjortlund

Reputation: 4027

A lot have happened since this question was first answered.

By taking a pessimistic error handling approach you get the most bang for the buck - 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.

Upvotes: 25

est
est

Reputation: 11885

If you are using sqlalchemy in Flask, when you query like 

MyModel.query.all()

and you get errors like 

  File "./lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 427, in _revalidate_connection
    "Can't reconnect until invalid "
StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back

You can simply reconnect DB by 

MyModel.query.session.close()
MyModel.query.all()

Upvotes: 2

zzzeek
zzzeek

Reputation: 75317

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.

Upvotes: 26

Related Questions