Flask+SQLAlchemy thows OperationalError when trying to filter records by foreign key column

Issue

So, I’m working on a little blog-style kind of web app as a personal project. I have the bul of it working by now, but I’ve been having trouble trying to implement comments on the posts.

The way I have my posts set up is through an SQLAlchemy ORM model, which works fine. It has content and title fields and what not, and a many-to-one relationship to the post author, like such:

class Post(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    title = db.Column(db.String(100), nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    content = db.Column(db.Text, nullable=False)
    feature_image = db.Column(db.String, nullable=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    comments = db.relationship('Comment', backref='original_post', lazy=True)

My comments follow the same kind of structure, having a many-to-one relationship to the post, like this:

class Comment(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    content = db.Column(db.Text, nullable=False)
    post_id = db.Column(db.Integer, db.ForeignKey('post.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

So, in order to have the comments show up below the post, I am trying to query the ‘comment’ table and filter for the ‘post_id’, which supposedly matches the post.id, from the ‘post’ table:

@app.route('/post/<int:post_id>')
def post(post_id):
    post = Post.query.get_or_404(post_id)
    comments = Comment.query.filter(Comment.original_post==post)

But when I try to load the post page, it throws me the following error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: comment.post_id
[SQL: SELECT comment.id AS comment_id, comment.date_posted AS comment_date_posted, comment.content AS comment_content, comment.post_id AS comment_post_id, comment.user_id AS comment_user_id 
FROM comment 
WHERE ? = comment.post_id]
[parameters: (1,)]
(Background on this error at: http://sqlalche.me/e/e3q8)

I looked through some other threads on the subject which suggested a .join before the query like this:

comments = Comment.query.join(Post).filter(Post.id == post_id).all()

But I get a similar error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: comment.post_id
[SQL: SELECT comment.id AS comment_id, comment.date_posted AS comment_date_posted, comment.content AS comment_content, comment.post_id AS comment_post_id, comment.user_id AS comment_user_id 
FROM comment JOIN post ON post.id = comment.post_id 
WHERE post.id = ?]
[parameters: (1,)]

Solution

My ‘comment’ table in the db file was simply missing the Foreign Key field.

Answered By – PedroBinotto

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published