处理sqlalchemy断开连接的更好方法 [英] Better approach to handling sqlalchemy disconnects

问题描述

我们一直在尝试sqlalchemy的断开连接处理，以及它如何与ORM集成。我们已经研究了文档，建议似乎是捕获断开连接异常，发出 rollback（）并重试代码。

We've been experimenting with sqlalchemy's disconnect handling, and how it integrates with ORM. We've studied the docs, and the advice seems to be to catch the disconnect exception, issue a rollback() and retry the code.

例如：

import sqlalchemy as SA

retry = 2
while retry:
    retry -= 1
    try:
        for name in session.query(Names):
            print name
        break
    except SA.exc.DBAPIError as exc:
        if retry and exc.connection_invalidated:
            session.rollback()
        else:
            raise

我遵循基本原理-您必须回滚所有活动交易并重播它们，以确保操作的顺序一致。

I follow the rationale -- you have to rollback any active transactions and replay them to ensure a consistent ordering of your actions.

BUT-这意味着向要使用数据的每个函数添加了很多额外的代码。此外，在 SELECT 的情况下，我们没有在修改数据，回滚/重新请求的概念不仅难看，而且违反了DRY（

BUT -- this means a lot of extra code added to every function that wants to work with data. Furthermore, in the case of SELECT, we're not modifying data and the concept of rollback/re-request is not only unsightly, but a violation of the principle of DRY (don't repeat yourself).

我想知道其他人是否会介意使用sqlalchemy分享如何处理断开连接。

I was wondering if others would mind sharing how they handle disconnects with sqlalchemy.

FYI：我们正在使用sqlalchemy 0.9.8和Postgres 9.2.9

FYI: we're using sqlalchemy 0.9.8 and Postgres 9.2.9

推荐答案

我喜欢的解决方法是将我所有的数据库代码放入lambda或闭包中，然后将其传递到一个辅助函数中，该函数将处理捕获断开连接异常，然后重试。

The way I like to approach this is place all my database code in a lambda or closure, and pass that into a helper function that will handle catching the disconnect exception, and retrying.

因此，以您的示例为例：

So with your example:

import sqlalchemy as SA

def main():
    def query():
        for name in session.query(Names):
            print name

    run_query(query)

def run_query(f, attempts=2):
    while attempts > 0:
        attempts -= 1
        try:
            return f() # "break" if query was successful and return any results
         except SA.exc.DBAPIError as exc:
            if attempts > 0 and exc.connection_invalidated:
                session.rollback()
            else:
                raise

您可以通过向 run_query 中传递一个布尔值来处理这种情况，以处理仅读取内容的情况，因此希望重试而不回滚

You can make this more fancy by passing a boolean into run_query to handle the case where you are only doing a read, and therefore want to retry without rolling back.

这有助于您满足DRY原则，因为所有用于管理重试和回滚的难看的样板代码都放在一个位置。

This helps you satisfy the DRY principle since all the ugly boiler-plate code for managing retries + rollbacks is placed in one location.

这篇关于处理sqlalchemy断开连接的更好方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！