在 sqlachemy 中触发 [英] Trigger in sqlachemy

问题描述

我有两个通过外键关联的表，这里它们使用的是声明式映射

I have two tables related via a foreign key, here they are using Declarative Mapping

class Task(DeclarativeBase):
    __tablename__ = 'task'
    id = Column(Integer, primary_key=True)
    state = Column(Integer, default=0)
    obs_id = Column(Integer, ForeignKey('obs.id'), nullable=False)

class Obs(DeclarativeBase):
    __tablename__ = 'obs'
    id = Column(Integer, primary_key=True)
    state = Column(Integer, default=0)

所以，当 obs.state 更改为值 2 时，我想更新相关的 task.state.目前我正在手动进行(使用称为任务的关系)

So, I would like to update the related task.state when obs.state is changed to value 2. Currently I'm doing it by hand (using a relationship called task)

obs.state = 2
obs.task.state = 2

但我更喜欢使用触发器来做.我已经检查过这在 sqlite 中有效

But I would prefer doing it using a trigger. I have checked that this works in sqlite

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;

但我找不到如何在 sqlalchemy 中表达这一点.我已阅读 insert多次更新默认值，但找不到方法.我不知道这是否可能.

But I can't find how to express this in sqlalchemy. I have read insert update defaults several times, but can't find the way. I don't know if it's even possible.

推荐答案

您可以使用 DDL 类:

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)

这是最可靠的方法:它适用于不使用 ORM 时的批量更新，甚至适用于应用程序之外的更新.但是也有缺点:

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:

• 你必须注意你的触发器存在并且是最新的；
• 它不可移植，所以如果你改变数据库，你必须重写它；
• SQLAlchemy 不会更改已加载对象的新状态，除非您将其过期(例如，使用某些事件处理程序).

下面是一个不太可靠的(仅在 ORM 级别进行更改时才有效)，但更简单的解决方案:

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

我的两个示例都以一种方式工作，即它们在 obs 更改时更新任务，但在任务更新时不要触摸 obs.您必须再添加一个触发器或事件处理程序来支持双向更改传播.

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.

这篇关于在 sqlachemy 中触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！