python_day19 Django多表关联查询-白红宇

python_day19 Django多表关联查询

阅读量：4310 次

发布时间：2019-06-06

本文共 11092 字，大约阅读时间需要 36 分钟。

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:

<label for="publish">出版社

{% for publish in publish_list %}

<option value="{

{ publish.id }}">{

{ publish.name }}</option>

{% endfor %}

</select>

</label>

</div>

<label for="authors">作者

{% for author in authors %}

<option value="{

{ author.id }}">{

{ author.name }}</option>

{% endfor %}

</select>

</label>

</div>

edit.html:

<label for="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>

<label for="authors">作者

{% 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

转载于:https://www.cnblogs.com/liweijing/p/7883555.html

你可能感兴趣的文章

期货市场技术分析06_长期图表和商品指数

查看>>

期货市场技术分析07_摆动指数和相反意见理论

查看>>

满屏的指标？删了吧，手把手教你裸 K 交易！

查看>>

不吹不黑 | 聊聊为什么要用99%精度的数据回测

五步git操作搞定Github中fork的项目与原作者同步

查看>>

git 删除远程分支

查看>>

删远端分支报错remote refs do not exist或git: refusing to delete the current branch解决方法

查看>>

python multiprocessing遇到Can’t pickle instancemethod问题

10 Things You Need To Know About Cocoa Auto Layout

查看>>

一个异步网络请求的坑：关于NSURLConnection和NSRunLoopCommonModes

查看>>