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+unsubscr...@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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/031ce5b7-9bed-42e3-b9c4-cba0fc2a2a43%40www.fastmail.com.
