BBS - 首页、个人站点、ORM
1 ajax和用户认证组件 ----PIL和session 2 ajax和form组件注册功能 (1) form请求和ajax上传文件 form请求: <form action="" novalidate enctype="multipart/form-data"> </form> request.FILES("avatar") ajax: formdata=new FormData() formdata.append("","") $.ajax({ url:"" processData:false, contentType:false data:formdata }) request.FILES("avatar") (2)图像预览 (3)form组件: class Userform(forms.Form): user=forms.Charfield() email=forms.Emailfield() def reg(): if request.method=="GET": form=Userform() return render(request,"reg.html",locals()) 在reg.html： 渲染方式： 1 ：{{form.as_p}} 2 : {{form.user}} {{form.user.label}} 3 : {%for field in form%} {{field}} {{field.label}} {%endfor%} def reg(): if request.method=="POST": form=Userform(request.POST) if form.is_valid(): form.cleaned_data else: form.errors return ...... (4) media配置： 静态文件的处理又包括STATIC和MEDIA两类，这往往容易混淆，在Django里面是这样定义的： MEDIA: 指用户上传的文件，比如在Model里面的FileFIeld，ImageField上传的文件。 STATIC:指服务器自己的文件。 示例： class User()： avatar = models.FileField(upload_to='avatars/', default="/avatars/default.png") if 实例化一个User对象的时候，avatar字段会接收一个文件对象，这个文件对象 会默认保存到项目的根目录对应的upload_to='avatars/'的位置 配置1： MEDIA_ROOT=os.path.join(BASE_DIR,"blog","media") if 实例化一个User对象的时候，avatar字段会接收一个文件对象，这个文件对象 会默认保存到MEDIA_ROOT对应路径的upload_to='avatars/'的位置 配置2： settings： MEDIA_URL="/media/" url.py: from django.views.static import serve from cnblog_s9 import settings # media 配置 url(r'^media/(?P<path>.*)$', serve, {'document_root': settings.MEDIA_ROOT}), http://127.0.0.1:8000/media/avatars/lufei.jpg 博客系统： 系统首页 admin:数据库后台管理web页面 个人站点： 查询 基于对象查询(子查询) 基于queryset查询(join查询)
一、首页
def index(request): article_list = Article.objects.all() return render(request, 'index.html',{'article_list':article_list})
# 登录 显示 用户名 ，注销 ， 修改密码 。。。
# 未登录 显示 登录 ， 注册
<ul class="nav navbar-nav navbar-right"> {% if request.user.username%} <li><a href="">{{ request.user.username }}</a></li> <li class="dropdown"> <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Dropdown <span class="caret"></span></a> <ul class="dropdown-menu"> <li><a href="#">注销</a></li> <li><a href="#">修改密码</a></li> <li role="separator" class="divider"></li> <li><a href="#">更换头像</a></li> </ul> </li> {% else %} <li><a href="">登录</a></li> <li><a href="">注册</a></li> {% endif %}
文章列表显示：以及用户头像点击到站点
<div class="col-md-7"> <div class="article_list"> {% for article in article_list %} <div class="article_item"> <div><h5><a href="">{{ article.title }}</a></h5></div> <div class="row"> <div class="col-md-2"><a href="/blog/{{ article.user.username }}"><img width="60" height="60" src="/media/{{ article.user.avatar }}" alt=""></a> </div> <div class="col-md-9 desc"> <p>{{ article.desc }}</p> </div> </div> <div class="small"> <span><a href="/blog/{{ article.user.username }}">{{ article.user.username }}</a>发布于</span> <span>{{ article.create_time|date:'Y-m-d' }}</span> <span class="glyphicon glyphicon-comment"></span>评论({{ article.comment_count }}) {# <span class="glyphicon glyphicon-comment"></span>评论({{ article.comment_set.count }}) #} <span class="glyphicon glyphicon-thumbs-up"></span>赞({{ article.up_count }}) </div> </div> <hr> {% endfor %} </div> </div>
注意点：
1. img 的 src
<img width="60" height="60" src="/media/{{ article.user.avatar }}" alt="">
2.小图标 点赞数 评论数
<span class="glyphicon glyphicon-comment"></span>评论({{ article.comment_count }})
<span class="glyphicon glyphicon-thumbs-up"></span>赞({{ article.up_count }})
因为：点赞数 评论数 要频繁的查，如果每次跨表，效率会低！
<span class="glyphicon glyphicon-comment"></span>评论({{ article.comment_set.count }})
所以 不使用这个，跨表查询 article.comment_set.count 而是 新加字段 comment_count
之后，在进行一次数据库迁移：makemigrations migrate
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>index</title> <link rel="stylesheet" href="/static/bootstrap-3.3.7/css/bootstrap.css"> <link rel="stylesheet" href="/static/css/login.css"> <style type="text/css"> .article_item .desc{ margin-left: -40px;} </style> </head> <body> {# 导航条#} <nav class="navbar navbar-inverse"> <div class="container-fluid"> <!-- Brand and toggle get grouped for better mobile display --> <div class="navbar-header"> <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1" aria-expanded="false"> <span class="sr-only">Toggle navigation</span> <span class="icon-bar"></span> <span class="icon-bar"></span> <span class="icon-bar"></span> </button> <a class="navbar-brand" href="#">博客园</a> </div> <!-- Collect the nav links, forms, and other content for toggling --> <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1"> <ul class="nav navbar-nav"> <li class="active"><a href="#">Link <span class="sr-only">(current)</span></a></li> <li><a href="#">Link</a></li> <li class="dropdown"> <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Dropdown <span class="caret"></span></a> <ul class="dropdown-menu"> <li><a href="#">Action</a></li> <li><a href="#">Another action</a></li> <li><a href="#">Something else here</a></li> <li role="separator" class="divider"></li> <li><a href="#">Separated link</a></li> <li role="separator" class="divider"></li> <li><a href="#">One more separated link</a></li> </ul> </li> </ul> <ul class="nav navbar-nav navbar-right"> {% if request.user.username%} <li><a href="">{{ request.user.username }}</a></li> <li class="dropdown"> <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Dropdown <span class="caret"></span></a> <ul class="dropdown-menu"> <li><a href="#">注销</a></li> <li><a href="#">修改密码</a></li> <li role="separator" class="divider"></li> <li><a href="#">更换头像</a></li> </ul> </li> {% else %} <li><a href="">登录</a></li> <li><a href="">注册</a></li> {% endif %} </ul> </div><!-- /.navbar-collapse --> </div><!-- /.container-fluid --> </nav> <div class="container-fluid"> <div class="col-md-2"> <div class="panel panel-primary"> <div class="panel-heading">Panel heading without title</div> <div class="panel-body"> Panel content </div> </div> <div class="panel panel-info"> <div class="panel-heading">Panel heading without title</div> <div class="panel-body"> Panel content </div> </div> <div class="panel panel-warning"> <div class="panel-heading">Panel heading without title</div> <div class="panel-body"> Panel content </div> </div> </div> <div class="col-md-7"> <div class="article_list"> {% for article in article_list %} <div class="article_item"> <div><h5><a href="">{{ article.title }}</a></h5></div> <div class="row"> <div class="col-md-2"><a href="/blog/{{ article.user.username }}"><img width="60" height="60" src="/media/{{ article.user.avatar }}" alt=""></a> </div> <div class="col-md-9 desc"> <p>{{ article.desc }}</p> </div> </div> <div class="small"> <span><a href="/blog/{{ article.user.username }}">{{ article.user.username }}</a>发布于</span> <span>{{ article.create_time|date:'Y-m-d' }}</span> <span class="glyphicon glyphicon-comment"></span>评论({{ article.comment_count }}) <span class="glyphicon glyphicon-thumbs-up"></span>赞({{ article.up_count }}) </div> </div> <hr> {% endfor %} </div> </div> <div class="col-md-3"> <div class="panel panel-danger"> <div class="panel-heading"> <h3 class="panel-title">Panel title</h3> </div> <div class="panel-body"> Panel content </div> </div> </div> </div> <script src="/static/js/jquery-3.2.1.min.js"></script> <script src="/static/bootstrap-3.3.7/js/bootstrap.min.js"></script> </body> </html>
3.学习
https://www.cnblogs.com/yuanchenqi/articles/8715364.html
http://www.cnblogs.com/yuanchenqi/category/1192114.html
二、admin
admin : 数据库后台管理web页面；
path('admin/', admin.site.urls),
admin.py
from django.contrib import admin # Register your models here. from .models import * admin.site.register(UserInfo) admin.site.register(Blog) admin.site.register(Category) admin.site.register(Tag) admin.site.register(Article) admin.site.register(ArticleDetail) admin.site.register(Article2Tag) admin.site.register(ArticleUpDown) admin.site.register(Comment)
http://127.0.0.1:8000/admin/
# 查
http://127.0.0.1:8000/admin/blog/article/
# 增
http://127.0.0.1:8000/admin/blog/article/add/
# 改
http://127.0.0.1:8000/admin/blog/article/1/change/
# 删
http://127.0.0.1:8000/admin/blog/article/1/delete/
三、个人站点
def homesite(request,username,**kwargs): # 当前站点得用户对象 user = UserInfo.objects.filter(username=username).first() if not user: return HttpResponse('404') # 当前站点对象 blog = user.blog # 查询当前站点对应得文章，以及分类，标签，日期归档得文章 if not kwargs: article_list = Article.objects.filter(user=user) else: condition = kwargs.get('condition') param = kwargs.get('param') if condition == 'cate': article_list = Article.objects.filter(user=user, category__title=param) elif condition == 'tag': article_list = Article.objects.filter(user=user, tags__title=param) else: year, month = param.split('-') article_list = Article.objects.filter(user=user).filter(create_time__year=year, create_time__month=month) # 查询站点所有每一个分类 以及 对应得文章数 分组！！ from django.db.models import Count # 查询站点所有每一个分类 以及 对应得文章数 分组！！ cate_list = Category.objects.filter(blog=blog).annotate(count = Count('article')).values('title','count') # 每一个标签以及对应得文章数 tag_list = Tag.objects.filter(blog=blog).annotate(count=Count('article')).values_list('title', 'count') # 日期归档 date_list = Article.objects.filter(user=user).extra( select={"create_ym": "DATE_FORMAT(create_time,'%%Y-%%m')"}).values('create_ym').annotate( c=Count('nid')).values_list('create_ym', 'c') return render(request,'homesite.html',locals())
我的分类 标签 日期归档
<div class="panel panel-info"> <div class="panel-heading">我的分类</div> <div class="panel-body"> {% for cate in cate_list %} {# <p>{{ cate.title }}({{ cate.article_set.all.count }})</p>#} <p><a href="/blog/{{ username }}/cate/{{ cate.title }}">{{ cate.title }}({{ cate.count }})</a></p> {% endfor %} </div> </div> <div class="panel panel-success"> <div class="panel-heading">我的标签</div> <div class="panel-body"> {% for tag in tag_list %} <p><a href="/blog/{{ username}}/tag/{{ tag.0 }}">{{ tag.0 }}({{ tag.1 }})</a></p> {% endfor %} </div> </div> <div class="panel panel-danger"> <div class="panel-heading">日期归档</div> <div class="panel-body"> {% for date in date_list %} <p><a href="/blog/{{ username }}/archive/{{ date.0 }}">{{ date.0 }}({{ date.1 }})</a></p> {% endfor %} </div> </div>
知识点一：
1. url分发，路径拼接
from django.urls import path,re_path,include
re_path(r'blog/',include('blog.urls')),
urlpatterns = [
re_path('(\w+)',views.homesite)
]
url 分发，路径拼接： http://127.0.0.1:8090/blog/egon/
2.分组查询（文章名称 文章数）
1.我的分类
# 查询站点所有每一个分类 以及 对应得文章数 分组 ！！
from django.db.models import Count
cate_list = Category.objects.filter(blog=blog).annotate(count = Count('article')).values('title','count')<p><a href="/blog/{{ username }}/cate/{{ cate.title }}">{{ cate.title }}({{ cate.count }})</a></p>
2.我的标签
# 每一个标签以及对应得文章数 分组 ！！
tag_list = Tag.objects.filter(blog=blog).annotate(count=Count('article')).values_list('title', 'count')<p><a href="/blog/{{ username}}/tag/{{ tag.0 }}">{{ tag.0 }}({{ tag.1 }})</a></p>
3.日期归档
# 日期归档以及对应得文章个数 （date_format extra() 单表分组）
date_list = Article.objects.filter(user=user).extra(
select={"create_ym": "DATE_FORMAT(create_time,'%%Y-%%m')"}).values('create_ym').annotate(
c=Count('nid')).values_list('create_ym', 'c')<p><a href="/blog/{{ username }}/archive/{{ date.0 }}">{{ date.0 }}({{ date.1 }})</a></p>
知识点二：
1.数据库得知识：
http://www.cnblogs.com/yuanchenqi/articles/7250680.html
2.DATE_FORMAT() EXTRA
1.data_format()
DATE_FORMAT(date,fmt) # 针对 mysql 依照指定得fmt格式化日期 date 值
strftime(fmt,date) # 针对 sqllite
eg:
SELECT DATE_FORMAT('1999-01-01 12:12:12', '%Y-%m-%d');
# 1999-01-01
2.orm强大：
数据迁移时，数据库换了,代码不用动！每次orm翻译成sql按照引擎翻译成 mysql .. oracle .. sqlite ..
纯sql语句，效率高！
orm提供了接口，可以写sql 但是在数据库迁移时，需要修改！！
3.extra函数：
是orm 开出来得一个 操作sql得接口！！
http://www.cnblogs.com/yuanchenqi/articles/7570003.html
简介：
extra(select=None, where=None, params=None,
tables=None, order_by=None, select_params=None)
eg:
queryResult=models.Article
.objects.extra(select={'is_recent': "create_time > '2017-09-05'"})
# in sqlite:
article_obj=models.Article.objects
.filter(nid=1)
.extra(select={"standard_time":"strftime('%%Y-%%m-%%d',create_time)"})
.values("standard_time","nid","title")
print(article_obj)
# <QuerySet [{'title': 'MongoDb 入门教程', 'standard_time': '2017-09-03', 'nid': 1}]>
queryResult=models.Article
.objects.extra(where=['nid in (1,3) OR title like "py%" ','nid>2'])
4.extra应用：
queryResult=models.Article.objects.all().extra(select={'xxx': "create_time > '2017-09-05'"})
id title desc create_time
1 a .. 2012-12
2 b .. 2012-11
3 c .. 2012-10
执行完上面 那句话
id title desc create_time xxx
1 a .. 2012-12 0
2 b .. 2012-11 0
3 c .. 2012-10 1
5.日期归档得分类：
1.
date_list = Article.objects.filter(user=user).extra(
select={"create_ym":"DATE_FORMAT(create_time,'%%Y-%%m')"}).values('title','create_time','create_ym')
# extra 得结果
<QuerySet [{'create_ym': '2018-06', 'title': 'DNS的主从，转发与负载功能',
'create_time': datetime.datetime(2018, 6, 4, 6, 0, tzinfo=<UTC>)},
{'create_ym': '2018-06', 'title': 'spring boot 2.0 源码分析（二）',
'create_time': datetime.datetime(2018, 6, 5, 16, 7, 41, tzinfo=<UTC>)}]>
2.
date_list = Article.objects.filter(user=user).extra(
select={"create_ym":"DATE_FORMAT(create_time,'%%Y-%%m')"}).values('create_ym').annotate(
c = Count('nid')).values_list('create_ym','c')
# extra 分组 得结果： <QuerySet [('2018-06', 2)]>
总结：
用的知识点：
单表分组 queryset.value('').annotate()
queryset.extra()
DATE_FORMAT(date,fmt)
知识点三：
1. 分类、标签、日期归档 对应得文章
1.分类：
http://127.0.0.1:8090/blog/egon/cate/openstack
2.标签：
http://127.0.0.1:8090/blog/egon/tag/dream
3.日期归档：
http://127.0.0.1:8090/blog/egon/archive/2018-06
知识点：
url 分配： 正则匹配，有名分组 **kwargs
urlpatterns = [
re_path('(?P<username>\w+)/(?P<condition>tag|cate|archive)/(?P<param>.*)',views.homesite),
# homesite(request,username=egon,condition=tag,param=python)
re_path('(?P<username>\w+)/$',views.homesite)
]
def homesite(request,username,**kwargs):
# 查询当前站点对应得文章，以及分类，标签，日期归档得文章
if not kwargs:
article_list = Article.objects.filter(user=user) # 基于queryset查询 一般用这种方法 效率高
else:
condition = kwargs.get('condition')
param = kwargs.get('param')
if condition == 'cate':
article_list = Article.objects.filter(user=user, category__title=param)
elif condition == 'tag':
article_list = Article.objects.filter(user=user, tags__title=param)
else:
year, month = param.split('-')
article_list = Article.objects.filter(user=user).filter(create_time__year=year, create_time__month=month)
return render(request,'homesite.html',locals())
2. 注意点：
注意：
如果时间对应得文章没有显示出来：
需配置：
TIME_ZONE = 'Asia/Shanghai'
USE_TZ = False
Django models通过DateTimeField保存到MySQL的时间的时区问题:
https://blog.csdn.net/win_turn/article/details/53000770
settings:
TIME_ZONE = 'Asia/Shanghai'
USE_TZ = False
# TIME_ZONE = 'UTC'
# USE_TZ = True
源码：
def now():
# Returns an aware or naive datetime.datetime, depending on settings.USE_TZ.
if settings.USE_TZ:
# timeit shows that datetime.now(tz=utc) is 24% slower
return datetime.utcnow().replace(tzinfo=utc)
else:
return datetime.now()
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>homesite</title> <link rel="stylesheet" href="/static/bootstrap-3.3.7/css/bootstrap.css"> <style type="text/css"> .header{ width: 100%; height: 40px; background-color: #336699; line-height: 40px; font-size: 16px; color: white;} .header p{ margin-left: 15px;} </style> </head> <body> <div class="header"> <p class="title">{{ blog.title }}</p> </div> <div class='container'> <div class="col-md-3"> <div class="panel panel-info"> <div class="panel-heading">我的分类</div> <div class="panel-body"> {% for cate in cate_list %} {# <p>{{ cate.title }}({{ cate.article_set.all.count }})</p>#} <p><a href="/blog/{{ username }}/cate/{{ cate.title }}">{{ cate.title }}({{ cate.count }})</a></p> {% endfor %} </div> </div> <div class="panel panel-success"> <div class="panel-heading">我的标签</div> <div class="panel-body"> {% for tag in tag_list %} <p><a href="/blog/{{ username}}/tag/{{ tag.0 }}">{{ tag.0 }}({{ tag.1 }})</a></p> {% endfor %} </div> </div> <div class="panel panel-danger"> <div class="panel-heading">日期归档</div> <div class="panel-body"> {% for date in date_list %} <p><a href="/blog/{{ username }}/archive/{{ date.0 }}">{{ date.0 }}({{ date.1 }})</a></p> {% endfor %} </div> </div> </div> <div class="col-md-8"> <div class="article_list"> {% for article in article_list %} <div class="article_item"> <div><h5><a href="">{{ article.title }}</a></h5></div> <div class="row"> <div class="col-md-9 desc"> <p>{{ article.desc }}</p> </div> </div> <div class="small"> 发布于 <span>{{ article.create_time|date:'Y-m-d' }}</span> <span class="glyphicon glyphicon-comment"></span>评论({{ article.comment_count }}) <span class="glyphicon glyphicon-thumbs-up"></span>赞({{ article.up_count }}) </div> </div> <hr> {% endfor %} </div> </div> </div> </body> </html>
四、ORM查询练习
models.py
from django.db import models class AuthorDetail(models.Model): nid = models.AutoField(primary_key=True) birthday = models.DateField() telephone = models.BigIntegerField() addr = models.CharField(max_length=64) class Author(models.Model): nid = models.AutoField(primary_key=True) name = models.CharField(max_length=32) age = models.IntegerField() # 与AuthorDetail建立一对一的关系 authorDetail = models.OneToOneField(to="AuthorDetail",on_delete=models.CASCADE,related_name='authors') class Publish(models.Model): nid = models.AutoField(primary_key=True) name = models.CharField(max_length=32) city = models.CharField(max_length=32) email = models.EmailField() class Book(models.Model): nid = models.AutoField(primary_key=True) title = models.CharField(max_length=32) publishDate = models.DateField() price = models.DecimalField(max_digits=5, decimal_places=2) keepNum = models.IntegerField() commentNum = models.IntegerField() # 与Publish建立一对多的关系,外键字段建立在多的一方 publish = models.ForeignKey(to="Publish", to_field="nid",on_delete=models.CASCADE) # 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个，自动创建第三张表 authors = models.ManyToManyField(to='Author')
orm查询
基于对象查询（子查询）、 基于queryset 和 __ 查询（join查询）、 单表分组 、多表分组
from django.shortcuts import render,HttpResponse from app01.models import * def query(request): ###################### 基于对象得跨表查询 （子查询）###################### """ 一对多： Book -------> Publish 正向查询按字段 反向查询按表名 小写_set """ # 1.查询 id=2 得书籍对应得出版社得邮箱 正向 ret = Book.objects.filter(nid=2).first().publish.email # 2.橘子出版社出版过得所有书籍得名字 反向 ret = Publish.objects.filter(name='橘子出版社').first().book_set.all().values('title') """ 多对多： Book -------> Author 正向查询按字段 反向查询按表名 小写_set """ # 1. 查询金瓶mei所有作者得名字 正向 ret = Book.objects.filter(title='金瓶mei').first().authors.all().values('name') # 2.作者alex 出版过得书籍得个数 反向 ret = Author.objects.filter(name='alex').first().book_set.all().values('title') ret = Author.objects.filter(name='alex').first().book_set.all().count() """ 一对一： Author -------> AuthorDetail 正向查询按字段 反向查询按表名 小写 或者自定义得 related_name='authors' """ # 1.查询alex得手机号 正向 ret = Author.objects.filter(name='alex').first().authorDetail.telephone # 2.住在烟台得作者得名字 反向 ret = AuthorDetail.objects.filter(addr='烟台').first().authors.name ret = AuthorDetail.objects.filter(addr='烟台') for i in ret: print(i.authors.name) ########################################### # 基于对象得跨表查询 （子查询） """ SELECT "app01_book"."nid", "app01_book"."title", "app01_book"."publishDate", "app01_book"."price", "app01_book"."publish_id" FROM "app01_book" WHERE "app01_book"."nid" = 1 ORDER BY "app01_book"."nid" ASC LIMIT 1; args=(1,) SELECT "app01_publish"."nid", "app01_publish"."name", "app01_publish"."city", "app01_publish"."email" FROM "app01_publish" WHERE "app01_publish"."nid" = 1; args=(1,) """ ###################### 基于queryset和__ 得跨表查询 ###################### """ 正向查询按字段 反向查询按表名 """ # 1.查询 价格为100 得书籍对应得出版社得邮箱 ret = Book.objects.filter(price=100).values('publish__email') """ values:内部 queryset = Book.objects.filter(price=100) temp = [] for obj in queryset: temp.append({ 'title':obj.title, 'publish_email':obj.publish_email }) temp """ # 2.橘子出版社出版过得所有书籍得名字 ret = Publish.objects.filter(name='橘子出版社').values("book__title") ret = Book.objects.filter(publish__name='橘子出版社').values('title') # 1. 查询金瓶mei所有作者得名字 ret = Book.objects.filter(title='金瓶mei').values('authors__name') ret = Author.objects.filter(book__title='金瓶mei').values('name') # 2.作者alex 出版过得书籍得个数 ret = Author.objects.filter(name='alex').values('book__title').count() ret = Book.objects.filter(authors__name='alex').values('title').count() # 1.查询alex得手机号 ret = Author.objects.filter(name='alex').values('authorDetail__telephone') ret = AuthorDetail.objects.filter(authors__name='alex').values('telephone') # 2.住在烟台得作者得名字 ret = Author.objects.filter(authorDetail__addr='烟台').values('name') ret = AuthorDetail.objects.filter(addr='烟台').values('authors__name') ########################################### # 基于queryset和__ 得跨表查询 """ SELECT "app01_book"."title", "app01_book"."price" FROM "app01_book" INNER JOIN "app01_publish" ON ("app01_book"."publish_id" = "app01_publish"."nid") WHERE "app01_publish"."name" = '人名出版社2' LIMIT 21; args=('人名出版社2',) """ ###################### 分组查询(annotate) 与 聚合查询（avg count sum min）###################### # 员工表 emp 部门表 dep
""" 单表分组 sql: select dep,AVG(salary) from emp group by dep ORM查询 Emp.objects.values('dep').annotate(dep_avg = AVG(salary)).values('dep','dep_avg') 多表分组 sql： select AVG(salary) from emp group by dep_id select dep.name,AVG(emp.salary) from emp inner join dep on (emp.dep_id == dep.id) group by emp.dep_id ORM查询： Dep.objects.all().annotate(avg = AVG('emp__salary')).values('name','avg') 注：
queryset.annotate: 按着select的字段进行 group by
""" # 1.每个出版社出版过得书名称，书得个数 from django.db.models import Count,Avg # ret = Publish.objects.all().annotate() # 每个出版社对象 被分为一个组 # select * from publish group by id ret = Publish.objects.all().annotate(book_count = Count('book__title')).values('name','book_count') # 2.每一个作者名字以及对应书籍得平均价格 ret = Author.objects.all().annotate(books_avg = Avg('book__price')).values('name','books_avg') # 3.查询每一本书得名字以及作者得个数 ret = Book.objects.all().annotate(authors_count = Count('authors')).values('title','authors_count') # 单表分组查询？ Book.objects.all().annotate() # 每一个book 得 id group by # select 哪个字段 就按 哪个字段 group by ret = Book.objects.all().values('title').annotate(c = Count('*')).values('title','c') # SELECT "app01_book"."title", COUNT(*) AS "c" FROM "app01_book" GROUP BY "app01_book"."title"; args=() ########################################### # from django.core import serializers # 将quertset转化成json # ret = serializers.serialize('json',ret) # return HttpResponse(ret) # import json # return HttpResponse(json.dumps(list(ret),ensure_ascii=False)) print(ret) return HttpResponse(ret)
