One to Many Relationships with Flask-SQLAlchemy

One to Many Relationships with Flask-SQLAlchemy

While building database applications with Flask-SQLAlchemy, you will come across a situation where you need to define a relationship between two database tables. While creating blogging application I came across such a situation where I had to define a One to Many Relationship between a blog post (Post Table) and post comments(Comments Table).

In order to define a One to Many Relationships with Flask-SQLAlchemy you need to first add a Foreign key on the Comments or Child table referencing the Post or Parent table. You can then define the relationship, by adding  the relationship() function on the Parent or Post table, as referencing a collection of comments represented by the Comments table.

Example Code
#Complete code at
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy(app)

class Post(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  author = db.Column(db.String(255))
  title = db.Column(db.String(255),nullable=False)
  content = db.Column(db.Text)
#Defining One to Many relationships with the relationship function on the Parent Table
  comments = db.relationship('Comments', backref="post", cascade="all, delete-orphan" , lazy='dynamic')

class Comments(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  author = db.Column(db.String(128),nullable=False)
  website = db.Column(db.String(255))
  content = db.Column(db.Text, nullable=False)
#Defining the Foreign Key on the Child Table
  post_id = db.Column(db.Integer, db.ForeignKey(''))

I have given the relationship() function the following arguments:
‘Comments’ : The referencing Child table
backref=”post” : This argument adds a post attribute on the Comments table, so you can access a Post via the Comments Class as
cascade=”all, delete-orphan”: This will delete all comments when a referenced post is deleted.
“lazy=’dynamic’”: This will return a query object which you can refine further like if you want to add a limit etc.

In order to access the comments for a particular post you need to add the below code to your controller and template respectively.
#Complete code at
from flask import render_template
from models import Post

@app.route('/post/' )
def single_post(id):
  post = Post.query.get(id)
  if post == None:
     return abort(404)
  return render_template('single_post.html', post=post)



{{ post.title }}
{{ post.content|safe }}

{%for comment in post.comments.all()%}


  • Pingback: Many to Many Relationships with Flask-SQLALchemy -()

  • Pingback: How to build CRUD app with Python, Flask, SQLAlchemy and MySQL -()

  • Pingback: Python PostgreSQL example -()

  • tiberiusgracus

    Thanks! Really appreciate your explanations of the arguments

    • Leo G

      Welcome. 🙂

  • Лом Якубов

    I’ve been looking for this kind of explanation for more than two months!
    Thank you!

    • Leo G

      your welcome 🙂

  • Zhou Peng

    Hi Leo, thanks for the great post with a clear explanation of the different arguments. I am trying to learn about sqlite and sqlalchemy, so may i ask a naive question please?

    In sqlite, i understand that: For an “ON UPDATE CASCADE” action, it means that the values stored in each dependent child key are modified to match the new parent key values. (quoted from

    When we use cascade=”all” in sqlalchemy relationship, does this achieve a similar effect? If it is not, what should I do to achieve that?

    Thank you very much in advance!