在烧瓶中创建多对多关系

Question

我需要创建一个学生管理系统，它可以帮助多个老师教多个学生，多个学生可以有多个老师。现在我已经在下面创建了一个代码。另外，如果你认为有更好的方法来实现我的目标，请指导，我对多对多的关系是新的，互联网上有这么多方法，这只是让人困惑：

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})"

错误是：

 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'

Answer 1

我会把assocication_table定义放在文件的末尾。