Python学习笔记27-多对多关系_python: 多对多组合-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43593303/article/details/103626846

Many-to-many relationalships

之前我们接触的都是one-many的关系，从来没有接触过many-many的关系，一对多的关系如下图所示：
在这里插入图片描述
tracks（单曲）属于album，一个album可能会有多个tracks

知识点1 什么是多对多关系

在这里插入图片描述
如上图所示，一本书可能有多个作者，一个作者可能写了多本书，因此在建立关系的时候不能简单使用一个外键来表示，需要在中间建立一个Junction Table，将关系分解为两个一对多的组合。
构建的模型如下图所示：
在这里插入图片描述
从模型图中可以看出，将一个多对多的关系拆解为两个一对多的关系，需要注意的是：

在member当中，两个外键和其对应的表格中的id是一一对应的关系；
在member当中，是不存在主键的；
在member当中，可以加一些其他的参数，例如“role”等用于说明用户的属性（老师？学生？）或者课程的属性等等。

知识点2 为多对多数据模型建数据库

创建一个这样的SQLite数据库，对应于相对应的SQL代码，具体如下图所示：
在这里插入图片描述
需要再次强调的是，在连接表Member当中，user_id和course_id是独立的一对一的关系。

下一步内容就是插入数据，我们在这里插入课程和用户的数据，具体代码如下所示：

INSERT INTO User (name, email) VALUES ('Jane', 'jane@tsugi.org');
INSERT INTO User (name, email) VALUES ('Ed', 'ed@tsugi.org');
INSERT INTO User (name, email) VALUES ('Sue', 'sue@tsugi.org');

INSERT INTO Course (title) VALUES ('Python');
INSERT INTO Course (title) VALUES ('SQL');
INSERT INTO Course (title) VALUES ('PHP');

接下来插入中间连接表格member的相关数据，具体代码如下所示：

INSERT INTO Member (user_id, course_id, role) VALUES (1, 1, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 1, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (3, 1, 0);

INSERT INTO Member (user_id, course_id, role) VALUES (1, 2, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 2, 1);

INSERT INTO Member (user_id, course_id, role) VALUES (2, 3, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (3, 3, 0);

注意这里的一一对应的关系，给出了每一个用户，及其所选课程以及，用户属性。
那么截止到目前，一个完整的逻辑模型就已经构建出来了，如下图所示：
在这里插入图片描述
接下来从表格中抓取数据：

SELECT User.name, Member.role, Course.title
FROM User JOIN Member JOIN Course
ON Member.user_id = User.id AND 
Member.course_id = Course.id
ORDER BY Course.title, Member.role DESC, User.name

这里值得一提的是排序的方式，课程是第一优先级，用户角色倒序是第二优先级，用户名是第三优先级。
在这里插入图片描述
上图是一个项目实例中的数据模型，图中标绿的部分就是membership的连接表格。

在Work example当中，有以下的注意点：
在这里插入图片描述
这一段代码的含义是创建一个表格，在第一次运行的时候就进行了创建，但是要求我们每一次运行程序的时候都需要Fresh Start，所以顶部的Drop语句就是实现对应的功能的。

还有一个重要的知识点：
在这里插入图片描述
在这里是对数据库中某个子表的通用的处理方式，第一行和第二行是实现对数据插入的功能，同时也保证了数据只插入一次（INSERT OR IGNORE）
第三行和第四行是获取唯一的主键（id），由于主键是自动递增的独立的数字，fetchone()[0]显示的最上面的一个数字，因此，course_id是一个唯一的整数。