python_day19 Django多表关联查询
class Book(models.Model):
title=models.CharField(max_length=32)
# 书籍与出版社： 一对多
publisher=models.ForeignKey(to="Publish",to_field="id") # 与这本书关联的出版社对象，因为是一对多的关系，所以，出版社对象只能有一个。
# 书籍与作者： 多对多
authors=models.ManyToManyField("Author")
class Publish(models.Model):
name=models.CharField(max_length=32)
class Author(models.Model):
name=models.CharField(max_length=32)
age=models.IntegerField()
tel=models.CharField(max_length=32)
单表操作：
1、添加
(1) 表.objects.create(**kwargs)
(2) obj=表(**kwargs)
obj.save()
1 模板语法：
（1） 自定义过滤器和标签
（2） 模板继承 :
base.html {% block %} {%end block%}
index(继承母版)： extend "base.html" {% block %} {%end block%}
2 ORM跨表添加
ORM一对多的添加
1、
publish_obj=Publish.objects.get(id=2)
表.objects.create(title="python",publisher=publish_obj)
2、
表.objects.create(title="python",publisher_id=2)
ORM多对多的添加
authors=models.ManyToManyField("Author") # 与这本书关联的作者对象集合
views:
def add(request):
if request.method == "POST":
book_obj=models.Book.objects.create(title=title,...,pulisher_id=publish_id)
绑定作者关系错误方法：
alex_id=models.Author.objects.get(name="alex").id
egon_id=models.Author.objects.get(name="egon").id
表.objects.create(book_id=book_obj.id,author_id=alex_id)
不能直接在第三张表中插入记录，因为没有第三张表名
authors字段：与这本书关联的作者对象集合
book_obj.authors.all() QuerySet
alex=models.Author.objects.get(name="alex")
book_obj.authors.add(alex,egon)
class Author():
def __str__(self):
return self.name+" "+str(self.age)
解除绑定关系
book_obj=models.Book.objects.get(nid=14)
alex=models.Author.get(name="alex")
book_obj.autors.remove(alex)
author_list=models.Author.objects.filter(id__gt=1)
book_obj.authors.remove(*author_list)
book_obj.authors.clear() 清空
绑定关系
book_obj.authors.add(obj,obj2,...)
book_obj.authors.add(*[])
解除关系
book_obj.authors.remove(obj,obj2,...)
book_obj.authors.remove(*[])
book_obj.authors.clear()
3 ORM跨表查询（1 基于对象 2 基于双下划线）
#####基于对象的跨表查询
###########################################一对多跨表查询########################
# 正向查询： 按字段
# 查询 python这本书的出版社的名称和地址
# book_python=models.Book.objects.filter(title="python").first()
#
# print(book_python.title)
# print(book_python.price)
#
# print(book_python.publisher) # Publish object : 与这本书关联的出版社的对象
# print(book_python.publisher.name)
# print(book_python.publisher.addr)
# 反向查询：按关联的表名（小写）_set
# 查询人民出版社出版过的所有书籍名称及价格
# pub_obj=models.Publish.objects.get(name="renmin")
# book_list=pub_obj.book_set.all() # QuerySet 与这个出版社关联的所有书籍对象
#
# for obj in book_list:
# print(obj.title,obj.price)
###########################################一对一查询########################
# 正向查询： 按字段
# 查询addr在沙河的作者
authorDetail=models.AuthorDetail.objects.get(addr="shahe")
print(authorDetail.author.name) # alex
# 反向查询：按 表名（小写）
# 查询 alex混迹在哪里
alex=models.Author.objects.get(name="alex")
print(alex.authordetail.addr) # shahe
###########################################多对多查询########################
# 多对多的正向 查询： 按字段
# 查询 python这本书的所有作者的姓名和年龄
# book_python=models.Book.objects.get(title="python")
# author_list=book_python.authors.all()
# for obj in author_list:
# print(obj.name,obj.age)
#
# book_pythons = models.Book.objects.filter(title="python")
# for book_python in book_pythons:
# author_list = book_python.authors.all()
# for obj in author_list:
# print(obj.name, obj.age)
# 多对多的反向查询 按关联的表名（小写）_set
# alex出版过的所有书籍的明显名称
# alex=models.Author.objects.get(name="alex")
# book_list=alex.book_set.all()
# for i in book_list:
# print(i.title,i.price)
一对一关系：
class AuthorDetail(models.Model):
addr=models.charFiled(max_length=32)
author=models.OneToOneFiled("Author")
数据库迁移
一对一查询：
alex的addr
author_obj=models.Author.objects.get(name="alex")
author_obj.AuthorDetail.addr
查询在沙河的作者
author_obj=models.AuthorDetail.objects.get(addr="shahe")
print(author_obj.author.name)
#####基于双下划线的跨表查询
JS：
var eles_p=document.getElementByTagName("p"); [p1,p2,p3,p4,p5]
for(var i=0;i<eles_p.length;i++){
eles_p[i].style.color="red"
}
jquery:
$("p").css("color","red")
正向查询：按字段
反向查询：按表名
# 查询 python这本书的价格
ret=models.Book.objects.filter(title="python").values("price","title")
print(ret) # <QuerySet [{'price': Decimal('122.00')}]>
#查询python这本书的出版社的名称和地址
# 正向查询 按字段 基于book表
# ret2=models.Book.objects.filter(title="python").values_list("publisher__name")
# print(ret2)
#
# # 反向查询 按表名 if 设置了related_name: 按设置值
# ret3=models.Publish.objects.filter(bookList__price=333).values_list("name","addr").distinct()
# print(ret3)
# 查询人民出版社出版过的所有书籍名称及价格
# ret4=models.Book.objects.filter(publisher__name="renmin").values("title","price")
# print(ret4.count())
# ret5=models.Publish.objects.filter(name="renmin").values("bookList__title","bookList__price")
# print(ret5.count())
#查询egon出过的所有书籍的名字(多对多)
# ret6=models.Author.objects.filter(name="egon").values_list("book__title")
# print(ret6)
# ret7=models.Book.objects.filter(authors__name__contains="eg").values("title")
# print(ret7)
# 地址以沙河开头的的作者出版过的所有书籍名称以及出版社名称
# ret8=models.Book.objects.filter(authors__authordetail__addr__startswith="sha").values("title","publisher__name")
# print(ret8)
sql与ORM：
SELECT `app01_publish`.`name`
FROM `app01_book`
INNER JOIN `app01_publish`
ON (`app01_book`.`publisher_id` = `app01_publish`.`id`)
WHERE `app01_book`.`title` = 'python'
LIMIT 21;
SELECT `app01_publish`.`name`
FROM `app01_publish` INNER JOIN `app01_book`
ON (`app01_publish`.`id` = `app01_book`.`publisher_id`)
WHERE `app01_book`.`title` = 'python'
LIMIT 21;
4 回顾聚合与分组
1 聚合函数 SUM AVG MIN MAX COUNT
2 聚合函数可以单独使用，不一定要和分组配合使用；只不过聚合函数与group by 搭配
3 统计每一个部门有多少人： select COUNT(name) from emp group by dep_id
select book.id ,book.title,count(1) from book join bookAuthor on book.id=bookAuthor.book_id group by book.id,book.title,
def juheJquery(request):
from django.db.models import Avg,Count,Sum,Min,Max
# 单纯聚合函数
# 计算所有图书的平均价格
# ret=models.Book.objects.all().aggregate(priceSum=Sum("price"))
# print(ret) # {'priceSum': Decimal('2158.00')}
# 统计每一本书的作者个数
# ret2=models.Book.objects.all().annotate(authors_num=Count("authors")) # QuerySet
# print(ret2) # [book_obj1,book_obj2,book_obj3,book_obj4,....]
#
# for obj in ret2:
# print(obj.nid,obj.title,obj.authors_num)
# 查询每一个出版社出版过的所有书籍的总价格
#方式1：
# ret3=models.Publish.objects.all().annotate(priceSum=Sum("bookList__price"))
#
# for obj in ret3:
# print(obj.id,obj.name,obj.priceSum)
# ret4 = models.Publish.objects.all().annotate(priceSum=Sum("bookList__price")).values("name","priceSum")
# print(ret4)
# 方式2：
# ret5=models.Book.objects.all().values("publisher__name").annotate(priceSum=Sum("price")).values("publisher__name","priceSum")
# print(ret5)
5 F与Q查询
F查询
class Book:read_num=models.IntegerField(default=0)
comment_num=models.IntegerField(default=0)
数据库迁移
def fqQuery(request):
return HttpResponse("OK")
ret1=models.Book.objects.filter(comment_num__gt=50)
from django.db.models import F,Q
#查询评论数大于阅读数
models.Book.objects.filter(comment_num__gt=F("read_num"))
#查询评论数大于阅读数2倍
models.Book.objects.filter(comment_num__gt=F("read_num")*2)
models.Book.objects.all().update(price=F(price)+10)
Q查询
from django.db.models import Q
models.Book.objects.filter(Q(comment_num)__gt=50|Q(read_num)__gt=50) 或的关系
models.Book.objects.filter(Q(comment_num)__gt=50&Q(read_num)__gt=50) 且的关系
models.Book.objects.filter(comment_num__gt=50,read_num__gt=50) 不加Q,且的关系
注：Q与,混用时，Q对象必须在关键字前面
============================================
ORM修改
1 obj.name="egon" obj.save() 效率低
2 表.objects.all().update(name="") 推荐
注意点：update方法是QuerySet数据类型的方法。model对象不能调用。
ORM删除
表.objects.filter().delete()
注意事项：
1 、 delete()是QuerySet数据类型的方法
2 、 级联删除
网页版实现关注点记录：
urls: url(r'^del/(\d+)', views.delete),
def add(request):
if request.method == "POST":
titles = request.POST.get("title")
pubdate = request.POST.get("pubdate")
price = request.POST.get("price")
publish_id = request.POST.get("publish")
# 一对多 添加数据 方式1
# publish_obj=models.Publish.objects.get(name="renmin")
# book_obj=models.Book.objects.create(title=title,price=price,pubDate=pubDate,publisher=publish_obj)
# 一对多 添加数据 方式2
book_obj=models.Book.objects.create(title=titles,price=price,pubDate=pubdate,publisher_id=publish_id)
#多对多绑定关系
authors_id=request.POST.getlist("authors")
authorList=models.Author.objects.filter(id__in=authors_id)
book_obj.authors.add(*authorList)
return redirect("/index/")
publish_list=models.Publish.objects.all()
authors=models.Author.objects.all()
return render(request,"add.html",{"publish_list":publish_list,"authors":authors})
def delete(request,id):
models.Book.objects.get(id=id).authors.clear()
models.Book.objects.filter(id=id).delete()
return redirect("/index/")
def edit(request,id):
if request.method == "POST":
titles = request.POST.get("title")
pubdate = request.POST.get("pubdate")
price = request.POST.get("price")
publish_id = request.POST.get("publish")
# 一对多 添加数据 方式1
# publish_obj=models.Publish.objects.get(name="renmin")
# book_obj=models.Book.objects.create(title=title,price=price,pubDate=pubDate,publisher=publish_obj)
# 一对多 添加数据 方式2
# book_obj = models.Book.objects.get(id=id)
models.Book.objects.filter(id=id).update(title=titles, price=price, pubDate=pubdate, publisher_id=publish_id)
# 多对多解除绑定关系
models.Book.objects.get(id=id).authors.clear()
#多对多绑定关系
authors_id = request.POST.getlist("authors")
authorList = models.Author.objects.filter(id__in=authors_id)
models.Book.objects.get(id=id).authors.add(*authorList)
return redirect("/index/")
book_obj=models.Book.objects.get(id=id)
publish_list=models.Publish.objects.all()
authors=models.Author.objects.all()
return render(request,"edit.html",{"book_obj":book_obj,"publish_list":publish_list,"authors":authors})
templates:
index.html:
<td>{{ book_obj.publisher.name }}</td>
<td>
{% for author in book_obj.authors.all %}
{{ author.name }}
{% endfor %}
</td>
<td>
<a href="/edit/{{ book_obj.id }}"><button class="btn btn-info">编辑</button></a>
<a href="/del/{{ book_obj.id }}"><button class="btn btn-danger">删除</button></a>
</td>
add.html:
<div class="form-group">
<label for="publish">出版社
<select name="publish" id="publish">
{% for publish in publish_list %}
<option value="{{ publish.id }}">{{ publish.name }}</option>
{% endfor %}
</select>
</label>
</div>
<div class="form-group">
<label for="authors">作者
<select name="authors" id="authors" multiple size="3">
{% for author in authors %}
<option value="{{ author.id }}">{{ author.name }}</option>
{% endfor %}
</select>
</label>
</div>
edit.html:
<div class="form-group">
<label for="publish">出版社
<select name="publish" id="publish">
{% for publish in publish_list %}
{% if book_obj.publisher == publish %}
<option value="{{ publish.id }}" selected="True">{{ publish.name }}</option>
{% else %}
<option value="{{ publish.id }}">{{ publish.name }}</option>
{% endif %}
{% endfor %}
</select>
</label>
</div>
<div class="form-group">
<label for="authors">作者
<select name="authors" id="authors" multiple size="3">
{% for author in authors %}
{% if author in book_obj.authors.all %}
<option value="{{ author.id }}" selected="True">{{ author.name }}</option>
{% else %}
<option value="{{ author.id }}">{{ author.name }}</option>
{% endif %}
{% endfor %}
</select>
</label>
</div>
今日作业：
1、整理博客
2、增删编辑
3、添加分页 http://www.cnblogs.com/yuanchenqi/articles/7652353.html#_label0
下周课程：
1 ajax
2 分页
3 COOKKIE SESSION
