UPDATE a child table base on an update in a parent table in SqlAlchemy

Question

Using postgresql with sqlalchemy, I want to let a child know when a value in parent table has been updated:

class Parent(Base):
    __tablename__ = "parent_table"

    id = mapped_column(Integer, primary_key=True)
    children = relationship("Child", cascade = "all,delete", back_populates="parent")
    some_val = mapped_column(Integer)

class Child(Base):
    __tablename__ = "child_table"

    id = mapped_column(Integer, primary_key=True)
    parent_id = mapped_column(ForeignKey("parent_table.id"))
    parent = relationship("Parent", back_populates="children")
    some_val_modified = mapped_column(Boolean)

In the above scenario, I want some_val_modified to be set to true whenever some_val has been modified in the parent class. Is there a way I can do that?

I was trying to make a function that achieves this using onupdate, but couldn't figure out the proper syntax or if this is even allowed using documentation.

You might check out the observer pattern. Maybe this might help : https://stackoverflow.com/questions/44499490/alternate-ways-to-implement-observer-pattern-in-python — JonSG, Mar 20 '23 at 16:49
This sounds like an [XY](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem/66378#66378) problem. Feel free to reveal your actual use case, as the details are likely to matter. Do you have many parent_table writers, and are unable to package the writing into a common helper method? Is there deliberate [denorm](https://en.wikipedia.org/wiki/Denormalization) going on here? Is the child typically read without a FK parent JOIN? Consider adding timestamps to your CREATE TABLEs and then compare the `modified` columns. Querying for recent mods is an efficient update technique. — J_H, Mar 20 '23 at 17:24

Savir · Answer 1 · 2023-03-20T19:38:01.537

⚠️ DISCLAIMER: This solution is pretty rough and barely tested. I'm also not the most knowledgeable person in Postgresql (well... or... anything else, for that matter). You should do some tests, probably some extra Googling and think of possible edge cases before applying it.

... this said...

There is a couple of things you could probably do:

You could add a SqlAlchemy listener (see docs) so every time a value is set for a Parent.some_val you update its children.

The code below will only update the Children if the Parent's some_val value was originally set to something (notice the skip if the initial value is NO_VALUE) and it's going to change.

from sqlalchemy import event, update
from sqlalchemy.orm import NO_VALUE, object_session

@event.listens_for(Parent.some_val, 'set')
def receive_set(target, value, oldvalue, initiator):
    if value == oldvalue or oldvalue == NO_VALUE:
        print("nothing to do (value didn't change or original value wasn't set)")
        return

    # Ok, value has changed
    print(f"target {target} (target.id={target.id})"
          f" value: {value} oldvalue: {oldvalue} initiator {initiator}")

    object_session(target).execute(
        update(Child).where(Child.parent_id == target.id)
        .values(some_val_modified=True)
    )

Keep in mind that this piece of code "lives" in the SqlAlchemy world and if something goes wrong in the SqlAlchemy (Python) execution, your database might end up in a wonky state.

That's why I usually prefer to move this responsibility to the database itself. Meaning: don't do this in Python/Sqlalchemy, but move a bit "lower" in the chain and do it via database functions and triggers (see docs). After all, the entity who knows best the database status is... the database itself.

First, when the Child table is created, also create the SQL function that will update the child table if some_val attribute in the parent has changed:

from sqlalchemy import DDL
from sqlalchemy import event

function_dll = DDL(f"""
    CREATE OR REPLACE FUNCTION parent_table_val_modified_watcher()
      RETURNS trigger
      LANGUAGE plpgsql AS
    $func$
    BEGIN
       IF NEW.some_val != OLD.some_val THEN
          UPDATE "{Child.__table__.schema}"."{Child.__table__.name}" SET
          some_val_modified = TRUE
          WHERE "{Child.__table__.schema}"."{Child.__table__.name}".parent_id = NEW.id;
       END IF;

       RETURN NEW;
    END
    $func$;
    """)
# Apply it after SqlAlchemy creates the Child table
event.listen(
    Child.__table__,
    'after_create',
    function_dll.execute_if(dialect='postgresql')
)

Then, create the trigger (the "listener" if you may) that will run that function when something (some Parent) is inserted or updated in the parent_table.

trig_ddl = DDL(f"""
    CREATE TRIGGER parent_table_val_modified_watcher AFTER INSERT OR UPDATE
    ON "{Parent.__table__.schema}"."{Parent.__table__.name}"
    FOR EACH ROW EXECUTE PROCEDURE
    parent_table_val_modified_watcher();
""")
event.listen(
    Parent.__table__,
    'after_create',
    trig_ddl.execute_if(dialect='postgresql')
)

UPDATE a child table base on an update in a parent table in SqlAlchemy

1 Answers1