今天所做的努力
都是在为明天积蓄力量

Django之orm模型2

from django.shortcuts import render,HttpResponse,redirect,reverse
from app02.models import *
# Create your views here.
def index(request):

    """
    :param request:
    :return:
    """
    # datas1 = [
    #     {
    #         'birthday':'2018-01-12',
    #         'telphone':18111111111,
    #         'addr':'chegndu'
    #     },
    #     {
    #         'birthday': '2017-02-12',
    #         'telphone': 18111111112,
    #         'addr': 'neijiang'
    #     }
    # ]
    #
    # for data1 in datas1:
    #     AuthorDetail.objects.create(**data1)

    # msg = 'AuthorDetail添加记录成功'

    # datas2 = [
    #     {
    #         'name':'ropon',
    #         'age':18,
    #         'authorDetail_id':1
    #     },
    #     {
    #         'name': 'pengge',
    #         'age': 20,
    #         'authorDetail_id': 2
    #     }
    # ]
    # for data2 in datas2:
    #     Author.objects.create(**data2)

    # msg = 'Author添加记录成功'
    
    # datas3 = [
    #     {
    #         'name':'XXX出版社',
    #         'city':'chengdu',
    #         'email':'chengdu@ropon.top'
    #     },
    #     {
    #         'name': 'YYY出版社',
    #         'city': 'meishan',
    #         'email': 'meishan@ropon.top'
    #     }
    # ]
    #
    # for data3 in datas3:
    #     Publish.objects.create(**data3)

    # msg = 'Publish添加记录成功'

    # 一对多 插入记录方式一
    # publish_obj = Publish.objects.get(id=1) # publish_obj是model对象
    # print(publish_obj.city)
    # print(publish_obj.email)
    # msg = publish_obj.name
    # book_obj = Book.objects.create(
    #     title='Python从入门到精通',
    #     pub_date='2016-11-10',
    #     price=120,
    #     publish=publish_obj
    # )
    # print(book_obj.publish)

    # 一对多 插入记录方式二 先到publish表查出对应id 然后赋给publish_id
    # book_obj = Book.objects.create(
    #     title='Django从入门到实战',
    #     pub_date='2017-11-10',
    #     price=110,
    #     publish_id=2
    # )
    # msg = 'Book添加记录成功'

    # 多对多
    # book_obj = Book.objects.create(
    #     title='Go从入门到精通',
    #     pub_date='2016-12-10',
    #     price=100,
    #     publish_id=2
    # )
    # # ropon = Author.objects.filter(name='ropon').first() # 得到的是model对象
    # pengge = Author.objects.filter(name='pengge').first() # 得到的是model对象
    # book_obj.authors.add() 将某些特定的model对象添加到被关联对象集合中
    # 方式一
    # book_obj.authors.add(ropon,pengge)
    # 方式二
    # book_obj.authors.add(1, 2)
    # 方式三
    # book_obj.authors.add(*[1, 2])
    # # book_obj.authors.add(ropon)  # book_obj.authors.add(*[])
    # book_obj.authors.add(pengge)  # book_obj.authors.add(*[])
    # print(book_obj.authors.all()) # <QuerySet [<Author: ropon>]>
    # print(book_obj.authors.all().first().age)
    # msg = 'Book多对多模型 添加记录成功'

    # book_obj.authors.remove() 将某些特定的model对象从被关联对象集合中移除
    # book_obj.authors.clear() 清空被关联对象集合
    # book_obj.authors.set() 先清空被关联对象集合再设置

    # msg = 'ok'
    # return HttpResponse(msg)
    return redirect(reverse("app02:books"))

def apiquery(request):

    """
    基于对象的跨表查询
    一对多
    正向查询是 关联属性所在的表 查询 关联记录 反之 是反向查询

            ---正向查询 按字段 Book.publish--->
    Book ----------------------------------------- Publish
            <---反向查询  按表名小写_set.all() pub_obj.book_set.all()

    多对多

            ---正向查询 按字段 Book.authors.all()--->
    Book -------------------------------------------------- Author
            <---反向查询  按表名小写_set.all() author_obj.book_set.all()

    一对一

             ----正向查询 按字段 Author.authorDetail--->
    Author ----------------------------------------------- AuthorDetail
            <---反向查询  按表名小写 AuthorDetail_obj.author
    """
    # 一对多查询
    # 查询id为1的书籍的出版社所在城市
    # 正向查询 按字段publish
    # book_obj = Book.objects.get(id=1) # 得到的是model对象
    # print(book_obj.publish) #得到的是id为1的书籍关联的出版社对象
    # print(book_obj.publish.city)

    # 反向查询 查询XXX出版社 出版的书籍名称
    # 默认book_set 可以通过ForeignKey() ManyToManyField() 定义related_name 修改 比如以下代码
    # publish = models.ForeignKey(to='Publish', to_field='id', on_delete=models.CASCADE, related_name=booklist)
    # publish = Publish.objects.get(name='XXX出版社') # 得到的是model对象
    # book_list = publish.book_set.all() # book_set.all() # 与XXX出版社关联的所有数据对象集合
    # print(book_list)
    # for obj in book_list:
    #     print(obj.title)

    # 多对多查询
    # 正向查询 按字段authors 查询PHP从入门到实战 所有作者的姓名及手机号
    # book_obj = Book.objects.filter(title='PHP从入门到实战').first()
    # authors = book_obj.authors.all() # 与这本书关联的所有作者的queryset集合
    # for author_obj in authors:
    #     print(author_obj.name,author_obj.authorDetail.telphone)

    # 反向查询 查询ropon出版过的书籍
    # author_obj = Author.objects.get(name='ropon') # 得到的是model对象
    # book_list = author_obj.book_set.all() # 与ropon作者关联的所有书籍
    # for book_obj in book_list:
    #     print(book_obj.title)

    """
    
    基于双下划线的跨表查询(基于join实现的)
    
    正向查询 按字段  反向查询 按表名小写
    
    """
    # 1.查询python这本书的出版社名称
    # 基于对象跨表查询
    # ret = Book.objects.filter(title='python').first().publish.name
    # print(ret)
    # 基于双下划线跨表查询
    # ret = Book.objects.filter(title='python').values_list('price')
    # 正向
    # ret = Book.objects.filter(title='python').values_list('publish__name')
    # print(ret)
    # 反向
    # ret = Publish.objects.filter(book__title='python').values_list('name')
    # print(ret)

    # 2.查询YYY出版社出版过所有书籍的名称
    # 基于对象跨表查询
    # ret = Publish.objects.filter(name='YYY出版社').first().book_set.all()
    # for ret_obj in ret:
    #     print(ret_obj.title)
    # 基于双下划线跨表查询
    # ret = Book.objects.filter(publish__name='YYY出版社').values_list('title')
    # print(ret)

    # 3.查询php这本书籍的作者的年龄
    # 基于对象跨表查询
    # ret = Book.objects.filter(title='php').first().authors.all()
    # for ret_obj in ret:
    #     print(ret_obj.age)
    # 基于双下划线跨表查询
    # ret = Book.objects.filter(title='php').values_list('authors__name')
    # print(ret)

    # 4.查询ropon出版过的所有书籍的名称
    # 基于对象跨表查询
    # ret = Author.objects.filter(name='ropon').first().book_set.all()
    # for ret_obj in ret:
    #     print(ret_obj.title)
    # 基于双下划线跨表查询
    # 正向
    # ret = Book.objects.filter(authors__name='ropon').values_list('title')
    # print(ret)
    # 反向
    # ret = Author.objects.filter(name='ropon').values_list('book__title')
    # print(ret)

    # 5.查询pengge的手机号
    # 基于对象跨表查询
    # ret = Author.objects.filter(name='pengge').first().authorDetail.telphone
    # print(ret)
    # 基于双下划线跨表查询
    # 正向
    # ret = Author.objects.filter(name='pengge').values_list('authorDetail__telphone')
    # print(ret)
    # 反向
    # ret = AuthorDetail.objects.filter(author__name='pengge').values_list('telphone')
    # print(ret)

    # 6.查询手机号为17111111113 作者的姓名
    # 基于对象跨表查询
    # ret = AuthorDetail.objects.filter(telphone=17111111113).first().author
    # print(ret.name)
    # 基于双下划线跨表查询
    # 正向
    # ret = Author.objects.filter(authorDetail__telphone=17111111113).values_list('name')
    # print(ret)
    # 反向
    # ret = AuthorDetail.objects.filter(telphone=17111111113).values_list('author__name')
    # print(ret)

    """
    
    连续跨表查询
    
    """
    # 1.查询YYY出版社 出版过的所有书籍的名字及作者
    # 正向
    # ret = Book.objects.filter(publish__name='YYY出版社').values_list('title','authors__name')
    # print(ret)
    # 反向
    # ret = Publish.objects.filter(name='YYY出版社').values_list('book__title','book__authors__name')
    # print(ret)

    # 2.查询手机以151开头 的作者出版过 的所有书籍名称及出版社名称
    # 正向
    # ret = Book.objects.filter(authors__authorDetail__telphone__startswith='151').values_list('title','publish__name')
    # print(ret)
    # 反向
    # ret = Author.objects.filter(authorDetail__telphone__startswith='151').values_list('book__title','book__publish__name')
    # print(ret)
    # ret = AuthorDetail.objects.filter(telphone__startswith='151').\
    #     values_list('author__book__title','author__book__publish__name')
    # print(ret)

    """
    
    聚合查询
    
    """
    from django.db.models import Avg, Max, Min, Count, Sum
    # aggregate() 是QuerySet的一个终止子句 返回的一个包含一些键值对的字典
    # 键的名称是聚合值的标识符 值是计算出来的聚合值
    # ret = Book.objects.all().aggregate(Avg('price'))
    # print(ret)
    # 如果你想要为聚合值指定一个名称 可以向聚合子句提供它
    # ret = Book.objects.all().aggregate(avg_price=Avg('price'))
    # print(ret)

    # Max Min 求最大值及最小值 Count 计数 Sum 求和
    # ret = Book.objects.all().aggregate(Avg('price'),Max('price'),Min('price'),Count('id'))
    # print(ret)

    # 分组查询 annotate() 为调用的QuerySet中每一个对象都生成一个独立的统计值
    # 单表分组查询
    # 1.查询每个出版社(id)及对应书籍的个数
    # annotate() 前values('字段名') 是哪个字段就按其字段分组
    # ret = Book.objects.values('publish_id').annotate(BookCounts = Count('id'))
    # print(ret)

    # 跨表分组查询
    # 跨表分组查询本质就是将关联表join成一张表 然后按单表思路进行分组查询
    # 2.查询每个出版社的名称及对应书籍的平均价格
    # 方式一
    # ret = Book.objects.values('publish_id').annotate(AvgPrice = Avg('price')).values_list('publish__name','AvgPrice')
    # print(ret)
    # 方式二
    # ret = Publish.objects.values('name').annotate(AvgPrice = Avg('book__price')).values_list('name','AvgPrice')
    # print(ret)

    # 3.查询每个作者的名字及出版的书籍最高价格
    # ret = Author.objects.values('name').annotate(MaxPrice=Max('book__price'))
    # print(ret)

    # 练习
    # 1.统计每个出版社 的最便宜的 书
    # 方式一
    # publish_list = Publish.objects.annotate(Min_Price=Min('book__price'))
    # for publish_obj in publish_list:
    #     print(publish_obj.name,publish_obj.Min_Price)

    # 方式二
    # ret = Publish.objects.annotate(Min_Price=Min('book__price')).values_list('name','Min_Price')
    # print(ret)

    # 2.统计每一本书 的作者个数
    # ret = Book.objects.annotate(AuthorsNum=Count('authors__name')).values_list('title','AuthorsNum')
    # print(ret)

    # 3.统计每一本以Py开头 的书籍 的作者个数
    # ret = Book.objects.filter(title__startswith='Py').annotate(AuthorsNum=Count('authors__name')).\
    #     values_list('title','AuthorsNum')
    # print(ret)

    # 4.统计 不止一个作者 的书籍名称
    # ret = Book.objects.annotate(AuthorsNum=Count('authors__name')).filter(AuthorsNum__gt=1).values_list('title')
    # print(ret)

    # 5.根据一个图书作者个数对QuerySet进行排序
    # ret = Book.objects.annotate(AuthorsNum=Count('authors__name')).order_by('AuthorsNum') # 升序
    # print(ret)

    # 6.查询每个作者出版的书籍 的总价格
    # ret = Author.objects.annotate(SumPrice=Sum('book__price')).values_list('name','SumPrice')
    # print(ret)

    """
    F/Q查询
    
    """
    # F() 一个模型中2个字段比较
    from django.db.models import F,Q
    # 查询评论数大于收藏数的 书籍 的名称
    # ret = Book.objects.filter(commnetNum__gt=F('keepNum')).values_list('title')
    # print(ret)

    # F()对象之间及F()对象和常数之间的加减乘除和取模等操作
    # ret = Book.objects.filter(commnetNum__gt=F('keepNum')*2)
    # print(ret)

    # 更新操作也可以使用F函数 比如每本书的价格提高10元
    # ret = Book.objects.all().update(price=F('price')+10) # 返回值影响的记录数
    # print(ret)

    # Q查询
    # Q对象可以使用&和|操作符组合 产生一个新的Q对象 & 和的意思  | 或的意思 ~ 取反的意思
    # 查询ropon或者pengge作者 出版的书籍 的名称
    ret = Book.objects.filter(Q(authors__name='ropon')|Q(authors__name='pengge'))
    print(ret)
    # 查询ropon和pengge作者 出版的书籍 的名称
    ret = Book.objects.filter(authors__name='ropon').filter(authors__name='pengge')
    print(ret)

    msg = 'query ok'
    return HttpResponse(msg)

def books(request):
    book_list = Book.objects.all()
    # print(book_list)
    return render(request,'app02/books.html',{'book_list':book_list})

def addbook(request):
    if request.method == 'POST':
        title = request.POST.get('title')
        pub_date = request.POST.get('pub_date')
        price = request.POST.get('price')
        publish_id = request.POST.get('publish_id')
        authors = request.POST.getlist('authors')
        # print(request.POST)
        # print(authors)
        # print(pub_date)

        book = Book.objects.create(
            title=title,
            pub_date=pub_date,
            price=price,
            publish_id=publish_id # 会自动处理成数字
        )
        book.authors.add(*authors)
        return redirect(reverse("app02:books"))
    else:
        pub_list = Publish.objects.all()
        # print(pub_list)
        authors_list = Author.objects.all()
        # print(authors_list)
        return render(request, 'app02/addbook.html', {'pub_list': pub_list,'authors_list':authors_list})

def editbook(request,eid):
    edit_book = Book.objects.filter(id=eid).first()
    print(edit_book)
    if request.method == 'POST':
        title = request.POST.get('title')
        pub_date = request.POST.get('pub_date')
        price = request.POST.get('price')
        publish_id = request.POST.get('publish_id')
        authors = request.POST.getlist('authors')

        Book.objects.filter(id=eid).update(
            title=title,
            pub_date=pub_date,
            price=price,
            publish_id=publish_id # 会自动处理成数字
        )
        edit_book.authors.set(authors)
        return redirect(reverse("app02:books"))
    else:
        pub_list = Publish.objects.all()
        authors_list = Author.objects.all()

        return render(request, 'app02/editbook.html', {'pub_list': pub_list,'authors_list':authors_list,'edit_book':edit_book})

def delbook(request,did):
    Book.objects.filter(id=did).delete()
    return redirect(reverse("app02:books"))
赞(5)
未经允许不得转载:流觞 » Django之orm模型2
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址