修改子表时如何更新父表时间戳？

Question

修改子表时如何更新父时间戳？在

我想使用父表时间戳检查rest客户机是否应该更新这些表的本地版本。在

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)

测试一下：

^{pr2}$

Answer 1

使用SQLAlchemyevents，正如这个question所回答的那样。在

下面是一个使用内存中的SQLite数据库和数据模型的自包含的Flask示例（注意，我已经将您的Children类改为Child，关系{}改为{}。在

浏览到三个routes/insert_child/、/delete_child/和/update_child/以查看父时间戳的更改。在

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)