add/update causing InvalidRequest for object with association_proxy lookup

[Nathan Johnson]

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

[Nathan Johnson]

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.

[Mike Bayer]

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

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/377ed850-d53e-4253-a43d-2ddfe04d8af5n%40googlegroups.com.

[Nathan Johnson]

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

[Mike Bayer]

that error message still describes an object that isn't associated with a Session.   If you organize your application to deal with objects only within the scope of a single session, and never once that session has been closed, you won't get that error message. 

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b965aceb-8829-4e1b-b650-f7016601b6dcn%40googlegroups.com.

[Mike Bayer]

oh an additional clarifying point.  if you are seeing that error message happen on a detached object, which seems to go away when you "expunge()" the object sooner, that's because your session is expiring on commit.  if your application works with detached objects, you must set expire_on_commit to False.    See the docs recently clarified at https://docs.sqlalchemy.org/en/14/orm/session_basics.html#session-committing .

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/27492fcd-e346-4b76-8d4e-69c609b48ca0%40www.fastmail.com.

[Nathan Johnson]

Hi Mike

Thanks very much for all the feedback, 'expunging the expunges' and setting expire_on_commit=False worked a treat. Had to add commits for read operations too, which felt a little awkward, but works nicely. 

Nathan