How Can Sqlalchemy Be Taught To Recover From A Disconnect?

According to http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html#disconnect-handling-pessimistic, SQLAlchemy can be instrumented to reconnect if an entry in the connection pool

Solution 1:

It looks like the checkout method is only called when you first get a connection from the pool (eg your connection = engine.connect() line)

If you subsequently lose your connection, you will have to explicitly replace it, so you could just grab a new one, and retry your sql:

try:
    result = connection.execute("select 'OK'")
except sqlalchemy.exc.OperationalError:  # may need more exceptions here
    connection = engine.connect()  # grab a new connection
    result = connection.execute("select 'OK'")  # and retry

This would be a pain to do around every bit of sql, so you could wrap database queries using something like:

defdb_execute(conn, query):
    try:
        result = conn.execute(query)
    except sqlalchemy.exc.OperationalError:  # may need more exceptions here (or trap all)
        conn = engine.connect()  # replace your connection
        result = conn.execute(query)  # and retryreturn result

The following:

result = db_execute(connection, "select 'OK'")

Should now succeed.

Another option would be to also listen for the invalidate method, and take some action at that time to replace your connection.