from django.conf.urls import url
from django.contrib import admin

from app01 import views
from app01 import views
'''给URL起别名,在URL后面加name参数'''
urlpatterns = [
    url(r'^admin/', admin.site.urls),
    url(r'^book/list/', views.book_list, name='book_list'),
    url(r'^book/add/', views.book_add,name='book_add'),
    url(r'^book/edit/(\d+)/', views.book_edit, name='book_edit'), # /book/edit/2/
    url(r'^book/del/(?P<n>\d+)/', views.book_del,name='book_del'), # /book/edit/2/
]

from django.urls import reverse

print(reverse('book_list')) #/book/list/
print(reverse('book_edit',args=(2,))) # /book/edit/1/  URL的无名分组参数
print(reverse('book_edit',kwargs={'n': 1,})) # /book/edit/1/  URL的有名分组参数


return redirect('book_list')
'''redirect重定向可以不用写reverse就可以反向解析,因为redirect方法里有reverse方法'''
return redirect(reverse('book_list'))

<!-- 无参数的  -->
<a class="btn btn-primary" href="{% url 'book_add' %}">添加书籍</a>

<!-- 无名分组参数的 -->
<a href="{% url 'book_edit' books.id 3 4 %}" class="btn btn-warning">编辑</a>

<!-- 有名分组 -->
<!-- 以下两种方法都可以 -->
<a href="{% url 'book_del' books.id %}" class="btn btn-danger">删除</a>
<a href="{% url 'book_del' n=books.id %}" class="btn btn-danger">删除</a>

from django.conf.urls import url, include
from django.contrib import admin

from app01 import views

'''URL分发使用include方法,命名空间使用namespace属性'''
urlpatterns = [
    url(r'^admin/', admin.site.urls),
    url(r'^app01/', include('app01.urls',namespace='app01')),
    url(r'^app02/', include('app02.urls',namespace='app02')),
]

from app01 import views

urlpatterns = [
    url(r'^index/', views.index, name='index'),
    url(r'^book/list/', views.book_list, name='book_list'),
    url(r'^book/add/', views.book_add, name='book_add'),
    url(r'^book/edit/(\d+)/', views.book_edit, name='book_edit'), 
    url(r'^book/del/(?P<n>\d+)/', views.book_del, name='book_del'), 
]

from app02 import views

urlpatterns = [
    url(r'^index/', views.index, name='index'),
]

def index(request):
    print(reverse('app01:index'))
    return HttpResponse('app01的路径:' + reverse('app01:index'))

def index(requset):
    print(reverse('app02:index'))
    return HttpResponse('app02的路径: ' + reverse('app02:index'))

# 一对一修改和一对多修改
models.Author.objects.filter(name='张三').update(
    name='李四',
    age=50,
    # ad=models.AuthorDetail.objects.get(id=1), 两种方式都可以
    ad_id=1,
)
# 多对多修改
obj = models.Book.objects.get(id=2)
obj.authors.set(['3',])
'''多对多修改使用set方法 注意:set的值是字符串,set的原理是clear+add'''

# 方式1
from django.db import connection
print(connection.queries)

# 方式2
'''settings.py文件中配置如下内容'''
LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console':{
            'level':'DEBUG',
            'class':'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level':'DEBUG',
        },
    }
}
'''方式1：配置之后,可以使用下面的代码执行sql语句'''
cursor = connection.cursor()
cursor.execute('select * from app01_book;')
print(cursor.fetchall())

'''方式2：也可以使用pymysql执行sql语句'''


'''方式3：可以使用objects下的raw方法执行sql语句'''
ret = models.Author.objects.raw('select * from app01_author;')  # 注意：只限于本表操作
print(ret)
for i in ret:
    print(i.name)

'''一对一:正向查询'''
# 查看张三作者的家庭住址


# 1.sql语句查询
select app01_authordetail.addr from app01_author inner join app01_authordetail on app01_author.ad_id app01_atuhordetail.id where app01_author.name='张三'
# 2.基于对象的跨表查询
obj = models.Author.objects.get(name='张三')
print(obj.ad.addr) 

'''一对一：反向查询'''
# 查询北京的作者名称
obj = models.AuthorDetail.objects.get(addr='北京')
print(obj.author.name) # 反向查询:对象.表名小写.属性

'''一对多:正向查询'''
# 查询西游记这本书的出版社
obj = models.Book.objects.get(title='西游记')
print(obj.publishs.name)

'''一对多:反向查询'''
'''一对多查询,给一查多,结果有多个:要使用对象.表名_set.all()'''
# 查询马哥出版社出版的书籍有哪些
ret = models.Publish.objects.get(name='马哥出版社')
books = ret.book_set.all()  # <QuerySet [<Book: Book object>, <Book: Book object>]>
print(books.values('title')) # all()查询的是queryset对象,想得到里面的具体内容,要用values(字段)

'''一对一:正向查询'''
# 查看张三作者的家庭住址
# 1.sql语句查询
select app01_authordetail.addr from app01_author inner join app01_authordetail on app01_author.ad_id app01_atuhordetail.id where app01_author.name='张三'
# 2.基于对象的跨表查询
obj = models.Author.objects.get(name='张三')
print(obj.ad.addr) 

'''一对一：反向查询'''
# 查询北京的作者名称
obj = models.AuthorDetail.objects.get(addr='北京')
print(obj.author.name) # 反向查询:对象.表名小写.属性
    

'''一对多:正向查询'''
# 查询西游记这本书的出版社
obj = models.Book.objects.get(title='西游记')
print(obj.publishs.name)

'''一对多:反向查询'''
'''一对多查询,给一查多,结果有多个:要使用对象.表名_set.all()'''
# 查询马哥出版社出版的书籍有哪些
ret = models.Publish.objects.get(name='马哥出版社')
books = ret.book_set.all()  # <QuerySet [<Book: Book object>, <Book: Book object>]>
print(books.values('title')) # all()查询的是queryset对象,想得到里面的具体内容,要用values(字段)
    
'''多对多:正向查询'''
# 查询西游记是谁写的
# 1.sql查询
select app01_author.name from app01_book inner join app01_book_authors on app01_book.id =
app01_book_authors.book_id inner join app01_author on app01_author.id = app01_book_authors.author_id

# 2.正向查询
obj = models.Book.objects.get(title='西游记')
print(obj.authors.all().values('name'))

'''多对多:反向查询'''
# 查询张三写了哪些书
obj = models.Author.objects.get(name='张三')
print(obj.book_set.all().values('title'))    

# 一对一
# 查看张三作者的家庭住址
'''正向写法'''
ret =  models.Author.objects.filter(name='张三').values('ad__addr')
'''反向写法'''
ret = models.AuthorDetail.objects.filter(author__name='张三').values('addr')
print(ret) # <QuerySet [{'addr': '北京'}]>

# 一对多
# 查询西游记这本书的出版社
'''正向写法'''
ret = models.Book.objects.filter(title='西游记').values('publishs__name')
print(ret) # <QuerySet [{'publishs__name': '小马哥出版社'}]>
'''反向写法'''
ret = models.Publish.objects.filter(book__title='西游记').values('name')
print(ret) # <QuerySet [{'name': '小马哥出版社'}]>

# 多对多
# 查询水浒传是谁写的
'''正向写法'''
ret = models.Book.objects.filter(title='水浒传').values('authors__name')
print(ret)
'''反向写法'''
ret = models.Author.objects.filter(book__title='水浒传').values('name')
print(ret) #<QuerySet [{'name': '王五'}, {'name': '赵六'}]>

from django.db.models import Avg,Max,Min,Count,Sum
# 聚合查询
'''聚合查询使用aggregate()方法'''
ret = models.Book.objects.all().aggregate(Avg('price'))
ret = models.Book.objects.all().aggregate(a=Avg('price'),m=Max('price'))
print(ret,type(ret)) #{'price__avg': 15.0} <class 'dict'>
'''注意结果为字典类型.'''

# 统计一下每个出版社出版书的平均价格
# 1.sql查询
select publishs_id,avg(price) from app01_book group by publishs_id;
select avg(app01_book.price) from app01_book inner join app01_publish on 
app01_book.publishs_id = app01_publish.id group by app01_publish.name;

# 2.分组查询
ret = models.Book.objects.values('publishs_id').annotate(a=Avg('price')) # 方式一

ret = models.Publish.objects.annotate(a=Avg('book__price')) # 方式二
# <QuerySet [<Publish: Publish object>, <Publish: Publish object>]>
print(ret.values('a','name'))

# 查询一下点赞数大于评论数的书籍
'''1.传统方法'''
ret = models.Book.objects.all()
book_list = []
for i in ret:
    if i.dianzan > i.comment:
        book_list.append(i)

'''2.F查询'''
from django.db.models import F
''''针对本表不同字段数据进行对比时或者本表字典做一些统一修改时使用F查询'''
# 点赞数大于评论数的
ret = models.Book.objects.filter(dianzan__gt=F('comment'))

'''F查询也支持统一修改'''
# 所有书籍上调10块
models.Book.objects.all().update(price=F('price')+10) # 支持四则运算

from django.db.models import Q
'''
| -- or
& -- and
~ -- not
'''

'''and与和or或'''
ret = models.Book.objects.filter(Q(comment__gt=30)|Q(dianzan__gt=50))

ret = models.Book.objects.filter(Q(comment__gt=30)&Q(dianzan__gt=50))
# 等同于# ret = models.Book.objects.filter(comment__gt=30,dianzan__gt=50)

ret = models.Book.objects.filter(Q(comment__gt=30)|Q(dianzan__gt=50),publishDate__year='2018')
# 注意没有Q包裹的条件,写在Q包裹的条件后面.并且Q查询和publishyear之间是and的关系

'''Q查询多层嵌套'''
ret = models.Book.objects.filter(Q(Q(comment__gt=30)|Q(dianzan__gt=50))&Q(xx=11),publishDate__year='2018')

'''条件取反:波浪线写在Q前面'''
# 取评论数小于等于30 的,或者点赞数大于50的
ret = models.Book.objects.filter(~Q(comment__gt=30)|Q(dianzan__gt=50))