Creating Many to Many Relationship in Flask-SqlAlchemy

Question

I need to create a Student Management System which can help associate in a way where multiple teachers can teach multiple students and multiple students can have multiple teachers . Now I have created a Code below .P.S. If You think there is a Better Way to achieve what I want , Please Guide , I am new to many to many relationships and there are so many ways on the internet , It is just confusing:

AssociationTable=db.Table('AssociationTable',
db.Column('id',db.Integer,primary_key=True),
db.Column('teacher_id',db.Integer,db.ForeignKey('Teacher.id')),
db.Column('student_id',db.Integer,db.ForeignKey('Student.id')))

class College(db.Model):
__tablename__='College'
id=db.Column(db.Integer,primary_key=True)
name=db.Column(db.String(30),unique=True,nullable=False)
departments=db.relationship('Department',backref='college',lazy=True)
students=db.relationship('Student',backref='student',lazy=True)
teachers=db.relationship('Teacher',backref='teacher',lazy=True)

def __repr__(self):
    return f"College(ID:{self.id}\nName:{self.name})"

class Department(db.Model):
__tablename__='Department'
id=db.Column(db.Integer,primary_key=True)
name=db.Column(db.String(30),nullable=False)
college=db.Column(db.Integer,db.ForeignKey('college.id'),nullable=False)
students=db.relationship('Student',backref='student',lazy=True)
teachers=db.relationship('Teacher',backref='teacher',lazy=True)

def __repr__(self):
    return f"Department(ID:{self.id}\nName:{self.name}\nCollege:{self.college})"

class Teacher(db.Model):
__tablename__='Teacher'
id=db.Column(db.Integer,primary_key=True)
name=db.Column(db.String(30),nullable=False)
username=db.Column(db.Integer,nullable=False,unique=True)
address=db.Column(db.String(80),nullable=False)
phone=db.Column(db.String(13),nullable=False)
students=db.relationship('Student',secondary=AssociationTable,backref='Teacher')
section=db.relationship('Student',backref='section',lazy=True)
department=db.Column(db.Integer,db.ForeignKey('department.id'),nullable=False)
college=db.Column(db.Integer,db.ForeignKey('college.id'),nullable=False)

def __repr__(self):
    return f"Teacher(ID:{self.id}\nName:{self.name}\nUsername:{self.username}\nAddress:{self.address}\nPhone:{self.phone},Section:{self.section}\nCollege:{self.college}\nDepartment:{self.department})"

class Student(db.Model):
__tablename__='Student'
id=db.Column(db.Integer,primary_key=True)
name=db.Column(db.String(30),nullable=False)
username=db.Column(db.Integer,nullable=False,unique=True)
address=db.Column(db.String(80),nullable=False)
phone=db.Column(db.String(13),nullable=False)
teachers=db.relationship('Teacher',secondary=AssociationTable,backref='Student')
section=db.Column(db.String(3),nullable=False)
department=db.Column(db.Integer,db.ForeignKey('department.id'),nullable=False)
college=db.Column(db.Integer,db.ForeignKey('college.id'),nullable=False)

def __repr__(self):
    return f"Student(ID:{self.id}\nName:{self.name}\nUsername:{self.username}\nAddress:{self.address}\nPhone:{self.phone},Section:{self.section}\nCollege:{self.college}\nDepartment:{self.department})"

The Error is :

 db.create_all()
Traceback (most recent call last):
  File "", line 1, in 
  File "C:\Program Files (x86)\Python36-32\lib\site-packages\flask_sqlalchemy\__init__.py", line 963, in create_all
    self._execute_for_all_tables(app, bind, 'create_all')
  File "C:\Program Files (x86)\Python36-32\lib\site-packages\flask_sqlalchemy\__init__.py", line 955, in _execute_for_all_tables
    op(bind=self.get_engine(app, bind), **extra)
  File "C:\Program Files (x86)\Python36-32\lib\site-packages\sqlalchemy\sql\schema.py", line 4005, in create_all
    tables=tables)
  File "C:\Program Files (x86)\Python36-32\lib\site-packages\sqlalchemy\engine\base.py", line 1940, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "C:\Program Files (x86)\Python36-32\lib\site-packages\sqlalchemy\engine\base.py", line 1549, in _run_visitor
    **kwargs).traverse_single(element)
  File "C:\Program Files (x86)\Python36-32\lib\site-packages\sqlalchemy\sql\visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "C:\Program Files (x86)\Python36-32\lib\site-packages\sqlalchemy\sql\ddl.py", line 736, in visit_metadata
    [t for t in tables if self._can_create_table(t)])
  File "C:\Program Files (x86)\Python36-32\lib\site-packages\sqlalchemy\sql\ddl.py", line 1095, in sort_tables_and_constraints
    dependent_on = fkc.referred_table
  File "C:\Program Files (x86)\Python36-32\lib\site-packages\sqlalchemy\sql\schema.py", line 3003, in referred_table
    return self.elements[0].column.table
  File "C:\Program Files (x86)\Python36-32\lib\site-packages\sqlalchemy\util\langhelpers.py", line 767, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "C:\Program Files (x86)\Python36-32\lib\site-packages\sqlalchemy\sql\schema.py", line 1892, in column
    tablekey)
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'AssociationTable.student_id' could not find table 'Student' with which to generate a foreign key to target column 'id'

Does the order matter? You are trying to set a foreign key before even adding the table — Chris Satchell
Chris , I tried Various Orders , It doesn't seem to work , Can You show me how ? — Tushar Guliany

Kyle Roux Kyle Roux · Accepted Answer · 2019-07-31T21:18:22

i would put the assocication_table defintion at the end of the file.

Creating Many to Many Relationship in Flask-SqlAlchemy

1 Answers