Modify other objects on update/insert

Question

I've got two mapped objects, Parent and Child.

class Parent(Base):
    __tablename__ = 'parent'
    id = ...
    name = ...
    date_modified = Column(SA_DateTime, default=DateTime.now,
                           onupdate=DateTime.now, nullable=False)

class Child(Base):
    __tablename__ = 'child'
    id = ...
    name = ...
    date_modified = Column(SA_DateTime, default=DateTime.now,
                           onupdate=DateTime.now, nullable=False)
    parent = relationship(Parent, backref='parent')

When a child is updated, I want not only Child.date_modified to be changed, but also Child.parent.date_modified.

I tried to do this like this:

@event.listens_for(Child, 'after_update')
def modified_listener(mapper, connection, target):
    if object_session(target).is_modified(target, include_collections=False):
        target.parent.date_modified = DateTime.now()

But this doesn't work, because I'm already in a flush and I get something like

SAWarning: Attribute history events accumulated on 1 previously clean instance within inner-flush event handlers have been reset, and will not result in database updates. Consider using set_committed_value() within inner-flush event handlers to avoid this warning.

How can I solve this with SQLAlchemy?

Answer 1

Basic update-parent-when-child-changes using SQLAlchemy events has been covered on this site before here and here, but in your case you're trying to update the parent during the flush, possibly using an update default value from the child, which will be visible after the update, or a new value entirely. Modifying the parent in the event handler is not as straightforward as you might first imagine:

Warning

Mapper-level flush events only allow very limited operations, on attributes local to the row being operated upon only, as well as allowing any SQL to be emitted on the given Connection. Please read fully the notes at Mapper-level Events for guidelines on using these methods; generally, the SessionEvents.before_flush() method should be preferred for general on-flush changes.

As you've noticed, simple

target.parent.date_modified = DateTime.now()

in your event handler warns:

SAWarning: Attribute history events accumulated on 1 previously clean instances within inner-flush event handlers have been reset, and will not result in database updates. Consider using set_committed_value() within inner-flush event handlers to avoid this warning.

set_committed_value() allows setting attributes with no history events, as if the set value was part of the original loaded state.

You've also noticed that receiving a target in an after update event handler does not guarantee that an UPDATE statement was actually emitted:

This method is called for all instances that are marked as “dirty”, even those which have no net changes to their column-based attributes, and for which no UPDATE statement has proceeded.

and

To detect if the column-based attributes on the object have net changes, and therefore resulted in an UPDATE statement, use object_session(instance).is_modified(instance, include_collections=False).

So a solution could be to use the information held in the event target to emit an UPDATE statement on the parent table using the given connection, and then to check if the Parent object is present in the session and set the committed value of it:

from sqlalchemy import event
from sqlalchemy.orm.attributes import set_committed_value
from sqlalchemy.orm.session import object_session

@event.listens_for(Child, 'after_update')                
def receive_child_after_update(mapper, connection, target):
    session = object_session(target)                       

    if not session.is_modified(target, include_collections=False):
        return                                                    

    new_date_modified = target.date_modified

    # Avoid touching the target.parent relationship attribute and
    # copy the date_modified value from the child to parent.
    # Warning: this will overwrite possible other updates to parent's
    # date_modified.
    connection.execute(
        Parent.__table__.
        update().        
        values(date_modified=new_date_modified).
        where(Parent.id == target.parent_id))

    parent_key = session.identity_key(Parent, target.parent_id)

    try:
        the_parent = session.identity_map[parent_key]

    except KeyError:
        pass

    else:
        # If the parent object is present in the session, update its
        # date_modified attribute **in Python only**, to reflect the
        # updated DB state local to this transaction.
        set_committed_value(
            the_parent, 'date_modified', new_date_modified)