Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 569
David White Avatar asked Aug 29 '16 21:08

David White


People also ask

Does SQLAlchemy support batching?

SQLAlchemy supports the widest variety of database and architectural designs as is reasonably possible. Unit Of Work. The Unit Of Work system, a central part of SQLAlchemy's Object Relational Mapper (ORM), organizes pending insert/update/delete operations into queues and flushes them all in one batch.

What is _sa_instance_state in SQLAlchemy?

_sa_instance_state is a non-database-persisted value used by SQLAlchemy internally (it refers to the InstanceState for the instance.

What does SQLAlchemy text do?

SQLAlchemy lets you just use strings, for those cases when the SQL is already known and there isn't a strong need for the statement to support dynamic features. The text() construct is used to compose a textual statement that is passed to the database mostly unchanged.

How does SQLAlchemy Session work?

In the most general sense, the Session establishes all conversations with the database and represents a “holding zone” for all the objects which you've loaded or associated with it during its lifespan. It provides the interface where SELECT and other queries are made that will return and modify ORM-mapped objects.


3 Answers

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)
like image 146
Chayemor Avatar answered Oct 04 '22 20:10

Chayemor


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.

like image 29
CharlieB Avatar answered Oct 03 '22 20:10

CharlieB


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).

like image 2
snakecharmerb Avatar answered Oct 04 '22 20:10

snakecharmerb