oh an additional clarifying point. if you are seeing that error message happen on a detached object, which seems to go away when you "expunge()" the object sooner, that's because your session is expiring on commit. if your application works with detached objects, you must set expire_on_commit to False. See the docs recently clarified at https://docs.sqlalchemy.org/en/14/orm/session_basics.html#session-committing .
On Mon, May 16, 2022, at 2:14 PM, Mike Bayer wrote: > that error message still describes an object that isn't associated with a > Session. If you organize your application to deal with objects only within > the scope of a single session, and never once that session has been closed, > you won't get that error message. > > > > On Mon, May 16, 2022, at 12:07 PM, Nathan Johnson wrote: >> Hi Mike >> >> Thanks a lot for taking a look and your speedy reply. >> >> The reason it was being used in this context is that it's the only way I >> could get the proxied attribute to resolve after the session had closed, >> despite the relationship having `lazy='subquery'` specified i.e. eager >> loading. >> >> Without the expunge, attempting to access the `lookup` attribute outside of >> the session results in: >> >> ``` >> File >> "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/associationproxy.py", >> line 193, in __get__ >> return inst.get(obj) >> File >> "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/associationproxy.py", >> line 546, in get >> target = getattr(obj, self.target_collection) >> File >> "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line >> 465, in __get__ >> return self.impl.get(state, dict_) >> File >> "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line >> 911, in get >> value = self.callable_(state, passive) >> File >> "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/strategies.py", line >> 832, in _load_for_state >> raise orm_exc.DetachedInstanceError( >> sqlalchemy.orm.exc.DetachedInstanceError: Parent instance <Dave at >> 0x7f974f015fd0> is not bound to a Session; lazy load operation of attribute >> '_lookup' cannot proceed (Background on this error at: >> http://sqlalche.me/e/14/bhk3) >> ``` >> >> Thanks >> >> Nathan >> On Wednesday, 11 May 2022 at 15:25:11 UTC+1 Mike Bayer wrote: >>> __ >>> the pattern you are using with expunge() is likely creating confusion as to >>> what objects are still in the Session and which ones aren't. I would >>> seek to organize your application such that the entire sequence of >>> load/manipulation operations with a set of objects proceeds under a single >>> Session() instance; when all is complete and the transaction is done, you >>> can then .close() that session which will expunge all objects. if the >>> objects are then being passed to a view layer etc., they can opearate in a >>> read-only fashion. >>> >>> otherwise, add()ing objects back to a session that were just expunged is >>> usually an antipattern unless there is a very specific use for it (such as >>> passing objects between workers), in which case it has to be done very >>> carefully. the .expunge() method should never be part of any broadly-used >>> pattern. >>> >>> there's more guidelines on Session use at >>> https://docs.sqlalchemy.org/en/14/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it >>> >>> >>> On Wed, May 11, 2022, at 6:24 AM, Nathan Johnson wrote: >>>> Hi >>>> >>>> I'm attempting to use an association_proxy approach to support a look up >>>> table with classical mapping. >>>> >>>> The problem I'm having is that attempting to update/add an existing object >>>> to a session causes: >>>> >>>> ------ >>>> Traceback (most recent call last): >>>> File "association_proxy_poc.py", line 118, in <module> >>>> add_with_lookup_association_proxy(session, read_obj) >>>> File "association_proxy_poc.py", line 80, in >>>> add_with_lookup_association_proxy >>>> session.add(obj) >>>> File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", >>>> line 2530, in add >>>> self._save_or_update_state(state) >>>> File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", >>>> line 2549, in _save_or_update_state >>>> self._save_or_update_impl(st_) >>>> File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", >>>> line 3095, in _save_or_update_impl >>>> self._update_impl(state) >>>> File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", >>>> line 3084, in _update_impl >>>> self.identity_map.add(state) >>>> File >>>> "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/identity.py", line >>>> 148, in add >>>> raise sa_exc.InvalidRequestError( >>>> sqlalchemy.exc.InvalidRequestError: Can't attach instance <LookUp at >>>> 0x7fb96ce55730>; another instance with key (<class '__main__.LookUp'>, >>>> (7,), None) is already present in this session. >>>> --------- >>>> >>>> This is my example code, apologies for the lack of highlighting (pasting >>>> from VSCode normally does this in gmail, but apparently not google >>>> groups). This works as expected until line 118 (annotated below). >>>> >>>> ------------------------- >>>> >>>> class LookUp(): >>>> def __init__(self, lookup_value: str): >>>> self.lookup_value = lookup_value >>>> >>>> >>>> class Dave: >>>> def __init__(self, lookup: str, id: int = None, updatable: str = None): >>>> self.id = id >>>> self.lookup = lookup >>>> self.updatable = updatable >>>> >>>> >>>> mapper_registry = registry() >>>> >>>> lookup_table = Table( >>>> 'lookup', >>>> mapper_registry.metadata, >>>> Column('id', SmallInteger, primary_key=True), >>>> Column('lookup_value', String(36), unique=True) >>>> ) >>>> >>>> dave_table = Table( >>>> 'dave', >>>> mapper_registry.metadata, >>>> Column('id', INTEGER(unsigned=True), primary_key=True), >>>> Column('updatable', String(36)), >>>> Column('lookup_id', SmallInteger, ForeignKey('lookup.id')) >>>> ) >>>> >>>> mapper_registry.map_imperatively(LookUp, lookup_table) >>>> mapper_registry.map_imperatively( >>>> Dave, >>>> dave_table, >>>> properties={ >>>> '_lookup': relationship(LookUp, uselist=False, lazy='subquery', >>>> cascade='expunge, save-update, merge'), >>>> } >>>> ) >>>> Dave.lookup = association_proxy('_lookup', 'lookup_value') >>>> >>>> >>>> from sqlalchemy import create_engine >>>> from sqlalchemy.orm import sessionmaker >>>> config = DbSettings() >>>> conn_str = config.db_conn_str >>>> engine = create_engine(conn_str, echo=True, pool_pre_ping=True) >>>> _sessionmaker = sessionmaker(autocommit=False, autoflush=False, >>>> bind=engine) >>>> >>>> >>>> def add_with_lookup_association_proxy(session, obj): >>>> if lookup := >>>> session.query(LookUp).filter_by(lookup_value=obj.lookup).one_or_none(): >>>> # Ensures we re-use exisitng LookUp records >>>> print(f"################## Re-using lookup {lookup}") >>>> obj._lookup = lookup >>>> >>>> session.add(obj) >>>> session.flush() >>>> session.expunge(obj) >>>> return obj >>>> >>>> >>>> def read_with_lookup_association_proxy(session, id, lookup): >>>> query = session.query(Dave).filter_by(id=id, lookup=lookup) >>>> obj = query.one() >>>> session.expunge(obj) >>>> return obj >>>> >>>> lookup = 'SOME HIGHLY REDUNDANT VALUE' >>>> >>>> with _sessionmaker() as session: >>>> new_obj = Dave(lookup=lookup) >>>> add_with_lookup_association_proxy(session, new_obj) >>>> session.commit() >>>> >>>> print(f"############## NEW {new_obj.lookup}") >>>> print(new_obj.lookup_id) >>>> >>>> with _sessionmaker() as session: >>>> read_obj = read_with_lookup_association_proxy(session, new_obj.id, >>>> new_obj.lookup) >>>> print(f"############## READ {read_obj.lookup}") >>>> read_obj.updatable = 'UPDATED' >>>> add_with_lookup_association_proxy(session, read_obj) # line 118 This >>>> line triggers the error >>>> session.commit() >>>> >>>> with _sessionmaker() as session: >>>> updated_obj = read_with_lookup_association_proxy(session, new_obj.id, >>>> new_obj.lookup) >>>> print(f"########## READ UPDATED {updated_obj.updatable}") >>>> >>>> ---------------- >>>> >>>> I have played around with the omitting the save-update cascade and adding >>>> the obj._lookup to the session directly, but this results in: >>>> >>>> ---- >>>> /usr/local/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py:835: >>>> SAWarning: Object of type <LookUp> not in session, add operation along >>>> 'Dave._lookup' won't proceed >>>> ---- >>>> >>>> Would really appreciate some insight as to what I'm getting wrong here. >>>> >>>> Thanks >>>> >>>> Nathan >>>> >>>> >>>> >>>> -- >>>> SQLAlchemy - >>>> The Python SQL Toolkit and Object Relational Mapper >>>> >>>> http://www.sqlalchemy.org/ >>>> >>>> To post example code, please provide an MCVE: Minimal, Complete, and >>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>>> description. >>>> --- >>>> You received this message because you are subscribed to the Google Groups >>>> "sqlalchemy" group. >>>> To unsubscribe from this group and stop receiving emails from it, send an >>>> email to [email protected]. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/sqlalchemy/377ed850-d53e-4253-a43d-2ddfe04d8af5n%40googlegroups.com >>>> >>>> <https://groups.google.com/d/msgid/sqlalchemy/377ed850-d53e-4253-a43d-2ddfe04d8af5n%40googlegroups.com?utm_medium=email&utm_source=footer>. >>> >> >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >> description. >> --- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/sqlalchemy/b965aceb-8829-4e1b-b650-f7016601b6dcn%40googlegroups.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/b965aceb-8829-4e1b-b650-f7016601b6dcn%40googlegroups.com?utm_medium=email&utm_source=footer>. > > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/27492fcd-e346-4b76-8d4e-69c609b48ca0%40www.fastmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/27492fcd-e346-4b76-8d4e-69c609b48ca0%40www.fastmail.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/dfb9feb7-7aff-47e5-af71-3e963b9516e3%40www.fastmail.com.
