模型层-多表操作

多表操作

多表操作一般会涉及到数据库中常见的3种关系

• 一对一 OneToOne
• 多对多 ManyToMany
• 一对多 ForeignKey

接下来就是对初始的模型的准备

模型表创建

以图书管理系统为例, 可以很好的展现上面的三种关系.

from django.db import models

class Book(models.Model):
    name = models.CharField(max_length=32)
    price = models.FloatField()
    pub_time = models.DateField(auto_now_add=True)

    publish = models.ForeignKey(to='Publish')
    authors = models.ManyToManyField(to='Author')

    def __str__(self):
        return self.name

class Publish(models.Model):
    name = models.CharField(max_length=32)
    addr = models.CharField(max_length=32)
    pub_detail = models.OneToOneField(to='PublishDetail')

    def __str__(self):
        return self.name

class PublishDetail(models.Model):
    email = models.EmailField()

class Author(models.Model):
    name = models.CharField(max_length=32)
    phone = models.CharField(max_length=20)

    def __str__(self):
        return self.name

上述表关系的简单分析:

• 书籍与作者是多对多的关系, 这里选择让Django帮我们创建第三张关系表
• 书籍与出版社是一对多的关系, 外键需要建在多的一方
• 出版社与出版社详情是一对一的关系, 外键需要建在查询频率高的表中.
• 在Django2.0之前建立外键关系, 默认是级联删除与更新

利用Navicat的逆向数据库到模型表关系的功能, 看出有以下关系.

注意点:

• 表的名称myapp_modelName，是根据 模型中的元数据自动生成的，也可以覆写为别的名称　　
• 模型表id 字段是自动添加的
• 对于外键字段，Django 会在字段名上添加"_id" 来创建数据库中的列名
• Django 会根据settings 中指定的数据库类型来使用相应的SQL 语句。
• 定义好模型之后，需要告诉Django _使用_这些模型。就是修改配置文件中的INSTALL_APPS中设置，在其中添加models.py所在应用的名称。
• 外键字段 ForeignKey 有一个 null=True 的设置(它允许外键接受空值 NULL)，可以赋给它空值 None 。

添加数据

模型表已经创建好了, 需要为其快速添加数据

import os
import random

if __name__ == "__main__":
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "manytomany.settings")
    import django

    django.setup()

    from app01 import models

    # 由于有外键约束, 需要先添加出版社详细信息表, 只创建3个出版社
    emails = ['110@qq.com', '120@126.com', '130@163.com']
    models.PublishDetail.objects.bulk_create(
        [models.PublishDetail(email=email) for email in emails]
    )

    details = models.PublishDetail.objects.all()

    # 创建出版社, 对应于出版社详细信息
    infos = [
        {'name': '北方出版社', 'addr': '北方'},
        {'name': '南方出版社', 'addr': '南方'},
        {'name': '东方出版社', 'addr': '东方'},
    ]

    publishes = [models.Publish(pub_detail=detail, **info) for info, detail in zip(infos, details)]
    print(publishes)
    models.Publish.objects.bulk_create(publishes)

    publishes = list(models.Publish.objects.all())

    # 创建书籍
    infos = [
        {'name': '三国演义', 'price': 15.3, 'pub_time': '2018-01-03', 'publish': random.choice(publishes)},
        {'name': '水浒传', 'price': 24, 'pub_time': '2018-07-21', 'publish': random.choice(publishes)},
        {'name': '西游记', 'price': 17.5, 'pub_time': '2017-01-03', 'publish': random.choice(publishes)},
        {'name': '西厢记', 'price': 35.5, 'pub_time': '2019-09-15', 'publish': random.choice(publishes)},
        {'name': '红楼梦', 'price': 40, 'pub_time': '2018-06-03', 'publish': random.choice(publishes)},
        {'name': '聊斋', 'price': 15.3, 'pub_time': '2019-09-12', 'publish': random.choice(publishes)},
        {'name': '封神演义', 'price': 20.9, 'pub_time': '2019-05-12', 'publish': random.choice(publishes)},
        {'name': '论语', 'price': 45, 'pub_time': '2019-07-30', 'publish': random.choice(publishes)},
    ]
    models.Book.objects.bulk_create(
        [models.Book(**info) for info in infos]
    )

    # 创建作者表
    infos = [
        {'name': 'tank', 'phone': '110'},
        {'name': 'jason', 'phone': '120'},
        {'name': 'egon', 'phone': '130'},
        {'name': 'root', 'phone': '140'},
    ]
    models.Author.objects.bulk_create(
        [models.Author(**info) for info in infos]
    )

    # 快速创建关系

    authors = list(models.Author.objects.all())
    books = list(models.Book.objects.all())

    for book in books:
        book.authors.add(*random.sample(authors, random.randint(1, len(authors))))

ps: 以上快速创建的过程中, 遇到了一个坑, 使用bulk_create 或 create添加数据时, 返回的对象, 如果我们自己不指定id, 然后直接去使用, 与有外键关系的表创建新对象, 会直接报错. 最后添加成功时, 还是需要重新查询才能够得到有id的对象.

在了解跨表查询的语法之前, 还需要知道关联字段之间如何通过对象实现增删改的操作.

通过对象的增删改

"关联管理器"(RelatedManager)是在一对多或者多对多的关联上下文中使用的管理器。

它存在于下面两种情况：

1. 外键关系的反向查询
2. 多对多关联关系

简单来说就是在多对多表关系并且这一张多对多的关系表是有Django自动帮你建的情况下，下面的方法才可使用。当第三张关系表是我们自己创建的话, 并且manytomany字段的through指定的是我们自己的表, 那么下面的方法都不可使用.

此外, 需要注意的是下面的方法都是需要获取到具体对象才可使用, 不要在QuerySet对象上使用下面这些方法.

方法

create()

创建一个关联对象，并自动写入数据库，且在第三张双方的关联表中自动新建上双方对应关系。

>>> author = models.Author.objects.filter(pk=2).first()
>>> author.book_set.create(name='book1', price=14.4, pub_time='2000-01-01', publish_id=2)
<Book: book1>
    
1. 先获取到作者对象
2. 由作者对象反向查询跨到书籍表
3. 新增一本书籍并保存
4. 到作者与书籍的第三张表中新增两者之间的多对多关系并保存

add()

把指定的model对象添加到第三张关联表中, 这是专门创建多对多关系的方法. 需要注意的是add方法是传入多个位置参数.

通过对象添加关系

>>> authors = models.Author.objects.all()
>>> models.Book.objects.last().authors.add(*authors)

通过对象id添加关系

>>> models.Book.objects.last().authors.add(*[1, 2])

set()

更新某个对象在第三张表中的关联对象。不同于上面的add是添加,set相当于重置, 这里要注意set方法需要传入的是一个可迭代对象.

models.Book.objects.last().authors.set([1, 2])

remove()

从关联对象集中移除执行的model对象(移除对象在第三张表中与某个关联对象的关系)

>>> book_obj = models.Book.objects.first()
>>> book_obj.authors.remove(3)

clear()

从关联对象集中移除一切对象。(移除所有与对象相关的关系信息)

>>> book_obj = models.Book.objects.first()
>>> book_obj.authors.clear()

注意：

对于ForeignKey对象，clear()和remove()方法仅在null=True时存在, 被关联对象也可以通过类似于publish.book_set.add/set 等方法跨表修改关系.

举个例子：

ForeignKey字段没设置null=True时，

class Book(models.Model):
    title = models.CharField(max_length=32)
    publisher = models.ForeignKey(to=Publisher)

没有clear()和remove()方法：

>>> models.Publisher.objects.first().book_set.clear()
Traceback (most recent call last):
  File "<input>", line 1, in <module>
AttributeError: 'RelatedManager' object has no attribute 'clear'

当ForeignKey字段设置null=True时，

class Book(models.Model):
    name = models.CharField(max_length=32)
    publisher = models.ForeignKey(to=Class, null=True)

此时就有clear()和remove()方法：

>>> models.Publisher.objects.first().book_set.clear()

再次强调：

1. 对于所有类型的关联字段，add()、create()、remove()和clear(),set()都会马上更新数据库。换句话说，在关联的任何一端，都不需要再调用save()方法。

查询

基于对象的跨表查询

正向查询: 通过建立有外键字段的表来跨到其他表来查询

1 查询名字为北方出版社的邮箱

>>> models.Publish.objects.filter(name='北方出版社').first().pub_detail.email
'110@qq.com'

2 查询书籍id=3的出版社的名字

>>> models.Book.objects.filter(id=3).first().publish.name
'东方出版社'

3 查询书籍id为7的作者的电话

>>> models.Book.objects.filter(id=7).first().authors.all()
<QuerySet [<Author: jason>, <Author: egon>]>

这里需要注意多对多需要all()来获取所有查询集对象

反向查询: 通过被关联表为起始来查询关联表的数据信息

1 查询邮箱号码为110@qq.com的出版社的名字

>>> models.PublishDetail.objects.filter(email='110@qq.com').first().publish.name
'北方出版社'

2 查询出版社名字为南方出版社的所有书籍

models.Publish.objects.filter(name='南方出版社').first().book_set.all()
<QuerySet [<Book: 聊斋>]>

3 查询作者电话为110写的书籍名字

>>> models.Author.objects.filter(phone='110').first().book_set.all()
<QuerySet [<Book: 三国演义>, <Book: 红楼梦>, <Book: 聊斋>]>

通过上面的反向查询可以看出来, 多对多或一对多关系的反向查询需要通过表名小写_set来跨表, 再通过all()来获取查询结果.

当然你可以通过在 ForeignKey() 和ManyToManyField的定义中设置 related_name 的值来覆写 表名小写_set 的名称。例如，如果 Publish model 中做一下更改：

publish = ForeignKey(Book, related_name='bookList')

那么接下来就会如我们看到这般:

# 查询北方出版过的所有书籍
publish=Publish.objects.filter(name="人民出版社")
book_list=publish.bookList.all()  # 与人民出版社关联的所有书籍对象集合

基于双下划线的跨表查询

Django 还提供了一种直观而高效的方式在查询(lookups)中表示关联关系，它能自动确认 SQL JOIN 联系。要做跨关系查询，就使用两个下划线来链接模型(model)间关联字段的名称，直到最终链接到你想要的model 为止.

它的查询规则与基于对象的查询规则类似, 正向查询按字段, 反向查询按表名小写.

1 查询北方出版社的邮箱地址

# 正向查询
>>> models.Publish.objects.filter(name='北方出版社').values('pub_detail__email')
<QuerySet [{'pub_detail__email': '110@qq.com'}]>
# 反向查询
>>> models.PublishDetail.objects.filter(publish__name='北方出版社').values('email')
<QuerySet [{'email': '110@qq.com'}]>

2 查询南方出版社出版的所有的书

# 正向查询
>>> models.Book.objects.filter(publish__name='南方出版社').values('name', 'price')
<QuerySet [{'name': '聊斋', 'price': 15.3}]>
# 反向查询
>>> models.Publish.objects.filter(name='南方出版社').values('book__name', 'book__price')
<QuerySet [{'book__name': '聊斋', 'book__price': 15.3}]>

3 查询作者名字为jason的所有书

# 正向查询
>>> models.Book.objects.filter(authors__name='jason').values('name', 'price')
<QuerySet [{'name': '水浒传', 'price': 24.0}, {'name': '西游记', 'price': 17.5}, {'name': '西厢记', 'price': 35.5}, {'name': '红楼梦', 'price': 40.0}, {'name': '封神演义', 'price': 20.9}]>
# 反向查询
>>> models.Author.objects.filter(name='jason').values(书名=F('book__name'), 价格=F('book__price'))
<QuerySet [{'书名': '水浒传', '价格': 24.0}, {'书名': '西游记', '价格': 17.5}, {'书名': '西厢记', '价格': 35.5}, {'书名': '红楼梦', '价格': 40.0}, {'书名': '封神演义', '价格': 20.9}]>

当查询出来的名字太长的时候, 我们可以使用F类来为变量去别名.

聚合查询

聚合查询的原理与mysql中常用的5个聚合函数sum, avg, max, min, count一样, 一般是作用于分组上的数据的. 它们的位置位于from django.db.models import Avg, Sum, Max, Min, Count

在Django中, 利用到的是QuerySet对象中的aggregate()方法, 它也是一个终止语句, 返回值是包含键值对的字典.

>>> models.Book.objects.all().aggregate(Avg('price'), Max('price'), Count('pk'), Avg('price'), Sum('price'))
{'price__avg': 26.687500000000004, 'price__max': 45.0, 'pk__count': 8, 'price__sum': 213.50000000000003}

分组查询

annotate()为调用的QuerySet中每一个对象都生成一个独立的统计值（统计方法用聚合函数）。

总结 ：跨表分组查询本质就是将关联表join成一张表，再按单表的思路进行分组查询。　

查询的例子:

1 统计每一本书作者个数

>>> models.Book.objects.annotate(c=Count('authors')).values('name', 'c')
<QuerySet [{'name': '三国演义', 'c': 2}, {'name': '水浒传', 'c': 1}, {'name': '西游记', 'c': 3}, {'name': '西厢记', 'c': 1}, {'name': '红楼梦', 'c': 4}, {'name': '聊斋', 'c': 1}, {'name': '封神演义', 'c': 2}, {'name': '论语', 'c': 1}]>

2 统计每一个出版社的最便宜的书

>>> models.Publish.objects.annotate(price=Min('book__price')).values('name', 'price')
<QuerySet [{'name': '东方出版社', 'price': 15.3}, {'name': '北方出版社', 'price': 20.9}, {'name': '南方出版社', 'price': 15.3}]>

3 统计每一本以西开头的书籍的作者个数

>>> models.Book.objects.filter(name__startswith='西').annotate(c=Count('authors')).values('name', 'c')
<QuerySet [{'name': '西游记', 'c': 3}, {'name': '西厢记', 'c': 1}]>

4 统计每一个作者的数的数目

>>> models.Author.objects.annotate(c=Count('book')).values('name', 'c')
<QuerySet [{'name': 'tank', 'c': 3}, {'name': 'jason', 'c': 5}, {'name': 'egon', 'c': 4}, {'name': 'root', 'c': 3}]>

5 统计不止一个作者的图书

>>> models.Book.objects.annotate(c=Count('authors')).filter(c__gt=1).values('name', 'c')
<QuerySet [{'name': '三国演义', 'c': 2}, {'name': '西游记', 'c': 3}, {'name': '红楼梦', 'c': 4}, {'name': '封神演义', 'c': 2}]>

6 根据一本图书作者数量的多少对查询集 QuerySet进行排序

>>> models.Book.objects.annotate(c=Count('authors')).order_by('-c').values('name', 'c')
<QuerySet [{'name': '红楼梦', 'c': 4}, {'name': '西游记', 'c': 3}, {'name': '封神演义', 'c': 2}, {'name': '三国演义', 'c': 2}, {'name': '水浒传', 'c': 1}, {'name': '论语', 'c': 1}, {'name': '聊斋', 'c': 1}, {'name': '西厢记', 'c': 1}]>

7 查询各个作者出的书的总价格

models.Author.objects.annotate(s=Sum('book__price')).values('name', 's')
<QuerySet [{'name': 'tank', 's': 70.6}, {'name': 'jason', 's': 137.9}, {'name': 'egon', 's': 93.69999999999999}, {'name': 'root', 's': 102.5}]>

8 查询每个出版社的名称和书籍个数

>>> models.Publish.objects.annotate(c=Count('book')).values('name', 'c')
<QuerySet [{'name': '北方出版社', 'c': 2}, {'name': '南方出版社', 'c': 1}, {'name': '东方出版社', 'c': 5}]>

F查询

在Django中, 如果需要通过两个字段之间的比较, 那就必须要利用到F查询.

# 新的模型表
class Goods(models.Model):
    sale = models.IntegerField()
    storage = models.IntegerField()
    name = models.CharField(max_length=32)

示例: 查询卖出数大于库存数的水果

>>> from django.db.models import F
>>> models.Goods.objects.filter(sale__gt=F('storage')).values('name', 'sale', 'name')
<QuerySet [{'name': '石榴', 'sale': 200}, {'name': '西瓜', 'sale': 50}, {'name': '桔子', 'sale': 999}, {'name': '葡萄', 'sale': 666}]>

示例2: 将每个商品的售货数增加50

>>> models.Goods.objects.update(sale=F('sale') + 50)
7

Django 支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。基于此可以对表中的数值类型进行数学运算.

Q查询

Django默认支持的查询的条件之间是逻辑与的关系, 如果我们需要改成逻辑或操作, 那么只能通过Django提供的Q查询了.

示例1: 查询卖出数大于400的或库存大于1000的

>>> models.Goods.objects.filter(Q(sale__gt=400) | Q(storage__gt=1000)).values('sale', 'storage')
<QuerySet [{'sale': 1050, 'storage': 2000}, {'sale': 550, 'storage': 4000}, {'sale': 1049, 'storage': 800}, {'sale': 716, 'storage': 555}]>

示例2: 查询库存数小于100或库存数不大于500的商品名字

>>> models.Goods.objects.filter(Q(sale__lt=100) | ~Q(storage__gt=500)).values('name')
<QuerySet [{'name': '香蕉'}, {'name': '石榴'}, {'name': '西瓜'}]>

Q查询的与或非操作分别对应于符号 & | ~. 使用他们可以完成比较复杂的查询操作