SQLAlchemy在某些插入后出现“此事务已关闭”错误

Question

我正在使用Flask和SQLAlchemy（两者都是独立的，因此没有Flask-SQLAlchemy）来针对PostgreSQL实例构建RESTAPI

为了手动测试API，我通过使用requests模块执行POST请求，向API发送几个带有随机数据的请求。由于后面的API没有大量的数据（每10秒一个请求），我决定每2秒发送一个请求以进行测试

一般来说，这种方法的效果与预期一致。我能够用所需的表初始化空数据库，并通过API将数据写入每个表。但是，在一些任意数量的请求之后，会引发sqlalchemy.exc.ResourceClosedError: This transaction is closed异常（请参阅下面的完整回溯）

会话绑定到我的routes.py中Flask的app实例，如下所示：

# -- app/routes.py --
# (simplified version)

from http import HTTPStatus

from flask import request, jsonify

from app import app
from . import db


app.session = db.connector.Session()


@app.route('/')
def root_endpoint():
    return 'This is the root endpoint.'


@app.route('/api/v1.0/measurement/', methods=['POST'])
def post_measurement():
    measurements = request.json
    # doing some data parsing here ...
    measurements = [db.models.Measurement(**measurement) for measurement in measurements]

    app.session.add_all(measurements)
    app.session.commit()

    ids = [m.id for m in measurements]

    return jsonify({"message": "Created new measurements.", "ids": ids}), HTTPStatus.CREATED

数据库会话（作用域会话）由mydb模块中的connector.py提供：

# -- db/connector.py --
# (simplified version)

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker

DB_SERVER_URL = "postgresql+psycopg2://postgres:postgres@localhost/grafana"

engine = create_engine(
    DB_SERVER_URL,
    echo=True,
)

session_factory  = sessionmaker(
    bind=engine,
    autoflush=False,
    autocommit=False,
)

Session = scoped_session(session_factory)

Base = declarative_base()

此外，数据库模型Measurement定义为so（db/models.py）：

# -- db/connector.py --
# (simplified version))

from sqlalchemy import Column, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from sqlalchemy.types import DateTime, Float, Integer, String

from .connector import Base


class Measurement(Base):
    __tablename__ = 'measurement'

    id = Column(
        Integer,
        primary_key=True,
    )

    timestamp = Column(
        DateTime,
        index=True,
        nullable=False,
    )

    payload = Column(
        Float,
        index=True,
        nullable=False,
    )

    payload_type = Column(
        String,
        index=True,
        nullable=False,
    )

如前所述，在任意数量的请求之后，我得到了一个异常，如下所示。此后所有其他POST请求也将失败（由于此未解决的错误）

我想，这一定与会话和事务管理有关，但我不明白我在这里缺少什么。我怎样才能缩小这个问题的范围？我错过了什么

---

第一个异常的回溯（导致HTTP状态代码500）：

[2021-01-09 19:25:19,930] ERROR in app: Exception on /api/v1.0/measurement/ [POST]
Traceback (most recent call last):
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2642, in _flush
    flush_context.execute()
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 621, in execute
    persistence.delete_obj(
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 325, in delete_obj
    states_to_delete = list(
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 462, in _organize_states_for_delete
    for state, dict_, mapper, connection in _connections_for_states(
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1602, in _connections_for_states
    connection = uowtransaction.transaction.connection(base_mapper)
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 312, in connection
    self._assert_active()
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 305, in _assert_active
    raise sa_exc.ResourceClosedError(closed_msg)
sqlalchemy.exc.ResourceClosedError: This transaction is closed

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/co16/flaskapi/app/routes.py", line 137, in post_measurement
    app.session.commit()
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1046, in commit
    self.transaction.commit()
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 504, in commit
    self._prepare_impl()
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 483, in _prepare_impl
    self.session.flush()
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2540, in flush
    self._flush(objects)
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 82, in __exit__
    compat.raise_(value, with_traceback=traceback)
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 520, in rollback
    self._assert_active(prepared_ok=True, rollback_ok=True)
  File "/home/co16/flaskapi/env/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 305, in _assert_active
    raise sa_exc.ResourceClosedError(closed_msg)
sqlalchemy.exc.ResourceClosedError: This transaction is closed