Can I "touch" a SQLAlchemy record to trigger "onupdate"?

Question

Here's a SQLAlchemy class:

class MyGroup(Base):
    __tablename__ = 'my_group'
    group_id = Column(Integer, Sequence('my_seq'), primary_key=True)
    group_name = Column(String(200), nullable=False, index=True)
    date_created = Column(DateTime, default=func.now())
    date_updated = Column(DateTime, default=func.now(), onupdate=func.now())

Anytime I add a group_name or (for example) update the group_name, the date_updated field will get updated. That's great.

But sometimes there are cases where I want to mark a group as "updated" even if the group record itself did not change (for example, if data in a different but related table is updated).

I could do it manually:

group = session.query(MyGroup).filter(MyGroup.group_name=='Some Group').one()
group.date_updated = datetime.datetime.utcnow()
session.commit()

but I'd really rather let the model do it in its own way, rather than recreate a Python process to manually update the date. (For example, to avoid mistakes like where maybe the model uses now() while the manual function mistakenly uses utcnow())

Is there a way with SQLAlchemy to "touch" a record (kind of like UNIX touch) that wouldn't alter any of the record's other values but would trigger the onupdate= function?

Answer 1

Just to add to this answer, the documentation states the following:

The Column.default and Column.onupdate keyword arguments also accept Python functions. These functions are invoked at the time of insert or update if no other value for that column is supplied, and the value returned is used for the column’s value.

Key part being: are invoked at the time of insert or update if no other value for that column is supplied. Key part of the key part: if no other value for that column is supplied

So with a simple update statement with empty values, does the trick:

from sqlalchemy import update    
stmt = update(ModelName).where(ModelName.column.in_(column_values)).values()
db.engine.execute(update_product_mapping_info)

I am using the sqlalchemy.sql.expression.update for this, documentation here.

Here's the Model column definition I have:

from datetime import datetime
last_updated = Column(db.DateTime, onupdate=datetime.utcnow)

Answer 2

To show a complete example, building on @Chayemor's answer I did the following:

import sqlalchemy.sql.functions as func
from sqlalchemy import Column, Integer, String, update

from . import database as db


Base = declarative_base()


class Object(Base):
    __tablename__ = "objects"

    id = Column(Integer, primary_key=True, nullable=False)
    last_update = Column(
        DateTime, 
        server_default=func.now(), 
        onupdate=func.current_timestamp()
    )

    def touch(self):
        stmt = update(Game).where(Game.id == self.id)
        db.engine.execute(stmt)

From here, running obj.touch() updates its last_update field in the database without changing any other data.

Answer 3

Another way to do this is to call orm.attributes.flag_modified on an instance and attribute. SQLAlchemy will mark the attribute as modified even though it is unchanged and generate an update.

with Session.begin() as s:
    mg = s.execute(sa.select(MyGroup)).scalar_one()
    orm.attributes.flag_modified(mg, 'group_name')

Note that the "dummy" update will be included in the generated SQL's SET clause

UPDATE tbl 
  SET group_name=%(group_name)s, 
      date_updated=now() 
  WHERE tbl.group_id = %(tbl_group_id)s

in contrast with that generated by Chayemor's answer:

UPDATE tbl 
  SET date_updated=now() 
  WHERE tbl.group_name = %(group_name_1)s

This may be significant (consider triggers for example).