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>&nbsp;&nbsp;&nbsp;
                        <span>{{ article.create_time|date:'Y-m-d' }}</span>&nbsp;&nbsp;&nbsp;&nbsp;
                        <span class="glyphicon glyphicon-comment"></span>评论({{ article.comment_count }})&nbsp;&nbsp;&nbsp;
                        {#  <span class="glyphicon glyphicon-comment"></span>评论({{ article.comment_set.count }})&nbsp;&nbsp;&nbsp;#}
                        <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>&nbsp;&nbsp;&nbsp;
                        <span>{{ article.create_time|date:'Y-m-d' }}</span>&nbsp;&nbsp;&nbsp;&nbsp;
                        <span class="glyphicon glyphicon-comment"></span>评论({{ article.comment_count }})&nbsp;&nbsp;&nbsp;
                        <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">
                        发布于&nbsp;&nbsp;&nbsp;
                        <span>{{ article.create_time|date:'Y-m-d' }}</span>&nbsp;&nbsp;&nbsp;&nbsp;
                        <span class="glyphicon glyphicon-comment"></span>评论({{ article.comment_count }})&nbsp;&nbsp;&nbsp;
                        <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)