[Python] Using SQLAlchemy 2.0 to scope database transactions to pytest modules via Postgres

I am working to migrate an older codebase from SQLAlchemy 1.3 to 2.0. I successfully migrated to 1.4, but I am running into trouble with the testing suite. The tests are using Postgres SAVEPOINTs and monkeypatching session.commit => session.flush to add two nested layers of isolation:

• Root level tests just get standard "each test method is wrapped in a separate transaction, rolled back at the end of the test" per the official recommendations
• Tests in modules, however, are in nested transactions with some monkeypatching that prevents the outer transaction from getting closed (FWIW the package_session logic is basically identical to the root-level session fixture, except with the monkeypatch):
  @pytest.fixture(scope="package")
  def package_session(test_engine: Engine, monkeypatch_package) -> Session:
  # Create a nested transaction that includes standard module data
  connection = test_engine.connect()
  transaction = connection.begin()
  session = Session(bind=connection)
  # Overwrite commits with flushes so that we can query stuff, but it's in the same transaction
  monkeypatch_package.setattr(session, "commit", session.flush)
  # Create our data that is shared by the tests in this module
  _create_data_for_test(session)
  
  try:
  yield session
  finally:
  transaction.rollback()
  connection.close()
  
  
  @pytest.fixture(scope="function")
  def session(package_session):
  """Return a nested transaction on the outer session, to prevent rolling back shared data"""
  package_session.begin_nested()
  try:
  yield package_session
  finally:
  package_session.rollback()

Under SQLAlchemy 1.4 this works, but upon upgrading to 2.0 the test suite is littered with errors about missing shared data (no low-level SQLAlchemy errors, though, unlike when I updated this pattern from 1.3 to 1.4).

I've been fighting this for a couple days now, so I figured I'd ask: is this pattern just not possible in 2.0? I could populate shared data in the "function" scoped session if I had to (just will slow the test suite down), but I figured I'd ask if anyone can spot something obvious that I'm doing wrong, since I liked the pattern under 1.3/1.4.

Continue reading...