How to check merged connections in SQLAlchemy before passing them to application code?

Question

We have a somewhat unreliable database server for various reasons, and as a result, sometimes the database connections used by my application disappear from it. Connections are SQLAlchemy 0.6.5 connections to PostgreSQL db in the Pylons 1.0 web environment.

What I want is some way to catch most of them without a user-visible error; Ideally, I would test the connection at the pool level before returning it from the engine. I control the creation of the engine, so I'm fine.

What is the best (most idomatic / cleanest) way to achieve this? I understand that there will always be the possibility of a connection between validation and use, but it will be quite rare in this environment, and therefore does not bother me.

Answer 1

You can use the pool listener:

class ConnectionChecker(sqlalchemy.interfaces.PoolListener): def checkout(self, dbapi_con, con_record, con_proxy): if not is_valid_connection(dbapi_con): # a new connection will be used raise sqlalchemy.exc.DisconnectionError 

Left for you is how to implement is_valid_connection for your use case.