CODEWITHSUNDEEP
mysqlsqlalchemy
Noam
Noam

Reputation: 1804

sqlalchemy trigger automatic insert after delete on different table

I'm using SQLAlchemy with python, wondering if it is possible to automatically insert a record into table Deleted Users when a record has been deleted from table Users.

I know that using a foreign key, I can use the on_delete attribute to delete records from connected tables but I need to insert record.

For the sake of simplicity let's assume both tables have the same schema (2 columns; user_id and username.

Thanks

Upvotes: 0

Views: 2536

Answers (1)

zchtodd
zchtodd

Reputation: 1150

Triggers or a "deleted" column might be the most reliable way to do this, but if you'd like to do it using SQLAlchemy, the event API could be the way to go.

from sqlalchemy import event
from sqlalchemy.sql import dml, select

def before_flush(session, flush_context, instances):
    for instance in session.deleted:
        if isinstance(instance, User):
            # insert into the deleted user table

def before_execute(conn, clauseelement, multiparams, params):
    if isinstance(clauseelement, dml.Delete):
       if clauseelement.table.name == "user":
           query = select([clauseelement.table])
           if clauseelement._whereclause is not None:
               query = query.where(clauseelement._whereclause)
           for row in conn.execute(query):
               # insert into the deleted user table

event.listen(session, "before_flush", before_flush)
event.listen(engine, "before_execute", before_execute)

The before_flush function would capture ORM deletes while the before_execute captures generic queries that delete from the user table.

Upvotes: 1

Related Questions