SQLAlchemy之orm多表关系操作

发表于 更新于 分类于 ， Changyan： 本文字数： 1.1k 阅读时长 ≈ 4 分钟

之前SQLAlchemy介绍及使用中介绍了下SQLAlchemy的基本操作，本文继续进阶介绍多表操作中，orm关系模型如何处理。

这里我们来举例说明一对多和多对多的操作(一对一可以划入多对一故不再讲)。

环境准备

数据库连接引擎

文件名为alchemy_base.py

1
2
3
4
5
6
7
8
9
# 导入数据库基础模型
from sqlalchemy.ext.declarative import declarative_base
# 导入数据库连接引擎
from sqlalchemy import create_engine

# 实例化数据库模型基类
Base = declarative_base()
# 创建数据库连接引擎
conn = create_engine("mysql+pymysql://root:@127.0.0.1:3306/sqlalchemysss?charset=utf8")

数据表模型

文件名为：alchemy_create_table.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# 导入数据库列、类型
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship

# 导入数据库模型基类和数据库连接引擎
from alchemy_base import Base, conn

# 创建数据表

# 学员表
class Students(Base):
    __tablename__ = 'students'
    id = Column(Integer, primary_key=True, comment="学生主键")
    name = Column(String(32), comment='学生姓名')
    # 城市id
    city_id = Column(Integer, ForeignKey('citys.id'), comment='城市id')
    # 关联城市，一对多
    student2city = relationship('Citys', backref = 'city2student')
    # 关联教师，多对多
    student2teacher = relationship('Teachers', secondary="studentTeacher", backref = "teacher2student")
# 教师表
class Teachers(Base):
    __tablename__ = 'teachers'
    id = Column(Integer, primary_key=True, comment="学生主键")
    name = Column(String(32), comment='学生姓名')

# 城市表
class Citys(Base):
    __tablename__ = 'citys'
    id = Column(Integer, primary_key=True, comment="学生教师关系主键")
    name = Column(String(32), comment='学生姓名')

# 学生城市表
class StudentTeacher(Base):
    __tablename__ = 'studentTeacher'
    id = Column(Integer, primary_key=True, comment="学生主键")
    # 学生id
    student_id = Column(Integer, ForeignKey('students.id'), comment='学生id')
    # 教师id
    teacher_id = Column(Integer, ForeignKey('teachers.id'), comment='教师id')

# 检索所有继承Base的模型对象，并在数据库中创建所有数据表
Base.metadata.create_all(conn)

增加数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
# 导入会话
from sqlalchemy.orm import sessionmaker

# 导入数据库连接引擎
from alchemy_base import conn
# 导入数据表模型
from alchemy_create_table import Students, Teachers, Citys


# 创建会话
Sessions = sessionmaker(conn)
# 开启会话
db_session = Sessions()

# 一对多
# 正向添加
# stu_obj = Students(name="张学友", student2city=Citys(name="香港"))
# res = db_session.add(stu_obj)
# print(res)

# 反向添加
# city_obj = Citys(name='台湾', city2student=[Students(name="陈奕迅"), Students(name="周杰伦")])
# res = db_session.add(city_obj)
# print(res)

# 多对多

# 正向添加
# stu_obj = Students(name='郭富城', student2teacher=[Teachers(name="孙悟空"), Teachers(name="唐僧")])
# res = db_session.add(stu_obj)
# print(res)

# 反向添加
teacher_obj = Teachers(name="猪八戒", teacher2student=[Students(name="peppa"), Students(name="乔治")])
res = db_session.add(teacher_obj)
print(res)

# 提交会话
db_session.commit()
# 关闭会话
db_session.close()

查询数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
# 导入会话
from sqlalchemy.orm import sessionmaker

# 导入数据库连接引擎
from alchemy_base import conn
# 导入数据表模型
from alchemy_create_table import Students, Teachers, Citys


# 创建会话
Sessions = sessionmaker(conn)
# 开启会话
db_session = Sessions()

# 一对多
# 正向查询
# 查询id为2的学生姓名及所在城市
# stu_obj = db_session.query(Students).filter(Students.id==2).first()
# print(stu_obj.name, stu_obj.student2city.name)

# 反向查询
# 查询所在城市为台湾的所有学生姓名
# city_obj = db_session.query(Citys).filter(Citys.name=="台湾").first()
# print(city_obj.name)
# for stu_obj in city_obj.city2student:
#     print(stu_obj.name)

# 多对多

# 正向查询
# 查询学生姓名为郭富城的所有老师姓名
# stu_obj = db_session.query(Students).filter(Students.name=="郭富城").first()
# print(stu_obj.name)
# # 查询学生对应的老师
# for teacher_obj in stu_obj.student2teacher:
#     print(teacher_obj.name)

# 反向查询
# 查询猪八戒的所有学生
teacher_obj = db_session.query(Teachers).filter(Teachers.name=="猪八戒").first()
print(teacher_obj.name)
# 查询对应的学生
for stu_obj in teacher_obj.teacher2student:
    print(stu_obj.name)
# 关闭会话
db_session.close()

修改数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# 导入会话
from sqlalchemy.orm import sessionmaker

# 导入数据库连接引擎
from alchemy_base import conn
# 导入数据表模型
from alchemy_create_table import Students, Teachers, Citys


# 创建会话
Sessions = sessionmaker(conn)
# 开启会话
db_session = Sessions()

# 一对多
# 正向更新
# 将张学友城市姓名都修改为魔都
# stu_obj = db_session.query(Students).filter(Students.name=="张学友").first()
# res = db_session.query(Citys).filter(Citys.id == stu_obj.city_id).update({"name":'魔都'})
# print(res)

# 反向更新
# 将所在城市为台湾的所有学生姓名改为蔡蔡
# city_obj = db_session.query(Citys).filter(Citys.name=="台湾").first()
# res = db_session.query(Students).filter(Students.city_id==city_obj.id).update({"name":"蔡蔡"})
# print(res)

# 提交会话
db_session.commit()
# 关闭会话
db_session.close()

删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 导入会话
from sqlalchemy.orm import sessionmaker

# 导入数据库连接引擎
from alchemy_base import conn
# 导入数据表模型
from alchemy_create_table import Students, Teachers, Citys


# 创建会话
Sessions = sessionmaker(conn)
# 开启会话
db_session = Sessions()

# 一对多

# 将所在城市为台湾的所有学生删除
# city_obj = db_session.query(Citys).filter(Citys.name=="台湾").first()
# res = db_session.query(Students).filter(Students.city_id==city_obj.id).delete()
# print(res)

# 多对多
# 删除郭富城姓名为唐僧的老师(仅删除关联关系，不删除教师表中的该条数据)
# stu_obj = db_session.query(Students).filter(Students.name=="郭富城").first()
# teacher_obj = db_session.query(Teachers).filter(Teachers.name=="唐僧").first()
# res = stu_obj.student2teacher.remove(teacher_obj)
# print(res)

# 提交会话
db_session.commit()
# 关闭会话
db_session.close()