Sqlalchemy - update column based on changes in another column

Question

I'm using sqlalchemy but find documentation difficult to search.

I've these two columns:

    verified = Column(Boolean, default=False)
    verified_at = Column(DateTime, nullable=True)

I'd like to create a function that does something like this:

    if self.verified and not oldobj.verified:
        self.verified_at = datetime.datetime.utcnow
    if not self.verified and oldobj.verified:
        self.verified_at = None

I'm not sure where to put code like this. I could put it in the application, but would prefer the model object took care of this logic.

Answer 1

I think what you're looking for is a Hybrid Property.

from sqlalchemy.ext.hybrid import hybrid_property

class VerifiedAsset(Base):
    id = Column(Integer, primary_key=True)
    verified_at = Column('verified_at', String(24))

    @hybrid_property
    def verification(self):
        return self.verified_at;

    @verification.setter
    def verification(self, value):
        if value and not self.verification:
            self.verified_at = datetime.datetime.utcnow
        if not value and self.verification:
            self.verified_at = None
        # Presumably you want to handle your other cases here

You want to update your verified_at value in a particular way based on some incoming new value. Use properties to wrap the underlying value, and only update when it is appropriate, and only to what you're actually persisting in the db.

Answer 2

You can use sqlalchemy's events registration to put code like that: http://docs.sqlalchemy.org/en/latest/core/event.html. Basically, you can subscribe to certain events that happen in the Core and ORM. I think it's a clean way to manage what you want to achieve.

You would use the listen_for() decorator, in order to hook when those columns change.

Answer 3

Reading "Changing Attribute Behavior" and "ORM Events" is a good start on trying to solve this type of problem.

One way to go about it would be to set an event listener that updates the timestamp:

@event.listens_for(MyModel.verified, 'set')
def mymodel_verified_set(target, value, oldvalue, initiator):
    """Set verified_at"""
    if value != oldvalue:
        target.verified_at = datetime.datetime.utcnow() if value else None