web框架开发-Django模型层(2)-多表操作

很重要，都是精华

多表关系模型

一对一

一旦确定表关系是一对一，在两张表中的任意一张表中建立关联字段+Unique

一对多

一旦确定表关系是一对多，创建关联字段在多的表中

多对多

一旦确定表关系是多对多，创建第三章关系表

ORM生成关系表模型

假定下面这些概念，字段和关系

作者模型：一个作者有姓名和年龄。

作者详细模型：把作者的详情放到详情表，包含生日，手机号，家庭住址等信息。作者详情模型和作者模型之间是一对一的关系（one-to-one）

出版商模型：出版商有名称，所在城市以及email。

书籍模型： 书籍有书名和出版日期，一本书可能会有多个作者，一个作者也可以写多本书，所以作者和书籍的关系就是多对多的关联关系(many-to-many);一本书只应该由一个出版商出版，所以出版商和书籍是一对多关联关系(one-to-many)。

模型建立如下：

from django.db import models

# Create your models here.


class Author(models.Model):
    nid = models.AutoField(primary_key=True)
    name=models.CharField( max_length=32)
    age=models.IntegerField()

    # 与AuthorDetail建立一对一的关系
    authorDetail=models.OneToOneField(to="AuthorDetail",on_delete=models.CASCADE)

class AuthorDetail(models.Model):

    nid = models.AutoField(primary_key=True)
    birthday=models.DateField()
    telephone=models.BigIntegerField()
    addr=models.CharField( max_length=64)

class Publish(models.Model):
    nid = models.AutoField(primary_key=True)
    name=models.CharField( max_length=32)
    city=models.CharField( max_length=32)
    email=models.EmailField()


class Book(models.Model):

    nid = models.AutoField(primary_key=True)
    title = models.CharField( max_length=32)
    publishDate=models.DateField()
    price=models.DecimalField(max_digits=5,decimal_places=2)

    # 与Publish建立一对多的关系,外键字段建立在多的一方
    publish=models.ForeignKey(to="Publish",to_field="nid",on_delete=models.CASCADE)

    # 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个，自动创建第三张表。而不是增加了一个字段。
    authors=models.ManyToManyField(to='Author',)

注意事项：

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

添加表记录

一对多

方式1:
   publish_obj=Publish.objects.get(nid=1)
   book_obj=Book.objects.create(title="三国演义",publishDate="2012-12-12",price=100,publish=publish_obj)
  

方式2:
   book_obj=Book.objects.create(title="水浒传",publishDate="2012-12-12",price=100,publish_id=1)

两种方法区别仅在于方式一的关联键添加的是一个对象，方式二添加的是一个id值，但是他们运行后的结果都是一样的

即：都会生成一个字段publish_id，而且，都可以使用book_obj.publish得到一个publish对象！

多对多

绑定方法

    # 当前生成的书籍对象
    book_obj=Book.objects.create(title="追风筝的人",price=200,publishDate="2012-11-12",publish_id=1)
    # 为书籍绑定的做作者对象
    yuan=Author.objects.filter(name="yuan").first() # 在Author表中主键为2的纪录
    egon=Author.objects.filter(name="alex").first() # 在Author表中主键为1的纪录

    # 绑定多对多关系,即向关系表book_authors中添加纪录
    book_obj.authors.add(yuan,egon)    #  将某些特定的 model 对象添加到被关联对象集合中。   =======    book_obj.authors.add(*[])或者使用键值（1,2）

注意：之前用authors=models.ManyToManyField(to='Author',)，无法用一对多的方法添加，因为没有创建这个表的类，这个类是Django自己创建出来的

多对多关系其它常用API：

book_obj.authors.remove()      # 将某个特定的对象从被关联对象集合中去除。    ======   book_obj.authors.remove(*[])
book_obj.authors.clear()       #清空被关联对象集合
book_obj.authors.set()         #先清空再设置

more

例子：

######################绑定多对多的关系##############################################

    book_obj=Book.objects.create(title="西游记",price=100,publishDate="2012-12-12",publish_id=1)

    egon=Author.objects.get(name="egon")
    alex=Author.objects.get(name="alex")

    #绑定多对多关系的API
    book_obj.authors.add(egon,alex)
    book_obj.authors.add(1,2,3)
    book_obj.authors.add(*[1,2,3])

    #解除多对多关系

    book=Book.objects.filter(nid=4).first()
    book.authors.remove(2)
    #book.authors.remove(*[1,2])

    book.authors.clear()


 

book_obj.authors.all()

    #查询主键为4的书籍的所有作者的名字
    book=Book.objects.filter(nid=4).first()
    print(book.authors.all()) # [obj1,obj2...] queryset: 与这本书关联的所有作者对象集合
    ret=book.authors.all().values("name")
    print(ret)


    关键点:

    一 book_obj.publish=Publish.objects.filter(id=book_obj.publish_id).first()

    二 book_obj.authors.all()
       关键点:book.authors.all()  # 与这本书关联的作者集合

        1 book.id=3
        2 book_authors
            id  book_id  author_ID
            3      3             1
            4      3             2

        3  author
           id   name
           1   alex
           2   egon

    book_obj.authors.all()    ------->   [alex,egon]

基于对象的跨表查询

正向查询和反向查询的概念

A-B
关联属性在A表中

正向查询: A------>B
反向查询: B------>A

一对多

正向查询:按字段
反向查询:表名小写_set.all()

    # 一对多查询的正向查询 : 查询西游记这本书的出版社的名字

    book_obj=Book.objects.filter(title="西游记").first()
    print(book_obj.publish) # 与这本书关联出版社对象
    print(book_obj.publish.name)
    对应sql:
    select publish_id from Book where title="西游记"
    select name from Publish where id=1


    # 一对多查询的反向查询 : 查询人民出版社出版过的书籍名称

    publish=Publish.objects.filter(name="人民出版社").first()
    ret=publish.book_set.all()
    print(ret)

多对多

正向查询:按字段
反向查询:表名小写_set.all()

# 多对多查询的正向查询 : 查询西游记这本书的所有作者的名字

    book_obj=Book.objects.filter(title="西游记").first()
    author_list=book_obj.authors.all() # queryset对象  [author_obj1,...]

    for author in author_list:
        print(author.name)

    # 多对多查询的反向查询 : 查询alex出版过的所有书籍名称

    alex=Author.objects.filter(name="alex").first()

    book_list=alex.book_set.all()
    for book in book_list:
        print(book.title)

一对一

正向查询:按字段
反向查询:表名小写

    # 一对一查询的正向查询 : 查询alex的手机号

    alex=Author.objects.filter(name="alex").first()
    print(alex.authordetail.telephone)

    # 一对一查询的反向查询 : 查询手机号为110的作者的名字和年龄

    ad=AuthorDetail.objects.filter(telephone="110").first()
    print(ad.author.name)
    print(ad.author.age)

基于双下划綫的跨表查询

要点

要点一：

双下划线代表跨表，使用双下划线，即通知内部引擎的方式。

要点二:

正向查询按字段,

反向查询按表名小写

此要求是对于跨表的！分清楚是正向还是反向很重要

要点三：

已知条件在哪张表，哪张表用字段，非那张表用双下划线跨表，匹配条件或者求值！

一对多

# 一对多查询 : 查询西游记这本书的出版社的名字

    # 方式1:
    ret=Book.objects.filter(title="西游记").values("publish__name")
    print(ret) # <QuerySet [{'publish__name': '南京出版社'}]>

    # 方式2:
    ret=Publish.objects.filter(book__title="西游记").values("name")
    print(ret)

多对多

# 多对多查询 : 查询西游记这本书的所有作者的名字

    # 方式1:
    # 需求: 通过Book表join与其关联的Author表,属于正向查询:按字段authors通知ORM引擎join book_authors与author

    ret=Book.objects.filter(title="西游记").values("authors__name")
    print(ret) # <QuerySet [{'authors__name': 'alex'}, {'authors__name': 'egon'}]>

    # 方式2:
    # 需求: 通过Author表join与其关联的Book表,属于反向查询:按表名小写book通知ORM引擎join book_authors与book表
    ret=Author.objects.filter(book__title="西游记").values("name")
    print(ret) # <QuerySet [{'name': 'alex'}, {'name': 'egon'}]>

一对一

# 一对一查询的查询 : 查询alex的手机号

    # 方式1:
    # 需求: 通过Author表join与其关联的AuthorDetail表,属于正向查询:按字段authordetail通知ORM引擎join Authordetail表

    ret=Author.objects.filter(name="alex").values("authordetail__telephone")
    print(ret) # <QuerySet [{'authordetail__telephone': 110}]>

    # # 方式2:
    # # 需求: 通过AuthorDetail表join与其关联的Author表,属于反向查询:按表名小写author通知ORM引擎join Author表
    ret=AuthorDetail.objects.filter(author__name="alex").values("telephone")
    print(ret) # <QuerySet [{'telephone': 110}]>

连续跨表查询

# 练习: 手机号以110开头的作者出版过的所有书籍名称以及书籍出版社名称

    # 方式1:
    # 需求: 通过Book表join AuthorDetail表, Book与AuthorDetail无关联,所以必需连续跨表
    ret=Book.objects.filter(authors__authordetail__telephone__startswith="110").values("title","publish__name")
    print(ret)
    #
    # 方式2:
    ret=Author.objects.filter(authordetail__telephone__startswith="110").values("book__title","book__publish__name")
    print(ret)

related_name

反向查询时，如果定义了related_name ，则用related_name替换表名，例如：

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



# 练习: 查询人民出版社出版过的所有书籍的名字与价格(一对多)

# 反向查询 不再按表名:book,而是related_name:bookList


     queryResult=Publish.objects
　　　　　　　　　　　　　　.filter(name="人民出版社")
　　　　　　　　　　　　　　.values_list("bookList__title","bookList__price")

聚合查询

aggregate(*args, **kwargs) 聚合统计函数

返回值是一个字典

# 计算所有图书的平均价格

>>> from django.db.models import Avg

>>> Book.objects.all().aggregate(Avg('price'))

{'price__avg': 34.35}




aggregate()是QuerySet 的一个终止子句，意思是说，它返回一个包含一些键值对的字典。键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。如果你想要为聚合值指定一个名称，可以向聚合子句提供它。

>>> Book.objects.aggregate(average_price=Avg('price'))

{'average_price': 34.35}




如果你希望生成不止一个聚合，你可以向aggregate()子句中添加另一个参数。所以，如果你也想知道所有图书价格的最大值和最小值，可以这样查询：

>>> from django.db.models import Avg, Max, Min，count

>>> Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))

{'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}

分组查询

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

返回值是queryset

单表分组查询

emp:

5id  name age   salary    dep
  1   alex  12   2000     销售部
  2   egon  22   3000     人事部
  3   wen   22   5000     人事部

    # 示例1
    # 查询每一个部门的名称以及员工的平均薪水

    SQL：select dep,Avg(salary) from emp group by dep

    ret=Emp.objects.values("dep").annotate(avg_salary=Avg("salary"))
    print(ret) # <QuerySet [{'avg_salary': 5000.0, 'dep': '保安部'}, {'avg_salary': 51000.0, 'dep': '教学部'}]>

    # 单表分组查询的ORM语法: 单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段"))

    # 示例2
    # 查询每一个省份的名称以及员工数

    ret=Emp.objects.values("province").annotate(c=Count("id"))
    print(ret) # <QuerySet [{'province': '山东省', 'c': 2}, {'province': '河北省', 'c': 1}]>

    # 补充知识点:

    ret=Emp.objects.all()
    print(ret)  # select * from emp
    ret=Emp.objects.values("name")
    print(ret)  # select name from emp

    Emp.objects.all().annotate(avg_salary=Avg("salary"))  # 在单表分组下，按照主键进行group by 没有意义。

多表分组查询

Book表

        id   title    date      price  publish_id
        1    红楼梦    2012-12-12    101       1
        2    西游记    2012-12-12    101       1
        3    三国演绎    2012-12-12    101       1
        4    水浒传    2012-12-12    301       2


     Publish表
        id    name      addr   email
        1    人民出版社    北京       123@qq.com
        2    南京出版社    南京       345@163.com

    ## 示例1 查询每一个出版社的名称以及出版的书籍个数

    ret = Publish.objects.values("id").annotate(c=Count("book__title"))
    print(ret)  # <QuerySet [{'nid': 1, 'c': 3}, {'nid': 2, 'c': 1}]>

    ret = Publish.objects.values("name").annotate(c=Count("book__title"))
    print(ret)  # <QuerySet [{'name': '人民出版社', 'c': 3}, {'name': '南京出版社', 'c': 1}]>

    ret = Publish.objects.values("id").annotate(c=Count("book__title")).values("name", "c") 通过value取值
    print(ret)  # <QuerySet [{'name': '人民出版社', 'c': 3}, {'name': '南京出版社', 'c': 1}]>

    ## 示例2 查询每一个作者的名字以及出版过的书籍的最高价格
    ret = Author.objects.values("pk").annotate(max_price=Max("book__price")).values("name", "max_price")
    print(ret)

    # 总结 跨表的分组查询的模型:
    # 每一个后表模型.objects.values("pk").annotate(聚合函数(关联表__统计字段))

    # 示例3 查询每一个书籍的名称以及对应的作者个数
    ret = Book.objects.values("pk").annotate(c=Count("authors__name")).values("title", "c")
    print(ret)

    #################### 跨表分组查询的另一种玩法  ####################

    # 示例1 查询每一个出版社的名称以及出版的书籍个数
    # ret=Publish.objects.values("id").annotate(c=Count("book__title")).values("name","email","c") #ret是一个对象，里面有所有的字段信息
    # ret=Publish.objects.all().annotate(c=Count("book__title")).values("name","c","city")  # all（）按照表publish的所有字段，结果一样

  # ret=Publish.objects.all().annotate(c=Count("book__title"))显示两个publish 对象，而且加all 和不加all也是一样的
    ret=Publish.objects.annotate(c=Count("book__title")).values("name","c","city")
    print(ret)

总结

# 总结 跨表的分组查询的模型:
          # 每一个后的表模型.objects.values("pk").annotate(聚合函数(关联表__统计字段)).values("表模型的所有字段以及统计字段")
          # 每一个后的表模型.objects.annotate(聚合函数(关联表__统计字段)).values("表模型的所有字段以及统计字段")

临时给表添加字段

直接在相应的类中添加新字段，

然后必须要添加默认数据，否则会报错

然后重新迁移数据。

F查询

在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？

Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。

# 查询评论数大于收藏数的书籍
 
   from django.db.models import F
   Book.objects.filter(commnetNum__lt=F('keepNum'))

Django 支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。

# 查询评论数大于收藏数2倍的书籍
    Book.objects.filter(commnetNum__lt=F('keepNum')*2)

修改操作也可以使用F函数,比如将每一本书的价格提高30元：

Book.objects.all().update(price=F("price")+30)

Q查询

filter() 等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询（例如OR 语句），你可以使用Q 对象。

from django.db.models import Q
Q(title__startswith='Py')

Q 对象可以使用& 和| 操作符组合起来。当一个操作符在两个Q 对象上使用时，它产生一个新的Q 对象。

bookList=Book.objects.filter(Q(authors__name="yuan")|Q(authors__name="egon"))

等同于下面的SQL WHERE 子句：

WHERE name ="yuan" OR name ="egon"

你可以组合& 和|  操作符以及使用括号进行分组来编写任意复杂的Q 对象。同时，Q 对象可以使用~ 操作符取反，这允许组合正常的查询和取反(NOT) 查询：

bookList=Book.objects.filter(Q(authors__name="yuan") & ~Q(publishDate__year=2017)).values_list("title")

查询函数可以混合使用Q 对象和关键字参数。所有提供给查询函数的参数（关键字参数或Q 对象）都将"AND”在一起。但是，如果出现Q 对象，它必须位于所有关键字参数的前面。例如：

bookList=Book.objects.filter(Q(publishDate__year=2016) | Q(publishDate__year=2017),
                              title__icontains="python")