Skip to content

多表关系 - 多对多

简介

多对多是数据库关系中的一种描述，表示多个数据行可以与多个其他数据行相关联。在关系型数据库中，多对多关系通常需要中间表来实现。

多对多关系的一个常见例子是学生和老师之间的关系。一个学生可以有多位老师，而一位老师也可以教多名学生。这种关系可以用多对多关系来描述，其中中间表（通常称为关联表或交叉表）用于跟踪学生和老师之间的关联。

uml diagram

多对多使用场景

在数据库中，多对多关系通常需要三个表：两个主要表和一个中间表。中间表包含两个主要表的外键，用于建立它们之间的关联。这种关系允许灵活地管理数据，同时保持数据的一致性。

总之，多对多关系适用于许多不同的情况，其中多个数据行需要与多个其他数据行相关联，同时保持数据的一致性和灵活性。

定义使用多对多关系

建立多对多关系

关系数据库系统通常不允许在两个表之间实施直接的多对多关系。为避免这一问题，可以通过使用称为联接表的第三个表，将多对多关系拆分为两个一对多关系。

联接表中的每个记录都包含一个匹配字段，该字段包含其联接的两个表的主键值。（在联接表中，这些匹配字段是外键。）当从联接表联接的任何一个表创建联接表的记录时，会为这些外键字段填充数据。

以下是定义多对多关系的步骤。

  1. 定义三张表。
  2. 需要创建一个中间表（关系表）。
  3. 使用 relationship() 方法关联两张表。
# 创建一个中间表
teacher_student_rel = Table(
    # 中间表的名字，最好能体现哪两张表
    "teacher_student",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    # 其中一张表的描述，作为一个外键，指向 teacher 表的id
    Column("teacher_id", Integer, ForeignKey("teacher.id")),
    # 其中一张表的描述，作为一个外键，指向 student 表的id
    Column("student_id", Integer, ForeignKey("student.id"))
)


# 创建老师的映射
class Teacher(Base):

    __tablename__ = "teacher"

    id = Column(Integer, primary_key=True)
    # name 老师的姓名
    # nullable=False 不为空，unique=True 唯一约束，不允许重复
    name = Column(String(100), nullable=False, unique=True)

    # 参数一：指向另一张表，另外一张表的类名
    # 参数二：secondary ，指向中间表的变量名
    # 参数三：反向指向，backref  当前表的别名
    students = relationship(
        "Student",
        secondary=teacher_student_rel,
        backref="teachers"
    )

    def __repr__(self):
        return f"<Teacher id={self.id} teacher_name={self.name}>"


# 创建学生的映射
class Student(Base):

    __tablename__ = "student"

    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False, unique=True)

    def __repr__(self):
        return f"<Student id='{self.id}' name='{self.name}'>"

relationship() 解析

relationship() 方法可以建立两表之间的映射关系，方便访问。

参数一：关联另一张表，另外一张表的类名。

参数二：secondary 指向中间表的变量名。

uml diagram

  • 参数三：backref 反向引用。

uml diagram

创建表

首先，需要创建数据库连接，然后在数据库中创建表。

# 创建数据库连接
engine = create_engine('sqlite:///school.db')
DBSession = sessionmaker(bind=engine)
db_session: Session = DBSession()

if __name__ == '__main__':
    # 创建表
    Base.metadata.create_all(engine)
    # 删除表
    # Base.metadata.drop_all(engine)

添加数据

  • 创建学生数据。
  • 创建老师数据。
  • 创建关联数据。
# 添加三个学生，三个老师
stu1 = Student(name="学生1")
stu2 = Student(name="学生2")
stu3 = Student(name="学生3")

tea1 = Teacher(name="老师1")
tea2 = Teacher(name="老师2")
tea3 = Teacher(name="老师3")

db_session.add_all([stu1, stu2, stu3, tea1, tea2, tea3])

# 建立关联关系
# 老师1 --关联-- 学生1，学生2
# 老师2 --关联-- 学生2，学生3
tea1.students = [stu1, stu2]
tea2.students = [stu2, stu3]

# 提交操作
db_session.commit()
# 关闭连接
db_session.close()

查询数据

  • 查询学生对应的老师。
  • 查询老师对应的学生。
# 查询学生id=1 对应的老师
stu1 = db_session.query(Student).filter_by(id=1).first()
print(stu1.teachers[0].name)

# 查询老师对应的学生
tea1 = db_session.query(Teacher).filter_by(id=1).first()
print(tea1.students[1].name)

更新数据

  • 查询学生 id=1，修改对应的老师姓名。
  • 查询老师 id=1，修改学生的姓名。
# 修改学生 id=1 的老师姓名
stu1 = db_session.query(Student).filter_by(id=1).first()
stu1.teachers[0].name = "老师1修改1"
db_session.commit()
db_session.close()

# 修改老师 id=1 对应的学生姓名
tea1 = db_session.query(Teacher).filter_by(id=1).first()
tea1.students[0].name = "学生1修改1"

# 提交操作
db_session.commit()
# 关闭连接
db_session.close()

删除数据

  • 删除学生。
  • 删除老师。
  • 自动删除关联关系。
# 删除学生
stu1 = db_session.query(Student).filter_by(id=1).first()
db_session.delete(stu1)
# 提交操作
db_session.commit()

# 删除老师
tea1 = db_session.query(Teacher).filter_by(id=1).first()
db_session.delete(tea1)

# 提交操作
db_session.commit()
# 关闭连接
db_session.close()

总结

在本章节中，介绍了多表关系中多对多关系的概念和相关操作。

  1. 多对多关系简介
  2. 多对多使用场景
  3. 建立多对多关系
  4. 创建表
  5. 添加数据
  6. 查询数据
  7. 更新数据
  8. 删除数据