15. Django 2.1.7 模型 - 条件查询、模糊查询、空查询、比较查询、范围查询、日期查询

Devops海洋的渔夫

发布于 2022-01-17 09:58:04

4.1K0

发布于 2022-01-17 09:58:04

文章被收录于专栏：Devops专栏Devops专栏

上一篇Django 2.1.7 模型 - MVT模型增删功能讲述了关于MVT模型中列表的增加数据以及删除数据的功能，在数据返回的过程中，也有部分关于模型之间关联查询的数据。

本篇章将着重讲述模型之间的关联查询。

参考文献

https://docs.djangoproject.com/zh-hans/2.1/topics/db/queries/

打开mysql数据中的日志功能

为了方便后续使用模型查看执行的sql语句，配置/etc/my.cnf文件，开启日志记录。

general_log=ON
general_log_file=/var/lib/mysql/mysql.log

注意：配置日志的用户权限需要设置为mysql用户，不然由于权限不足无法创建日志。

配置之后，重启mysql服务，并查看生成日志，如下：

[root@server01 ~]# 
[root@server01 ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

# 在另一个窗口执行查询sql，然后查看日志执行的语句
[root@server01 mysql]# tail -f /var/lib/mysql/mysql.log 
2019-06-11T14:36:15.870481Z	    3 Field List	auth_group_permissions 
2019-06-11T14:36:15.870825Z	    3 Field List	auth_permission 
2019-06-11T14:36:15.871316Z	    3 Field List	auth_user 
2019-06-11T14:36:15.871863Z	    3 Field List	auth_user_groups 
2019-06-11T14:36:15.872190Z	    3 Field List	auth_user_user_permissions 
2019-06-11T14:36:15.872539Z	    3 Field List	django_admin_log 
2019-06-11T14:36:15.872954Z	    3 Field List	django_content_type 
2019-06-11T14:36:15.873407Z	    3 Field List	django_migrations 
2019-06-11T14:36:15.873699Z	    3 Field List	django_session 
2019-06-11T14:36:15.874048Z	    3 Query	select * from assetinfo_serverinfo

好了，可以查看日志之后，下面就可以在模型执行查询，然后观察在mysql中的执行sql语句。

字段查询

使用模型来实现sql中where的功能，可以通过调用 过滤器filter()、exclude()、get() 来实现。

其中，"属性名_id"表示外键对应对象的id值。

语法如下：

 说明：属性名称和比较运算符间使用两个下划线，所以属性名不能包括多个下划线。 ”

属性名称__比较运算符=值

看完上面几句话，肯定是似懂非懂的，那么我们来进入django的交互模式，引入数据模型类来查询一下看看。

F:\pythonProject\django-pratice>python3 manage.py shell
Python 3.7.2 (tags/v3.7.2:9a3ffc0492, Dec 23 2018, 23:09:28) [MSC v.1916 64 bit (AMD64)]
Type 'copyright', 'credits' or 'license' for more information
IPython 7.2.0 -- An enhanced Interactive Python. Type '?' for help.

In [1]: from assetinfo.models import ServerInfo,MiddlewareInfo

In [2]:

条件查询

1) 精确查询

exact：表示精确查询。

首先在mysql查询一个 id = 1 的服务器信息，如下：

mysql> select * from assetinfo_serverinfo where id = 1;
+----+-----------------+--------------------+--------------------+---------------------+-----------+----------------------------+
| id | server_hostname | server_intranet_ip | server_internet_ip | server_shelves_date | is_delete | update_time                |
+----+-----------------+--------------------+--------------------+---------------------+-----------+----------------------------+
|  1 | 测试服务器      | 172.16.5.1         | 223.5.5.5          | 2019-06-10          |         0 | 2019-06-10 14:56:46.425830 |
+----+-----------------+--------------------+--------------------+---------------------+-----------+----------------------------+
1 row in set (0.00 sec)

查看mysql的查询日志如下：

那么下面使用django的模型查询看看，如下：

In [6]: serverinfo = ServerInfo.objects.filter( id__exact = 1 )

In [7]: for item in serverinfo:
   ...:     print(item)
   ...: 
ServerInfo object (1)

In [8]: for item in serverinfo:
   ...:     print(item.server_hostname)
   ...: 
测试服务器

再到日志看看执行的sql语句，如下：

2019-06-11T14:55:23.312240Z	    4 Query	SELECT `assetinfo_serverinfo`.`id`, `assetinfo_serverinfo`.`server_hostname`, `assetinfo_serverinfo`.`server_intranet_ip`, `assetinfo_serverinfo`.`server_internet_ip`, `assetinfo_serverinfo`.`server_shelves_date`, `assetinfo_serverinfo`.`update_time`, `assetinfo_serverinfo`.`is_delete` FROM `assetinfo_serverinfo` WHERE `assetinfo_serverinfo`.`id` = 1

那么如果执行get() 过滤器呢？

In [11]: ServerInfo.objects.get( id__exact = 1 )
Out[11]: <ServerInfo: ServerInfo object (1)>

日志打印执行sql如下：

2019-06-11T15:18:12.062269Z	    4 Query	SELECT `assetinfo_serverinfo`.`id`, `assetinfo_serverinfo`.`server_hostname`, `assetinfo_serverinfo`.`server_intranet_ip`, `assetinfo_serverinfo`.`server_internet_ip`, `assetinfo_serverinfo`.`server_shelves_date`, `assetinfo_serverinfo`.`update_time`, `assetinfo_serverinfo`.`is_delete` FROM `assetinfo_serverinfo` WHERE `assetinfo_serverinfo`.`id` = 1

跟filter过滤器执行是一样的。

那么 exclude() 方法呢？

In [12]: ServerInfo.objects.exclude( id__exact = 1 )
Out[12]: <QuerySet [<ServerInfo: ServerInfo object (2)>]>

日志打印sql如下：

2019-06-11T15:20:28.087415Z	    4 Query	SELECT `assetinfo_serverinfo`.`id`, `assetinfo_serverinfo`.`server_hostname`, `assetinfo_serverinfo`.`server_intranet_ip`, `assetinfo_serverinfo`.`server_internet_ip`, `assetinfo_serverinfo`.`server_shelves_date`, `assetinfo_serverinfo`.`update_time`, `assetinfo_serverinfo`.`is_delete` FROM `assetinfo_serverinfo` WHERE NOT (`assetinfo_serverinfo`.`id` = 1)  LIMIT 21

可以看到这是排除 id = 1 的结果集，是跟上面两个结果不同的。在mysql执行一下 exclude() 生成的sql语句，如下：

mysql> SELECT `assetinfo_serverinfo`.`id`, `assetinfo_serverinfo`.`server_hostname`, `assetinfo_serverinfo`.`server_intranet_ip`, `assetinfo_serverinfo`.`server_internet_ip`, `assetinfo_serverinfo`.`server_shelves_date`, `assetinfo_serverinfo`.`update_time`, `assetinfo_serverinfo`.`is_delete` FROM `assetinfo_serverinfo` WHERE NOT (`assetinfo_serverinfo`.`id` = 1)  LIMIT 21
    -> ;
+----+--------------------+--------------------+--------------------+---------------------+----------------------------+-----------+
| id | server_hostname    | server_intranet_ip | server_internet_ip | server_shelves_date | update_time                | is_delete |
+----+--------------------+--------------------+--------------------+---------------------+----------------------------+-----------+
|  2 | 预发布服务器       | 172.168.0.3        | 223.6.6.6          | 2019-06-11          | 2019-06-10 16:40:42.159529 |         0 |
+----+--------------------+--------------------+--------------------+---------------------+----------------------------+-----------+
1 row in set (0.00 sec)

mysql> 

mysql> select * from assetinfo_serverinfo where id <> 1;
+----+--------------------+--------------------+--------------------+---------------------+-----------+----------------------------+
| id | server_hostname    | server_intranet_ip | server_internet_ip | server_shelves_date | is_delete | update_time                |
+----+--------------------+--------------------+--------------------+---------------------+-----------+----------------------------+
|  2 | 预发布服务器       | 172.168.0.3        | 223.6.6.6          | 2019-06-11          |         0 | 2019-06-10 16:40:42.159529 |
+----+--------------------+--------------------+--------------------+---------------------+-----------+----------------------------+
1 row in set (0.00 sec)

mysql>

其实就是查询 id 不等于 1 的结果。

2) 模糊查询

contains：是否包含。

 说明：如果要包含%无需转义，直接写即可。 ”

例：查询服务器名称包含测试的服务器。

In [17]: ServerInfo.objects.filter( server_hostname__contains = "测试" )
Out[17]: <QuerySet [<ServerInfo: ServerInfo object (1)>]>

日志执行sql如下：

2019-06-11T15:46:25.036196Z	    4 Query	SELECT `assetinfo_serverinfo`.`id`, `assetinfo_serverinfo`.`server_hostname`, `assetinfo_serverinfo`.`server_intranet_ip`, `assetinfo_serverinfo`.`server_internet_ip`, `assetinfo_serverinfo`.`server_shelves_date`, `assetinfo_serverinfo`.`update_time`, `assetinfo_serverinfo`.`is_delete` FROM `assetinfo_serverinfo` WHERE `assetinfo_serverinfo`.`server_hostname` LIKE BINARY '%测试%'  LIMIT 21

可以看到LIKE BINARY '%测试%' 作为模糊匹配的条件，但是前后都写%的话，查询性能肯定不高，那么如何设置前后的%呢?

startswith、endswith：以指定值开头或结尾。

In [19]: ServerInfo.objects.filter( server_hostname__startswith = "测试" )
Out[19]: <QuerySet [<ServerInfo: ServerInfo object (1)>]>

In [20]:

对应执行SQL如下：

2019-06-11T15:50:03.661754Z	    4 Query	SELECT `assetinfo_serverinfo`.`id`, `assetinfo_serverinfo`.`server_hostname`, `assetinfo_serverinfo`.`server_intranet_ip`, `assetinfo_serverinfo`.`server_internet_ip`, `assetinfo_serverinfo`.`server_shelves_date`, `assetinfo_serverinfo`.`update_time`, `assetinfo_serverinfo`.`is_delete` FROM `assetinfo_serverinfo` WHERE `assetinfo_serverinfo`.`server_hostname` LIKE BINARY '测试%'  LIMIT 21

可以看到，当使用startswith执行查询，那么测试%则是在后面模糊查询。

In [20]: ServerInfo.objects.filter( server_hostname__endswith = "测试" )
Out[20]: <QuerySet []>

对应执行SQL如下：

2019-06-11T15:51:37.833362Z	    4 Query	SELECT `assetinfo_serverinfo`.`id`, `assetinfo_serverinfo`.`server_hostname`, `assetinfo_serverinfo`.`server_intranet_ip`, `assetinfo_serverinfo`.`server_internet_ip`, `assetinfo_serverinfo`.`server_shelves_date`, `assetinfo_serverinfo`.`update_time`, `assetinfo_serverinfo`.`is_delete` FROM `assetinfo_serverinfo` WHERE `assetinfo_serverinfo`.`server_hostname` LIKE BINARY '%测试'  LIMIT 21

可以看到，当使用endswith执行查询，那么 %测试则是在前面模糊查询。

 以上运算符都区分大小写，在这些运算符前加上i表示不区分大小写，如iexact、icontains、istartswith、iendswith. ”

3) 空查询

isnull：是否为null。

例：查询中间件名不为空的中间件信息。

In [21]: MiddlewareInfo.objects.filter( name__isnull = False )
Out[21]: <QuerySet [<MiddlewareInfo: MiddlewareInfo object (1)>, <MiddlewareInfo: MiddlewareInfo object (2)>, <MiddlewareInfo: Middlewar
eInfo object (3)>, <MiddlewareInfo: MiddlewareInfo object (4)>, <MiddlewareInfo: MiddlewareInfo object (5)>, <MiddlewareInfo: Middleware
Info object (6)>, <MiddlewareInfo: MiddlewareInfo object (7)>, <MiddlewareInfo: MiddlewareInfo object (8)>, <MiddlewareInfo: MiddlewareI
nfo object (9)>, <MiddlewareInfo: MiddlewareInfo object (10)>, <MiddlewareInfo: MiddlewareInfo object (11)>, <MiddlewareInfo: Middleware
Info object (12)>, <MiddlewareInfo: MiddlewareInfo object (13)>, <MiddlewareInfo: MiddlewareInfo object (14)>, <MiddlewareInfo: Middlewa
reInfo object (15)>, <MiddlewareInfo: MiddlewareInfo object (16)>]>

In [22]:

对应执行的SQL如下：

2019-06-11T15:58:14.759899Z	    4 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`.`name` IS NOT NULL  LIMIT 21

可以看到 name IS NOT NULL , 如果设置为 True，那么则是 IS NULL

4) 范围查询

in：是否包含在范围内。

例：查询 id 为1或3或5的中间件

In [23]: MiddlewareInfo.objects.filter( id__in = [1,3,5] )
Out[23]: <QuerySet [<MiddlewareInfo: MiddlewareInfo object (1)>, <MiddlewareInfo: MiddlewareInfo object (3)>, <MiddlewareInfo: Middlewar
eInfo object (5)>]>

In [24]:

对应SQL如下：

2019-06-11T16:02:34.126657Z	    4 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`.`id` IN (1, 3, 5)  LIMIT 21

查询条件为 id IN (1, 3, 5)，那么如果要查询 not in 呢？此时可以使用 exclude() 方法。

In [24]: MiddlewareInfo.objects.exclude( id__in = [1,3,5] )
Out[24]: <QuerySet [<MiddlewareInfo: MiddlewareInfo object (2)>, <MiddlewareInfo: MiddlewareInfo object (4)>, <MiddlewareInfo: Middlewar
eInfo object (6)>, <MiddlewareInfo: MiddlewareInfo object (7)>, <MiddlewareInfo: MiddlewareInfo object (8)>, <MiddlewareInfo: Middleware
Info object (9)>, <MiddlewareInfo: MiddlewareInfo object (10)>, <MiddlewareInfo: MiddlewareInfo object (11)>, <MiddlewareInfo: Middlewar
eInfo object (12)>, <MiddlewareInfo: MiddlewareInfo object (13)>, <MiddlewareInfo: MiddlewareInfo object (14)>, <MiddlewareInfo: Middlew
areInfo object (15)>, <MiddlewareInfo: MiddlewareInfo object (16)>]>

对应执行SQL如下：

2019-06-11T16:04:35.102702Z	    4 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 NOT (`assetinfo_middlewareinfo`.`id` IN (1, 3, 5))  LIMIT 21

把SQL在mysql执行一下看看，如下：

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`.`id` IN (1, 3, 5))  LIMIT 21;
+----+---------+------+-----------+----------------------------+----------------------------+-----------+
| id | name    | port | server_id | shelves_date               | update_time                | is_delete |
+----+---------+------+-----------+----------------------------+----------------------------+-----------+
|  2 | redis   | 6379 |         1 | 2019-06-10 14:56:46.150556 | 2019-06-10 17:38:20.712862 |         1 |
|  4 | kafka   | 9092 |         2 | 2019-06-10 16:42:25.561732 | 2019-06-10 17:39:29.302349 |         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 |
| 12 | mysql   | 3306 |         2 | 2019-06-10 17:12:12.558217 | 2019-06-10 17:12:12.558217 |         0 |
| 13 | mongodb | 3388 |         2 | 2019-06-10 17:15:18.327729 | 2019-06-10 17:15:18.327729 |         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 |
+----+---------+------+-----------+----------------------------+----------------------------+-----------+
13 rows in set (0.00 sec)

mysql>

可以看到已经提出了 id = 1,3,5 的数据。

5) 比较查询

gt、gte、lt、lte：大于、大于等于、小于、小于等于。

例：查询 id大于3 的中间件

In [26]: MiddlewareInfo.objects.filter( id__gt = 3 )
Out[26]: <QuerySet [<MiddlewareInfo: MiddlewareInfo object (4)>, <MiddlewareInfo: MiddlewareInfo object (5)>, <MiddlewareInfo: Middlewar
eInfo object (6)>, <MiddlewareInfo: MiddlewareInfo object (7)>, <MiddlewareInfo: MiddlewareInfo object (8)>, <MiddlewareInfo: Middleware
Info object (9)>, <MiddlewareInfo: MiddlewareInfo object (10)>, <MiddlewareInfo: MiddlewareInfo object (11)>, <MiddlewareInfo: Middlewar
eInfo object (12)>, <MiddlewareInfo: MiddlewareInfo object (13)>, <MiddlewareInfo: MiddlewareInfo object (14)>, <MiddlewareInfo: Middlew
areInfo object (15)>, <MiddlewareInfo: MiddlewareInfo object (16)>]>

对应的SQL如下：

2019-06-11T16:11:24.052080Z	    4 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`.`id` > 3  LIMIT 21

执行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 `assetinfo_middlewareinfo`.`id` > 3  LIMIT 21;
+----+---------+------+-----------+----------------------------+----------------------------+-----------+
| id | name    | port | server_id | shelves_date               | update_time                | is_delete |
+----+---------+------+-----------+----------------------------+----------------------------+-----------+
|  4 | kafka   | 9092 |         2 | 2019-06-10 16:42:25.561732 | 2019-06-10 17:39:29.302349 |         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 |
| 12 | mysql   | 3306 |         2 | 2019-06-10 17:12:12.558217 | 2019-06-10 17:12:12.558217 |         0 |
| 13 | mongodb | 3388 |         2 | 2019-06-10 17:15:18.327729 | 2019-06-10 17:15:18.327729 |         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 |
+----+---------+------+-----------+----------------------------+----------------------------+-----------+
13 rows in set (0.00 sec)

mysql>

不等于的运算符，使用exclude()过滤器。

In [27]: MiddlewareInfo.objects.exclude( id = 3 )

6) 日期查询

year、month、day、week_day、hour、minute、second：对日期时间类型的属性进行运算。

例：查询2019年加入数据的中间件。

In [29]: MiddlewareInfo.objects.filter( shelves_date__year = 2019  )
Out[29]: <QuerySet [<MiddlewareInfo: MiddlewareInfo object (1)>, <MiddlewareInfo: MiddlewareInfo object (2)>, <MiddlewareInfo: Middlewar
eInfo object (3)>, <MiddlewareInfo: MiddlewareInfo object (4)>, <MiddlewareInfo: MiddlewareInfo object (5)>, <MiddlewareInfo: Middleware
Info object (6)>, <MiddlewareInfo: MiddlewareInfo object (7)>, <MiddlewareInfo: MiddlewareInfo object (8)>, <MiddlewareInfo: MiddlewareI
nfo object (9)>, <MiddlewareInfo: MiddlewareInfo object (10)>, <MiddlewareInfo: MiddlewareInfo object (11)>, <MiddlewareInfo: Middleware
Info object (12)>, <MiddlewareInfo: MiddlewareInfo object (13)>, <MiddlewareInfo: MiddlewareInfo object (14)>, <MiddlewareInfo: Middlewa
reInfo object (15)>, <MiddlewareInfo: MiddlewareInfo object (16)>]>

对应的SQL如下：

2019-06-11T16:16:15.497283Z	    4 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` BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59.999999'  LIMIT 21

可以从上面看到，年份的查询条件就是区间查询 BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59.999999'

那么查询月份呢？

In [32]: MiddlewareInfo.objects.filter( shelves_date__month = 1  )
Out[32]: <QuerySet []>

In [33]:

对应的SQL如下：

2019-06-11T16:18:46.824127Z	    4 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 EXTRACT(MONTH FROM CONVERT_TZ(`assetinfo_middlewareinfo`.`shelves_date`, 'UTC', 'UTC')) = 1  LIMIT 21

可以看到月份的查询时区不对 EXTRACT(MONTH FROM CONVERT_TZ(assetinfo_middlewareinfo.shelves_date, 'UTC', 'UTC')) = 1

例：查询2019年5月1日后加入数据的中间件。

In [4]: from datetime import date

In [5]: MiddlewareInfo.objects.filter( shelves_date__gt = date(2019,5,1) )
G:\Python3\Python37\lib\site-packages\django\db\models\fields\__init__.py:1363: RuntimeWarning: DateTimeField MiddlewareInfo.shelves_date received a naive datetime (2019-05-01 00:0
0:00) while time zone support is active.
  RuntimeWarning)
Out[5]: <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如下：

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 `assetinfo_middlewareinfo`.`shelves_date` > '2019-04-30 16:00:00'  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 |
|  3 | nginx     |    80 |         2 | 2019-06-10 16:41:52.129517 | 2019-06-10 17:38:18.923155 |         1 |
|  4 | kafka     |  9092 |         2 | 2019-06-10 16:42:25.561732 | 2019-06-10 17:39:29.302349 |         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 |
| 12 | mysql     |  3306 |         2 | 2019-06-10 17:12:12.558217 | 2019-06-10 17:12:12.558217 |         0 |
| 13 | mongodb   |  3388 |         2 | 2019-06-10 17:15:18.327729 | 2019-06-10 17:15:18.327729 |         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 |
+----+-----------+-------+-----------+----------------------------+----------------------------+-----------+
16 rows in set (0.00 sec)

mysql>

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-09-20，如有侵权请联系 cloudcommunity@tencent.com 删除

sql