flask Flask-SQLAlchemy datetime utcnow

Question

I would like to create a blog web app, but when comments were committed by db.session.commit(), it will change the time field in table posts. what I really want to do is, time of post is when the post was committed, it won't change with commits of comments. Here is my code:

class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True, nullable=False)
    time = db.Column(db.DateTime, index=True, nullable=False, default=datetime.utcnow)
    text = db.Column(db.Text, nullable=False)
    num_of_comments = db.Column(db.Integer, index=True, default=0)    
    comments = db.relationship('Comment', backref='post', lazy='dynamic')

class Comment(db.Model):
    __tablename__ = 'comments'
    id = db.Column(db.Integer, primary_key=True)
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    author_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    post_id = db.Column(db.Integer, db.ForeignKey('posts.id'))
    text = db.Column(db.Text)

@main.route('/post/<int:id>', methods=['GET', 'POST'])
def post(id):
    post = Post.query.get_or_404(id)
    form = CommentForm()

    if form.validate_on_submit():
      comment_author = current_user._get_current_object()
      comment = Comment(text = form.text.data
                  post=post,
                  author=comment_author)
      db.session.add(comment)
      if post.num_of_comments == None:
        post.num_of_comments = 0
      post.num_of_comments += 1
      flash('Your comment has been submitted.')
      return redirect(url_for('.post', id=post.id))

    comments = post.comments.order_by(Comment.timestamp.desc())
    return render_template('post.html', posts=[post], form=form, comments=comments)

each time post.num_of_comments add by 1, it will change the corresponding post, and db.session.commit() the change, and this will cause the change of Post.time. How should I avoid the change?

Any help will be appreciated!! Thanks very much!!

Do you have any database triggers that might be updating posts.time? — Kelly Keller-Heikkila, Sep 21 '15 at 17:03
after each change, db will update the change. here is how I can do this: add SQLALCHEMY_COMMIT_ON_TEARDOWN = True in config.py — Jack Zhang, Sep 23 '15 at 03:43
i would like to keep the Post.time the time post was posted, not the time post was changed. — Jack Zhang, Sep 23 '15 at 03:49

score 0 · Accepted Answer · edited May 23 '17 at 11:44

The problem of the web app is not from the flask part but the mysql part: the time field of Post in mysql was defined as "on update", to fix this, you can alter the column with:

ALTER TABLE posts CHANGE `time` `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

and now, when you execute show columns in posts; in mysql, it will show no "on update" anymore.

I got the answer from How do I alter a mysql table column defaults? and https://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html thank you guys, anyway!!

flask Flask-SQLAlchemy datetime utcnow

1 Answers1