实体 | 实体 |
---|---|
出版社 | category |
作者 | tag |
书 | 文章 |
先学习一下基础的增删查改
django orm增删改查: https://www.cnblogs.com/iiiiiher/articles/8870906.html
## 增加
- 方法1:
People.objects.create(usernmae='maotai',age=22);
- 方法2:
p = People()
p.name = "maomao"
p.age = 23
p.save()
## 更新
## 查询: crud,重点是查询
- 查询所有列
People.objects.all()
[obj,obj2]
- 查询某几列
People.objects.all().values("id","username")
[{name:maotai,age:22},{name:maomao,age:22}]
People.objects.all().values_list("id","username")
[(1, 'maotai'), (2, 'maomai')]
People.objects.all()
<QuerySet [<People: name: maotai, age: 22>, <People: name: maomai, age: 23>, <People: name: maodou, age: 24>]>
People.objects.all().values('id','username')
<QuerySet [{'id': 1, 'username': 'maotai'}, {'id': 2, 'username': 'maomai'}, {'id': 3, 'username': 'maodou'}]>
People.objects.all().values_list('id','username')
<QuerySet [(1, 'maotai'), (2, 'maomai'), (3, 'maodou')]>
## 命令行查看真实sql
print(People.objects.all().query)
from django.shortcuts import render,HttpResponse
# Create your views here.
from app01.models import *
def index(request):
return render(request,"index.html")
def addbook(request):
# b=Book(name="python基础",price=99,author="mao",pub_date="2017-12-12")
# b.save()
Book.objects.create(name="linux",price=78,author="maotai",pub_date="2016-12-12")
#Book.objects.create(**dic)
return HttpResponse("添加成功")
def update(request):
Book.objects.filter(author="mao").update(price=999)
# b=Book.objects.get(author="maotai")
# b.price=120
# b.save()
# print(b)#<QuerySet [<Book: Book object>]>
# print(type(b))
return HttpResponse("修改成功!")
def delete(req):
Book.objects.filter(author="maotai").delete()
return HttpResponse("success")
def select(req):
# book_list=Book.objects.all()
# print(book_list)
# print(book_list[0])#Book object
# book_list = Book.objects.filter(id=2)
# book_list = Book.objects.all()[::2]
# book_list = Book.objects.all()[::-1]
# book_list = Book.objects.first()
# book_list = Book.objects.last()
# book_list = Book.objects.get(id=2)#只能取出一条记录时才不报错
# ret1=Book.objects.filter(author="maotai").values("name")
# ret2=Book.objects.filter(author="mao").values_list("name","price")
# book_list=Book.objects.exclude(author="mao").values("name","price")
# print(ret1)
# print(ret2)
# book_list= Book.objects.all().values("name").distinct()
# book_count= Book.objects.all().values("name").distinct().count()
# print(book_count)
#万能的 __
# book_list=Book.objects.filter(price__gt=50).values("name","price")
book_list=Book.objects.filter(name__contains="P").values_list("name","price")
return render(req,"index.html",{"book_list":book_list})
终极吐血整理版--2018年4月23日 17:56:27
实体 | 实体 |
---|---|
出版社 | category |
作者 | tag |
书 | 文章 |
一对多
创建
2种方法
查询
1,python这本书对应的出版社的名字
2,邮电出版社出版的所有书
多对多
创建(目的创建book和author关联关系)
book_obj.publish # 这本书对应的出版社,对象
book_obj.author # 这个书的作者集合, querysetset
book_obj.objects.all()
book_obj.objects.add(*author_obj)
查询
1,通author找出他的book: 龟叔写的书找出来
2,python这本书的作者
-
1, 一对多:
1, python这本书对应的出版社的名字
Book.objects.filter(name="python").values('publish__name')
Publish.objects.filter(book__name="python").values('name')
2, 邮电出版社出版的所有书
print(Publish.objects.filter(name="邮电出版社").values('book__name'))
print(Book.objects.filter(publish__name="邮电出版社").values('name'))
-
2, 多对多查询
1,通author找出他的book: 龟叔写的书找出来
print(Book.objects.filter(author__name="龟叔"))
print(Author.objects.filter(name="龟叔").values('book__name'))
2,python这本书的作者
print(Book.objects.filter(name="python").values('author__name'))
print(Author.objects.filter(book__name="python").values('name'))
聚合查询和分组查询
聚合:
from django.db.models import Avg, Max, Min, Sum, Count
-
print(Book.objects.aggregate(Min('price')))
print(Book.objects.aggregate(Sum('price')))
print(Book.objects.aggregate(Avg('price')))
龟叔出的书的价格总和
print(Book.objects.filter(author__name="龟叔").aggregate(Sum('price')))
print(Book.objects.filter(author__name="龟叔").aggregate(guishu_money = Sum('price')))
-
龟叔出了几本书?
print(Book.objects.filter(author__name="龟叔").aggregate(Count('name')))
-
分组聚合:
每个作者的出的书的价格和.
print(Book.objects.all().values("author__name").annotate(Sum("price")))
print(Book.objects.values("author__name").annotate(Sum("price"))) #Book.objects.values分组,聚合
# <QuerySet [{'author__name': None, 'price__sum': 108}, {'author__name': '林纳斯', 'price__sum': 89}, {'author__name': '毛台', 'price__sum': 89}, {'author__name': '龟叔', 'price__sum': 188}]>
-
SELECT
"app03_author"."name",
SUM("app03_book"."price") AS "price__sum"
FROM
"app03_book"
LEFT OUTER JOIN "app03_book_author" ON (
"app03_book"."id" = "app03_book_author"."book_id"
)
LEFT OUTER JOIN "app03_author" ON (
"app03_book_author"."author_id" = "app03_author"."id"
)
GROUP BY
"app03_author"."name"
LIMIT 21;
查各个出版社出版的最便宜的书的价格(分组和聚合不一定用在复合查询)
print(Publish.objects.values('name').annotate(Min("book__price")))
print(Publish.objects.values('name').annotateabc=Min("book__price")))
F查询和Q查询
- F/Q 且,或,非关系查询
from django.db.models import F,Q
1, 逗号: 且的关系
print(Book.objects.get(name="python", price=77))
2, F查询: 每本书price+10
# Book.objects.all().update(price=price+10) #这种是错误的
Book.objects.all().update(price=F('price') + 10)
3, Q查询: 或的关系
print(Book.objects.filter(Q(name='go') | Q(price=109)).values('name', 'price'))
4, ~: 非的关系
print(Book.objects.filter(Q(name='go') | ~Q(price=109)).values('name', 'price'))
5,__contains: 包含关系
print(Book.objects.filter(Q(name__contains='go')).values('name', 'price'))
queryset的特性([缓存]惰性机制)
queryset缓存:
1,只有调用结果时,才会执行sql
res = Book.objects.all() # 可以理解为一堆sql
print(res) # 此时才会执行sql
2,queryset具备缓存特性,
res = Book.objects.all()
for i in res: #第一次去执行sql,得到结果缓存
print(i)
print("*"*100)#第二次直接从缓存中取出数据
for i in res:
print(i)
3,缓存带来的危害, 中途遇到更新时,无法获取到最新值.
res = Book.objects.all()
for i in res:
print(i.price) # 打印原价
Book.objects.all().update(price=200)
for i in res:
print(i.price) # 更新原价
4,加入只想判断结果res.exists()
res = Book.objects.filter(price=100)
if res.exists(): #不会将一坨数据扔进缓存
print('ok')
5.如果结果非常巨大(返回迭代器)
objs = Book.objects.all().iterator()
注: 迭代器只允许迭代一次.
可迭代
# print(objs[1])
# print(objs[1:4])
# print(objs[::-1])
可切片
-
models操作
思路:
给publish插数据
给book插数据
给author插数据
给book_author插数据
调用外键: Book.publish # 这本书对应出版社的一条记录(对象)
调用多对多键: Book.author # 这本书绑定关系的作者的记录(queryset)
book_obj.author.all()
book_obj.author.add(author_obj)
book_obj.author.remove(author_obj)
author_obj = Author.objects.all()
book_obj.author.add(*author_obj)
通过book找它的author:
python这本书的作者
Author.objects.filter(book__name="python")
通过author找它的book
龟叔出过的书的name+price(用系统自带的manytomany方法)
Book.objects.filter(author__name="龟叔").values('name', 'price')
自定义book_author表:
查出id=2的book的author:(两张表借助第三张表book_author取数据)
方法1:
book_obj = Book.objects.get(id=2)
book_obj.book_author_set.all() # book_author的queryset对象
print(book_obj.book_author_set.all()[0].author) # 查出作者.
方法2:
Book.objects.filter(book_author__author_id__name="龟叔").values('name','price')
多对多:
创建多对多关系:
author = models.ManytoManyFiled("Author")
book对象它的所有的关联作者: obj = book_obj.authors.all()
绑定多对多的关系: obj.add(*QuerySet)
obj.add(author_obj)
如果想向第三张表插入值的方式绑定:
手动创建第三张表
class Book_Author(models.Model):
book_id = models.ForeignKey(Book, on_delete=models.CASCADE)
author_id = models.ForeignKey(Author, on_delete=models.CASCADE)
Book_Author.objects.create(book_id=2,author_id=3)
掌握: 通过filter value(双下划线)实现多对多的关联查询
龟叔出过的书的name+price(用系统自带的manytomany方法)
Book.objects.filter(author__name="龟叔").values('name', 'price')
1.聚合
2.分组
3.F查询和Q查询
多表查询:
一对多
1,sql
2,set
3,__
图书---2种-->出版社
图书<--1种--出版社
多对多
1,自动
操作第三张表, 为第三张表添加记录.
调用外键: Book.publish # 这本书对应出版社的一条记录(对象)
调用多对多键: Book.author # 这本书绑定关系的作者的记录(对象)
2,手动
通过book找它的author
通过author找它的book
给book添加author记录
即手动添加book_author关系
1,取出一本书 book_obj
2,取出一个作者author_obj
3,book_obj.add(author_obj)
F查询和Q查询
1.创建
1,p = People
p.name = "mt"
p.save()
2,People.objects.create(name="mt")
2.查询(重点****)
def home(request):
book_list = Book.objects.filter(id=1)
book_list = Book.objects.exclude(author='maotai').values('name', 'price')
# filter/all 取出的是一个queryset
book_list = Book.objects.all()
book_list = Book.objects.all()[::2]
book_list = Book.objects.all()[::-1]
# first/last get取到的是一个实例
book_list = Book.objects.first()
book_list = Book.objects.last()
book_list = Book.objects.get(id=1) # 结果超过一个后会报错
res = Book.objects.filter(author='maotai').values("name") # 结果是字典形式,kv
res = Book.objects.filter(author='maotai').values_list("name","price") # 结果是列表形式,好像只有key
# select name from book where author='maotai';
book_list = Book.objects.filter(author='maotai').values("name").distinct() # filter某些字段时, 去重
book_count = Book.objects.all().values("name").count() # 记录条数
# 模糊匹配
book_list = Book.objects.filter(name__contains="mao").values_list("name","price")
book_list = Book.objects.filter(id__gt=5).values_list('name','price')
3,删除
book_list = Book.objects.exclude(author='maotai').delete()
- 方法小结:
all()&filter()
queryset.update()
queryset.delete()
first/last&get
obj.save()
思路:
创建publish出版社/图书表(一对多)---添加图书的两种方法(主要是给publish_id赋值)
1,
Book.objects.create(name="流畅的python", price=89, pub_date="2017-06-06", publish_id=3)
2,
publish_obj = Publish.objects.filter(name="人民出版社")[0]
Book.objects.create(name="go action", price=22, pub_date="2018-04-04", publish=publish_obj)
-
1. 通过book查publish消息?
1,def __str__
2,查询publish的其他字段
2. 查看人民出版社出版过哪些书?
1,方法1: 正向
2,方法2: 逆向查: 利用django自带的book_set
多表操作(一对多):
1,正向查询
pub_obj = Publish.objects.get(name="人民出版社")
res = Book.objects.filter(publish=pub_obj).values('name', 'price')
2,逆向查询,利用django提供的book_set
pub_obj = Publish.objects.filter(name="人民出版社")[0]
print(pub_obj.book_set.all())
print(type(pub_obj.book_set.all()))
-
外键两种写法:
1,外键加引号
publish = models.ForeignKey("publish", on_delete=models.CASCADE, verbose_name="出版社")
2,外键不加引号.(外键model需要先定义)
publish = models.ForeignKey(Publish, on_delete=models.CASCADE, verbose_name="出版社")
通过__方法查找结果:
1,逆向查找: 外键__名称
- 查出人民出版社出版的图书
res = Book.objects.filter(publish__name="人民出版社").values('name', 'price') # 这里的publish是外键名
print("3,通过__方法查找结果: ", res)
print("-" * 50)
2,正向查找: 主表__name
2.1
# python这本书的出版社名字
print(Publish.objects.filter(book__name="python").values('name')) # 这里的book是表名, 这里name是publish表里的字段name
return HttpResponse("查询完毕, 结果请查看console")
2.2通过逆向查
res = Book.objects.filter(name="python").values("publish__name") # select publish__name
栗子
思路: 一对多: 这个出版社出版过哪些书? -- 这本书是哪个出版社出版的?
多对多: id=3的作者的信息
1. 人民出版社出版的图书的名字
1.1
pub_obj = Publish.objects.get(name="人民出版社")
res = Book.objects.filter(publish=pub_obj).values('name', 'price')
1.2
pub_obj = Publish.objects.filter(name="人民出版社")[0]
print(pub_obj.book_set.all())
1.3
res = Book.objects.filter(publish__name="人民出版社").values('name', 'price')
2. python这本书的出版社名字
2.1逆向(主表名__)
res = Publish.objects.filter(book__name="python").values('name')
2.2正向(外键名__)
res = Book.objects.filter(name="python").values("publish__name")
3. 在北京出版社出版的书
Book.objects.filter(publish__city__="北京").values('name')
4. 2017年上半年出的书的出版社名字
res = Book.objects.filter(pub_date__lt="2017-07-01", pub_date__gt="2017-01-01").values('publish_name')
5, 多对多的关系, id=3的所有作者信息
book_obj = Book.objects.get(id=3)
book_obj.author.all()
print(type(book_obj.author.all()))
数据模拟:
publish
author
book
Foreign_key: publish
ManyToMany: author
核心点:
一对多创建关系
一对多查询
publish:
1 人民出版社 北京
2 邮电出版社 西安
3 机械出版社 上海
book:
id name price pub_date publish
1 python 89 2017-01-07 1
2 go 99 2017-04-01 2
3 java 39 2017-06-01 3
4 ios 69 2017-12.24 3
Author:
id name age
1 龟叔 17
2 林纳斯 20
3 毛台 25
1 Jeff 42
book_author:
id book_id author_id
1 1 1
2 1 2
3 2 3
4 2 3
- models
class Publish(models.Model):
name = models.CharField(max_length=40, verbose_name="出版社")
city = models.CharField(max_length=40, verbose_name="出版社所在城市")
def __str__(self):
return self.name
class Meta:
verbose_name = "出版社"
verbose_name_plural = verbose_name
class Author(models.Model):
name = models.CharField(max_length=40, verbose_name="作者")
age = models.IntegerField(default=17, verbose_name="年龄")
def __str__(self):
return self.name
class Meta:
verbose_name = "作者"
verbose_name_plural = verbose_name
class Book(models.Model):
name = models.CharField(max_length=40, verbose_name="书名")
price = models.IntegerField(default=99, verbose_name="价格")
pub_date = models.DateField(verbose_name="出版时间")
publish = models.ForeignKey(Publish, on_delete=models.CASCADE, verbose_name="出版社")
# author = models.ManyToManyField(Author, verbose_name="作者")
def __str__(self):
return self.name
class Meta:
verbose_name = "图书"
verbose_name_plural = verbose_name
class Book_Author(models.Model):
book_id = models.ForeignKey(Book, on_delete=models.CASCADE)
author_id = models.ForeignKey(Author, on_delete=models.CASCADE)
- views
def addbook(request):
# 1.创建出版社
# Publish.objects.create(name="人民出版社",city="北京")
#
# p = Publish()
# p.name = "邮电出版社"
# p.city = "西安"
# p.save()
#
# p2 = Publish(name="机械出版社")
# p2.city = "上海"
# p2.save()
#
# print(Publish.objects.all())
# 2,创建图书
# Book.objects.create(name="python", price=89,pub_date='2017-01-07', publish_id=1)
# Book.objects.create(name="go", price=99, pub_date='2017-04-01',publish_id=2)
# Book.objects.create(name="ios", price=39,pub_date='2017-06-01', publish_id=3)
# Book.objects.create(name="java", price=69, pub_date='2017-12-24',publish_id=3)
# 3,创建作者
# Author.objects.create(name="龟叔",age=17)
# Author.objects.create(name="林纳斯",age=20)
# Author.objects.create(name="毛台",age=25)
# Author.objects.create(name="Jeff",age=33)
# 龟叔还写了ios
# book_obj = Book.objects.get(name="ios")
# author_obj = Author.objects.get(id=1)
# print(book_obj.author.all()) # ios谁写的?
# # book_obj.author.add(author_obj)
# 龟叔出过的书
# res = Book.objects.filter(author__name="龟叔").values('name', 'price')
# print(res)
#
# # python这本书谁写的
# res2 = Author.objects.filter(book__name="python")
# print(res2)
#
Book_Author.objects.create(book_id=2,author_id=1)
book_obj = Book.objects.get(id=2)
# book_obj.book_author_set.all() # book_author的queryset对象
print(book_obj.book_author_set.all()[0].author)
Book.objects.filter(book_author__author_id__name="龟叔").values('name','price')
# 创建自定义book_author表
Book.objects.filter(book_author__author__name="alex").values("name","price")
return HttpResponse("结果请查看console")
查看sql
追加扫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',
},
}
}