CODEWITHSUNDEEP
pythonsqlalchemy
Dan M
Dan M

Reputation: 51

Sqlalchemy - update column based on changes in another column

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.

Upvotes: 5

Views: 5699

Answers (4)

Ricky Levi
Ricky Levi

Reputation: 7997

You can also just say, that whenever the row was updated you can update the verified_at field like so:

verified_at = Column(DateTime, nullable=True, onupdate=datetime.utcnow)

Anytime, a specific row was changed & committed successfully - this verified_at will get updated as well to the current date/time.

Upvotes: -1

Ilja Everilä
Ilja Everilä

Reputation: 52929

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

Upvotes: 3

Mariano Anaya
Mariano Anaya

Reputation: 1296

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.

Upvotes: 3

Nathaniel Ford
Nathaniel Ford

Reputation: 21220

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.

Upvotes: 8

Related Questions