我们知道，<学生、课程、选课>，是一个典型的多对多关系。

现分别用 SQLAlchemy 与 fask-SQLAlchemy 实现。

声明：本人实测通过。

使用 SQLAlchemy

from sqlalchemy import Table, Column, Integer, String, ForeignKey

from sqlalchemy.orm import relationship, backref

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# 下表是用于关系的辅助表。对于这个辅助表， 强烈建议 不 使用模型，而是采用一个实际的表

# 此说法来源于：https://segmentfault.com/q/1010000003769460

# 选课表

sc = Table('sc', Base.metadata,

    Column('sno', String(10), ForeignKey('student.sno')),

    Column('cno', String(10), ForeignKey('course.cno'))

    )

# 学生表

class Student(Base):

    __tablename__ = 'student'

    sno = Column(String(10), primary_key=True)

    sname = Column(String(10))

    courses = relationship('Course',

         secondary=sc,

         backref=backref('student',lazy='dynamic'),

         lazy='dynamic'

         )

    def __repr__(self):

        return "<Student(sno='%s', sname='%s')>" % (self.sno, self.sname)

# 课程表

class Course(Base):

    __tablename__ = 'course'

    cno = Column(String(10), primary_key=True)

    cname = Column(String(10), index=True)

    students = relationship('Student',

         secondary=sc,

         backref=backref('course',lazy='dynamic'),

         lazy='dynamic'

         )

    def __repr__(self):

        return "<Course(cno='%s', cname='%s')>" % (self.cno, self.cname)

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

DB_CONNECT_STRING = 'sqlite://' # 'sqlite:///:memory:'

engine = create_engine(DB_CONNECT_STRING, echo=False)

DB_Session = sessionmaker(bind=engine)

session = DB_Session()

# 1. 创建表（如果表已经存在，则不会创建）

Base.metadata.create_all(engine)

# 2. 插入数据

# 不能这样：Student('201701', '张三')

some_students = [Student(sno='201701', sname='张三'),

                 Student(sno='201702', sname='李四'),

                 Student(sno='201703', sname='王五'),

                 Student(sno='201704', sname='赵六')]

session.add_all(some_students)

some_courses = [Course(cno='#1', cname='C'),

                Course(cno='#2', cname='C++'),

                Course(cno='#3', cname='Java'),

                Course(cno='#4', cname='Python')]

session.add_all(some_courses)

session.execute(sc.insert().values(sno='201701', cno='#1'))

session.execute(sc.insert().values(sno='201701', cno='#4'))

session.execute(sc.insert().values(sno='201702', cno='#2'))

session.execute(sc.insert().values(sno='201703', cno='#3'))

session.execute(sc.insert().values(sno='201704', cno='#4'))

session.commit()

#查询

student = session.query(Student).filter_by(sname='张三').one()

courses  = student.course.all()  #该学生选择的所有课程

print(courses)

course = session.query(Course).filter_by(cname='Python').one()

students  = course.student.all()  #选择该课程的所有学生

print(students)

使用 flask-SQLAlchemy

from flask import Flask

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://'

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db = SQLAlchemy(app)

# 学生-课程表（用于关系的辅助表。对于这个辅助表， 强烈建议 不 使用模型，而是采用一个实际的表）

sc = db.Table('sc',

    db.Column('sno', db.String(10), db.ForeignKey('student.sno')),

    db.Column('cno', db.String(10), db.ForeignKey('course.cno'))

    )

# 学生表

class Student(db.Model):

    __tablename__ = 'student'

    sno = db.Column(db.String(10), primary_key=True)

    sname = db.Column(db.String(10))

    courses = db.relationship('Course',

         secondary=sc,

         backref=db.backref('student',lazy='dynamic'),

         lazy='dynamic'

         )

    def __init__(self, sno, sname):

        self.sno = sno

        self.sname = sname

    def __repr__(self):

        return "<Student(sno='%s', sname='%s')>" % (self.sno, self.sname)

# 课程表

class Course(db.Model):

    __tablename__ = 'course'

    cno = db.Column(db.String(10), primary_key=True)

    cname = db.Column(db.String(10), index=True)

    students = db.relationship('Student',

         secondary=sc,

         backref=db.backref('course',lazy='dynamic'),

         lazy='dynamic'

         )

    def __init__(self, cno, cname):

        self.cno = cno

        self.cname = cname

    def __repr__(self):

        return "<Course(cno='%s', cname='%s')>" % (self.cno, self.cname)

# 1. 创建表（如果表已经存在，则不会创建）

db.create_all()

# 2. 插入数据

some_students = [Student('201701', '张三'),

                 Student('201702', '李四'),

                 Student('201703', '王五'),

                 Student('201704', '赵六')]

db.session.add_all(some_students)

some_courses = [Course('#1', 'C'),

                Course('#2', 'C++'),

                Course('#3', 'Java'),

                Course('#4', 'Python')]

db.session.add_all(some_courses)

#scs = [sc(201701, 1), # 报错："Table" object is not callable

#       sc(201701, 4),

#       sc(201702, 2),

#       sc(201703, 3),

#       sc(201704, 4)]

#db.session.add_all(scs)

# 改正如下

db.session.execute(sc.insert().values(sno='201701', cno='#1'))

db.session.execute(sc.insert().values(sno='201701', cno='#4'))

db.session.execute(sc.insert().values(sno='201702', cno='#2'))

db.session.execute(sc.insert().values(sno='201703', cno='#3'))

db.session.execute(sc.insert().values(sno='201704', cno='#4'))

db.session.commit()

#查询

student = Student.query.filter_by(sname='张三').one()

courses  = student.course.all()  #该学生选择的所有课程

print(courses)

course = Course.query.filter_by(cname='Python').one()

students  = course.student.all()  #选择该课程的所有学生

print(students)

感谢：

参考：https://segmentfault.com/q/1010000004567422