16. Django 2.1.7 模型 - 条件查询 F对象 Q对象 聚合查询
16. Django 2.1.7 模型 - 条件查询 F对象 Q对象 聚合查询
Devops海洋的渔夫
发布于 2022-01-17 09:57:29
发布于 2022-01-17 09:57:29
上一篇Django 2.1.7 模型 - 条件查询、模糊查询、空查询、比较查询、范围查询、日期查询讲述了关于Django模型的查询。
但是都是条件与常量的查询，以及单条件查询，那么本篇章来介绍F对象、Q对象、聚合查询等功能。
参考文献
https://docs.djangoproject.com/zh-hans/2.1/topics/db/queries/
F对象
之前的查询都是对象的属性与常量值比较，两个属性怎么比较呢？ 先来看看已有的mysql数据，如下：
mysql> select * from assetinfo_middlewareinfo;
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
| id | name | port | server_id | is_delete | shelves_date | update_time |
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
| 1 | memcached | 11211 | 1 | 1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:37:51.365155 |
| 2 | redis | 6379 | 1 | 1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:38:20.712862 |
| 3 | nginx | 80 | 2 | 1 | 2019-06-10 16:41:52.129517 | 2019-06-10 17:38:18.923155 |
| 4 | kafka | 9092 | 2 | 1 | 2019-06-10 16:42:25.561732 | 2019-06-10 17:39:29.302349 |
| 5 | test | 123 | 1 | 1 | 2019-06-10 17:05:16.632773 | 2019-06-10 17:05:16.632773 |
| 6 | test | 123 | 1 | 1 | 2019-06-10 17:06:20.120658 | 2019-06-10 17:06:20.121656 |
| 7 | test | 123 | 1 | 1 | 2019-06-10 17:06:43.193412 | 2019-06-10 17:06:43.193412 |
| 8 | test | 123 | 1 | 1 | 2019-06-10 17:07:03.747395 | 2019-06-10 17:07:03.747395 |
| 9 | test | 123 | 1 | 1 | 2019-06-10 17:08:43.372097 | 2019-06-10 17:08:43.372097 |
| 10 | test | 123 | 1 | 1 | 2019-06-10 17:09:37.877019 | 2019-06-10 17:09:37.877019 |
| 11 | test | 123 | 1 | 1 | 2019-06-10 17:11:45.403627 | 2019-06-10 17:11:45.403627 |
| 12 | mysql | 3306 | 2 | 0 | 2019-06-10 17:12:12.558217 | 2019-06-10 17:12:12.558217 |
| 13 | mongodb | 3388 | 2 | 1 | 2019-06-10 17:15:18.327729 | 2019-06-10 17:15:18.327729 |
| 14 | mongodb | 3306 | 1 | 1 | 2019-06-11 14:01:24.003175 | 2019-06-11 14:06:14.525648 |
| 15 | test | 123 | 1 | 0 | 2019-06-11 14:04:10.576241 | 2019-06-11 14:04:10.576241 |
| 16 | test | 3306 | 1 | 0 | 2019-06-11 14:06:05.608006 | 2019-06-11 14:06:05.608006 |
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
16 rows in set (0.00 sec)
可以看到上面的数据存在 shelves_date 与 update_time 不相等的情况，之前的常量比较SQL如下：
mysql> select * from assetinfo_middlewareinfo where shelves_date > "2019-06-10 17:38:20.712862" ;
+----+---------+------+-----------+-----------+----------------------------+----------------------------+
| id | name | port | server_id | is_delete | shelves_date | update_time |
+----+---------+------+-----------+-----------+----------------------------+----------------------------+
| 14 | mongodb | 3306 | 1 | 1 | 2019-06-11 14:01:24.003175 | 2019-06-11 14:06:14.525648 |
| 15 | test | 123 | 1 | 0 | 2019-06-11 14:04:10.576241 | 2019-06-11 14:04:10.576241 |
| 16 | test | 3306 | 1 | 0 | 2019-06-11 14:06:05.608006 | 2019-06-11 14:06:05.608006 |
+----+---------+------+-----------+-----------+----------------------------+----------------------------+
3 rows in set (0.00 sec)
mysql>
那么如果需要使用 shelves_date 与 update_time 进行大小比较，如下：
mysql> select * from assetinfo_middlewareinfo where shelves_date < update_time ;
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
| id | name | port | server_id | is_delete | shelves_date | update_time |
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
| 1 | memcached | 11211 | 1 | 1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:37:51.365155 |
| 2 | redis | 6379 | 1 | 1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:38:20.712862 |
| 3 | nginx | 80 | 2 | 1 | 2019-06-10 16:41:52.129517 | 2019-06-10 17:38:18.923155 |
| 4 | kafka | 9092 | 2 | 1 | 2019-06-10 16:42:25.561732 | 2019-06-10 17:39:29.302349 |
| 6 | test | 123 | 1 | 1 | 2019-06-10 17:06:20.120658 | 2019-06-10 17:06:20.121656 |
| 14 | mongodb | 3306 | 1 | 1 | 2019-06-11 14:01:24.003175 | 2019-06-11 14:06:14.525648 |
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
6 rows in set (0.00 sec)
mysql>
那么这种SQL按照上一篇的内容是无法实现的，下面来介绍F对象来解决这个问题。
语法如下：
F(属性名)
使用F对象需要导入库，如下：
from django.db.models import F
下面使用模型来查询 shelves_date < update_time 的结果，如下：
In [4]: from assetinfo.models import ServerInfo,MiddlewareInfo
In [5]: from django.db.models import F
In [6]: MiddlewareInfo.objects.filter( shelves_date__lt = F('update_time') )
Out[6]: <QuerySet [<MiddlewareInfo: MiddlewareInfo object (1)>, <MiddlewareInfo: MiddlewareInfo object (2)>, <MiddlewareInfo: MiddlewareInfo object (3)>, <MiddlewareInfo: Middlewar
eInfo object (4)>, <MiddlewareInfo: MiddlewareInfo object (6)>, <MiddlewareInfo: MiddlewareInfo object (14)>]>
In [7]:
对应执行的SQL如下：
2019-06-12T15:19:37.735397Z 12 Query SELECT `assetinfo_middlewareinfo`.`id`, `assetinfo_middlewareinfo`.`name`, `assetinfo_middlewareinfo`.`port`, `assetinfo_middlewareinfo`.`server_id`, `assetinfo_middlewareinfo`.`shelves_date`, `assetinfo_middlewareinfo`.`update_time`, `assetinfo_middlewareinfo`.`is_delete` FROM `assetinfo_middlewareinfo` WHERE `assetinfo_middlewareinfo`.`shelves_date` < (`assetinfo_middlewareinfo`.`update_time`) LIMIT 21
可以看到最后的where条件是
`assetinfo_middlewareinfo`.`shelves_date` < (`assetinfo_middlewareinfo`.`update_time`) LIMIT 21
那么如果对于比较的变量还要乘以2倍，例如：
mysql> select * from assetinfo_middlewareinfo where shelves_date < (update_time*2) ;
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
| id | name | port | server_id | is_delete | shelves_date | update_time |
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
| 1 | memcached | 11211 | 1 | 1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:37:51.365155 |
| 2 | redis | 6379 | 1 | 1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:38:20.712862 |
| 3 | nginx | 80 | 2 | 1 | 2019-06-10 16:41:52.129517 | 2019-06-10 17:38:18.923155 |
| 4 | kafka | 9092 | 2 | 1 | 2019-06-10 16:42:25.561732 | 2019-06-10 17:39:29.302349 |
| 5 | test | 123 | 1 | 1 | 2019-06-10 17:05:16.632773 | 2019-06-10 17:05:16.632773 |
| 6 | test | 123 | 1 | 1 | 2019-06-10 17:06:20.120658 | 2019-06-10 17:06:20.121656 |
| 7 | test | 123 | 1 | 1 | 2019-06-10 17:06:43.193412 | 2019-06-10 17:06:43.193412 |
| 8 | test | 123 | 1 | 1 | 2019-06-10 17:07:03.747395 | 2019-06-10 17:07:03.747395 |
| 9 | test | 123 | 1 | 1 | 2019-06-10 17:08:43.372097 | 2019-06-10 17:08:43.372097 |
| 10 | test | 123 | 1 | 1 | 2019-06-10 17:09:37.877019 | 2019-06-10 17:09:37.877019 |
| 11 | test | 123 | 1 | 1 | 2019-06-10 17:11:45.403627 | 2019-06-10 17:11:45.403627 |
| 12 | mysql | 3306 | 2 | 0 | 2019-06-10 17:12:12.558217 | 2019-06-10 17:12:12.558217 |
| 13 | mongodb | 3388 | 2 | 1 | 2019-06-10 17:15:18.327729 | 2019-06-10 17:15:18.327729 |
| 14 | mongodb | 3306 | 1 | 1 | 2019-06-11 14:01:24.003175 | 2019-06-11 14:06:14.525648 |
| 15 | test | 123 | 1 | 0 | 2019-06-11 14:04:10.576241 | 2019-06-11 14:04:10.576241 |
| 16 | test | 3306 | 1 | 0 | 2019-06-11 14:06:05.608006 | 2019-06-11 14:06:05.608006 |
+----+-----------+-------+-----------+-----------+----------------------------+----------------------------+
16 rows in set (0.00 sec)
使用模型的F对象也是可以直接乘以 2 倍处理的，如下：
In [8]: MiddlewareInfo.objects.filter( shelves_date__lt = F('update_time') * 2 )
Out[8]: <QuerySet [<MiddlewareInfo: MiddlewareInfo object (1)>, <MiddlewareInfo: MiddlewareInfo object (2)>, <MiddlewareInfo: MiddlewareInfo object (3)>, <MiddlewareInfo: Middlewar
eInfo object (4)>, <MiddlewareInfo: MiddlewareInfo object (5)>, <MiddlewareInfo: MiddlewareInfo object (6)>, <MiddlewareInfo: MiddlewareInfo object (7)>, <MiddlewareInfo: Middlewar
eInfo object (8)>, <MiddlewareInfo: MiddlewareInfo object (9)>, <MiddlewareInfo: MiddlewareInfo object (10)>, <MiddlewareInfo: MiddlewareInfo object (11)>, <MiddlewareInfo: Middlew
areInfo object (12)>, <MiddlewareInfo: MiddlewareInfo object (13)>, <MiddlewareInfo: MiddlewareInfo object (14)>, <MiddlewareInfo: MiddlewareInfo object (15)>, <MiddlewareInfo: Mid
dlewareInfo object (16)>]>
对应的SQL语句如下：
2019-06-12T15:26:57.158671Z 12 Query SELECT `assetinfo_middlewareinfo`.`id`, `assetinfo_middlewareinfo`.`name`, `assetinfo_middlewareinfo`.`port`, `assetinfo_middlewareinfo`.`server_id`, `assetinfo_middlewareinfo`.`shelves_date`, `assetinfo_middlewareinfo`.`update_time`, `assetinfo_middlewareinfo`.`is_delete` FROM `assetinfo_middlewareinfo` WHERE `assetinfo_middlewareinfo`.`shelves_date` < ((`assetinfo_middlewareinfo`.`update_time` * 2)) LIMIT 21
可以看到where条件是
`assetinfo_middlewareinfo`.`shelves_date` < ((`assetinfo_middlewareinfo`.`update_time` * 2)) LIMIT 21
Q对象
前面的查询可以看到都是单条件查询，并没有多个条件查询。 例如：执行mysql示例如下：
mysql> select * from assetinfo_middlewareinfo where server_id = 2 and shelves_date < update_time ;
+----+-------+------+-----------+-----------+----------------------------+----------------------------+
| id | name | port | server_id | is_delete | shelves_date | update_time |
+----+-------+------+-----------+-----------+----------------------------+----------------------------+
| 3 | nginx | 80 | 2 | 1 | 2019-06-10 16:41:52.129517 | 2019-06-10 17:38:18.923155 |
| 4 | kafka | 9092 | 2 | 1 | 2019-06-10 16:42:25.561732 | 2019-06-10 17:39:29.302349 |
+----+-------+------+-----------+-----------+----------------------------+----------------------------+
2 rows in set (0.00 sec)
mysql>
可以从上面的where条件看到有两个过滤条件。 第一个则是 server_id = 2 ， 第二个则是 shelves_date < update_time 那么再模型中，怎么写出来呢？
可以使用Q对象来实现，用法如下：
from django.db.models import Q
Q(属性名__运算符=值) & Q(属性名__运算符=值) ==> and
Q(属性名__运算符=值) | Q(属性名__运算符=值) ==> or
~Q(属性名__运算符=值) ==> not
使用模型编写该示例，如下：
In [1]: from assetinfo.models import ServerInfo,MiddlewareInfo
In [2]: from django.db.models import F,Q
In [3]:
In [5]: MiddlewareInfo.objects.filter( Q( server_id__exact = 2 ) & Q( shelves_date__lt = F('update_time') ) )
Out[5]: <QuerySet [<MiddlewareInfo: MiddlewareInfo object (3)>, <MiddlewareInfo: MiddlewareInfo object (4)>]>
In [6]:
对应sql如下：
2019-06-13T15:43:47.042246Z 14 Query SELECT `assetinfo_middlewareinfo`.`id`, `assetinfo_middlewareinfo`.`name`, `assetinfo_middlewareinfo`.`port`, `assetinfo_middlewareinfo`.`server_id`, `assetinfo_middlewareinfo`.`shelves_date`, `assetinfo_middlewareinfo`.`update_time`, `assetinfo_middlewareinfo`.`is_delete` FROM `assetinfo_middlewareinfo` WHERE (`assetinfo_middlewareinfo`.`server_id` = 2 AND `assetinfo_middlewareinfo`.`shelves_date` < (`assetinfo_middlewareinfo`.`update_time`)) LIMIT 21
上面是and条件的示例，那么现在来执行一下 or ，如下：
In [6]: MiddlewareInfo.objects.filter( Q( server_id__exact = 2 ) | Q( shelves_date__lt = F('update_time') ) )
Out[6]: <QuerySet [<MiddlewareInfo: MiddlewareInfo object (1)>, <MiddlewareInfo: MiddlewareInfo object (2)>, <MiddlewareInfo: M
iddlewareInfo object (3)>, <MiddlewareInfo: MiddlewareInfo object (4)>, <MiddlewareInfo: MiddlewareInfo object (6)>, <Middlewar
eInfo: MiddlewareInfo object (12)>, <MiddlewareInfo: MiddlewareInfo object (13)>, <MiddlewareInfo: MiddlewareInfo object (14)>]
>
对应的SQL如下：
2019-06-13T15:47:09.063544Z 14 Query SELECT `assetinfo_middlewareinfo`.`id`, `assetinfo_middlewareinfo`.`name`, `assetinfo_middlewareinfo`.`port`, `assetinfo_middlewareinfo`.`server_id`, `assetinfo_middlewareinfo`.`shelves_date`, `assetinfo_middlewareinfo`.`update_time`, `assetinfo_middlewareinfo`.`is_delete` FROM `assetinfo_middlewareinfo` WHERE (`assetinfo_middlewareinfo`.`server_id` = 2 OR `assetinfo_middlewareinfo`.`shelves_date` < (`assetinfo_middlewareinfo`.`update_time`)) LIMIT 21
最后再写一个not关系的，如下：
In [7]: MiddlewareInfo.objects.filter( ~Q( server_id__exact = 2 ) )
Out[7]: <QuerySet [<MiddlewareInfo: MiddlewareInfo object (1)>, <MiddlewareInfo: MiddlewareInfo object (2)>, <MiddlewareInfo: M
iddlewareInfo object (5)>, <MiddlewareInfo: MiddlewareInfo object (6)>, <MiddlewareInfo: MiddlewareInfo object (7)>, <Middlewar
eInfo: MiddlewareInfo object (8)>, <MiddlewareInfo: MiddlewareInfo object (9)>, <MiddlewareInfo: MiddlewareInfo object (10)>, <
MiddlewareInfo: MiddlewareInfo object (11)>, <MiddlewareInfo: MiddlewareInfo object (14)>, <MiddlewareInfo: MiddlewareInfo obje
ct (15)>, <MiddlewareInfo: MiddlewareInfo object (16)>]>
对应的执行SQL如下：
mysql> SELECT `assetinfo_middlewareinfo`.`id`, `assetinfo_middlewareinfo`.`name`, `assetinfo_middlewareinfo`.`port`, `assetinfo_middlewareinfo`.`server_id`, `assetinfo_middlewareinfo`.`shelves_date`, `assetinfo_middlewareinfo`.`update_time`, `assetinfo_middlewareinfo`.`is_delete` FROM `assetinfo_middlewareinfo` WHERE NOT (`assetinfo_middlewareinfo`.`server_id` = 2) LIMIT 21;
+----+-----------+-------+-----------+----------------------------+----------------------------+-----------+
| id | name | port | server_id | shelves_date | update_time | is_delete |
+----+-----------+-------+-----------+----------------------------+----------------------------+-----------+
| 1 | memcached | 11211 | 1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:37:51.365155 | 1 |
| 2 | redis | 6379 | 1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:38:20.712862 | 1 |
| 5 | test | 123 | 1 | 2019-06-10 17:05:16.632773 | 2019-06-10 17:05:16.632773 | 1 |
| 6 | test | 123 | 1 | 2019-06-10 17:06:20.120658 | 2019-06-10 17:06:20.121656 | 1 |
| 7 | test | 123 | 1 | 2019-06-10 17:06:43.193412 | 2019-06-10 17:06:43.193412 | 1 |
| 8 | test | 123 | 1 | 2019-06-10 17:07:03.747395 | 2019-06-10 17:07:03.747395 | 1 |
| 9 | test | 123 | 1 | 2019-06-10 17:08:43.372097 | 2019-06-10 17:08:43.372097 | 1 |
| 10 | test | 123 | 1 | 2019-06-10 17:09:37.877019 | 2019-06-10 17:09:37.877019 | 1 |
| 11 | test | 123 | 1 | 2019-06-10 17:11:45.403627 | 2019-06-10 17:11:45.403627 | 1 |
| 14 | mongodb | 3306 | 1 | 2019-06-11 14:01:24.003175 | 2019-06-11 14:06:14.525648 | 1 |
| 15 | test | 123 | 1 | 2019-06-11 14:04:10.576241 | 2019-06-11 14:04:10.576241 | 0 |
| 16 | test | 3306 | 1 | 2019-06-11 14:06:05.608006 | 2019-06-11 14:06:05.608006 | 0 |
+----+-----------+-------+-----------+----------------------------+----------------------------+-----------+
12 rows in set (0.00 sec)
聚合函数
使用aggregate()过滤器调用聚合函数。聚合函数包括：Avg，Count，Max，Min，Sum，被定义在django.db.models中。
首先执行一个sql的聚合函数，如下：
mysql> select count(1) from assetinfo_middlewareinfo;
+----------+
| count(1) |
+----------+
| 16 |
+----------+
1 row in set (0.00 sec)
mysql>
在日常的业务中，经常有统计表数量的情况，那么模型需要怎么写呢？如下：
In [9]: MiddlewareInfo.objects.count()
Out[9]: 16
那么sum方法呢？如下：
mysql> select sum(server_id) from assetinfo_middlewareinfo;
+----------------+
| sum(server_id) |
+----------------+
| 20 |
+----------------+
1 row in set (0.00 sec)
对应模型如下：
In [14]: from django.db.models import Sum
In [15]: MiddlewareInfo.objects.aggregate(Sum('server_id'))
Out[15]: {'server_id__sum': Decimal('20')}
In [16]:
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-09-20，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。