SQLAlchemy 与 fask-SQLAlchemy 中的多表查询例子

2017-10-17 1043

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介：

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