How to update parent table timestamp when child table is modified?

Question

How can I update parent timestamp when child table is modified?

I would like to use parent table timestamp for checking if my rest client should update it's local version of these tables.

class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    version = db.Column(db.Integer)
    timestamp = db.Column(db.DateTime,
                          default=datetime.utcnow,
                          onupdate=datetime.utcnow)
    childs = db.relationship('Children',
                             backref='parent',
                             lazy='dynamic',
                             cascade="all, delete-orphan")

class Children(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    version = db.Column(db.Integer)
    timestamp = db.Column(db.DateTime,
                          default=datetime.utcnow,
                          onupdate=datetime.utcnow)
    parent_id = db.Column(db.Integer, db.ForeignKey('parent.id'), nullable=False)

And test this:

    db.create_all()
    parent = Parent(version=1)
    child = Children(version=1, parent=parent)
    db.session.add_all([parent, child])
    db.session.commit()
    print "parent timestamp: %s, child timestamp %s" % (parent.timestamp, child.timestamp)
    previous_timestamp = parent.timestamp
    parent.version = 2
    db.session.add(parent)
    db.session.commit()
    assert parent.timestamp != previous_timestamp # this works
    print "parent timestamp: %s, child timestamp %s" % (parent.timestamp, child.timestamp)
    previous_timestamp = parent.timestamp
    child.version = 2
    db.session.add(child)
    db.session.commit()
    # this fails. Parent timestamp is not updated when child is modified
    assert parent.timestamp != previous_timestamp
    print "parent timestamp: %s, child timestamp %s" % (parent.timestamp, child.timestamp)

Answer 1

Use SQLAlchemy events as answered by this question.

See below a self-contained Flask example using an in-memory SQLite database and your data models (note I've changed your Children class to Child and the relationship childs to children.

Browse to the three routes /insert_child/, /delete_child/ and /update_child/ to see the parent timestamp change.

from datetime import datetime
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy, event

app = Flask(__name__)
app.config['DEBUG'] = True
app.config['SECRET_KEY'] = 'super-secret'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)


class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    version = db.Column(db.Integer)
    timestamp = db.Column(db.DateTime,
                          default=datetime.utcnow,
                          onupdate=datetime.utcnow)
    children = db.relationship('Child',
                             backref='parent',
                             lazy='dynamic',
                             cascade="all, delete-orphan")


class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    version = db.Column(db.Integer)
    timestamp = db.Column(db.DateTime,
                          default=datetime.utcnow,
                          onupdate=datetime.utcnow)
    parent_id = db.Column(db.Integer, db.ForeignKey('parent.id'), nullable=False)


@event.listens_for(Parent.children, 'append')
@event.listens_for(Parent.children, 'remove')
def receive_append_or_remove(target, value, initiator):
    # Update when a child is added or removed
    target.timestamp = datetime.utcnow()


@event.listens_for(Child.version, 'set')
def receive_attribute_change(target, value, oldvalue, initiator):
    # Update when a child's "version" attribute is set
    if target.parent:
        target.parent.timestamp = datetime.utcnow()


def render_html():
    _html = ""
    parents = Parent.query.all()
    for parent in parents:
        _html += "<h4>Parent : {version}; Timestamp: {timestamp}</h4>".format(version=parent.version, timestamp=parent.timestamp)
        _html += "<ul>"
        for child in parent.children:
            _html += "<li>Child : {version}; Timestamp: {timestamp}</li>".format(version=child.version, timestamp=child.timestamp)
        _html += "</ul>"
    return _html


@app.route('/')
def index():
    return render_html()


@app.route('/insert_child/')
def insert_child():
    parent = Parent.query.first()
    child_version = parent.children.count() + 1
    child = Child(version=child_version, parent=parent)
    db.session.add(child)
    db.session.commit()
    return render_html()


@app.route('/delete_child/')
def delete_child():
    parent = Parent.query.first()
    if parent.children.count() > 0:
        last_child = parent.children[-1]
        parent.children.remove(last_child)
        db.session.commit()
    return render_html()


@app.route('/update_child/')
def update_child():
    parent = Parent.query.first()
    first_child = parent.children[0]
    first_child.version += 1
    db.session.commit()
    return render_html()


@app.before_first_request
def before_first_request():
    db.drop_all()
    db.create_all()

    parent = Parent(version=1)
    child = Child(version=1, parent=parent)
    db.session.add_all([parent, child])
    db.session.commit()

if __name__ == '__main__':
    app.run(debug=True)