Attaching a pre-built query to a scoped_session in SQLAlchemy

Question

I'm receiving a weird error, AttributeError: 'scoped_session' object has no attribute '_autoflush', when attempting to execute a pre-built query on a scoped_session in the SQLAlchemy ORM.

I'm using a context manager to yield a scoped_session,

@contextmanager
def connect(my_session_factory):
    session = scoped_session(my_session_factory)
    try:
        yield session
    except Exception as exception:
        session.rollback()
        raise exception
    finally:
        session.close()

and then am using it like so:

from sqlalchemy.orm import Query

query = Query(my_model).offset(my_offset).limit(my_limit)
with connect(my_session_factory) as session:
    instances = query.with_session(session).all()
    return instances

This, however, raises the exception above.

I note also that queries of the form session.query(my_model) work just fine.

Where am I going wrong? Many thanks!

Answer 1

Okay - I don't have an answer to the question as posed, but I do seem to have a workaround.

The issue appears to be with the proxying behaviour of the scoped_session object. As I understand it, the scoped_session() method takes a sessionmaker object and uses that to create thread-local session object. The scoped_session() method, however, does not return this thread-local session. Instead it returns a scoped_session object that in some way (on which I'm not entirely clear) houses the thread-local session. To access this thread-local session directly, you can do scoped_session.registry(), or instead simply scoped_session() where scoped_session here is a scoped_session object that has been returned by the scoped_session method.

my_scoped_session = scoped_session(my_session_factory)
my_local_session = my_scoped_session()

Now here's the issue: the documentation seems to suggest that calls such as my_scoped_session.query(...).all() and my_local_session.query(...).all() are equivalent, thanks to the proxying behaviour of the scoped_session object. I have found this to be true for the most part, however not so in my original problem case.

If you do my_query = Query(...) (i.e. build a non-session bound query), and then attach that to a scoped_session object (hoping to take advantage of the scoped_session proxying machinery, such that my_query is handled in the context of the scoped_session thread-local session), by way of my_instances = my_query.with_session(my_scoped_session).all() or similar, you get the traceback in my original question.

My workaround is to skip over the scoped_session object's proxying machinery altogether, and instead to bind the my_query directly to my_local_session.

my_query = Query(...).filter(...).sort(...)
my_instances = my_query.with_session(my_local_session).all()

This seems to work out. If, however, anyone would care to chime in on the dangers (if any?) of working with scoped_session(my_session_factory)() directly, rather than with scoped_session(my_session_factory) (which most online tutorials seem to do), then I'd be grateful!