Attaching a pre-built query to a scoped_session in SQLAlchemy
python, session, sqlalchemy
Solution
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!
Problem
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!