四十九、django单表操作,多表操作,一对多,多对多,分组查询,
发布时间:2020-12-20 10:39:40 所属栏目:Python 来源:网络整理
导读:单表操作: - 增删改查 # 增 # 方式1: create # book_obj = models.Book.objects.create(title=‘三国‘,price=19.99,create_time=‘2019-11-11‘) # print(book_obj.title) # 方式2:对象点save()方法 # from datetime import datetime # ctime = datetime
单表操作: - 增删改查 # 增 # 方式1: create # book_obj = models.Book.objects.create(title=‘三国‘,price=19.99,create_time=‘2019-11-11‘) # print(book_obj.title) # 方式2:对象点save()方法 # from datetime import datetime # ctime = datetime.now() # book_obj = models.Book(title=‘三国演义‘,price=96.66,create_time=ctime) # book_obj.save()
# print(models.Book.objects.all()) # print(models.Book.objects.get(id=1)) # print(models.Book.objects.get(pk=1)) """ pk会自动查找到当前数据的主键字段 """ # print(models.Book.objects.filter(pk=2))
# 1.update # models.Book.objects.filter(pk=1).update(title=‘三国演义‘) # 2.对象.save() # book_obj = models.Book.objects.get(pk=1) # book_obj.price = 666.66 # book_obj.save()
# < 1 > all(): 查询所有结果 # < 2 > filter(**kwargs): 它包含了与所给筛选条件相匹配的对象 # < 3 > get(**kwargs): 返回与所给筛选条件相匹配的对象,返回结果有且只有一个,如果符合筛选条件的对象超过一个或者没有都会抛出错误。(源码就去搂一眼~诠释为何只能是一个对象) # < 4 > exclude(**kwargs): 它包含了与所给筛选条件不匹配的对象 # print(models.Book.objects.exclude(pk=1)) # 只要pk不是1的数据全部查询出来 # < 5 > order_by(*field): 对查询结果排序(‘-id‘) / (‘price‘) # print(models.Book.objects.order_by(‘price‘)) # 默认是升序 # print(models.Book.objects.order_by(‘-price‘)) # 加负号就是降序 # < 6 > reverse(): 对查询结果反向排序 >> > 前面要先有排序才能反向 # print(models.Book.objects.order_by(‘price‘).reverse()) # < 7 > count(): 返回数据库中匹配查询(QuerySet) # print(models.Book.objects.count()) # 对查询出来的结果进行一个计数 # 的对象数量。 # < 8 > first(): 返回第一条记录 # print(models.Book.objects.filter(pk=1).first()) # < 9 > last(): 返回最后一条记录 # print(models.Book.objects.all()) # print(models.Book.objects.all().last()) # < 10 > exists(): 如果QuerySet包含数据,就返回True,否则返回False # print(models.Book.objects.filter(pk=1000)) # print(models.Book.objects.filter(pk=1000).exists()) # < 11 > values(*field): 返回一个ValueQuerySet——一个特殊的QuerySet,运行后得到的并不是一系列 # model的实例化对象,而是一个可迭代的字典序列 # print(models.Book.objects.values(‘title‘,‘price‘)) # 得到的结果是列表套字典 # < 12 > values_list(*field): 它与values() # print(models.Book.objects.values_list(‘title‘,‘price‘)) # 得到的结果是列表套元组 # 非常相似,它返回的是一个元组序列,values返回的是一个字典序列 # < 13 > distinct(): 从返回结果中剔除重复纪录 """ 去重的前提是 一定要有完全重复的数据 才能去重 """ # print(models.Book.objects.filter(title=‘三国演义‘).distinct()) # print(models.Book.objects.values(‘title‘,‘price‘,‘create_time‘).distinct()) 查看orm内部sql语句的方法有哪些 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 models # Create your models here. class Book(models.Model): tittle = models.CharField(max_length=64) price = models.DecimalField(max_digits=8,decimal_places=2) publish_date = models.DateField(auto_now_add=True) # auto_now_add=True只在数据创建的时候更新 publish = models.ForeignKey(to="Publish") authors = models.ManyToManyField(to="Author") def __str__(self): return self.tittle class Publish(models.Model): name = models.CharField(max_length=32) addr = models.CharField(max_length=32) def __str__(self): return self.name class Author(models.Model): name = models.CharField(max_length=32) age = models.IntegerField() author_detail = models.OneToOneField(to="AuthorDetail") def __str__(self): return self.name class AuthorDetail(models.Model): phone = models.BigIntegerField() addr = models.CharField(max_length=64) def __str__(self): return self.addr # 一对多字段: # models.Book.objects.create(tittle="三国演义",price=189.99,publish_id=1) # publish_obj = models.Publish.objects.filter(pk=2).first() # models.Book.objects.create(tittle=‘红楼梦‘,price=999.99,publish=publish_obj)
# models.Book.objects.filter(pk=4).update(publish_id=3) # 方式二:传对象 # publish_obj = models.Publish.objects.filter(pk=4).first() # models.Book.objects.filter(id=5).update(publish=publish_obj)
# models.Publish.objects.filter(pk=2).delete() # 级联更新,这边表删除,建外键的表也删除
# res = models.Book.objects.filter(pk=6).first() # print(res.tittle,res) res既有返回值,也是对象
# book_obj = models.Book.objects.filter(pk=4).first() # print(book_obj.authors) # 对象点击多对多字段 会直接跨到多对多的第三张表 然后可以直接进行操作 # book_obj.authors.add(5,9) # 通过多对多对象点字段,在第三张表add添加,可以添加一个或多个 # 也支持添加对象,进行操作,和添加id数字一样 # author_obj1 = models.Author.objects.filter(pk=5).first() # author_obj2 = models.Author.objects.filter(pk=9).first() # book_obj.authors.add(author_obj1,author_obj2) # 改 # book_obj = models.Book.objects.filter(pk=5).first() # book_obj.authors.set([4,9])
# author_obj = models.Author.objects.filter(pk=1).first() # author_obj1 = models.Author.objects.filter(pk=2).first() # author_obj2 = models.Author.objects.filter(pk=3).first() # book_obj.authors.set([author_obj,]) # book_obj.authors.set([author_obj,author_obj1,author_obj2])
# book_obj = models.Book.objects.filter(pk=4).first() # book_obj.authors.remove(9,4) # author_obj = models.Author.objects.filter(pk=1).first() # author_obj1 = models.Author.objects.filter(pk=2).first() # author_obj2 = models.Author.objects.filter(pk=3).first() # book_obj.authors.remove(author_obj) # book_obj.authors.remove(author_obj1,author_obj2)
# book_obj = models.Book.objects.filter(pk=6).first() # book_obj.authors.clear() # 清空当前书籍与作者的所有关系 """ # book_obj = models.Book.objects.filter(pk=4).first() # print(book_obj.publish.name) # print(book_obj.publish.addr)
# book_obj = models.Book.objects.filter(pk=4).first() # print(book_obj.authors.all()) # res = book_obj.authors.all() # for r in res: # print(r.name)
# author_obj = models.Author.objects.filter(name="jason").first() # print(author_obj.author_detail.addr)
# author_obj = models.Author.objects.filter(name="jason").first() # print(author_obj.book_set.all())
# author_detail_obj = models.AuthorDetail.objects.filter(phone=110).first() # print(author_detail_obj.author.name)
# book_obj = models.Book.objects.filter(pk=4).first() # author_list = book_obj.authors.all() # for author_obj in author_list: # print(author_obj.author_detail.phone)
"""神奇的双下滑查询""" # 查询价格大于200的书籍 # res = models.Book.objects.filter(price__gt=200) __gt大于 # print(res) # 查询价格小于200的书籍 # res = models.Book.objects.filter(price__lt=200) __lt小于 # print(res) # 查询价格大于等于200.22的书籍 # res = models.Book.objects.filter(price__gte=200.22) _gte大于等于 # print(res) # 查询价格小于等于200.22的书籍 # res = models.Book.objects.filter(price__lte=200.22) __lte小于等于 # print(res) # 查询价格要么是200,要么是300,要么是666.66 # res = models.Book.objects.filter(price__in=[200,300,666.66]) # print(res) # 查询价格在200到800之间的 # res = models.Book.objects.filter(price__range=(200,800)) # 两边都包含 # print(res) # 查询书籍名字中包含p的 """原生sql语句 模糊匹配 like % _ """ # res = models.Book.objects.filter(title__contains=‘p‘) # 仅仅只能拿小写p __contains # res = models.Book.objects.filter(title__icontains=‘p‘) # 忽略大小写 # print(res) # 查询书籍是以三开头的 # res = models.Book.objects.filter(title__startswith=‘三‘) # res1 = models.Book.objects.filter(title__endswith=‘p‘) # print(res) # print(res1) # 查询出版日期是2017的年(******) res = models.Book.objects.filter(create_time__year=‘2017‘) print(res) ?
# 正向 # res = models.Author.objects.filter(name="jason").values("author_detail__phone") # print(res) # 反向 # res1 = models.AuthorDetail.objects.filter(author__name=‘jason‘).values(‘phone‘) # print(res1)
# 正向 # res = models.Author.objects.filter(name="jason").values("author_detail__phone","age") # print(res) # 反向 # res1 = models.AuthorDetail.objects.filter(author__name=‘jason‘).values(‘phone‘,"author__age") # print(res1)
# 反向 # res = models.Author.objects.filter(author_detail__phone=120).values("age") # print(res) # 正向 # res1 = models.AuthorDetail.objects.filter(phone=120).values("author__age") # print(res1)
# res= models.Book.objects.filter(pk=4).values("authors__author_detail__phone") # print(res) # res1 = models.Book.objects.filter(pk=1).values(‘外键字段1__外键字段2__外键字段3__普通字段‘) # print(res)
# res = models.Publish.objects.filter(name=‘北方出版社‘).values(‘book__tittle‘,‘book__price‘) # print(res)
# res = models.Book.objects.filter(price__gt=19,publish__name=‘北方出版社‘).values(‘title‘,‘publish__name‘) # print(res)
# from django.db.models import Max,Min,Count,Avg,Sum # # # res = models.Book.objects.aggregate(Sum(‘price‘)) # res1 = models.Book.objects.aggregate(Avg(‘price‘)) # res2 = models.Book.objects.aggregate(Count(‘price‘)) # res3 = models.Book.objects.aggregate(Max(‘price‘)) # res4 = models.Book.objects.aggregate(Min(‘price‘)) # res5 = models.Book.objects.aggregate(Max(‘price‘),Min(‘price‘),Count(‘pk‘),Avg(‘price‘),Sum(‘price‘)) # print(res5) # print(res) # print(res1) # print(res2) ?
from django.db.models import Max,Sum res = models.Book.objects.annotate(author1_num = Count(‘authors‘)).values(‘author1_num‘,‘tittle‘) print(res)
# res = models.Publish.objects.annotate(mmp = Min(‘book__price‘)).values(‘name‘,‘mmp‘) # print(res)
# res = models.Book.objects.annotate(author_num=Count(‘authors‘)).filter(author_num__gt=1) # print(res)
# res = models.Author.objects.annotate(sp=Sum(‘book__price‘)).values(‘name‘,‘sp‘) # print(res)
# res = models.Book.objects.filter(kucun__gt=F(‘maichu‘)) # print(res) # 将书籍库存数全部增加1000 from django.db.models.functions import Concat from django.db.models import Value # ret3 = models.Book.objects.update(tittle=Concat(F(‘tittle‘),Value(‘新款‘))) # models.Book.objects.update(title = F(‘title‘)+‘新款‘) # 不能这么写
# res = models.Book.objects.filter(title=‘三国演义‘,price=444.44) # filter只支持and关系 # res1 = models.Book.objects.filter(Q(title=‘三国演义‘),Q(price=444)) # 如果用逗号 那么还是and关系 # res2 = models.Book.objects.filter(Q(title=‘三国演义‘)|Q(price=444)) # res3 = models.Book.objects.filter(~Q(title=‘三国演义‘)|Q(price=444)) # print(res2)
# q = Q() # q.connector = ‘or‘ # 修改查询条件的关系 默认是and # q.children.append((‘title__contains‘,‘三国演义‘)) # 往列表中添加筛选条件 # q.children.append((‘price__gt‘,444)) # 往列表中添加筛选条件 # res = models.Book.objects.filter(q) # filter支持你直接传q对象 但是默认还是and关系 # print(res) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |