如何教 SQLAlchemy 从断开连接中恢复? [英] How can SQLAlchemy be taught to recover from a disconnect?

问题描述

根据 http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html#disconnect-handling-pessimistic，如果连接池中的条目不再有效，可以检测 SQLAlchemy 以重新连接.我创建了以下测试用例来对此进行测试:

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 is no longer valid. I create the following test case to test this:

import subprocess
from sqlalchemy import create_engine, event
from sqlalchemy import exc
from sqlalchemy.pool import Pool

@event.listens_for(Pool, "checkout")
def ping_connection(dbapi_connection, connection_record, connection_proxy):
    cursor = dbapi_connection.cursor()
    try:
        print "pinging server"
        cursor.execute("SELECT 1")
    except:
        print "raising disconnect error"
        raise exc.DisconnectionError()
    cursor.close()

engine = create_engine('postgresql://postgres@localhost/test')

connection = engine.connect()

subprocess.check_call(['psql', str(engine.url), '-c',
    "select pg_terminate_backend(pid) from pg_stat_activity " +
    "where pid <> pg_backend_pid() " +
    "and datname='%s';" % engine.url.database],
    stdout=subprocess.PIPE)

result = connection.execute("select 'OK'")
for row in result:
    print "Success!", " ".join(row)

但我没有恢复，而是收到此异常:

But instead of recovering I receive this exception:

sqlalchemy.exc.OperationalError: (OperationalError) terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

由于终端上印有pinging server"，因此可以安全地得出事件侦听器已连接的结论.如何教 SQLAlchemy 从断开连接中恢复?

Since "pinging server" is printed on the terminal it seems safe to conclude that the event listener is attached. How can SQLAlchemy be taught to recover from a disconnect?

推荐答案

它看起来像 checkout 方法仅在您第一次从池中获得连接时调用(例如您的 connection = engine.connect()行)

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

如果您随后失去连接，则必须明确替换它，因此您可以获取一个新连接，然后重试您的 sql:

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

这将很难处理每一个 sql，因此您可以使用以下内容包装数据库查询:

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

def db_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 retry
    return result

以下内容:

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

现在应该成功了.

另一种选择是同时监听 invalidate 方法，并在当时采取一些措施来替换您的连接.

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

这篇关于如何教 SQLAlchemy 从断开连接中恢复?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！