Hi Mike Thanks very much for all the feedback, 'expunging the expunges' and setting expire_on_commit=False worked a treat. Had to add commits for read operations too, which felt a little awkward, but works nicely.
Nathan On Monday, 16 May 2022 at 19:19:56 UTC+1 Mike Bayer wrote: > 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 sqlalchemy+...@googlegroups.com. > 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 sqlalchemy+...@googlegroups.com. > 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 sqlalchemy+...@googlegroups.com. > > 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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b270181d-43e1-49cc-8069-c1a961364211n%40googlegroups.com.
