Add trigger to SQLAlchemy Base Class

Question

I'm making a SQLAlchemy base class for a new Postgres database and want to have bookkeeping fields incorporated into it. Specifically, I want to have two columns for modified_at and modified_by that are updated automatically. I was able to find out how to do this for individual tables, but it seems like making this part of the base class is trickier.

My first thought was to try and leverage the declared_attr functionality, but I don't actually want to make the triggers an attribute in the model so that seems incorrect. Then I looked at adding the trigger using event.listen:

trigger = """
CREATE TRIGGER update_{table_name}_modified
BEFORE UPDATE ON {table_name}
FOR EACH ROW EXECUTE PROCEDURE update_modified_columns()
"""

def create_modified_trigger(target, connection, **kwargs):
    if hasattr(target, 'name'):
        connection.execute(modified_trigger.format(table_name=target.name))

Base = declarative_base()

event.listen(Base.metadata,'after_create', create_modified_trigger)

I thought I could find table_name using the target parameter as shown in the docs but when used with Base.metadata it returns MetaData(bind=None) rather than a table.

I would strongly prefer to have this functionality as part of the Base rather than including it in migrations or externally to reduce the chance of someone forgetting to add the triggers. Is this possible?

zachalam · Accepted Answer · 2015-05-29 22:17:28Z

5

I was able to sort this out with the help of a coworker. The returned MetaData object did in fact have a list of tables. Here is the working code:

modified_trigger = """
CREATE TRIGGER update_{table_name}_modified
BEFORE UPDATE ON {table_name}
FOR EACH ROW EXECUTE PROCEDURE update_modified_columns()
"""

def create_modified_trigger(target, connection, **kwargs):
    """
    This is used to add bookkeeping triggers after a table is created. It hooks
    into the SQLAlchemy event system. It expects the target to be an instance of
    MetaData.
    """
    for key in target.tables:
        table = target.tables[key]
        connection.execute(modified_trigger.format(table_name=table.name))

Base = declarative_base()

event.listen(Base.metadata, 'after_create', create_modified_trigger)

answered May 29, 2015 at 22:17

zachalam

1011 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add trigger to SQLAlchemy Base Class

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related