如何使用SQLAlchemy关闭和重新连接以避免空闲事务超时。

Question

我写了一个Python应用程序，使用SQLAlchemy连接到Postgres数据库。在db.py中定义了engine和session。

engine = create_engine(URL(**settings.DATABASE))
session = scoped_session(sessionmaker(bind=engine))

大多数数据库操作都在service.py中，该文件从db.py中导入session。

from app.db import engine, session

def get_door_ids():
    result = session.query(ControllerDetail.door_id).distinct().all()
    ids = [c[0] for c in result]
    return ids

def get_last_health_cd(door_id):
    result = session.query(ControllerDetail.door_health_cd).filter(ControllerDetail.door_id == door_id).order_by(ControllerDetail.etl_insert_ts.desc()).first()
    return result[0]

现在一切都很好，但问题是我需要每隔几分钟重复执行相同的操作。因此，我在我的主模块中编写了以下代码：

try:
    while True:
        run_task()
        time.sleep(120)
except KeyboardInterrupt:
    print('Manual break by user')

数据库每隔一分钟就会超时空闲连接。因此，每次进程休眠超过1分钟时，我都会收到错误提示。

psycopg2.InternalError: terminating connection due to idle-in-transaction timeout
SSL connection has been closed unexpectedly

我想知道是否有一种方法可以在 time.sleep(120) 后关闭会话并重新打开它，以避免超时。也许在主模块中，我可以将 session 从 db 中作为全局变量导入，并以某种方式将其传递给 services 中的方法。我该怎么做？由于 main 导入了来自 services 的函数，因此我无法从 main 而不是 db 中导入 services 中的 session。

- ddd

Answer 1

首先，你应该通过提交/回滚及时结束你的事务。 关闭 会话也将隐式回滚事务。在作用域会话中移除将关闭会话（并从注册表中删除会话）。总之，在这一点上，“我何时构建会话，何时提交它，何时关闭它？”是一个不错的阅读材料。

有些人可能会感到惊讶的是，Session 在你开始与数据库通信时立即启动了新的事务。然后就轮到你来结束这个事务了。如果没有看到 run_task() 实际上做了什么，很难说哪部分应该处理会话的生命周期，但可以肯定的是，如果任务每隔几分钟运行一次，你不应该让事务挂起那么长时间。

- Ilja Everilä

---

我在run_task()之前创建了scoped_session，并在之后关闭了它。现在session是run_task(session)的参数。因此，所有其他函数也将以session作为它们的参数。不确定将session作为这些查询函数的参数是否是常见做法，但它可以正常工作。 - ddd

使用线程本地作用域会使界限变得有些模糊，因为其中一个卖点是您不需要显式地传递会话（虽然这样做也没有错），如果您的任务（或其他）由工作线程处理，则可以轻松使用scoped_session将会话的生命周期与工作线程的生命周期绑定。当您说在run_task()之后关闭了作用域会话时，循环调用run_task()并且它实际上在同一线程中运行吗？如果不是，则创建了一个会话，而工作线程则创建了另一个会话。 - Ilja Everilä

我没有使用线程。这是一个警报引擎，需要每两分钟运行一次，并轮询数据库以查找异常。run_task 的作用就是查询并发送电子邮件。 - ddd

哦，那样的话你根本不需要 scoped_session。只需要从 sessionmaker() 获取一个普通的 Session 实例就可以了，将其传递是一个不错的模式；至少它使得测试更容易，并且促进了可重用性。 - Ilja Everilä

Answer 2

• 增加超时时间

  create_engine(URL(**settings.DATABASE), connect_args={'connect_timeout': 10})

• 当出现错误时创建新的会话
• 你可以通过session.close()关闭连接

- mad_

---

客户端的超时设置会覆盖服务器的设置吗？由于会话是在db.py中创建，然后被导入到其他模块中，因此没有简单的方法来创建/关闭会话并使所有方法使用之前相同的会话。 - ddd

这与您的服务器设置有关。您可以根据需要增加时间。 - mad_

数据库由我们的DBA管理。超时是他不愿更改的事情之一。为了在主模块中关闭并重新连接“会话”，我需要将其作为全局从“db”导入，并将其传递给“services”中的方法。我该怎么做？ - ddd