CODEWITHSUNDEEP
pythonsqlalchemy
Nik Klassen
Nik Klassen

Reputation: 711

How do I update related object on update in SQLAlchemy?

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)

Upvotes: 2

Views: 3301

Answers (1)

Mikko Ohtamaa
Mikko Ohtamaa

Reputation: 83768

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.

Upvotes: 1

Related Questions