在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时进入状态。目前，我正在手工操作（使用称为任务的关系）

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中表达它。我已阅读插入多次更新默认值，但找不到方法。我不知道这是否有可能。

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:

• 您必须注意触发器的存在并保持最新状态； <​​/ li>
  
• 它不是可移植的，因此如果您更改数据库，则必须重写它；


• 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屋！