数据库模型关系

星哥玩云

发布于 2022-09-14 17:26:08

2.7K0

发布于 2022-09-14 17:26:08

文章被收录于专栏：开源部署

一、概述

主表没有声明关系的表为主表
从表声明关系的表为从表
对应关系
- 1:1 一对一
- 1:N 一对多
- M:N 多对多
一对一以及一对多共同属性on_delete
- 作用控制删除
- 值
  - models.CASCADE 默认值当主表的数据删除则从表数据默认删除
  - models.PROTECT 保护模式删除主表数据，如果从表中有关联的数据则无法实现删除删除从表数据，主表数据不动
  - models.SET_NULL 空值模式当主表数据被删除则从表外的字段的值设置为null 一定将这个字段设置为null=True
  - models.SET_DEFAULT 默认值模式

二、一对一

说明

使用OneToOneField创建1对1的模型关系将要创建对应关系的模型添加OneToOneField

使用场景

表的字段太多，需要拆分

关系的位置

哪张表都可以

创建模型 User和IdCard

创建模型 User

<span class="hljs-comment">#创建用户表</span>
<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">User</span><span class="hljs-params">(models.Model)</span>:</span>
    username = models.CharField(max_length=<span class="hljs-number">20</span>,db_index=<span class="hljs-keyword">True</span>,null=<span class="hljs-keyword">True</span>)
    sex = models.BooleanField(default=<span class="hljs-keyword">True</span>)
    age = models.IntegerField(default=<span class="hljs-number">20</span>)
    info = models.CharField(max_length=<span class="hljs-number">100</span>,default=<span class="hljs-string">'info'</span>)
    icon = models.CharField(max_length=<span class="hljs-number">60</span>,default=<span class="hljs-string">'default.jpg'</span>)
    isDelete = models.BooleanField(default=<span class="hljs-keyword">False</span>)
    createTime = models.DateTimeField(auto_now_add=<span class="hljs-keyword">True</span>)
    <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Meta</span>:</span>
        db_table = <span class="hljs-string">'users'</span>

创建模型IdCard 并添加模型一对一关系 OneToOneField

<span class="hljs-comment">#1对1的表关系</span>
<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">IdCard</span><span class="hljs-params">(models.Model)</span>:</span>
    num = models.CharField(max_length=<span class="hljs-number">18</span>)
    name = models.CharField(max_length=<span class="hljs-number">8</span>)
    sex = models.BooleanField(default=<span class="hljs-keyword">True</span>)
    birth = models.DateTimeField(auto_now_add=<span class="hljs-keyword">True</span>)
    address = models.CharField(max_length=<span class="hljs-number">100</span>,default=<span class="hljs-string">'地址'</span>)
    <span class="hljs-comment"># user = models.OneToOneField(User) #1对1的外键 默认删除</span>
    <span class="hljs-comment"># 保护模式 如果删除主表中与从表对应关系的数据 则不能删除</span>
    <span class="hljs-comment"># user = models.OneToOneField(User,on_delete=models.PROTECT)</span>
    <span class="hljs-comment"># 保护模式</span>
    user = models.OneToOneField(User,on_delete=models.SET_NULL,null=<span class="hljs-keyword">True</span>) 
    <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Meta</span>:</span>
        db_table = <span class="hljs-string">'idcards'</span>

一对一数据添加

说明：先添加主表数据通过主表数据关联添加从表数据

添加主表User数据

<span class="hljs-comment">#添加用户信息</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">adduser</span><span class="hljs-params">(req)</span>:</span>
    u = User.addUser(<span class="hljs-string">'lucky'</span>)
    u.save()
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'添加用户信息'</span>)

添加从表IdCard数据

<span class="hljs-comment">#添加1对1的idcard数据</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">addIdCard</span><span class="hljs-params">(req)</span>:</span>
 		<span class="hljs-keyword">import</span> random
    u = User.objects.first()
    idcard = IdCard(num=random.randrange(<span class="hljs-number">1000000000</span>,<span class="hljs-number">10000000000</span>),name=<span class="hljs-string">'lucky'</span>,user=u)
    idcard.save()
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'添加卡的信息'</span>)

一对一数据查询–主获取从

说明：关系是隐性属性

格式：主表对象.从表模型类名小写.从表属性

需求：通过一用户，获取他的身份信息

<span class="hljs-comment">#数据查询</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">showOneToOne</span><span class="hljs-params">(req)</span>:</span>
    u = User.objects.first()
    print(u.idcard) <span class="hljs-comment">#idcard关联的数据</span>
    print(u.idcard.num) <span class="hljs-comment">#取出idcard对象的num属性</span>
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'一对一数据的查询'</span>)

一对一数据查询–从获取主

说明：关系是直接声明的，它是一个显性的属性

格式：从表对象.外键属性.主表属性

需求：通过身份证信息查询用户的信息

<span class="hljs-comment"># 数据查询</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">showOneToOne</span><span class="hljs-params">(req)</span>:</span>
  idcard = IdCard.objects.first()
  print(idcard.user)  <span class="hljs-comment">#查询卡对应用户的对象</span>
  print(idcard.user.icon) <span class="hljs-comment">#查询卡对应用户的对象的icon属性</span>
 <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'一对一数据的查询'</span>)

一对一数据的删除

说明：删除主表和从表查看关系变化以及更改on_delete属性

删除主表数据

<span class="hljs-comment">#数据的删除</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">deleteOneToOne</span><span class="hljs-params">(req)</span>:</span>
    <span class="hljs-comment">#删除主表数据   默认从表数据随着主表数据而删除</span>
    u = User.objects.first()
    u.delete()
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'删除了第一个主表的数据'</span>)

删除从表数据

<span class="hljs-comment">#数据的删除</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">deleteOneToOne</span><span class="hljs-params">(req)</span>:</span>
    <span class="hljs-comment">#删除从表数据  主表数据 没有改变</span>
    idcard = IdCard.objects.first()
    idcard.delete()
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'删除了第一条从表的数据'</span>)

三、一对多

说明

使用ForeignKey创建一对多的模型关系将要创建对应关系的模型添加ForeignKey

关系的位置

写在多的那一端

创建模型 grade和students

<span class="hljs-comment">#班级表</span>
<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Grade</span><span class="hljs-params">(models.Model)</span>:</span>
    gname = models.CharField(max_length=<span class="hljs-number">15</span>)
    gnum = models.IntegerField()
    ggirlnum = models.IntegerField()
    gboynum = models.IntegerField()
    <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Meta</span>:</span>
        db_table = <span class="hljs-string">'grades'</span>

<span class="hljs-comment">#学生表</span>
<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Students</span><span class="hljs-params">(models.Model)</span>:</span>
    sname = models.CharField(max_length=<span class="hljs-number">10</span>)
    ssex = models.BooleanField(default=<span class="hljs-keyword">True</span>)
    sage = models.IntegerField(default=<span class="hljs-number">20</span>)
    sgrade = models.ForeignKey(Grade, on_delete=models.CASCADE) <span class="hljs-comment">#1对多的外键 默认删除</span>
    <span class="hljs-comment"># sgrade = models.ForeignKey(Grade,on_delete=models.PROTECT) #保护模式 如果删除主表中与从表对应关系的数据 则不能删除</span>
    <span class="hljs-comment"># sgrade = models.ForeignKey(Grade,on_delete=models.SET_NULL, null=True)  # 保护模式</span>
    <span class="hljs-comment"># sgrade = models.ForeignKey(Grade, on_delete=models.SET_DEFAULT, default=1)  # 设置默认值</span>
    <span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">__str__</span><span class="hljs-params">(self)</span>:</span>
        <span class="hljs-keyword">return</span> self.sname
    <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Meta</span>:</span>
        db_table = <span class="hljs-string">'students'</span>

一对多数据添加

说明：先添加主表数据通过主表数据关联添加从表数据

添加班级数据

<span class="hljs-comment">#添加grade信息</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">addGrade</span><span class="hljs-params">(req)</span>:</span>  				   Grade(gname=<span class="hljs-string">'python1807'</span>,gnum=<span class="hljs-number">63</span>,ggirlnum=<span class="hljs-number">6</span>,gboynum=<span class="hljs-number">57</span>).save()   Grade(gname=<span class="hljs-string">'python1808'</span>,gnum=<span class="hljs-number">50</span>,ggirlnum=<span class="hljs-number">2</span>,gboynum=<span class="hljs-number">48</span>).save()
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'添加grade信息'</span>)

给班级添加对应学生数据

<span class="hljs-comment">#添加students信息</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">addStudents</span><span class="hljs-params">(req)</span>:</span>
    Students(sname=<span class="hljs-string">'孙悟空'</span>,sgrade=Grade.objects.get(pk=<span class="hljs-number">1</span>)).save()
    Students(sname=<span class="hljs-string">'猪八戒'</span>,sgrade=Grade.objects.get(pk=<span class="hljs-number">1</span>)).save()
    Students(sname=<span class="hljs-string">'沙僧'</span>,sgrade=Grade.objects.get(pk=<span class="hljs-number">2</span>)).save()
    Students(sname=<span class="hljs-string">'唐僧'</span>,sgrade=Grade.objects.get(pk=<span class="hljs-number">2</span>)).save()
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'添加students信息'</span>)

一对多数据查询–主获取从

说明：关系是隐性属性

格式：主表对象.从表模型类名小写_set.过滤器

需求：已知一个班级，获取该班级中的所有学生

<span class="hljs-comment">#1对多查询</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">oneToManyShow</span><span class="hljs-params">(req)</span>:</span>
    g = Grade.objects.get(pk=<span class="hljs-number">1</span>)
    s = g.students_set.all()
    <span class="hljs-keyword">for</span> i <span class="hljs-keyword">in</span> s:
        print(i)
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'查询一对多'</span>)

一对多数据查询–从获取主

说明：关系是直接声明的，它是一个显性的属性

格式：从表对象.外键属性.主表属性

需求：已知一个学生，获取该学生所在班级的信息

<span class="hljs-comment"># 一对多查询</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">oneToManyShow</span><span class="hljs-params">(req)</span>:</span>
  s = Students.objects.get(pk=<span class="hljs-number">1</span>)
  s = s.grade.name  
  <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'查询一对多'</span>)

一对多数据删除

删除主表数据 (查看从表数据变化)

<span class="hljs-comment">#删除主表数据  默认还是 主表数据删除 从表对应数据也被删除</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">deleteGrade</span><span class="hljs-params">(req)</span>:</span>
    g = Grade.objects.first()
    name = g.gname
    g.delete()
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'删除主表数据{}'</span>.format(name))

删除从表数据 (查看主表数据变化)

<span class="hljs-comment">#删除主表数据  默认还是 主表数据删除 从表对应数据也被删除</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">deleteStudents</span><span class="hljs-params">(req)</span>:</span>
    s = Students.objects.first()
    name = s.gname
    s.delete()
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'删除从表数据{}'</span>.format(name))

四、多对多

说明

使用ManyToManyField创建多对多的模型关系将要创建对应关系的模型添加ManyToManyField

原理

底层是通过两个外键实现，单独有一张表来管理外键，自动生成

关系的位置

哪张表都可以

创建模型 User和Posts

<span class="hljs-comment">#创建用户表</span>
<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">User</span><span class="hljs-params">(models.Model)</span>:</span>
    username = models.CharField(max_length=<span class="hljs-number">20</span>,db_index=<span class="hljs-keyword">True</span>,null=<span class="hljs-keyword">True</span>)
    sex = models.BooleanField(default=<span class="hljs-keyword">True</span>)
    age = models.IntegerField(default=<span class="hljs-number">20</span>)
    info = models.CharField(max_length=<span class="hljs-number">100</span>,default=<span class="hljs-string">'info'</span>)
    icon = models.CharField(max_length=<span class="hljs-number">60</span>,default=<span class="hljs-string">'default.jpg'</span>)
    isDelete = models.BooleanField(default=<span class="hljs-keyword">False</span>)
    createTime = models.DateTimeField(auto_now_add=<span class="hljs-keyword">True</span>)
    <span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">__str__</span><span class="hljs-params">(self)</span>:</span>
        <span class="hljs-keyword">return</span> self.username
    <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Meta</span>:</span>
        db_table = <span class="hljs-string">'user'</span>

<span class="hljs-comment">#以下为多对多  posts关联用户</span>
<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Posts</span><span class="hljs-params">(models.Model)</span>:</span>
    title = models.CharField(max_length=<span class="hljs-number">20</span>,default=<span class="hljs-string">'标题'</span>)
    article = models.CharField(max_length=<span class="hljs-number">200</span>,default=<span class="hljs-string">'article'</span>)
    createtime = models.DateTimeField(auto_now=<span class="hljs-keyword">True</span>)
    users = models.ManyToManyField(User)
    <span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">__str__</span><span class="hljs-params">(self)</span>:</span>
        <span class="hljs-keyword">return</span> self.title
    <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Meta</span>:</span>
        db_table = <span class="hljs-string">'posts'</span>

多对多添加数据

User与Posts模型数据正常添加

<span class="hljs-comment">#添加用户信息</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">adduser</span><span class="hljs-params">(req)</span>:</span>
    u = User.addUser(<span class="hljs-string">'lucky'</span>)
    u.save()
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'添加用户信息'</span>)
  
<span class="hljs-comment">#添加posts信息</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">addPosts</span><span class="hljs-params">(req)</span>:</span>
    Posts(title=<span class="hljs-string">'以后的你感谢现在拼搏的自己'</span>).save()
    Posts(title=<span class="hljs-string">'阿甘正传'</span>).save()
    Posts(title=<span class="hljs-string">'斗罗大陆'</span>).save()
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'添加Posts信息'</span>)

添加一个多对多的数据 add

<span class="hljs-comment">#添加收藏</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">addColl</span><span class="hljs-params">(req)</span>:</span>
    u1 = User.objects.first()
    p1 = Posts.objects.first()
    p1.users.add(u1)
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'添加多对多的数据'</span>)

添加多个多对多的数据 add

<span class="hljs-comment">#添加收藏</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">addColl</span><span class="hljs-params">(req)</span>:</span>
    u1 = User.objects.first()
    u2 = User.objects.last()
    p = Posts.objects.get(pk=<span class="hljs-number">2</span>)
    p.users.add(u1,u2) <span class="hljs-comment">#添加多个对象</span>
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'添加多对多的数据'</span>)

多对多数据查询–主获取从

说明：关系是隐性属性

格式：主表对象.从表模型类名小写_set.过滤器

需求：查询用户收藏哪些帖子

<span class="hljs-comment">#查询 用户收藏了哪些帖子</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">showPosts</span><span class="hljs-params">(req)</span>:</span>
    u = User.objects.first()
    data = u.posts_set.all()
    <span class="hljs-keyword">for</span> i <span class="hljs-keyword">in</span> data:
        print(i)
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'查询收藏了哪些帖子'</span>)

多对多数据查询从获取主

说明：关系是直接声明的，它是一个显性的属性

格式：从表对象.外键属性.过滤器

需求：已知一个博客查询都被哪些用户收藏

<span class="hljs-comment">#查看帖子对应多的关系</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">showUsers</span><span class="hljs-params">(req)</span>:</span>
    p1 = Posts.objects.first()
    data = p1.users.all()
    <span class="hljs-keyword">for</span> i <span class="hljs-keyword">in</span> data:
        print(i)
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'查询帖子被哪些用户收藏了'</span>)

多对多数据删除

删除一条数据

<span class="hljs-comment">#删除</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">deleteData</span><span class="hljs-params">(req)</span>:</span>
    p1 = Posts.objects.first()
    u1 = User.objects.first()
    p1.users.remove(u1)
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'1号用户取消收藏1号帖子'</span>)

删除多条数据

<span class="hljs-comment">#删除</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">deleteData</span><span class="hljs-params">(req)</span>:</span>
    p2 = Posts.objects.get(pk=<span class="hljs-number">1</span>)
    u1 = User.objects.first()
    u2 = User.objects.get(pk=<span class="hljs-number">2</span>)
    p2.users.remove(u1,u2)
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'1、2号用户取消收藏1号帖子'</span>)

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

add