高级querySet

高级querySet

查看执行sql

print (Author.objects.all().query)
print (Author.objects.filter(name="WeizhongTu").query)

values_list获得元组形式结果

uthors = Author.objects.values_list('name', 'qq')
authors
list(authors)

#如果只需要一个字段，可以指定flat=True
Author.objects.values_list('name', flat=True)
list(Author.objects.values_list('name', flat=True))
#查询 twz915 这个人的文章标题
Article.objects.filter(author__name='twz915').values_list('title', flat=True)

values 获取字典形式的结果

Author.objects.values('name', 'qq')
list(Author.objects.values('name', 'qq'))
Article.objects.filter(author__name='twz915').values('title')

values_list和values返回的也是queryset，这个操作是lazy的，尽量去遍历而不是转化成列表

extra 实现 别名，条件，排序等

比如 Author 中有 name， Tag 中有 name 我们想执行
SELECT name AS tag_name FROM blog_tag;
可以用select实现

tags = Tag.objects.all().extra(select={'tag_name': 'name'})
tags[0].name
tags[0].tag_name

看下查询的sql
print (Tag.objects.all().extra(select={'tag_name': 'name'}).query)
SELECT (name) AS "tag_name", "blog_tag"."id", "blog_tag"."name" FROM "blog_tag"
但是这里发现有tag_name和name，可以用defer排除原来的name

print (Tag.objects.all().extra(select={'tag_name': 'name'}).defer('name').query)
SELECT (name) AS "tag_name", "blog_tag"."id" FROM "blog_tag"

annotate 聚合 计数，求和，平均数等

计数

from django.db.models import Count
Article.objects.all().values('author_id').annotate(count=Count('author')).values('author_id', 'count')

获取作者的名称及作者的文章数

Article.objects.all().values('author__name').annotate(count=Count('author')).values('author__name', 'count')

求和
求一个作者所有文章平均分

from django.db.models import Sum
Article.objects.values('author__name').annotate(sum_score=Sum('score')).values('author__name', 'sum_score')

平均

from django.db.models import Avg
Article.objects.values('author_id').annotate(avg_score=Avg('score')).values('author_id', 'avg_score')

自定义聚合

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        )

使用方法，比如针对日志字段如下：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')

https://docs.djangoproject.com/en/1.11/topics/db/aggregation/

select_related优化一对一、多对一查询

配置尾部加上log打印sql

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console': {
            'class': 'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'level': 'DEBUG' if DEBUG else 'INFO',
        },
    },
}

一般情况

from blog.models import *
articles = Article.objects.all()[:10]
a1.title
a1.author_id
#如果查询多对一的author，默认会再次查询数据库
a1.author.name  

优化

#这里不执行sql
articles = Article.objects.all().select_related('author')[:10]
#执行sql
a1 = articles[0]
a1.title
#这里不会再次查询
a1.author.name

查询Article.objects.all().select_related('author')[:10]的内容为

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;

优化一对多、多对多查询

select_related是通过join一次取出多个内容
prefetch_related用于一对多、多对多，再执行一条sql，把两次执行的结果关联到一起
一次把需要的内容都查询出来

articles = Article.objects.all().prefetch_related('tags')[:10]
articles

不适用prefetch_related时

articles = Article.objects.all()[:3]
for a in articles:
	print (a.title, a.tags.all())

(0.000) SELECT "blog_article"."id", "blog_article"."title", "blog_article"."author_id", "blog_article"."content", "blog_article"."score" FROM "blog_article" LIMIT 3; args=()
(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" = 1 LIMIT 21; args=(1,)
Django 教程_1 <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" = 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>]>

使用prefetch_related

articles = Article.objects.all().prefetch_related('tags')[:3]
for a in articles:
	print a.title, a.tags.all()

(0.000) SELECT "blog_article"."id", "blog_article"."title", "blog_article"."author_id", "blog_article"."content", "blog_article"."score" FROM "blog_article" LIMIT 3; args=()
(0.000) 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>]>

defer排除不需要字段

排除后，sql中不查询该字段

Article.objects.all().defer('content')

only仅选择需要字段

queryset查询必须带id

Author.objects.all().only('name')

sql查询

authors =  Author.objects.raw('select name from blog_author limit 1')