如何教SQLAlchemy从断开恢复?
根据http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html#disconnect-handling-pessimistic,如果连接池中的条目不再有效,可以检测SQLAlchemy重新连接。 我创建了以下测试用例来测试它:
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)
但不是恢复,我收到此异常:
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.
由于“ping服务器”被打印在终端上,所以可以安全地断定附加了事件监听器。 如何教SQLAlchemy从断开恢复?
它看起来像只有当你第一次从池中获得连接时才会调用checkout方法(例如,你的connection = engine.connect()
行)
如果你后来失去连接,你将不得不明确地替换它,所以你可以拿一个新的连接,然后重试你的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的每一处都会遇到一些麻烦,所以你可以使用类似下面的方法来包装数据库查询:
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方法,并在那时采取一些行动来取代你的连接。
链接地址: http://www.djcxy.com/p/83407.html上一篇: How can SQLAlchemy be taught to recover from a disconnect?
下一篇: Set specific URL for static files using Spark Framework