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.
