以下介绍SQLAlchemy的一对多查询，SQLAlchemy是Python的一个ORM框架，这个框架的进一步封装Flask-SQLAlchemy常用于Flask项目，作为Flask与数据库交互的工具。
from sqlalchemy import create_engine, Column, String, Integer, Boolean, ForeignKey
from sqlalchemy.orm import declarative_base, Session, relationship
DB_TYPE = 'mysql'
DB_DRIVE = 'pymysql'
USER = 'root'
PWD = 'Tomboy_study'
HOST = '192.168.93.130'
PORT = 3306
DB_NAME = 'study'
DB_URI = f'{DB_TYPE}+{DB_DRIVE}://{USER}:{PWD}@{HOST}:{PORT}/{DB_NAME}'
engine = create_engine(DB_URI)
Base = declarative_base()
class Classes(Base):
__tablename__ = "t_class"
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32), nullable=False, unique=True)
description = Column(String(256))
# student = relationship('Student')
def __repr__(self):
return f'<{self.id}, {self.name}, {self.description}>'
class Student(Base):
__tablename__ = 't_student'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(10), nullable=False)
age = Column(Integer)
sex = Column(Boolean)
# 1对多 ForeignKey的关键字要建立在 多一边
class_id = Column(Integer, ForeignKey('t_class.id'))
# 将主表的数据注入到这个字段
classes = relationship('Classes', backref='stu')
@property
def gender(self):
return "男" if self.sex == 1 else "女"
@gender.setter
def gender(self, val):
if val == '男':
return 1
else:
return 0
def to_dict(self):
return {
'id': self.id,
'name': self.name,
'age': self.age,
'gender': self.gender
}
def __repr__(self):
return f'<{self.id}, {self.name}, {self.age}, {self.gender}>'
def create_data():
stu1 = Student(name="Tomboy", age=27, sex=0, class_id=1)
stu2 = Student(name="幺叔", age=18, sex=1, class_id=1)
stu3 = Student(name="盼盼", age=19, sex=0, class_id=1)
clses = Classes(name="79班", description="难忘但并未奉献")
with Session(engine) as sess:
sess.add(clses)
sess.commit()
with Session(engine) as sess:
sess.add(stu1)
sess.add(stu2)
sess.add(stu3)
sess.commit()
def query_data1():
"""SQL思想 + ORM实现一对多，多对一查询"""
# 正向查找：根据班级查学生
with Session(engine) as sess:
classes = sess.query(Classes).filter(Classes.id == 1).first()
print(classes)
stu_list = sess.query(Student).filter(Student.class_id == classes.id).all()
for stu in stu_list:
print(stu)
print('\n' + "*" * 20 + "分割线" + "*" * 20 + '\n')
# 反向查找：根据学生查班级
with Session(engine) as sess:
stu = sess.query(Student).filter(Student.id == 1).first()
print(stu)
classes = sess.query(Classes).filter(Classes.id == stu.id).first()
print(classes)
def query_data2():
"""
ORM实现一对多、多对一查询：需要增加relationship字段
最简单的方式就是两边都添加relationship，因为两边都会用到
"""
# 正向查找：根据班级找学生
with Session(engine) as sess:
classes = sess.query(Classes).filter(Classes.id == 1).first()
print(classes)
stu_list = classes.student
for stu in stu_list:
print(stu)
print('\n' + "*" * 20 + "分割线" + "*" * 20 + '\n')
# 反向查找：根据学生查班级
with Session(engine) as sess:
stu = sess.query(Student).filter(Student.id == 1).first()
print(stu)
classes = stu.classes
print(classes)
def query_data3():
"""
query_data2有警告，所以我们一般不会在两方都加relationship
可以通过给多的一方添加relationship，然后在relationship里面增加属性backref来实现
没有relationship的一方通过backref指定的字段去访问
"""
# 正向查找：根据班级找学生
with Session(engine) as sess:
classes = sess.query(Classes).filter(Classes.id == 1).first()
print(classes)
stu_list = classes.stu
for stu in stu_list:
print(stu)
print('\n' + "*" * 20 + "分割线" + "*" * 20 + '\n')
# 反向查找：根据学生查班级
with Session(engine) as sess:
stu = sess.query(Student).filter(Student.id == 1).first()
print(stu)
classes = stu.classes
print(classes)
if __name__ == '__main__':
Base.metadata.create_all(engine)
# create_data()
# query_data1()
# query_data2()
query_data3()
