after_insert event on different table

Question

I have Table A and Table B where I'd like to add/update/delete columns in Table B on Table A events. I've tried the following:

Mapper Events a.k.a @event.listens_for(SomeClass, 'before_insert')

The documentation states

Mapper-level flush events are designed to operate on attributes local to the immediate object being handled and via SQL operations with the given Connection only... Operations that are not supported in mapper events include: Session.add() Session.delete()... Mapped relationship attribute set/del events

This limits whatever I can do with other tables. For instance,

def after_insert_listener(mapper, connection, target):
    target.value = "New Value"

works perfectly fine while

def after_insert_listener(mapper, connection, target):
    target.child.count += 1

doesn't do the trick. Putting db.session.commit() gives me this error

ResourceClosedError: This transaction is closed

I tried using SessionEvents.before_flush(), but neither could it be attached to a particular model nor did I understand how to use it.

Flask-SQLAlchemy signals

I tried using models_committed signal:

@models_committed.connect_via(app)
def on_models_committed(sender, changes):
    for obj, change in changes:
        if change == 'delete' and hasattr(obj, '__after_delete__'):
            obj.__after_delete__()
        elif change == 'update' and hasattr(obj, '__after_update__'):
            obj.__after_update__()
        elif change == 'insert' and hasattr(obj, '__after_insert__'):
            obj.__after_insert__()

class TableA(db.Model):
    def __after_insert__(self):
        self.child.count += 1
        self.value = "New Value"
        db.session.commit()

This gave me

InvalidRequestError: This session is in 'committed' state; no further SQL can be emitted within this transaction.

How can I update instances of a model after another model is inserted?

Ibrahim Ibrahim · Accepted Answer · 2015-02-19T17:55:19

You can try with this,

        @event.listens_for(B, "after_insert")
        def after_insert_listener(mapper, connection, target):
            A_table = A.__table__
            connection.execute(
                    A_table.update().
                     where(A_table.id==target.a_table.id). # Do Foreign key join porperly 
                     values(value='New Value')
            )

after_insert event on different table

1 Answers