我们知道，<学生、课程、选课>，是一个典型的多对多关系。
现分别用 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)
