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.
