Creating a Many-to-Many Relationship with Flask-SQLAlchemy

Flask is a micro framework in Python, and when combined with SQLAlchemy, it becomes a powerful tool for creating a web application with a complex database relationship. One of these relationships is called a many-to-many relationship, where multiple records in one table are related to multiple records in another table. To briefly define what I mean by micro framework, Flask is considered a micro framework because it provides the user to important functionality, mainly giving the ability to send http requests to the database. In this blog post we will go over setting up and managing a many-to-many relationship using Flask-SQLAlchemy.

Understanding A Many-to-Many Relationship

A many-to-many relationship exists when multiple records in one table are associated with multiple records in another table, with a “join” table that connects them together.

In this blog post, we will be creating three tables: “Student”, “Course”, and a join table that is typically named by combining the table names like, “Student_Course”. The “Student_Course” join table will contain foreign keys that reference the “Student” and the “Course” tables, that will establish a many-to-many relationship.

Creating the Database

Lets define the “Student” and “Course” models in your Flask application, and then create a join table “Student_Course” to establish a many-to-many relationship:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Student(db.Model):
  __tablename__ = "student"
  
  id = db.Column(db.Integer, primary_key = True
  name = db.Column(db.String(50), nullable = False
  
  course = db.relationship('Course', secondary = 'student_course', back_populates = 'student'

class Course(db.Model):
  __tablename__ = "course"
  
  id = db.Column(db.Integer, primary_key = True)
  title = db.Column(db.String(50), nullable = False)

  student = db.relationship('Student', secondary = 'student_course',back_populate = 'course'

#join table
student_course = db.Table(
  'student_course',
  db.Column('student_id', db.Integer, db.ForeignKey('student.id')),
  db.Column('course_id', db.Integer, db.ForeignKey('course_id'))
)

Add Data to Tables

#create a new student
new_student = Student(name = "Justin Becker")
db.session.add(new_student)

#create course
new_course = Course(title = "Flask-SQLAlchemy 101")
db.session.add(new_course)

#Enroll student in the new course
new_student.course.append(new_course)

#commit changes to the database
db.session.commit()

From this point, we are able to query the students that are enrolled in the “Flask-SQLAlchemy 101” course:

fs_query = Course.query.filter_by(title="Flask-SQLAlchemy 101".first()
student_in_fs = fs_query.student

for student in student_in_fs:
  print(student.name)

Flask-SQLAlchemy simplifies the process of creating a many-to-many relationship in a Flask application. By defining the appropriate models and using the “relationship” function, you can easily manage complex data structures and build web applications that handle intricate database relationships.

We’ve covered the basics of setting up and using many-to-many relationships in Flask-SQLAlchemy with a simple database model. This knowledge will equip you to create web applications that handle complex data relationships efficiently and effectively.

Resources:

• Flask Framework Cookbook by shalabh Aggarwal
• Flask Web Development: Developing Web Applications with Python
• How To Use Many-to-Many Database Relationships with Flask-SQLAlchemy | DigitalOcean
• python — Setting up many-to-many relationship in flask sqlalchemy — Stack Overflow