三 .复习python的 ORM 操作

一.ORM连表高级操作https://www.cnblogs.com/yuanchenqi/articles/8963244.html

from django.db import models

# 作者
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(auto_now_add=True)
    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',)

from django.shortcuts import render,HttpResponse
# https://www.cnblogs.com/yuanchenqi/articles/8963244.html
from  myapp import  models
from django.db.models import Sum, Count, Avg, Max


def index(request):
    return HttpResponse("OK")

def show_date(request):
    # #####################基于对象查询(子查询)##############################
    #                按字段（publish）
    # 一对多   book  ----------------->  publish
    #               <----------------
    #                 book_set.all()

    # 正向按照字段查询
    aa=models.Book.objects.filter(title="python").first()
    cc= aa.publish.email
    bb= aa.publish.city
    dd=aa.publish.name
    print(cc,bb,dd)   # 11111  南京   南方出版社



    # 一对多  book<----------------------------------publish   # 反向查询按     表名小写_set.all()
    publish_obj= models.Publish.objects.filter(name="四川出版社").first()
    book_all=publish_obj.book_set.all()   # <QuerySet [<Book: Book object (2)>, <Book: Book object (4)>]>
    for i in  book_all:
        print(i.title)   # java  css




    print("**************************************************")


    #                按字段（authors.all()）
    # 多对多   book  ----------------------->  author
    #               <----------------
    #                  book_set.all()
    css=models.Book.objects.filter(title="css").first()         # < QuerySet[ < Author: Authorobject(2) >, < Author: Authorobject(4) >] >
    aut_all=css.authors.all()
    for i in aut_all:
        print(i.name,i.age)       # 李四  33     哈哈  44


    aut=models.Author.objects.filter(name="张三").first()
    aut_book=aut.book_set.all()
    print(aut_book)  # <QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (3)>]>
    for i in  aut_book:
         print(i.title)  # python    java  html





    print("**************************************************")

    #                  按字段 authorDetail
    # 一对一   author  ----------------------->  authordetail
    #                <----------------
    #                  按表名  author

    aa=models.Author.objects.filter(name='李四').first()
    bb=aa.authorDetail.telephone
    ad= aa.authorDetail.addr
    print(bb,ad)    # 44444444  李四北京




    # adr=models.AuthorDetail.objects.filter(addr="李四北京").first()
    # po=adr.author.name
    # print(po)     # 李四

    adr = models.AuthorDetail.objects.filter(addr="李四北京")
    for ads in adr:
       print(ads.author.name)    # 李四

        # 对应sql:
        #
        #    select publish_id from Book where title="python"
        #    select email from Publish where nid =   1










    # #####################基于queryset和__查询（join查询）############################

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



    # 查询python这本书籍的出版社的邮箱
    ret=models.Book.objects.filter(title="python").values("publish__email")   #  正向查询：按字段
    print(ret)      # < QuerySet[{'publish__email': '11111'}] >
    print(ret.query)
       # SELECT"myapp_publish"."email"FROM"myapp_book"INNERJOIN"myapp_publish"ON("myapp_book"."publish_id" = "myapp_publish"."nid")WHERE"myapp_book"."title" = python




    ret1 = models.Publish.objects.filter(name="四川出版社").values("book__title")    #  反向查询：表名小写
    print(ret1,"AAAAAAAAAAAAA")      # < QuerySet[{'book__title': 'java'}, {'book__title': 'css'}] > AAAAAAAAAAAAA
    print(ret1.query)   # 生成的sql语句
                        # SELECT "myapp_book"."title" FROM"myapp_publish"LEFT OUTERJOIN"myapp_book" ON("myapp_publish"."nid" = "myapp_book"."publish_id") WHERE"myapp_publish"."name" = 四川出版社

    # 查询王五的手机号
    # 方式1：
    ret = models.Author.objects.filter(name="王五").values("authorDetail__telephone")

    # 方式2：
    models.AuthorDetail.objects.filter(author__name="王五").values("telephone")


    # 查询手机号以151开头的作者出版过的书籍名称以及书籍对应的出版社名称
    ret = models.Book.objects.filter(authors__authorDetail__telephone__startswith="151").values('title',"publish__name")
    print(ret.query)



    # 重庆出版社出版的书籍名称
    # 方式1：
    ret1 = models.Publish.objects.filter(name="重庆出版社").values("book__title")
    print("111111111====>", ret1.query)
    # 方式2：
    ret2 = models.Book.objects.filter(publish__name="重庆出版社").values("title")
    print("2222222222====>", ret2.query)




    # #####################聚合函数############################


    # 查看所有书籍的价格总和
    r=models.Book.objects.all().aggregate(price=Sum("price"))
    print(r)   # {'price': Decimal('204000.00')}

    # 查看所有作者的平均年龄
    ae=models.Author.objects.all().aggregate(age=Avg("age"))
    print(ae)      # {'age': 30.75}





    # #####################分组查询############################

    rt=models.Author.objects.values("name")
    print(rt)     # < QuerySet[{'name': '张三'}, {'name': '李四'}, {'name': '王五'}, {'name': '哈哈'}] >
    # 查询每一个作者的名字以及出版过价格最高的书籍
    rc=models.Author.objects.values("name").annotate(max_p=Max("book__price")).values("name","max_p")
    print(rc,"wwwwwwwwwwwww")
    # <QuerySet [{'name': '哈哈', 'max_p': Decimal('60000.00')}, {'name': '张三', 'max_p': Decimal('80000.00')}, {'name': '李 四', 'max_p': Decimal('80000.00')}, {'name': '王五', 'max_p': Decimal('40000.00')}]>

    # 查询每一个出版社出版的书籍平均价格
    c=models.Publish.objects.values("name").annotate(aaa=Avg("book__price")).values("name","aaa")
    print(c,"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA")
    # <QuerySet [{'name': '南方出版社', 'aaa': 20000.0}, {'name': '四川出版社', 'aaa': 70000.0}, {'name': '重庆出版社', 'aaa': 22000.0}]>

    # 查询每一本书籍作者个数

    co= models.Book.objects.values("title").annotate(aa=Count("authors__name")).values("title","aa")
    print(co)

    # <QuerySet [{'title': 'c++', 'aa': 0}, {'title': 'css', 'aa': 2}, {'title': 'html', 'aa': 3}, {'title': 'java', 'aa': 2}, {'title': 'python', 'aa': 2}]>

    return HttpResponse("1111")