Pytest fails with `sqlalchemy.exc.UnboundExecutionError: This session is not bound to a single Engine or Connection, and no context was provided to locate a binding.`

[gmassman]

I just installed sqlalchemy-continuum to track changes to a few tables in a flask app. I was able to get the package up and running, but now my tests are failing when pytest fixtures are invoked. For what it's worth, the db.session binds to several databases, which is well supported by flask-sqlalchemy. I'm using Python 3.11.1.

Here are the subset of package versions which could be relevant:

alembic==1.12.1 ; python_version >= '3.7'
flask==2.3.3
flask-login==0.6.3
flask-migrate==3.1.0
flask-sqlalchemy==3.0.5
pytest==7.4.3
sqlalchemy==1.4.50
sqlalchemy-continuum==1.4.0
sqlalchemy-utils==0.41.1 ; python_version >= '3.6'
werkzeug==3.0.1 ; python_version >= '3.8'

This is the abridged stack trace:

> pytest -x
tests/test_api.py ..E

_________________________________________ ERROR at setup of test_post_api _________________________________________

    @pytest.fixture
    def group():
        """Default test group."""
        group = Group(name="test_group")
        db.session.add(group)
>       db.session.commit()

tests/support/default_groups.py:27:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:1454: in commit
    self._transaction.commit(_to_root=self.future)
.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:832: in commit
    self._prepare_impl()
.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:811: in _prepare_impl
    self.session.flush()
.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:3449: in flush
    self._flush(objects)
.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:3478: in _flush
    self.dispatch.before_flush(self, flush_context, objects)
.venv/lib/python3.11/site-packages/sqlalchemy/event/attr.py:247: in __call__
    fn(*args, **kw)
.venv/lib/python3.11/site-packages/sqlalchemy_continuum/manager.py:339: in before_flush
    uow = self.unit_of_work(session)
.venv/lib/python3.11/site-packages/sqlalchemy_continuum/manager.py:301: in unit_of_work
    conn = session.connection()
.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:1543: in connection
    bind = self.get_bind(**bind_arguments)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.orm.session.Session object at 0x136f2c310>, mapper = None, clause = None, bind = None, _sa_skip_events = None, _sa_skip_for_implicit_returning = False

    def get_bind(
        self,
        mapper=None,
        clause=None,
        bind=None,
        _sa_skip_events=None,
        _sa_skip_for_implicit_returning=False,
    ):
    **Doc string redacted**
        # this function is documented as a subclassing hook, so we have
        # to call this method even if the return is simple
        if bind:
            return bind
        elif not self.__binds and self.bind:
            # simplest and most common case, we have a bind and no
            # per-mapper/table binds, we're done
            return self.bind

        # we don't have self.bind and either have self.__binds
        # or we don't have self.__binds (which is legacy).  Look at the
        # mapper and the clause
        if mapper is clause is None:
            if self.bind:
                return self.bind
            else:
>               raise sa_exc.UnboundExecutionError(
                    "This session is not bound to a single Engine or "
                    "Connection, and no context was provided to locate "
                    "a binding."
                )
E               sqlalchemy.exc.UnboundExecutionError: This session is not bound to a single Engine or Connection, and no context was provided to locate a binding.

.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:2056: UnboundExecutionError
============================================================================================ short test summary info =============================================================================================
ERROR tests/test_api.py::test_post_api - sqlalchemy.exc.UnboundExecutionError: This session is not bound to a single Engine or Connection, and no context was provided to locate a binding.

I definitely need to support multiple database binds, so if anyone has any idea how to get around this issue it would be much appreciated! Thanks in advance.

[marksteward]

I admittedly don't use multiple binds myself, but we create the version session with the same bind, so it should just work.

I'm confused by these line numbers:

.venv/lib/python3.11/site-packages/sqlalchemy_continuum/manager.py:339: in before_flush
    uow = self.unit_of_work(session)
.venv/lib/python3.11/site-packages/sqlalchemy_continuum/manager.py:301: in unit_of_work
    conn = session.connection()

Have you made local changes?

[gmassman]

I admittedly don't use multiple binds myself, but we create the version session with the same bind, so it should just work.

I'm confused by these line numbers:

.venv/lib/python3.11/site-packages/sqlalchemy_continuum/manager.py:339: in before_flush
    uow = self.unit_of_work(session)
.venv/lib/python3.11/site-packages/sqlalchemy_continuum/manager.py:301: in unit_of_work
    conn = session.connection()

Have you made local changes?

Oh you know what, I had the file open in Pycharm while I was debugging and it must have run the Black trigger on the code. Here's the stack trace with a fresh copy of sqlalchemy_continuum:

tests/support/default_groups.py:27:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:1454: in commit
    self._transaction.commit(_to_root=self.future)
.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:832: in commit
    self._prepare_impl()
.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:811: in _prepare_impl
    self.session.flush()
.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:3449: in flush
    self._flush(objects)
.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:3478: in _flush
    self.dispatch.before_flush(self, flush_context, objects)
.venv/lib/python3.11/site-packages/sqlalchemy/event/attr.py:247: in __call__
    fn(*args, **kw)
.venv/lib/python3.11/site-packages/sqlalchemy_continuum/manager.py:343: in before_flush
    uow = self.unit_of_work(session)
.venv/lib/python3.11/site-packages/sqlalchemy_continuum/manager.py:305: in unit_of_work
    conn = session.connection()
.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:1543: in connection
    bind = self.get_bind(**bind_arguments)
E               sqlalchemy.exc.UnboundExecutionError: This session is not bound to a single Engine or Connection, and no context was provided to locate a binding.

.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:2056: UnboundExecutionError

[gmassman]

I have found a workaround for now, although it's not ideal. In my top level conftest.py file where I initialize the test databases, I added a call to sqlalchemy_continuum.remove_versioning():

@pytest.fixture(scope="session")
def db(test_client):
    test_dbs = [None, "A", "B"]
    database.create_all(test_dbs)
    remove_versioning()

    yield database

    close_all_sessions()
    database.drop_all(test_dbs)

This isn't great because I won't be able to test any of the functionality of sqlalchemy-continuum. If anyone knows of a better workaround that doesn't require removing all the sqlalchemy-continuum event listeners, I'd really appreciate the input.