Is possible to create Column in SQLAlchemy which is going to be automatically populated with time when it inserted/updated last time?

Question

Is it possible to create Column in SQLAlchemy which is going to be automatically populated with time when it inserted/updated last time ?

I created models, inherited from Base class

class Base(object):
    def __tablename__(self):
        return self.__name__.lower()
    id = Column(Integer, primary_key=True)
    last_time = Column(TIMESTAMP, server_default=func.now())

Base = declarative_base(cls=Base)

class EntityModel(Base):
    __tablename__ = 'entities'
    settlement_id = Column(Integer, ForeignKey('settlements.id'), nullable=False)
    type = Column(String(20), nullable=False)
    level = Column(Integer, nullable=False, default=0)
    energy = Column(Float, nullable=False, default=0)
    position_x = Column(Integer, default=0)
    position_y = Column(Integer, default=0)

    def __repr__(self):
        return "<Entity('%s')>" % (self.type)

Every time when I update EntityModel I want to the last_time be updated on system function.now(). I can do this on the database level with triggers but I would rather do on application level if is it possible.

Solution 1

In Base class add onupdate in the last statement as follows:

from sqlalchemy.sql import func

last_time = Column(TIMESTAMP, server_default=func.now(), onupdate=func.current_timestamp())

Solution 2

If you use MySQL, I believe you can only have one auto-updating datetime column, so we use SQLAlchemy's event triggers instead.

You just attach a listener to the 'before_insert' and 'before_update' hooks and update as necessary:

from sqlalchemy import event

@event.listen(YourModel, 'before_insert')
def update_created_modified_on_create_listener(mapper, connection, target):
  """ Event listener that runs before a record is updated, and sets the create/modified field accordingly."""
  target.created = datetime.utcnow()
  target.modified = datetime.utcnow()

@event.listen(YourModel, 'before_update')
def update_modified_on_update_listener(mapper, connection, target):
  """ Event listener that runs before a record is updated, and sets the modified field accordingly."""
  # it's okay if this field doesn't exist - SQLAlchemy will silently ignore it.
  target.modified = datetime.utcnow()

I knew nobody would ever remember to add this to new models, so I tried to be clever and add it for them.

All our models inherit from a base object we cleverly called "DatabaseModel". We check who inherits from this object and dynamically add the triggers to all of them.

It's OK if a model doesn't have the created or modified field - SQLAlchemy appears to silently ignore it.

class DatabaseModel(db.Model):
  __abstract__ = True

  #...other stuff...

  @classmethod
  def _all_subclasses(cls):
    """ Get all subclasses of cls, descending. So, if A is a subclass of B is a subclass of cls, this
    will include A and B.
    (Does not include cls) """
    children = cls.__subclasses__()
    result = []
    while children:
      next = children.pop()
      subclasses = next.__subclasses__()
      result.append(next)
      for subclass in subclasses:
        children.append(subclass)
    return result

def update_created_modified_on_create_listener(mapper, connection, target):
  """ Event listener that runs before a record is updated, and sets the create/modified field accordingly."""
  # it's okay if one of these fields doesn't exist - SQLAlchemy will silently ignore it.
  target.created = datetime.utcnow()
  target.modified = datetime.utcnow()

def update_modified_on_update_listener(mapper, connection, target):
  """ Event listener that runs before a record is updated, and sets the modified field accordingly."""
  # it's okay if this field doesn't exist - SQLAlchemy will silently ignore it.
  target.modified = datetime.utcnow()


for cls in DatabaseModel._all_subclasses():
  event.listen(cls, 'before_insert',  update_created_modified_on_create_listener)
  event.listen(cls, 'before_update',  update_modified_on_update_listener)

Solution 3

It is worth nothing that, if you follow Rachel Sanders' recommendation, you should definitely do:

if object_session(target).is_modified(target, include_collections=False):
    target.modified = datetime.utcnow()

as part of the update_modified_on_update_listener() event listener, otherwise you'll do tons of redundant database updates. Checkout http://docs.sqlalchemy.org/en/latest/orm/events.html#mapper-events under the section "before_update" for more information.

Comments

[Joe Holloway]

You don't want to call the function in the column definition, but rather pass a reference to the function itself.

[tigeronk2]

func.now() is not a function call. This only helps SQLAlchemy generate the SQL expression to get the timestamp.

[Joe Holloway]

Gotcha, I misunderstood what OP was saying with 'rather do on application level' as wanting to call a Python function to do it in his models (i.e. datetime.now)

[weatherfrog]

Thank you. But I believe it should be @event.listens_for(...) instead of @event.listen(...).

[FelixEnescu]

Since MySQL 5.6.5 (dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html) you can have multiple auto-updating datetime columns

[Mark Amery]

Anyone puzzled by the presence of both func.now and func.current_timestamp in the code above, note that they are aliases of each other (see docs.sqlalchemy.org/en/latest/core/…).

[mblakesley]

Why does this solution work but this one doesn't: server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"). It's frustrating because the SqlAlchemy docs recommend the latter.

[Emilio]

This exact example is explained here in their docs, including the create_date and last_modified cases.