Django数据库模糊、统计查询

一、Django数据库模糊查询

1.1 Q查询

from django.db.models import Q
Q(question__startswith='What')

Q(question__startswith='Who') | Q(question__startswith='What')

#等同于以下SQL语句:
WHERE question LIKE 'Who%' OR question LIKE 'What%'
 

Q(question__startswith='Who') | ~Q(pub_date__year=2018)
 
 
Poll.objects.get(
    Q(question__startswith='Who'),
    Q(pub_date=date(2018, 8, 8)) | Q(pub_date=date(2018, 8, 6))
)
#等同于以下SQL语句:
 
SELECT * from polls WHERE question LIKE 'Who%'
    AND (pub_date = '2017-05-02' OR pub_date = '2009-05-06')
21
22 Problems.objects.filter(Q(tag__contains='search')).order_by("-weight")

 

---

 

1.2  大于、大于等于、小于、小于等于：

# __gt 大于>
# __gte 大于等于>=
# __lt 小于<
# __lte 小于等于<=

Model.objects.filter(age__gt=10) # 查询年龄大于10岁的人
Model.objects.filter(age__gte=10) # 查询年龄大于等于10岁的人
Model.objects.filter(age__lt=10) # 查询年龄小于10岁的人
Model.objects.filter(age__lte=10) # 查询年龄小于等于10岁的人

#不等于/不包含于
Model.objects.filter().excute(age=10)    # 查询年龄不为10的人
Model.objects.filter().excute(age__in=[10, 20])  # 查询年龄不在 [10, 20] 的人

---

 

1.3 filter中的模糊操作

# __exact 精确等于 like 'aaa'
# __iexact 精确等于 忽略大小写 ilike 'aaa'
# __contains 包含 like '%aaa%'
# __icontains 包含,忽略大小写 ilike '%aaa%'，但是对于sqlite来说，contains的作用效果等同于icontains。
# __startswith 以…开头
# __istartswith 以…开头 忽略大小写
# __endswith 以…结尾
# __iendswith 以…结尾，忽略大小写
# __range 在…范围内
# __year 日期字段的年份
# __month 日期字段的月份
# __day 日期字段的日

# 用法：
Model.objects.filter(title__exact="python") #等价于 title  like  'python'

Model.objects.filter(title__contains="python") #等价于 title  like  '%python%'

Model.objects.filter(title__icontains="python")  # 忽略大小写 

Model.objects.filter(title__startswith="py") # 等价于  title like 'py%'

Model.objects.filter(title__endswith="aa") # 等价于  title like '%aa'

Model.objects.filter(pub_date__year=2012) # 日期字段的2012年份

Model.objects.filter().excute(age=10) # 查询年龄不为10的人
Model.objects.filter().excute(age__in=[10, 20]) # 查询年龄不在 [10, 20] 的人

---

 

1.4 范围查询

# 例：查询1980年发表的图书。
BookInfo.objects.filter(bpub_date__year=1980)
# 例：查询1980年1月1日后发表的图书。
from datetime import date
BookInfo.objects.filter(bpub_date__gt=date(1980,1,1))

# g)range在...范围内
BookInfo.objects.filter(name__regex="^abc")  # 正则表达式查询
BookInfo.objects.filter(name__iregex="^abc")  # 正则表达式不区分大小写

# exclude（返回不满足条件的数据。）方法示例：
# 例：查询id不为3的图书信息。
BookInfo.objects.exclude(id=3)
User.objects.filter().excute(age=10) // 查询年龄不为10的用户
User.objects.filter().excute(age__in=[10, 20]) // 查询年龄不为在 [10, 20] 的用户

 

---

 

二、其他方法

all():	查询所有结果
filter(**kwargs):	它包含了与所给筛选条件相匹配的对象
get(**kwargs):	返回与所给筛选条件相匹配的对象，返回结果有且只有一个， 如果符合筛选条件的对象超过一个或者没有都会抛出错误。
exclude(**kwargs):	它包含了与所给筛选条件不匹配的对象
order_by(*field):	对查询结果排序 用法:order_by(’-price’) DESC 降序
reverse():	对查询结果反向排序
count():	返回数据库中匹配查询(QuerySet)的对象数量。
first():	返回第一条记录
last():	返回最后一条记录
exists():	如果QuerySet包含数据，就返回True，否则返回False 相当于limit 1(用途查询这个表中是否有值)
values(*field):	用法:Book.objects.all.values(‘title’,‘price’) 返回值是<queryset[{‘title’:‘aa’,‘pirce’:12},{}]
values_list(*field):	用法:Book.objects.all.values_list(‘title’,‘price’) 返回值是<queryset[(‘aa’,12),(‘bb’,33)]
distinct():	从返回结果中剔除重复纪录 用法:Book.objects.all.values(‘title’,‘price’).distinct() 错误用法 Book.objects.all.distinct() 因为id不相同,其他相同,无法去重

 

 

annotate(args, *kwargs)
 
# 使用提供的聚合表达式查询对象。
# 表达式可以是简单的值、对模型（或任何关联模型）上的字段的引用或者聚合表达式（平均值、总和等）。
# annotate()的每个参数都是一个annotation，它将添加到返回的QuerySet每个对象中。
# 关键字参数指定的Annotation将使用关键字作为Annotation 的别名。 匿名参数的别名将基于聚合函数的名称和模型的字段生成。 只有引用单个字段的聚合表达式才可以使用匿名参数。 其它所有形式都必须用关键字参数。
# 例如，如果正在操作一个Blog列表，你可能想知道每个Blog有多少Entry：
>>> from django.db.models import Count
>>> q = Blog.objects.annotate(Count('entry'))
# The name of the first blog
>>> q[0].name
'Blogasaurus'
# The number of entries on the first blog
>>> q[0].entry__count
42

---

 

 三、聚合查询

提到聚合函数，首先我们要知道的就是这些聚合函数是不能在django中单独使用的，要想在django中使用django聚合函数，就必须把这些聚合函数放到支持他们的方法内，这样才能执行。支持聚合函数的方法有两种，分别是aggregate和annotate，这两种方法执行的原生SQL以及结果都有很大的区别，下面我们以实例操作的方式一一介绍：

　　# 示例模型：
　　class Author(models.Model):
　　　　"""作者模型"""
　　　　name = models.CharField(max_length=100)
　　　　age = models.IntegerField()
　　　　email = models.EmailField()

　　class Book(models.Model):
　　　　"""图书模型"""
　　　　name = models.CharField(max_length=100)
　　　　author = models.ForeignKey(‘Author‘,on_delete=models.CASCADE)
　　　　price = models.FloatField()

　　class BookOrder(models.Model):
　　　　"""图书订单模型"""
　　　　book = models.ForeignKey(‘Book‘,on_delete=models.CASCADE)
　　　　sailprice = models.FloatField()
　　　　create_time = models.DateTimeField(auto_now_add=True)

　　1、aggregate：这个方法在执行聚合函数的时候，支持聚合连表(如使用ForeignKey)中的字段，使用于类QuerySet对象上，是对QuerySet整个对象的某个属性汇总，在汇总时不会使用该模型的主键进行group by进行分组，得到的是一个结果字典，例如提取所有作者的平均年龄，示例代码如下：
　　from django.db.models import Avg
　　from django.db import connection

　　result = Author.objects.aggregate(avg_age=Avg(‘age‘))
　　print(connection.queries) # 打印执行时所有的查询语句

　　2、annotate：这个函数在执行聚合函数的时候，会为QuerySet中的每个对象生成一个独立的摘要，我们可以看做为查询的模型增加一个新的属性，这个属性的值就是使用聚合函数所得到的值，在使用这个聚合函数的时候annotate会使用这个模型的主键进行group by进行分组，然后根据分组的结果进行聚合，这一点正符合为QuerySet中每个对象增加一个独立摘要的事实。使用这个方法执行聚合函数，得到的结果是一个QuerySet对象，结果依然能够调用filter()、order_by()甚至annotate()进行再次聚合，现在我想提取每一本书的平均销售的价格(注意销售价格在BookOrder表中)：
　　from django.db.models import Avg
　　from django.db import connection

　　books = Book.objects.annotate(avg=Avg(‘bookorder__sailprice‘))
　　for book in books:
　　print(‘%s/%s‘%(book.name,book.avg)) # 注意这里的avg属性就是annotate执行聚合函数得到的
　　print(connection.queries)

聚合函数：
　　在Django中，聚合函数都是在django.db.models模块下的，具体的聚合函数有Avg、Count、Max、Min、Sum，现在我们一一介绍这些函数的作用：
　　1、Avg：计算平均值，使用于与数值相关的字段，如果使用aggregate方法来执行这个函数，那么会得到一个字典，默认情况下，字典的键为field__avg，值为执行这个聚合函数所得到的值，示例代码如下：
　　# 计算所有作者的平均年龄
　　result = Author.objects.aggregate(Avg(‘age‘))
　　print(result) # 结果为：{"age__avg": 23.8}

　　如果想要使用自定义的键，那么可以把aggregate中的未知参数变为关键字参数，该关键字就是得到的键，示例代码如下：
　　result = Author.objects.aggregate(avgAge=Avg(‘age‘))
　　print(result) # 结果为：{"avgAge": 23.8}

　　如果使用annotate方法执行这个函数，那么得到的结果就是一个QuerySet对象，只不过这个对象中的每一个都会添加一个属性，这个属性的名称其实和上面的键一样，可以使用默认也可以自定义，使用方法与在aggregate中键名的定义一样，这里就不再赘述：
　　books = Book.objects.annotate(avg=Avg(‘bookorder__sailprice‘))
　　for book in books:
　　　　print(‘%s/%s‘%(book.name,book.avg)) # 注意这里的avg属性就是annotate执行聚合函数得到的
　　print(connection.queries)

　　2、Count：计算数量，基本用法与Avg相同，在使用这个聚合函数的时候可以传递一个distinct参数用来去重：
　　# 计算总共有多少个订单
　　result = BookOrder.objects.aggregate(total=Count(‘id‘,distanct=True))
　　print(result) # 结果为：{"total": 18}

　　# 计算每本书的订单量
　　books = Book.objects.annotate(total=Count(‘bookorder__id‘))
　　for book in books:
　　print(‘%s/%s‘%(book.name,book.total))

　　3、Max和Min：计算某个字段的最大值和最小值，用法与Avg一样

　　4、Sum：计算总和，用法与Avg一样

models.UserSubmit.objects.filter(visible=1).aggregate(Count("id"))
models.News.objects.filter(visible=0).aggregate(Count("new_id"))