How can I check pooled connections in SQLAlchemy before handing them off to my application code?

We have a slightly unreliable database server, for various reasons, and as a consequence sometimes the database connections used by my application vanish out from under it. The connections are SQLAlchemy 0.6.5 connections to a PostgreSQL db in a Pylons 1.0 web runtime.

What I want is some way to catch most of these without a user-visible error; ideally, I'd test the connection at the pool level 开发者_运维问答before returning it from the engine. I control the creation of the engine, so I'm okay there.

What's the best (most idomatic / cleanest) way to accomplish this? I realize that there will always be the possibility of the connection dying between the check and the usage, but that's going to be pretty rare in this environment, and is therefore not a concern to me.

You could use a 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.