Oh, forgot to clarify, if it wasn't obvious... The LookUp is of course in
the session already for the initial object creation, it's only on updating
the created object that this error manifests.
On Wednesday, 11 May 2022 at 11:24:41 UTC+1 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/b4e158b0-e97e-49ea-bc69-7fe52795bc3en%40googlegroups.com.
