CODEWITHSUNDEEP
pythonpostgresqlsqlalchemyflask-sqlalchemy
shiouming
shiouming

Reputation: 1949

sqlalchemy onupdate inconsistent behavior for Query.update()

I'm implementing a restful POST API with Flask, using sqlalchemy to update resource in PostgreSQL, say MyResource:

class MyResource(db.Model):
    __tablename__ = 'my_resource'

    res_id = Column(Integer, primary_key=True)
    <other columns>
    time_updated = Column(TIMESTAMP(timezone=True), onupdate=datetime.now(timezone.utc))

There's a MyResource instance derived from API's request payload, let's call it input_instance. Below is my approach for updating the resource:

input_instance_dict = input_instance.__dict__
input_instance_dict.pop('_sa_instance_state', None)  # this extra meta field will cause error in update().
update_count = MyResource.query.filter(MyResource.res_id == input_instance.res_id).update(input_instance_dict)
db.session.commit()

With above, columns are updated except time_updated, which remains null; I expect it to be updated with current date time.

If I remove the time_updated field from input prior to calling Query.update(),

input_instance_dict.pop('time_updated', None)

then the null value in time_updated column will be updated with current date time, BUT... on subsequent updates, this column's value remains the same old value.

My doubt is that, even with time_updated field removed from input dict, onupdate only takes effect for first update but not afterwards. Why? Thanks.

--- Update 12/23 10:56am GMT+8

Additional observation, I just re-triggered the same update as last night's twice, time_updated column is getting updated for first retry but not the second ones. Which means, After the very first update, onupdate takes effect on and off for following updates. I can't figure out the pattern, when it'll work and when it won't.

Upvotes: 1

Views: 1366

Answers (1)

shiouming
shiouming

Reputation: 1949

Similar problem is also observed for the other timestamp field to be populated with default, say... a record was inserted yesterday, but all records inserted today end up having the same time_created value as yesterday's value.

time_created = Column(TIMESTAMP(timezone=True), nullable=False, default=datetime.now(timezone.utc))

After changing the argument (for default and onupdate), replacing the python datetime function with sqlalchemy.func.now() resolves the weird behaviour.

time_created = Column(TIMESTAMP(timezone=True), nullable=False, default=func.now())
time_updated = Column(TIMESTAMP(timezone=True), onupdate=func.now())

I'm not sure why the behaviour difference, there are many tutorials that using datetime function as argument, I wonder if those programs having the same problem.

Upvotes: 1

Related Questions