修改子表时如何更新父表时间戳? [英] How to update parent table timestamp when child table is modified?
问题描述
修改子表后如何更新父时间戳?
我想使用父表时间戳检查我的其余客户端是否应该更新这些表的本地版本.
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)
并对此进行测试:
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)
请参见下面的一个独立的Flask示例,该示例使用内存中的SQLite数据库和您的数据模型(请注意,我已将Children
类更改为Child
,并将关系childs
更改为children
.>
浏览到三个路由/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)
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)
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)
这篇关于修改子表时如何更新父表时间戳?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!