3.18 数据查询
3.18.1 两个字段之间的对比
首先需要导入
from django.db.models import F
def choicebook(request):
ret1 = BookInfo.objects.filter(count__gt=F('seltcount'))
print(ret1)
ret2 = BookInfo.objects.filter(seltcount__exact=F('count')*2)
print(ret2)
ret3 = BookInfo.objects.filter(seltcount__exact=F('count')*2,seltcount__gt=0)
print(ret3)
ret4 = BookInfo.objects.filter(seltcount__exact=F('count') * 2).filter(seltcount__gt=0)
print(ret4)
return HttpResponse('这是查找数据')
展示:
<QuerySet [<BookInfo: xas>]>
<QuerySet [<BookInfo: 西游记>, <BookInfo: 红楼梦>, <BookInfo: 三国演义>, <BookInfo: 水浒传>, <BookInfo: wo>, <BookInfo: 我写的>]>
<QuerySet [<BookInfo: 我写的>]>
<QuerySet [<BookInfo: 我写的>]>
注意点:
F对象是用来两个属性之间的对比
3.18.2 各个关系的与或非操作
首先需要导入
from django.db.models import Q
def choicebook(request):
# todo 实现逻辑的或关系,也就是or
# Q是逻辑关系
# 找出销量大于0的书籍
ret5 = BookInfo.objects.filter(seltcount__gt=0)
ret6 = BookInfo.objects.filter(Q(seltcount__gt=0))
print('ret5',ret5)
print('ret6',ret6)
#查找销量大于0 或者 阅读量小于0的书籍
ret7 = BookInfo.objects.filter(Q(seltcount__gt=0) | Q(count__lt=0))
print('ret7',ret7)
# 查找销量大于0 且 阅读量小于0的书籍
ret8 = BookInfo.objects.filter(Q(seltcount__gt=0) & Q(count__lt=0))
print('ret8',ret8)
# 查找销量不等于0的书籍
ret9 = BookInfo.objects.filter(~Q(seltcount__exact=0))
print('ret9',ret9)
return HttpResponse('这是查找数据')
展示:
ret5 <QuerySet [<BookInfo: 我写的>]>
ret6 <QuerySet [<BookInfo: 我写的>]>
ret7 <QuerySet [<BookInfo: 我写的>, <BookInfo: 再来一本>]>
ret8 <QuerySet []>
ret9 <QuerySet [<BookInfo: 我写的>]>
注意点:
Q对象用来表示各个关系的与或非的操作, & | ~
3.18.3 聚合函数
from django.db.models import Sum,Avg,Count,Min,Max,StdDev
def choicebook(request):
print('#######')
ret10 = BookInfo.objects.count()
print('ret10',ret10)
ret11 = BookInfo.objects.aggregate(Sum('count'))
print('ret11',ret11)
return HttpResponse('这是查找数据')
展示:
ret10 8
ret11 {'count__sum': 100}
3.18.4 排序
def choicebook(request):
ret12 = BookInfo.objects.all().order_by('seltcount')
print('ret12',ret12)
return HttpResponse('这是查找数据')
展示:
ret12 <QuerySet [<BookInfo: 西游记>, <BookInfo: 红楼梦>, <BookInfo: 三国演义>, <BookInfo: 水浒传>, <BookInfo: wo>, <BookInfo: xas>, <BookInfo: 再来一本>, <BookInfo: 我写的>]>
3.18.5 存在外键的两表关联查询
3.18.5.1 一对多
def bookinfo1(request):
try:
book = BookInfo.objects.get(bookname='西游记')
print(book)
peoples = book.personinfo_set.all()
print('西游记', peoples)
except Exception as e:
print(e)
return HttpResponse('OK')
展示:
西游记
西游记 <QuerySet [<PersonInfo: 孙悟空>, <PersonInfo: 唐僧>, <PersonInfo: 猪八戒>, <PersonInfo: 沙僧>]>
3.18.5.2 多对一
def bookinfo1(request):
try:
obj = PersonInfo.objects.get(pname='孙悟空')
print(obj)
print(obj.book)
print(obj.book_id)
print(obj.book.id)
except Exception as e:
print(e)
return HttpResponse('OK')
展示:
孙悟空
西游记
1
1
3.18.5.3 关联过滤查询
def bookinfo1(request):
'''
select * from bookinfo where id=(select book_id from personinfo where pname='孙悟空')
'''
obj1 = PersonInfo.objects.get(pname='孙悟空')
print(obj1.book_id)
book_obj = BookInfo.objects.get(id=obj1.book_id)
print(book_obj)
book_obj2 = BookInfo.objects.filter(personinfo__pname__exact='孙悟空')
print(book_obj2)
person_obj = PersonInfo.objects.filter(book__seltcount__gt=0)
print(person_obj)
return HttpResponse('OK')
展示:
1
西游记
<QuerySet [<BookInfo: 西游记>]>
<QuerySet []>