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.
