本文的主要内容：
1、查看Django QuerySet 执行的SQL
2、获得的查询结果直接以类似list方式展示
3、如何在Django中给一个字段取一个别名
4、annotate 聚合 计数，求和，求平均数等
5、优化SQL，减少多对一，一对多，多对多时插叙次数
6、如何汁取出需要的字段，排除某些字段
7、自定义一个自定义聚合功能，比如group_concat
新建一个项目learn，建一个app名字为blog
django-admin startproject learn
cd learn/
django-admin startapp blog
把blog配置到settings.py中的INSTALL_APPS中，DATABASES中数据库内容配置好
在learn/init配置：
import pymysql
pymysql.install_as_MySQLdb()
blog/models.py 代码如下：
from __future__ import unicode_literalsfrom # 必须放在第一行，否则报错
django.db import models
# Create your models here.
from django.utils.encoding import python_2_unicode_compatible
# 兼容python2及python3的装饰器
@python_2_unicode_compatible
class Author(models.Model):
name = models.CharField(max_length=50)
qq = models.CharField(max_length=10)
addr = models.TextField()
email = models.EmailField()
def __str__(self):
return self.name
@python_2_unicode_compatible
class Article(models.Model):
title = models.CharField(max_length=50)
author = models.ForeignKey(Author)
content = models.TextField()
score = models.IntegerField()
tags = models.ManyToManyField('Tag')
def __str__(self):
return self.title
@python_2_unicode_compatible
class Tag(models.Model):
name = models.CharField(max_length=50)
def __str__(self):
return self.name
比较简单，假设一篇文章只有一个作者（Author），一个作者可以写多篇文章（Article），一篇文章可以有多个标签（Tag）
创建migrations 然后migrate 在数据库中生成相应的表
python manage.py makemigrations
python manage.py migrate
生成一些示例数据，运行initdb.py
#!/user/bin/env python
# -*- coding: utf-8 -*-
# @Time : 17-10-20 下午4:25
# @Author : taytay
# @File : initdb.py.py
from __future__ import unicode_literals
import random
from learn.wsgi import *
from blog.models import Author, Article, Tag
author_name_list = ['WeizhongTu', 'twz915', 'dachui', 'zhe', 'zhen']
article_title_list = ['Django 教程', 'Python 教程', 'HTML 教程']
def create_authors():
for author_name in author_name_list:
author, created = Author.objects.get_or_create(name=author_name)
# 随机生成9位数的QQ，
author.qq = ''.join(
str(random.choice(range(10))) for _ in range(9)
)
author.addr = 'addr_%s' % (random.randrange(1, 3))
author.email = '%s@ziqiangxuetang.com' % (author.addr)
author.save()
def create_articles_and_tags():
# 随机生成文章
for article_title in article_title_list:
# 从文章标题中得到 tag
tag_name = article_title.split(' ', 1)[0]
tag, created = Tag.objects.get_or_create(name=tag_name)
random_author = random.choice(Author.objects.all())
for i in range(1, 21):
title = '%s_%s' % (article_title, i)
article, created = Article.objects.get_or_create(
title=title, defaults={
'author': random_author, # 随机分配作者
'content': '%s 正文' % title,
'score': random.randrange(70, 101), # 随机给文章一个打分
}
)
article.tags.add(tag)
def main():
create_authors()
create_articles_and_tags()
if __name__ == '__main__':
main()
print("Done!")
执行initdb.py
python initdb.py
Done!
导入数据后，我们确认一下数据是不是已经导入
In [1]: from blog.models import *
In [2]: Article.objects.all()
Out[2]: <QuerySet [<Article: Django 教程_1>, <Article: Django 教程_2>,
<Article: Django 教程_3>, <Article: Django 教程_4>, <Article: Django 教程_5>,
<Article: Django 教程_6>, <Article: Django 教程_7>, <Article: Django 教程_8>,
<Article: Django 教程_9>, <Article: Django 教程_10>, <Article: Django 教程_11>,
<Article: Django 教程_12>, <Article: Django 教程_13>, <Article: Django 教程_14>,
<Article: Django 教程_15>, <Article: Django 教程_16>, <Article: Django 教程_17>,
<Article: Django 教程_18>, <Article: Django 教程_19>, <Article: Django 教程_20>,
'...(remaining elements truncated)...']>
In [3]: Author.objects.all()
Out[3]: <QuerySet [<Author: WeizhongTu>, <Author: twz915>, <Author: dachui>, <Author: zhe>, <Author: zhen>]>
In [4]: Tag.objects.all()
Out[4]: <QuerySet [<Tag: Django>, <Tag: Python>, <Tag: HTML>]>
1、查看Django QuerySet执行的SQL
In [6]: print(str(Author.objects.all().query))
SELECT `blog_author`.`id`, `blog_author`.`name`, `blog_author`.`qq`, `blog_author`.`addr`, `blog_author`.`email` FROM `blog_author`
# 简化一下，就是：SELECT id, name, qq, addr, email FROM blog_author;
In [7]: print(str(Author.objects.filter(name='WeizhongTu').query))
SELECT `blog_author`.`id`, `blog_author`.`name`, `blog_author`.`qq`, `blog_author`.`addr`, `blog_author`.`email` FROM `blog_author` WHERE `blog_author`.`name` = WeizhongTu
# 简化一下，就是：SELECT id, name, qq, addr, email FROM blog_author WHERE name=WeizhongTu;
所以，当不知道Django做了什么时，你可以把执行的 SQL 打出来看看，也可以借助 django-debug-toolbar 等工具在页面上看到访问当前页面执行了哪些SQL，耗时等。
还有一种办法就是修改一下 log 的设置
2、values_list 获取元组形式结果
比如我们要获取作者的name 和 qq
In [9]: authors = Author.objects.values_list('name', 'qq')
In [10]: authors
Out[10]: <QuerySet [('WeizhongTu', '933907840'), ('twz915', '256556948'), ('dachui', '038432635'), ('zhe', '189468508'), ('zhen', '244009912')]>
In [11]: list(authors)
Out[11]:
[('WeizhongTu', '933907840'),
('twz915', '256556948'),
('dachui', '038432635'),
('zhe', '189468508'),
('zhen', '244009912')]
# 如果只需要一个字段，可以指定flat=True
In [12]: authors = Author.objects.values_list('name', flat=True)
In [13]: authors
Out[13]: <QuerySet ['WeizhongTu', 'twz915', 'dachui', 'zhe', 'zhen']>
In [14]: list(authors)
Out[14]: ['WeizhongTu', 'twz915', 'dachui', 'zhe', 'zhen']
#如果查询‘zhen’这个人的文章标题
In [18]: Article.objects.filter(author__name='zhen').values_list('title', flat=T
...: rue)
Out[18]: <QuerySet ['HTML 教程_1', 'HTML 教程_2', 'HTML 教程_3', 'HTML 教程_4', 'HTML 教程_5', 'HTML 教程_6', 'HTML 教程_7', 'HTML 教程_8', 'HTML 教程_9', 'HTML 教程_10', 'HTML 教程_11', 'HTML 教程_12', 'HTML 教程_13', 'HTML 教程_14', 'HTML 教程_15', 'HTML 教程_16', 'HTML 教程_17', 'HTML 教程_18', 'HTML 教程_19', 'HTML 教程_20']>
3、values 获取字典形式的结果
In [20]: authors = Author.objects.values('name', 'qq')
In [21]: authors
Out[21]: <QuerySet [{'qq': '933907840', 'name': 'WeizhongTu'}, {'qq': '256556948', 'name': 'twz915'}, {'qq': '038432635', 'name': 'dachui'}, {'qq': '189468508', 'name': 'zhe'}, {'qq': '244009912', 'name': 'zhen'}]>
In [23]: list(authors)
Out[23]:
[{'name': 'WeizhongTu', 'qq': '933907840'},
{'name': 'twz915', 'qq': '256556948'},
{'name': 'dachui', 'qq': '038432635'},
{'name': 'zhe', 'qq': '189468508'},
{'name': 'zhen', 'qq': '244009912'}]
注意：
1、values_list 和 values 返回的并不是真正的 列表 或 字典，也是QuerySet，他们也是lazy evaluation 的（惰性评估，通俗地说，就是用的时候才真正的去数据库查）
2、如果查询后后没有使用，在数据库更新后在使用，你发现得到的是新内容，如果想要旧内容保持，数据库更新后不要变，可以list一下
3、如果只是遍历这些结果，没有必要list他们转成列表（浪费内存，数据大的时候更要谨慎）
4、extra 实现别名，条件，排序等
extra 中可实现别名，条件，排序等，后面两个用filter，exclude一般都能实现，排序用order_by也能实现，我们主要看一下别名
比如，Author中有name ,Tag 中有name 我们想执执行
select name as tag_name from blog_tag;
这样的语句，就可以用select来实现，如下：
In [2]: tags = Tag.objects.all().extra(select={'tag_name':'name'}
In [3]: tags[0].name
Out[3]: 'Django'
In [4]: tags[0].tag_name
Out[4]: 'Django'
我们发现name 和 tag_name都可以使用，确认一下执行的SQL
In [6]: Tag.objects.all().extra(select={'tag_name': 'name'}).query.__str__()
Out[6]: 'SELECT (name) AS `tag_name`, `blog_tag`.`id`, `blog_tag`.`name` FROM `blog_tag`'
我们发现查询的时候弄了两次（name）as 'tag_name' 和 'blog_tag'.'name'
如果我，我们只想其中一个能用，可用defer 排除掉原来的name
In [7]: Tag.objects.all().extra(select={'tag_name':'name'}).defer('name').query.__str__
...: ()
Out[7]: 'SELECT (name) AS `tag_name`, `blog_tag`.`id` FROM `blog_tag`'
5、annotate 聚合 计数，求和，平均数等
5.1 计数
我们来计算一下每个作者的文章数（我们每个作者都导入的Article的篇数一样，所以下面的每个都一样）
In [14]: from django.db.models import Count
In [15]: Article.objects.all().values('author_id').annotate(count=Count('author')).valu
...: es('author_id', 'count')
Out[15]: <QuerySet [{'author_id': 2, 'count': 40}, {'author_id': 5, 'count': 20}]>
这是怎么工作的呢？
In [16]: Article.objects.all().values('author_id').annotate(count=Count('author')).valu
...: es('author_id', 'count').query.__str__()
Out[16]: 'SELECT `blog_article`.`author_id`, COUNT(`blog_article`.`author_id`) AS `count` FROM `blog_article` GROUP BY `blog_article`.`author_id` ORDER BY NULL'
简化一下SQL：select author_id, Count(author_id) as count from blog_article group by author_id
我们可以获取作者的名称 及 作者的文章数
In [21]: Article.objects.all().values('author__name').annotate(count=Count('author')).v
...: alues('author__name', 'count')
Out[21]: <QuerySet [{'count': 40, 'author__name': 'twz915'}, {'count': 20, 'author__name': 'zhen'}]>
备注：作者名称中blog_author 这张表中，author_id 在blog_article 表中本身就有的
5.2 求和 与平均值
from django.db.models import Avg
In [24]: Article.objects.values_list('author__name').annotate(avg_score=Avg('sco
...: re')).values_list('author__name', 'avg_score')
Out[24]: <QuerySet [('twz915', 85.2), ('zhen', 83.25)]>
# 执行的SQL
In [25]: Article.objects.values_list('author__name').annotate(avg_score=Avg('sco
...: re')).values_list('author__name', 'avg_score').query.__str__()
Out[25]: 'SELECT `blog_author`.`name`, AVG(`blog_article`.`score`) AS `avg_score` FROM `blog_article` INNER JOIN `blog_author` ON (`blog_article`.`author_id` = `blog_author`.`id`) GROUP BY `blog_author`.`name` ORDER BY NULL'
5.3 求一个作者所有文章的总分
from django.db.models import Sum
In [31]: Article.objects.values('author__name').annotate(sum_score=Sum('score'))
...: .values('author__name', 'sum_score')
Out[31]: <QuerySet [{'author__name': 'twz915', 'sum_score': 3408}, {'author__name': 'zhen', 'sum_score': 1665}]>
# 执行的SQL
In [32]: Article.objects.values('author__name').annotate(sum_score=Sum('score'))
...: .values('author__name', 'sum_score').query.__str__()
Out[32]: 'SELECT `blog_author`.`name`, SUM(`blog_article`.`score`) AS `sum_score` FROM `blog_article` INNER JOIN `blog_author` ON (`blog_article`.`author_id` = `blog_author`.`id`) GROUP BY `blog_author`.`name` ORDER BY NULL'
6、select_related 优化一对一，多对一查询
开始之前我们修改一个settings.py 让Django打印出在数据库中执行的语句
settings.py尾部加上
InLOGGING = {
'version': 1,
'disable_existing_loggers': False,
'handlers': {
'console': {
'class': 'logging.StreamHandler',
},
},
'loggers': {
'django.db.backends': {
'handlers': ['console'],
'level': 'DEBUG' if DEBUG else 'INFO',
},
},
}
这样当 DEBUG 为 True 的时候，我们可以看出 django 执行了什么 SQL 语句
In [6]: Author.objects.all()
Out[6]: (0.035) SELECT `blog_author`.`id`, `blog_author`.`name`, `blog_author`.`qq`, `blog_author`.`addr`, `blog_author`.`email` FROM `blog_author` LIMIT 21; args=()
# 这部分就是打出的 log。 <QuerySet [<Author: WeizhongTu>, <Author: twz915>, <Author: dachui>, <Author: zhe>, <Author: zhen>]>
假如，我们取出10篇Django相关的文章，并需要用到作者的姓名
In [7]: articles = Article.objects.all()[:10]
In [14]: In [9]: a = articles[0] # 取第一篇
(0.002) SELECT `blog_article`.`id`, `blog_article`.`title`, `blog_article`.`author_id`, `blog_article`.`content`, `blog_article`.`score` FROM `blog_article` LIMIT 1; args=()
In [10]: a.title
Out[10]: 'Django 教程_1'
In [11]: a.author_id
Out[11]: 2
In [12]: a.author.name # 再次查询了数据库
(0.027) SELECT VERSION(); args=None
(0.046) SELECT `blog_author`.`id`, `blog_author`.`name`, `blog_author`.`qq`, `blog_author`.`addr`, `blog_author`.`email` FROM `blog_author` WHERE `blog_author`.`id` = 2; args=(2,)
Out[12]: 'twz915'
这样的话我们遍历查询结果的时候就会查询很多次数据库，能不能只查询一次，把作者的信息也查出来呢？
当然可以，这时就用到 select_related，我们的数据库设计的是一篇文章只能有一个作者，一个作者可以有多篇文章。
现在要查询文章的时候连同作者一起查询出来，“文章”和“作者”的关系就是多对一，换句说说，就是一篇文章只可能有一个作者。
In [20]: articles = Article.objects.all().select_related('author')[:10]
In [21]: a = articles[0]
(0.002) SELECT `blog_article`.`id`, `blog_article`.`title`, `blog_article`.`author_id`, `blog_article`.`content`, `blog_article`.`score`, `blog_author`.`id`, `blog_author`.`name`, `blog_author`.`qq`, `blog_author`.`addr`, `blog_author`.`email` FROM `blog_article` INNER JOIN `blog_author` ON (`blog_article`.`author_id` = `blog_author`.`id`) LIMIT 1; args=()
In [22]: a.title
Out[22]: 'Django 教程_1'
In [23]: a.author.name # 没有再次查询数据库！！
Out[23]: 'twz915'
7、prefetch_related 优化一对多，多对多查询
和select_related 功能类似，但是实现不同
select_related 是使用SQL JOIN 一次性去除相关的内容
prefetch_related 用于 一对多，多对多的情况，这时select_related 用不了，因为当前一条有好几天与之相关的内容
prefech_related是通过再执行一条额外的SQL语句，然后用python把两次SQL查询的内容关联（joining）到一起
我们来看个例子，查询文章的同时，查询文章对应的标签，‘文章’ 与 ‘标签’是多对多的关系。
In [32]: articles = Article.objects.all().prefetch_related('tags')[:10]
In [33]: articles
Out[33]: (0.001) SELECT `blog_article`.`id`, `blog_article`.`title`, `blog_article`.`author_id`, `blog_article`.`content`, `blog_article`.`score` FROM `blog_article` LIMIT 10; args=()
(0.001) SELECT (`blog_article_tags`.`article_id`) AS `_prefetch_related_val_article_id`, `blog_tag`.`id`, `blog_tag`.`name` FROM `blog_tag` INNER JOIN `blog_article_tags` ON (`blog_tag`.`id` = `blog_article_tags`.`tag_id`) WHERE `blog_article_tags`.`article_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); args=(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
<QuerySet [<Article: Django 教程_1>, <Article: Django 教程_2>, <Article: Django 教程_3>, <Article: Django 教程_4>, <Article: Django 教程_5>, <Article: Django 教程_6>, <Article: Django 教程_7>, <Article: Django 教程_8>, <Article: Django 教程_9>, <Article: Django 教程_10>]>
遍历查询的结果：
In [34]: articles = Article.objects.all().prefetch_related('tags')[:3]
In [35]: for a in articles:
...: print(a.title, a.tags.all())
...:
(0.002) SELECT `blog_article`.`id`, `blog_article`.`title`, `blog_article`.`author_id`, `blog_article`.`content`, `blog_article`.`score` FROM `blog_article` LIMIT 3; args=()
(0.001) SELECT (`blog_article_tags`.`article_id`) AS `_prefetch_related_val_article_id`, `blog_tag`.`id`, `blog_tag`.`name` FROM `blog_tag` INNER JOIN `blog_article_tags` ON (`blog_tag`.`id` = `blog_article_tags`.`tag_id`) WHERE `blog_article_tags`.`article_id` IN (1, 2, 3); args=(1, 2, 3)
Django 教程_1 <QuerySet [<Tag: Django>]>
Django 教程_2 <QuerySet [<Tag: Django>]>
Django 教程_3 <QuerySet [<Tag: Django>]>
不用prefetch_related 时：
(0.001) SELECT `blog_article`.`id`, `blog_article`.`title`, `blog_article`.`author_id`, `blog_article`.`content`, `blog_article`.`score` FROM `blog_article` LIMIT 3; args=()
(0.001) SELECT `blog_tag`.`id`, `blog_tag`.`name` FROM `blog_tag` INNER JOIN `blog_article_tags` ON (`blog_tag`.`id` = `blog_article_tags`.`tag_id`) WHERE `blog_article_tags`.`article_id` = 1 LIMIT 21; args=(1,)
Django 教程_1 <QuerySet [<Tag: Django>]>
(0.001) SELECT `blog_tag`.`id`, `blog_tag`.`name` FROM `blog_tag` INNER JOIN `blog_article_tags` ON (`blog_tag`.`id` = `blog_article_tags`.`tag_id`) WHERE `blog_article_tags`.`article_id` = 2 LIMIT 21; args=(2,)
Django 教程_2 <QuerySet [<Tag: Django>]>
(0.000) SELECT `blog_tag`.`id`, `blog_tag`.`name` FROM `blog_tag` INNER JOIN `blog_article_tags` ON (`blog_tag`.`id` = `blog_article_tags`.`tag_id`) WHERE `blog_article_tags`.`article_id` = 3 LIMIT 21; args=(3,)
Django 教程_3 <QuerySet [<Tag: Django>]>
8、defer 排出不需要的字段
在复杂的情况下，表中可能有些字段内容非常多，取出来转化成Python对象会占用大量的资源
这时候可以用defer来排除这些字段，比如我们在文章列表页面，只需要文章的标题合作者，没有要把文章的内容也获取出来（因为会转化成python对象，浪费内存）
In [51]: Article.objects.all()
Out[51]: (0.001) SELECT `blog_article`.`id`, `blog_article`.`title`, `blog_article`.`author_id`, `blog_article`.`content`, `blog_article`.`score` FROM `blog_article` LIMIT 21; args=()
<QuerySet [<Article: Django 教程_1>, <Article: Django 教程_2>, <Article: Django 教程_3>, <Article: Django 教程_4>, <Article: Django 教程_5>, <Article: Django 教程_6>, <Article: Django 教程_7>, <Article: Django 教程_8>, <Article: Django 教程_9>, <Article: Django 教程_10>, <Article: Django 教程_11>, <Article: Django 教程_12>, <Article: Django 教程_13>, <Article: Django 教程_14>, <Article: Django 教程_15>, <Article: Django 教程_16>, <Article: Django 教程_17>, <Article: Django 教程_18>, <Article: Django 教程_19>, <Article: Django 教程_20>, '...(remaining elements truncated)...']>
In [52]: Article.objects.all().defer('content', 'id')
Out[52]: (0.001) SELECT `blog_article`.`id`, `blog_article`.`title`, `blog_article`.`author_id`, `blog_article`.`score` FROM `blog_article` LIMIT 21; args=() # 这一没有查content 和 id字段了
<QuerySet [<Article: Django 教程_1>, <Article: Django 教程_2>, <Article: Django 教程_3>, <Article: Django 教程_4>, <Article: Django 教程_5>, <Article: Django 教程_6>, <Article: Django 教程_7>, <Article: Django 教程_8>, <Article: Django 教程_9>, <Article: Django 教程_10>, <Article: Django 教程_11>, <Article: Django 教程_12>, <Article: Django 教程_13>, <Article: Django 教程_14>, <Article: Django 教程_15>, <Article: Django 教程_16>, <Article: Django 教程_17>, <Article: Django 教程_18>, <Article: Django 教程_19>, <Article: Django 教程_20>, '...(remaining elements truncated)...']>
9、only仅选择需要的字段
和defer相反，only用于取出需要的字段，加入我们要查出作者的名称
In [54]: Author.objects.all().only('name')
Out[54]: (0.001) SELECT `blog_author`.`id`, `blog_author`.`name` FROM `blog_author` LIMIT 21; args=()
<QuerySet [<Author: WeizhongTu>, <Author: twz915>, <Author: dachui>, <Author: zhe>, <Author: zhen>]>
细心的同学会发现，我们让查 name ， id 也查了，这个 id 是 主键，能不能没有这个 id 呢？
试一下原生的 SQL 查询
In [26]: authors = Author.objects.raw('select name from blog_author limit 1')
In [27]: author = authors[0]
(0.000) select name from blog_author limit 1; args=()
---------------------------------------------------------------------------
InvalidQuery Traceback (most recent call last)
<ipython-input-27-51c5f914fff2> in <module>()
----> 1author = authors[0]
/usr/local/lib/python2.7/site-packages/django/db/models/query.pyc in __getitem__(self, k)
1275
1276 def __getitem__(self, k):
-> 1277 return list(self)[k]
1278
1279 @property
/usr/local/lib/python2.7/site-packages/django/db/models/query.pyc in __iter__(self)
1250 if skip:
1251 if self.model._meta.pk.attname in skip:
-> 1252 raise InvalidQuery('Raw query must include the primary key')
1253 model_cls = self.model
1254 fields =[self.model_fields.get(c)for c in self.columns]
InvalidQuery: Raw query must include the primary key
报错信息说 非法查询，原生SQL查询必须包含 主键！
再试试直接执行 SQL
tu@pro ~/zqxt $ python manage.py dbshell
SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> select name from blog_author limit 1;
WeizhongTu <--- 成功！！！
虽然直接执行SQL语句可以这样，但是 django queryset 不允许这样做，一般也不需要关心，反正 only 一定会取出你指定了的字段。
10、自定义聚合功能
我们掐年看到了django.db.models中有Count，Avg、Sum等，但是有一些没有的，比如GROUP_CONCAT，他用来聚合时降负荷某分株条件（group_by）的不同的值，连到一起，作为整体返回
我们来演示一下，如何实现GROUP_CONCAT功能
新建一个文件，比如 my_aggregate.py
from django.db.models import Aggregate, CharField
class GroupConcat(Aggregate):
function = 'GROUP_CONCAT'
template = '%(function)s(%(distinct)s%(expressions)s%(ordering)s%(separator)s)'
def __init__(self, expression, distinct=False, ordering=None, separator=',', **extra):
super(GroupConcat, self).__init__(
expression,
distinct='DISTINCT ' if distinct else '',
ordering=' ORDER BY %s' % ordering if ordering is not None else '',
separator=' SEPARATOR "%s"' % separator,
output_field=CharField(),
**extra )
使用时先引入 GroupConcat这个类，比如居户后的错误日志记录有这些字段，time、level、info
我们想把level、info一样的聚到一起，按时间和发生次数倒序排列，并含有每次日止的发生时间
ErrorLogModel.objects.values('level', 'info').annotate(
count=Count(1), time=GroupConcat('time', ordering='time DESC', separator=' | ')
).order_by('-time', '-count')
