我们知道，<学生、课程、选课>，是一个典型的多对多关系。
现分别用 SQLAlchemy 与 fask-SQLAlchemy 实现。声明：本人实测通过。
使用 SQLAlchemy
from sqlalchemy import Table, Column, Integer, String, ForeignKeyfrom sqlalchemy.orm import relationship, backreffrom sqlalchemy.ext.declarative import declarative_baseBase = 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 " " % (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 " " % (self.cno, self.cname) from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerDB_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 Flaskfrom flask_sqlalchemy import SQLAlchemyapp = Flask(__name__)app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://'app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = Truedb = 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 " " % (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 " " % (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) 感谢：
参考：