多表操作查询
1. 表关系models
from django.db import models
class Author(models.Model):
"""
作者表
"""
name = models.CharField(max_length=32)
age = models.IntegerField()
sex = models.CharField(max_length=20, default='male')
au = models.OneToOneField("AuthorDetail",on_delete=models.CASCADE)
def __str__(self):
return self.name
class AuthorDetail(models.Model):
"""
作者详细信息表
"""
birthday = models.DateField()
telephone = models.CharField(max_length=11)
addr = models.CharField(max_length=64)
# class Meta:
# db_table='authordetail' #指定表名
# ordering = ['-id',]
def __str__(self):
return self.telephone + self.addr
class Publish(models.Model):
"""
出版社表
"""
name = models.CharField(max_length=32)
city = models.CharField(max_length=32)
def __str__(self):
return self.name
class Book(models.Model):
"""
书籍表
"""
title = models.CharField(max_length=32)
publishDate = models.DateField()
price = models.DecimalField(max_digits=5,decimal_places=2)
comment = models.FloatField(default=100) # 评论数
good = models.FloatField(default=100) # 点赞数
# publishs=models.ForeignKey(to="Publish",on_delete=models.CASCADE 级联,related_name='xxx',related_query_name='xx' 设置别名，两者都存在，按后者)
publishes = models.ForeignKey(to="Publish",on_delete=models.CASCADE,)
authors = models.ManyToManyField(to='Author',)
def __str__(self):
return self.title
2. 增加
1. 一对一
# au_obj = models.AuthorDetail.objects.get(id=4)
models.Author.objects.create(
name='海贼王',
age=59,
# 两种方式
au_id=4 # 以id形式添加
# au=au_obj # 以对象形式添加
)
2. 一对多
# pub_obj = models.Publish.objects.get(id=3)
models.Book.objects.create(
title='xx2',
price=13,
publishDate='2011-11-12',
# publishs=pub_obj , #类属性作为关键字时,值为model对象
publishs_id=3 # 如果关键字为数据库字段名称,那么值为关联数据的值
)
3. 多对多
# ziwen = models.Author.objects.get(id=3)
# haigou = models.Author.objects.get(id=5)
new_obj = models.Book.objects.create(
title='护理第二部',
price=0.5,
publishDate='2019-09-29',
publishs_id=2,
)
new_obj.authors.add(3,5) # #*args **kwargs
new_obj.authors.add(*[3,5]) # 用的最多,
new_obj.authors.add(ziwen, haigou)
new_obj.authors.set([1,3])
正向：对象.关联属性.set(列表)
反向：对象.表名小写_set.set(列表)
3. 删除
# 一对一
# models.AuthorDetail.objects.filter(id=3).delete()
# models.Author.objects.filter(id=3).delete()
# 一对多
# models.Publish.objects.filter(id=3).delete()
# models.Book.objects.filter(id=4).delete()
# 多对多
book_obj = models.Book.objects.get(id=2)
# book_obj.authors.add() # 添加
# book_obj.authors.remove(1) #删除 2 1
# book_obj.authors.clear() # 清除
# book_obj.authors.set(['1','5']) # 先清除再添加,相当于修改
注意：
对于ForeignKey对象，clear()和remove()方法仅在null=True时存在（一对多，多对一）
ForeignKey字段没设置null=True时，
class Book(models.Model):
title = models.CharField(max_length=32)
publisher = models.ForeignKey(to=Publisher)
没有clear()和remove()方法：
>>> models.Publisher.objects.first().book_set.clear()
Traceback (most recent call last):
File "<input>", line 1, in <module>
AttributeError: 'RelatedManager' object has no attribute 'clear'
当ForeignKey字段设置null=True时，
class Book(models.Model):
name = models.CharField(max_length=32)
publisher = models.ForeignKey(to=Class, null=True)
此时就有clear()和remove()方法：
>>> models.Publisher.objects.first().book_set.clear()
4. 改
ret = models.Publish.objects.get(id=2)
models.Book.objects.filter(id=5).update(
# title='华丽丽',
publishs=ret,
# publishs_id=2,
)
5. 外键
# 查询
# 一对一
# 关系属性写在表1,关联到表2,那么通过表1的数据去找表2的数据,叫做正向查询,返过来就是反向查询
# 查询一下王洋的电话号码
# 正向查询 对象.属性
# obj = models.Author.objects.filter(name='王洋').first()
# ph = obj.au.telephone
# print(ph)
# 查一下电话号码为120的作者姓名
# 反向查询 对象.小写的表名
# obj = models.AuthorDetail.objects.filter(telephone=120).first()
# ret = obj.author.name #陈硕
# print(ret)
# 一对多
# 查询一下 海狗的怂逼人生这本书是哪个出版社出版的 正向查询
# obj = models.Book.objects.filter(title='海狗的怂逼人生').first()
# ret = obj.publishs.name
# print(ret) #24期出版社
# 查询一下 24期出版社出版过哪些书
# obj = models.Publish.objects.filter(name='24期出版社').first()
#
# ret = obj.book_set.all() #<QuerySet [<Book: 母猪的产后护理>, <Book: 海狗的怂逼人生>]>
# for i in ret:
# print(i.title)
# 多对多
# 海狗的怂逼人生 是哪些作者写的 -- 正向查询
# obj = models.Book.objects.filter(title='海狗的怂逼人生').first()
# ret = obj.authors.all()
#
# print(ret) #<QuerySet [<Author: 王洋>, <Author: 海狗>]>
# for i in ret:
# print(i.name)
# 查询一下海狗写了哪些书 -- 反向查询
# obj = models.Author.objects.filter(name='海狗').first()
# ret = obj.book_set.all()
# print(ret)
# for i in ret:
# print(i.publishs.name)
# print(i.title)
# return HttpResponse('ok')
示例:
# 一对一
# 查询一下王洋的电话号码
# ret = models.Author.objects.filter(name='王洋').values('au__telephone')
# ret = models.AuthorDetail.objects.filter(author__name='王洋').values('telephone')
# print(ret) #<QuerySet [{'au__telephone': '110'}]> #<QuerySet [{'telephone': '110'}]>
#
# 一对多
# 海狗的怂逼人生这本书是哪个出版社出版的
# ret = models.Book.objects.filter(title='海狗的怂逼人生').values('publishs__name')
# print(ret) #<QuerySet [{'publishs__name': '24期出版社'}]>
# ret = models.Publish.objects.filter(book__title='海狗的怂逼人生').values('name')
# print(ret) #<QuerySet [{'name': '24期出版社'}]>
#查询一下24期出版社出版了哪些书
# ret = models.Publish.objects.filter(name='24期出版社').values('book__title')
# print(ret) #<QuerySet [{'book__title': '华丽的产后护理'}, {'book__title': '海狗的怂逼人生'}]>
# ret = models.Book.objects.filter(publishs__name='24期出版社').values('title')
# print(ret) #<QuerySet [{'title': '华丽的产后护理'}, {'title': '海狗的怂逼人生'}]>
# 多对多
#海狗的怂逼人生 是哪些作者写的
# 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': '海狗'}]>
# return render(request,'index.txt',{'x':123,'y':456})
# related_name
# 查询一下24期出版社出版了哪些书
ret = models.Publish.objects.filter(name='24期出版社').values('xxx__title') #xxx代替反向查询的小写表名
print(ret)
聚合查询 [`æɡrɪɡeɪt]
ret = models.Book.objects.all().aggregate(a=Avg('price'),m=Max('price'))
print(ret)
有别名
#{'a': 45.1, 'm': Decimal('200.00')}
无别名
#{'price__avg': 45.1, 'price__max': Decimal('200.00')}
# python字典格式,也就是说,聚合查询是orm语句的结束
分组查询
# 每个出版社出版的书的平均价格
# 用的是publish表的id字段进行分组
# ret = models.Book.objects.values('publishs__id').annotate(a=Avg('price'))
# 用的book表的publishs_id字段进行分组
# ret = models.Book.objects.values('publishs_id').annotate(a=Avg('price'))
# print(ret)
# ret = models.Publish.objects.annotate(a=Avg('book__price')).values('a')
# print(ret) #<QuerySet [{'a': None}, {'a': 71.166667}, {'a': 6.0}]>
F查询
from django.db.models import Avg, Sum, Max, Min, Count,F
# 查询一下评论数大于点赞数的书
ret = models.Book.objects.filter(comment__gt=F('good'))
print(ret)
# 将所有书的价格上调100块
models.Book.objects.all().update(
price=F('price')+100
)
# 引申 同时在char字段后边添加文字
from django.db.models.functions import Concat
from django.db.models import Value
models.Book.objects.all().update(title=Concat(F("title"), Value("("), Value("第一版"), Value(")")))
Q查询
与 & 或 | 非 ~
from django.db.models import Avg, Sum, Max, Min, Count, F,Q
ret = models.Book.objects.filter(Q(id=2)&Q(Q(price__gt=112)|~Q(comment__lte=200)))
print(ret)
views
from django.shortcuts import render,HttpResponse,redirect
from app01 import models
from django.db.models import Avg,Sum,Max,Min,Count,F,Q
# Create your views here.
def index(request):
#1.增加 一对一
# models.AuthorDetail.objects.create(
# birthday="2000-11-11",
# telephone="555",
# addr="美国",
# )
# au_obj = models.AuthorDetail.objects.get(id=5)
# models.Author.objects.create(
# name="海王",
# age=66,
# au_id=5,
# #或者 au="au_obj"
# )
# 2. 一对多 书籍和出版社
# pub_obj = models.Publish.objects.get(id=3)
#
# models.Book.objects.create(
# title="xx2",
# price=33,
# publishDate="2011-11-12",
# comment=324,
# good=444,
# publishes=pub_obj, #类属性作为关键字时,值为model对象
# # publishes_id=3, # 如果关键字为数据库字段名称,那么值为关联数据的值
# )
# 3. 多对多 书籍和作者
# sese = models.Author.objects.get(id=1)
# xingxing = models.Author.objects.get(id=2)
#
# new_obj = models.Book.objects.create(
# title="海贼王",
# price="45",
# publishDate="2009-11-14",
# comment=399,
# good=666,
# publishes_id=2,
# )
# 三种方法
# new_obj.authors.add(*[1,2]) #用的最多
# new_obj.authors.add(1,2)
# new_obj.authors.add(sese,xingxing)
# 4. 删除 一对一
# models.AuthorDetail.objects.filter(id=5).delete()
# models.Author.objects.filter(id=4).delete()
# 5. 删除 一对多
# models.Publish.objects.filter(id=4).delete()
# models.Book.objects.filter(id=4).delete()
# 6.删除 多对多
# book_obj = models.Book.objects.get(id=8)
# # book_obj.authors.add() #添加
# book_obj.authors.remove(1) # 删除作者1，书籍8
# book_obj.authors.clear() #清除关系
# book_obj.authors.set(["3","4"]) #先清除再添加
# 7. 改 修改书籍名和出版社
# ret = models.Publish.objects.get(id=2)
# models.Book.objects.filter(id=7).update(
# title="ooo",
# publishes=ret,
# # publishes_id=2
# )
# 8. 基于对象的跨表查询 一对一
# 关系属性写在表1,关联到表2,那么通过表1的数据去找表2的数据,叫做正向查询,返过来就是反向查询
# 正向查询用对象.属性，反向查询用对象.小写的表名
# 1.查询韩星的电话号 str原因
# obj = models.Author.objects.filter(name="韩星").first()
# print(type(obj))
# ph = obj.au.telephone
# print(ph)
# 反向查询已知电话444，查作者
# obj = models.AuthorDetail.objects.filter(telephone=444).first()
# pa = obj.author.name
# print(pa)
# 9. 基于对象的跨表查询 一对多
# 已知书籍名，查询出版社，正向
# obj = models.Book.objects.filter(title="大主宰").first()
# ph = obj.publishes.name
# print(ph)
# 已知出版社，查询所有书籍,反向,对象.表名_set.all()
# obj = models.Publish.objects.filter(name="24期出版社").first()
# ret = obj.book_set.all() # <QuerySet [<Book: 风云>, <Book: 莽荒纪>, <Book: ooo>, <Book: 海贼王>]>
# print(ret)
# for i in ret:
# print(i.title)
# 10.多对多
# 已知书籍名，查询作者,正向查询
# obj = models.Book.objects.filter(title="万剑道尊").first()
# ret = obj.authors.all() #<QuerySet [<Author: 邸宗超>, <Author: 韩星>, <Author: 李文宝>]>
# for i in ret:
# print(i.name)
# print(i.au.telephone) #连环查询，有对象，正向查询
# 已知作者名，查看书籍
# obj = models.Author.objects.filter(name="韩星").first()
# ret = obj.book_set.all()
# print(ret) #<QuerySet [<Book: 朝花夕拾>, <Book: 浮云>, <Book: 大主宰>, <Book: 万剑道尊>]>
# for i in ret:
# print(i.title)
# 11. 基于双下划线的跨表查询
# 一对一 正向
# 已知作者名，查询电话号
# ret1 = models.Author.objects.filter(name="韩星").values("au__telephone")
# print(ret1) #<QuerySet [{'au__telephone': '222'}]>
# ret2 = models.AuthorDetail.objects.filter(author__name="韩星").values("telephone")
# print(ret2) #<QuerySet [{'telephone': '222'}]>
# for i in ret1:
# print(i["au__telephone"])
# for j in ret2:
# print(j["telephone"])
# 一对多
# 已知书名查询出版社
# ret1 = models.Book.objects.filter(title="大主宰").values("publishes__name")
# print(ret1) # <QuerySet [{'publishes__name': '老男人出版社'}]>
# ret2 = models.Publish.objects.filter(book__title="大主宰").values("name")
# print(ret2) # <QuerySet [{'name': '老男人出版社'}]>
# for i in ret1:
# print(i["publishes__name"])
# for j in ret2:
# print(j["name"])
# 已知出版社，查询书籍名
# ret1 = models.Publish.objects.filter(name="24期出版社").values("book__title")
# print(ret1)
# ret2 = models.Book.objects.filter(publishes__name="24期出版社").values("title")
# print(ret2)
# for i in ret1:
# print(i["book__title"])
# for j in ret2:
# print(j["title"])
# 多对多
# 已知书籍名，查询作者
# ret1 = models.Book.objects.filter(title="万剑道尊").values("authors__name")
# print(ret1)
# ret2 = models.Author.objects.filter(book__title="万剑道尊").values("name")
# print(ret2)
# for i in ret1:
# print(i["authors__name"])
# 12. 聚合查询
# 统计所有书籍的平均价格,别名可以不写
# ret1 = models.Book.objects.all().aggregate(a=Avg("price"),m=Max("price"))
# print(ret1)
# 每个出版社出版书的平均价格
# 1.根据publish表的id字段进行分组，别名可以不写
# ret1 = models.Book.objects.values("publishes__id").annotate(a=Avg("price"))
# print(ret1)
# 2. 根据book的publishs_id字段进行分组，等同于publishes
# ret2 = models.Book.objects.values("publishes_id").annotate(a=Avg("price"))
# print(ret2)
# ret3 = models.Book.objects.values("publishes","id").annotate(a=Avg("price"))
# print(ret3) #多条件分组
# 此方法连表查询（book__price),查询的值是a，默认用publish的id分组
# ret4 = models.Publish.objects.annotate(a=Avg("book__price")).values("a")
# print(ret4)
# F查询
#查询一下评论数大于点赞数的书
# ret = models.Book.objects.all()
# l1 = []
# for i in ret:
# if i.comment > i.good:
# l1.append(i)
# for j in l1:
# print(j.title)
# 双下方法
# ret = models.Book.objects.filter(comment__gt=F("good"))
# print(ret)
# for i in ret:
# print(i.title)
#将所有的书籍价格上调100块
# ret = models.Book.objects.all()
# for i in ret:
# i.price += 100
# i.save()
# models.Book.objects.all().update(
# price=F("price") - 100
# )
# Q查询 或 | 与 & 非~
# 查询书籍价格大于80并且评论数大于等于200
# ret = models.Book.objects.filter(price__gt=80,comment__gte=200) #和，
# print(ret)
# 查询书籍价格大于80或者评论数大于等于600
# ret = models.Book.objects.filter(Q(price__gt=80)|Q(comment__gte=600))
# print(ret)
#查询id=2，书籍价格大于80或者评论数大于等于600
# ret = models.Book.objects.filter(Q(Q(price__gt=80)|Q(comment__gte=600))&Q(id=2))
# print(ret)
# 1 查询每个作者的姓名以及出版的书的最高价格
# ret1 = models.Author.objects.values("name").annotate(m=Max("book__price"))
# print(ret1)
# ret2 = models.Book.objects.values("authors__name").annotate(Max("price"))
# print(ret2) #有点小错误
# 2 查询作者id大于2作者的姓名以及出版的书的最高价格
# ret1 = models.Author.objects.filter(id__gt=2).annotate(m=Max("book__price")).values("name","m")
# print(ret1)
# 3 查询作者id大于2或者作者年龄大于等于20岁的女作者的姓名以及出版的书的最高价格
# 不对，等于不用Q，直接逗号 # ret1 = models.Author.objects.filter(Q(id__gt=2)|Q(age__gte=20)&Q(sex="female")).annotate(m=Max("book__price")).values("sex","m")
# ret1 = models.Author.objects.filter(Q(id__gt=2)|Q(age__gte=20),sex="female").annotate(m=Max("book__price")).values("sex","m")
# print(ret1)
# 4 查询每个作者出版的书的最高价格的平均值
# ret1 = models.Author.objects.values("name").annotate(m=Max("book__price")).aggregate(a=Avg('m'))
# # ret1 = models.Author.objects.annotate(m=Max("book__price")).aggregate(a=Avg('m')) # 默认id分组
# print(ret1)
# 5 每个作者出版的所有书的最高价格以及最高价格的那本书的名称
ret1 = models.Author.objects.annotate(m=Max("book__price")).values("name","m")
print(ret1)
return HttpResponse("OK")
将orm转换为sql语句
# 将下述代码放入settings中
LOGGING = {
'version': 1,
'disable_existing_loggers': False,
'handlers': {
'console':{
'level':'DEBUG',
'class':'logging.StreamHandler',
},
},
'loggers': {
'django.db.backends': {
'handlers': ['console'],
'propagate': True,
'level':'DEBUG',
},
}
}
事务
from django.db import transaction
# 注意try的位置是在事物的外部，在内部时报错前会对数据库进行修改，不符合事物的特点
try:
with transaction.atomic():
# 一系列的操作
models.Book.objects.update(publisher_id=4)
models.Book.objects.update(publisher_id=3)
int('ss')
models.Book.objects.update(publisher_id=2)
models.Book.objects.update(publisher_id=5)
except Exception as e:
print(e)
在Python脚本中调用Django环境
1. 在项目下创建文件夹，并创建py文件
2.输入以下代码
import os # 将数据库路径导入
# 外部文件执行数据库命令，注意修改路径
# os.environ.setdefault("DJANGO_SETTINGS_MODULE", "about_orm.settings")
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "homework.settings")
import django # 导入django
django.setup() # 启动django运行程序
from app01 import models # 导入models模块
# 写逻辑代码查询即可，已连接了Django环境
关于别名
publishers = models.ForeignKey(to="Publisher",related_name='books',related_query_name='xx')
关联字段中有此属性时，models.Publisher.objects.filter(xx__title="谁是谁")用到表名小写时替换，优先级
related_query_name 》 related_name 》 表名小写 author__title
