流觞强烈向大家推荐一个好网站，【我要自学网】，教程由在校老师录制，有办公会计、平面设计、室内设计、机械设计、网页编程、影视动画等教程.....让你足不出门，都可以体验学校的专业教育！
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"))
