Django之多表操作

表与表之间关系的创建

一对一、多对一、多对多

实例：我们来假定下面这些概念，字段和关系

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

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

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

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

一本书只应该由一个出版商出版，所以出版商和书籍是一对多关联关系(one-to-many)。

from django.db import models

# Create your models here.


class Author(models.Model):
    '''
    作者表
    '''
    name=models.CharField( max_length=32)
    age=models.IntegerField()

    ad = models.OneToOneField(to='AuthorDetail')
    # ad_id = models.OneToOneField('AuthorDetail')
    # ad_id = models.OneToOneField(to='AuthorDetail',on_delete=models.SET_NULL,null=True)
    # ad_id = models.OneToOneField(to='AuthorDetail',to_field='nid')
    def __str__(self):
        return self.name

class AuthorDetail(models.Model):
    '''
    作者详细信息表
    '''
    birthday=models.DateField()
    telephone=models.BigIntegerField()
    addr=models.CharField( max_length=64)

class Publish(models.Model):
    name=models.CharField( max_length=32)
    city=models.CharField( max_length=32)


class Book(models.Model):

    title = models.CharField( max_length=32)

    price=models.DecimalField(max_digits=5,decimal_places=2) #999.99

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

    def __str__(self):
        return self.title

关于多对多表的三种创建方式(了解)

方式一：自行创建第三张表

class Book(models.Model):
    title = models.CharField(max_length=32, verbose_name="书名")


class Author(models.Model):
    name = models.CharField(max_length=32, verbose_name="作者姓名")


# 自己创建第三张表，分别通过外键关联书和作者
class Author2Book(models.Model):
    author = models.ForeignKey(to="Author")
    book = models.ForeignKey(to="Book")

    class Meta:
        unique_together = ("author", "book")

方式二：通过ManyToManyField自动创建第三张表（常用）

class Book(models.Model):
    title = models.CharField(max_length=32, verbose_name="书名")


# 通过ORM自带的ManyToManyField自动创建第三张表
class Author(models.Model):
    name = models.CharField(max_length=32, verbose_name="作者姓名")
    books = models.ManyToManyField(to="Book", related_name="authors")

方式三：设置ManyTomanyField并指定自行创建的第三张表

class Book(models.Model):
    title = models.CharField(max_length=32, verbose_name="书名")


# 自己创建第三张表，并通过ManyToManyField指定关联
class Author(models.Model):
    name = models.CharField(max_length=32, verbose_name="作者姓名")
    books = models.ManyToManyField(to="Book", through="Author2Book", through_fields=("author", "book"))
    # through_fields接受一个2元组（'field1'，'field2'）：
    # 其中field1是定义ManyToManyField的模型外键的名（author），field2是关联目标模型（book）的外键名。


class Author2Book(models.Model):
    author = models.ForeignKey(to="Author")
    book = models.ForeignKey(to="Book")

    class Meta:
        unique_together = ("author", "book")

注意：

当我们需要在第三张关系表中存储额外的字段时，就要使用第三种方式。

但是当我们使用第三种方式创建多对多关联关系时，就无法使用orm提供的set、add、remove、clear方法来管理多对多的关系了，需要通过第三张表的model来管理多对多关系。

to
    设置要关联的表。

to_field
    设置要关联的字段。
    
on_delete
    同ForeignKey字段。

to
    设置要关联的表

to_field
    设置要关联的表的字段

related_name
    反向操作时，使用的字段名，用于代替原反向查询时的'表名_set'。
related_query_name
    反向查询操作时，使用的连接前缀，用于替换表名。

on_delete
    当删除关联表中的数据时，当前表与其关联的行的行为。

多对多的参数：
    to
        设置要关联的表

    related_name
        同ForeignKey字段。

    related_query_name
        同ForeignKey字段。
    through
        在使用ManyToManyField字段时，Django将自动生成一张表来管理多对多的关联关系。

        但我们也可以手动创建第三张表来管理多对多关系，此时就需要通过        
    through来指定第三张表的表名。

    through_fields
        设置关联的字段。

    db_table
        默认创建第三张表时，数据库中表的名称。

元信息
    ORM对应的类里面包含另一个Meta类，而Meta类封装了一些数据库的信息。主要字段如下:
class Author2Book(models.Model):
    author = models.ForeignKey(to="Author")
    book = models.ForeignKey(to="Book")
    class Meta:
        unique_together = ("author", "book")

db_table
    ORM在数据库中的表名默认是 app_类名，可以通过db_table可以重写表名。db_table = 'book_model'

index_together
    联合索引。

unique_together
    联合唯一索引。

ordering
    指定默认按什么字段排序。
    ordering = ['pub_date',]
    只有设置了该属性，我们查询到的结果才可以被reverse()，否则是能对排序了的结果进行反转(order_by()方法排序过的数据)

注意事项：

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

关于on_delete（了解）

on_delete
当删除关联表中的数据时，当前表与其关联的行的行为。

models.CASCADE
删除关联数据，与之关联也删除


models.DO_NOTHING
删除关联数据，引发错误IntegrityError


models.PROTECT
删除关联数据，引发错误ProtectedError


models.SET_NULL
删除关联数据，与之关联的值设置为null（前提FK字段需要设置为可空）


models.SET_DEFAULT
删除关联数据，与之关联的值设置为默认值（前提FK字段需要设置默认值）


models.SET

删除关联数据，
a. 与之关联的值设置为指定值，设置：models.SET(值)
b. 与之关联的值设置为可执行对象的返回值，设置：models.SET(可执行对象)

多表的增删改

操作前先简单的录入一些数据：还是create和save两个方法，和单表的区别就是看看怎么添加关联字段的数据

publish表：

author表：

authordetail表:

一对多

    # 第一种方法：
    publish_obj = models.Publish.objects.filter(id=2)[0]
    models.Book.objects.create(
        title="linux",
        price=100,
        publish=publish_obj
    )
    # 第二种方法(用的多)：
    models.Book.objects.create(
        title="python",
        price=20,
        publish_id=2,
    )

models.Book.objects.get(id=1).delete()

models.Book.objects.filter(id=2).update(price=300)

一对一

    models.Author.objects.create(
        name="周星驰",
        age=60,
        authorDetail=models.AuthorDetail.objects.get(id=2)
    )
    models.Author.objects.create(
        name="三哥",
        age=23,
        authorDetail_id=1,  # 常用
    )

models.Author.objects.get(name="三哥").delete()

models.Author.objects.filter(id=1).update(age=66)

多对多 

    book_obj = models.Book.objects.create(
        title="go",
        price=10,
        publish_id=1,
    )
    author1 = models.Author.objects.get(id=1)
    author5 = models.Author.objects.get(id=5)
    
    book_obj = models.Book.objects.get(id=3)
    
    book_obj.authors.add(author1,author5)  # 最不常用
    book_obj.authors.add(1,5) 
    book_obj.authors.add(*[1,5])  # 常用

    book_obj = models.Book.objects.get(id=3)
    book_obj.authors.remove(1)
    book_obj.authors.remove(1,5)
    book_obj.authors.remove(*[1,5])
    book_obj.authors.clear()  # 全部清空
    book_obj.authors.set(["6",'8'])  # 先清空book_id为3的内容，重新增加相应author_id对应的。

改也是.set()。注意：里面的列表内容不需要打散

基于对象的跨表查询

跨表查询是分组查询的基础

一对多查询（Publish 与 Book）

正向查询(按字段：publish)：关联属性字段所在的表查询被关联表的记录就是正向查询，反之就是反向查询

# 查询主键为2的书籍的出版社所在城市

book_obj = models.Book.objects.get(id=2)
book_obj = models.Book.objects.filter(pk=2).first() # 是一样的
ret = book_obj.publish.city
print(ret)

反向查询(按表名：book_set,因为加上_set是因为反向查询的时候，你查询出来的可能是多条记录的集合)：

# 查询沙河出版社出版过哪些书籍

publish_obj = models.Publish.objects.get(name="沙河出版社")
book_list = publish_obj.book_set.all()
print(book_list)

一对一查询(Author与AuthorDetail) 

正向查询(按字段：authorDetail)：

# 周星驰是哪个城市的

author_obj = models.Author.objects.get(name="周星驰")
addrs = author_obj.authorDetail.addr
print(addrs)

反向查询(按表名：author)：不需要_set，因为一对一正向反向都是找到一条记录

# 谁住在朱辛庄

ad_obj = models.AuthorDetail.objects.get(addr="朱辛庄")
print(ad_obj.author.name)

多对多查询(Author与Book)

 

正向查询(按字段：authors)：

# 查询python的作者是谁

book_obj = models.Book.objects.get(title="python")
author_list = book_obj.authors.all()
print(author_list)

反向查询(按表名：book_set)：

# 周杰伦写了哪些书

author_obj = models.Author.objects.get(name="周杰伦")
print(author_obj.book_set.all())

基于双下划线的跨表查询（基于join实现的）

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

一对多查询

# 查询主键为2的城市名称

# 正向查询，属性名双下划线连表
ret = models.Book.objects.filter(id=2).values("publish__city")
print(ret)

# 反向查询，类名小写
ret = models.Publish.objects.filter(book__id=2).values('city')
print(ret)

# 以上两种是一样的

比较下对象和双下划线的一对多

# 沙河出版社出版了哪些书籍

# 对象方法查找
publish_obj = models.Publish.objects.get(name="沙河出版社")
book_list = publish_obj.book_set.all()
print(book_list)

# 双下划线方法

# 正向查询
ret = models.Book.objects.filter(publish__name="沙河出版社").values("title")
print(ret)

# 反向查询
ret = models.Publish.objects.filter(name="沙河出版社").values("book__title")
print(ret)

一对一查询

# 周星驰是哪个城市的

# 正向查询
ret = models.Author.objects.filter(name="周星驰").values("authorDetail__addr")
print(ret)

# 反向查询
ret = models.AuthorDetail.objects.filter(author__name="周星驰").values("addr")
print(ret)

同样对比下对象和双下划线

# 谁住在朱辛庄

# 对象方法
ad_obj = models.AuthorDetail.objects.get(addr="朱辛庄")
print(ad_obj.author.name)

# 双下划线方法
# 正向 （按照属性连表）
ret = models.Author.objects.filter(authorDetail__addr="朱辛庄").values("name")
print(ret)

# 反向 （按照类名小写连表）
ret = models.AuthorDetail.objects.filter(addr="朱辛庄").values("author__name")
print(ret)

多对多查询

# 查询python的作者是谁

# 正向查询
ret = models.Book.objects.filter(title="python").values("authors__name")
print(ret)

# 反向查询
ret = models.Author.objects.filter(book__title="python").values("name")
print(ret)

举例对比下对象和双下划线

# 周杰伦写了哪些书

# 对象(都是反向的这个例子)
author_obj = models.Author.objects.get(name="周杰伦")
print(author_obj.book_set.all())

# 双下划线
ret = models.Book.objects.filter(authors__name="周杰伦").values("title")
print(ret)
ret = models.Author.objects.filter(name="周杰伦").values("book__title")
print(ret)

 综合练习下

# 查询沙河出版社出版过的所有书籍的名字以及作者的姓名

ret = models.Publish.objects.filter(name="沙河出版社").values("book__title","book__authors__name")
print(ret)

# 手机号大于115的作者出版过的所有书籍名称以及出版社名称
ret = models.Author.objects.filter(authorDetail__telephone__gt=115).values("book__title","book__publish__name")
print(ret)
ret = models.AuthorDetail.objects.filter(telephone__gt=115).values("author__book__title","author__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") 

聚合查询、分组查询、F查询和Q查询

聚合查询

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

# 求book表的价格平均值

from django.db.models import Avg

ret = models.Book.objects.all().aggregate(avg = Avg("price"))
print(ret)

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

from django.db.models import Avg, Max, Min

Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))  #count('id'),count(1)也可以统计个数,Book.objects.all().aggregete和Book.objects.aggregate()，都可以

F查询

# book表的价格+1

models.Book.objects.filter(price=F("price")).update(price=F("price")+1)

# filter中放什么条件都可以。

在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？我们在book表里面加上两个字段：点赞数：zan，评论数：comment

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

# 查询评论数大于点赞数的

ret= models.Book.objects.filter(comment__gt=F("zan"))
print(ret)

Q查询

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

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

Q 对象可以使用&(与) 、|（或）、~（非） 操作符组合起来。当一个操作符在两个Q 对象上使用时，它产生一个新的Q 对象。

# 查询价格大于50或者评论大于20的书籍

ret = models.Book.objects.filter(Q(price__gt=50)|Q(comment__gt=20))
print(ret)
# 与
ret = models.Book.objects.filter(Q(price__gt=50)&Q(comment__gt=20))
# 取反
ret = models.Book.objects.filter(Q(price__gt=50)|~Q(comment__gt=20))

分组查询***

 还是基于上面的表进行练习

# 单表的  查询每个出版社出版书籍的平均价格和自己的id
ret = models.Book.objects.values("publish_id").annotate(a=Avg("price"))
print(ret)

# 多表的  查询每个出版社名字及出版书籍的最高价格
ret = models.Book.objects.values("publish__name").annotate(m=Max("price"))
print(ret)
# 反向查询
ret = models.Publish.objects.values("name").annotate(m=Max("book__price"))
print(ret)
# 另一种写法
ret = models.Publish.objects.annotate(m=Max("book__price")).values("name","m")
print(ret)

 综合练习题：

# 1 查询每个作者的姓名以及出版的书的最高价格
ret = models.Book.objects.values("authors__name").annotate(m=Max("price"))
print(ret)

# 2 查询作者id大于2作者的姓名以及出版的书的最高价格
ret = models.Author.objects.filter(id__gt=2).values("name").annotate(m=Max("book__price"))
print(ret)

# 3 查询作者id大于2或者作者年龄大于等于20岁的女作者的姓名以及出版的书的最高价格
ret = models.Author.objects.filter(Q(id__gt=2)|Q(age__gte=40),sex="女").values("name").annotate(m=Max("book__price"))
print(ret)

# 4 查询每个作者出版的书的最高价格 的平均值
ret = models.Author.objects.annotate(m=Max("book__price")).aggregate(Avg("m"))
print(ret)

# 5 每个作者出版的所有书的最高价格的那本书的名称
ret= models.Author.objects.annotate(m=Max("book__price")).values("book__title")
print(ret)  # 有bug
使用sql写

# 5 每个作者出版的所有书的价格以及最高价格的那本书的名称（通过orm玩起来就是个死题，需要用原生sql）

'''
    select title,price from (select app01_author.id,app01_book.title,app01_book.price from app01_author INNER JOIN app01_book_authors on app01_author.id=
app01_book_authors.author_id INNER JOIN app01_book on app01_book.id=
app01_book_authors.book_id ORDER BY app01_book.price desc) as b  GROUP BY id
'''

 

ORM执行原生sql语句

在模型查询API不够用的情况下，我们还可以使用原始的SQL语句进行查询。

Django 提供两种方法使用原始SQL进行查询：一种是使用raw()方法，进行原始SQL查询并返回模型实例；另一种是完全避开模型层，直接执行自定义的SQL语句。

执行原生查询

raw()管理器方法用于原始的SQL查询，并返回模型的实例：

注意：raw()语法查询必须包含主键。

这个方法执行原始的SQL查询，并返回一个django.db.models.query.RawQuerySet 实例。 这个RawQuerySet 实例可以像一般的QuerySet那样，通过迭代来提供对象实例。

举例：

class Person(models.Model):
    first_name = models.CharField(...)
    last_name = models.CharField(...)
    birth_date = models.DateField(...)

可以像下面这样执行原生SQL语句

for p in Person.objects.raw('SELECT * FROM myapp_person'):
    print(p)

raw()查询可以查询其他表的数据。

举例：

ret = models.Student.objects.raw('select id, tname as hehe from app02_teacher')
    for i in ret:
        print(i.id, i.hehe)

raw()方法自动将查询字段映射到模型字段。还可以通过translations参数指定一个把查询的字段名和ORM对象实例的字段名互相对应的字典

d = {'tname': 'haha'}
    ret = models.Student.objects.raw('select * from app02_teacher', translations=d)
    for i in ret:
        print(i.id, i.sname, i.haha)

原生SQL还可以使用参数，注意不要自己使用字符串格式化拼接SQL语句，防止SQL注入！

d = {'tname': 'haha'}
    ret = models.Student.objects.raw('select * from app02_teacher where id > %s', translations=d, params=[1,])
    for i in ret:
        print(i.id, i.sname, i.haha)

直接执行自定义SQL

有时候raw()方法并不十分好用，很多情况下我们不需要将查询结果映射成模型，或者我们需要执行DELETE、 INSERT以及UPDATE操作。在这些情况下，我们可以直接访问数据库，完全避开模型层。

我们可以直接从django提供的接口中获取数据库连接，然后像使用pymysql模块一样操作数据库。

from django.db import connection, connections
cursor = connection.cursor()  # cursor = connections['default'].cursor()
cursor.execute("""SELECT * from auth_user where id = %s""", [1])
ret = cursor.fetchone()

Python脚本中调用Django环境

 如果你想通过自己创建的python文件在django项目中使用django的models，那么就需要调用django的环境：

import os

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

    from app01 import models  #引入也要写在上面三句之后

    books = models.Book.objects.all()
    print(books)