-->

How do I update related object on update in SQLAlc

2019-05-25 02:41发布

问题:

I have a one-to-one relationship between a Rule and a RuleStats object. I am trying to modify the last_updated field on the RuleStats when any field on the Rule is updated. If they were not two separate classes it would look something like this.

class Rule(Base):
    __tablename__ = 'rules'

    def __init__(self, **kwargs):
        Base.__init__(self, **kwargs)
        self.stats = RuleStats(rule=self)

    id = Column(Integer, autoincrement=True, primary_key=True)
    description = Column(String, nullable=False, default='')
    # ... more fields

    last_updated = Column(DateTime, default=datetime.now, onupdate=datetime.now, nullable=False)

But how do I do it if I have two separate objects,

class Rule(Base):
    __tablename__ = 'rules'

    def __init__(self, **kwargs):
        Base.__init__(self, **kwargs)
        self.stats = RuleStats(rule=self)

    id = Column(Integer, autoincrement=True, primary_key=True)
    description = Column(String, nullable=False, default='')
    # ... more fields

    stats = relationship('RuleStats', uselist=False, backref='rule', cascade='all, delete-orphan')

class RuleStats(Base):
    __tablename__ = 'rule_stats'

    def __init__(self, **kwargs):
        Base.__init__(self, **kwargs)

    rule_id = Column(Integer, ForeignKey('rules.id'), primary_key=True)
    last_updated = Column(DateTime, default=datetime.now, nullable=False)

回答1:

I think the simplest approach would be

  • Make function Rule.update() and do updates only through this specific function in your application. Example:

    from sqlalchemy.orm.attributes import set_attribute
    
    class Rule:
    
       def update(self, **kwargs):
           """Update instance attributes and bump last_modified date in stats"""
           for key, value in kwargs.items():
                   set_attribute(self, key value)
           self.stats.last_updated = now()
    

Then:

 rule.update(description="Python rocks!")

If you want to make it transparent to the user, so that RuleState is always updated, even though you poke Rule attributes directly, you could use Python properties for this. This approach, however, leads more complex code base and I could not recommend it outhand.

Alternatively, you can use database triggers to execute the update on the database side. Update trigger example for PostgreSQL.

There most likely exists an method doing this internally in advanced SQLAlchemy, but I don't have enough insight to tell where to start poking.