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:
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.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.