Trigger in sqlachemy

by

You can create trigger in the database with DDL class:

update_task_state = DDL('''\
CREATE TRIGGER update_task_state UPDATE OF state ON obs
  BEGIN
    UPDATE task SET state = 2 WHERE (obs_id = old.id) and (new.state = 2);
  END;''')
event.listen(Obs.__table__, 'after_create', update_task_state)

This is the most reliable way: it will work for bulk updates when ORM is not used and even for updates outside your application. However there disadvantages too:

  • You have to take care your trigger exists and up to date;
  • It’s not portable so you have to rewrite it if you change database;
  • SQLAlchemy won’t change the new state of already loaded object unless you expire it (e.g. with some event handler).

Below is a less reliable (it will work when changes are made at ORM level only), but much simpler solution:

from sqlalchemy.orm import validates

class Obs(DeclarativeBase):
    __tablename__ = 'obs'
    id = Column(Integer, primary_key=True)
    state = Column(Integer, default=0)
    @validates('state')
    def update_state(self, key, value):
        self.task.state = value
        return value

Both my examples work one way, i.e. they update task when obs is changes, but don’t touch obs when task is updated. You have to add one more trigger or event handler to support change propagation in both directions.

More Related Contents:

  1. How to convert SQLAlchemy row object to a Python dict?
  2. jsonify a SQLAlchemy result set in Flask [duplicate]
  3. Using OR in SQLAlchemy
  4. SQLAlchemy ORM conversion to pandas DataFrame
  5. SQLAlchemy: unexpected results when using `and` and `or`
  6. Writing to MySQL database with pandas using SQLAlchemy, to_sql
  7. sqlalchemy: how to join several tables by one query?
  8. How to create an SQL View with SQLAlchemy?
  9. Serve image stored in SQLAlchemy LargeBinary column
  10. When do I need to use sqlalchemy back_populates?
  11. SQLAlchemy ManyToMany secondary table with additional fields
  12. Can SQLAlchemy be used with Google Cloud SQL?
  13. Updates to JSON field don’t persist to DB
  14. ImportError: No module named MySQLdb
  15. How do I select literal values in an sqlalchemy query?
  16. Get inserted key before commit session
  17. Querying with function on Flask-SQLAlchemy model gives BaseQuery object is not callable error
  18. SQLAlchemy multiple foreign keys in one mapped class to the same primary key
  19. SQLAlchemy – performing a bulk upsert (if exists, update, else insert) in postgresql
  20. SQLAlchemy default DateTime
  21. Setting SQLAlchemy autoincrement start value
  22. flask-sqlalchemy or sqlalchemy
  23. python pandas to_sql with sqlalchemy : how to speed up exporting to MS SQL?
  24. SQLAlchemy Obtain Primary Key With Autoincrement Before Commit
  25. SQLAlchemy: get Model from table name. This may imply appending some function to a metaclass constructor as far as I can see
  26. SQLAlchemy – can you add custom methods to the query object?
  27. pandas to_sql all columns as nvarchar
  28. How to make mysql connection that requires CA-CERT with sqlalchemy or SQLObject
  29. Can’t change system default SQLite binary
  30. add column to SQLAlchemy Table

Leave a Comment