python 之 Django框架(orm单表查询、orm多表查询、聚合查询、分组查询、F查询、 Q查询、事务、Django ORM执行原生SQL)
12.329 orm单表查询
import os if __name__ == '__main__': # 指定当前py脚本需要加载的Django项目配置信息 os.environ.setdefault("DJANGO_SETTINGS_MODULE", "orm_demo.settings") import django django.setup() # 启动Django项目 from app01 import models #返回QuerySet对象的方法: ret = models.Book.objects.all() print(ret) # QuerySet类型:书籍对象的列表 ret = models.Book.objects.filter(title="围城") # QuerySet类型 --> 书籍对象的列表 # id值大于1 ret = models.Book.objects.filter(id__gt=1) # id值小于3 ret = models.Book.objects.filter(id__lt=3) # 出版日期是2017年的书 ret = models.Book.objects.filter(publisher_date__year=2017) # 出版日期大于2017年 ret = models.Book.objects.filter(publisher_date__year__gt=2017) # 书名中包含'曌'的书 ret = models.Book.objects.filter(title__contains="曌") # 书名中包含'曌'的书并且出版年份是2018年 ret = models.Book.objects.filter(title__contains="曌", publisher_date__year=2018) # get方法如果符合筛选条件的对象超过一个或者没有都会抛出错误。 #符合筛选条件的对象只有一个，则返回具体的类对象实例：书籍对象，而不是列表 ret = models.Book.objects.get(id=10) print(ret) #报错 # 使用filter检索的时候没有满足条件的数据就返回一个空 QuerySet ret = models.Book.objects.filter(id=10) print(ret) #[] # 将满足条件的去掉，留下不满足条件的 ret = models.Book.objects.exclude(id__in=[1,3,4]) print(ret) # 按字段排序 ret = models.Book.objects.all().order_by("price") # QuerySet类型：根据price字段对所有数据排序 ret = models.Book.objects.all().order_by("-price") #反转 ret = models.Book.objects.all().order_by("price").reverse() # 按照出版时间排序后反转再去字段值 # QuerySet类型：字段及字段值的字典的列表 ret = models.Book.objects.all().order_by("publisher_date").reverse().values("title") #特殊的QuerySet: #values() 取字段的值，以字典返回 ret = models.Book.objects.filter(publisher_date__year=2018).values("title", "publisher_date") print(ret) # QuerySet类型：字段及字段值的字典的列表 #values_list() 取字段的值，以元组返回 ret = models.Book.objects.filter(publisher_date__year=2018).values_list("title", "publisher_date") # QuerySet类型：字段值的元祖的列表 # 连表查询 ret = models.Book.objects.all().values("publisher__name").distinct() print(ret) # QuerySet类型：字段及字段值的字典的列表，并去重 #返回数字的方法 # count 计数 ret = models.Book.objects.all().count() # 数字：结果集中数据的个数 #返回具体对象 #first()和last() ret = models.Book.objects.all().first() #结果集中的第一个对象 #get() ret = models.Book.objects.get(id=1) #返回匹配到的对象，有且仅有一个 #返回布尔值的方法 # 判断结果集中是否有数据 ret = models.Book.objects.all().exists() # 布尔值：结果集中是否有数据
12.3210 orm多表查询(方式二)
ForeignKey操作:书籍表(Book表)外键关联出版社表(Publisher表)
正向查找:
1.基于对象(子查询) book_obj = models.Book.objects.first() # 第一本书对象 print(book_obj.publisher) # 得到这本书关联的出版社对象 print(book_obj.publisher.name) # 得到出版社对象的名称 2.基于双下划线字段方法(联表查询) print(models.Book.objects.values_list("publisher__name"))
反向查找：
1.基于对象(子查询) publisher_obj = models.Publisher.objects.first() # 找到第一个出版社对象 books = publisher_obj.book_set.all() # 找到第一个出版社出版的所有书籍对象 titles = books.values_list("title") # 找到第一个出版社出版的所有书籍的书名 #如果设置了 related_name="books" #publisher= models.ForeignKey(to="Publisher", related_name="books") publisher_obj = models.Publisher.objects.first() ret = publisher_obj.books.all() print(ret)#<QuerySet [<Book: 西瓜物语>, <Book: 香蕉物语>]> 2.基于双下划线的字段方法(联表查询) ret = models.Publisher.objects.filter(id=1).values_list("book__title") print(ret)#<QuerySet [('西瓜物语',), ('香蕉物语',)]> titles = models.Publisher.objects.values_list("book__title") print(titles)#<QuerySet [('西瓜物语',), ('香蕉物语',), ('番茄物语',), (None,)]> #如果related_query_name="books"或者 related_name="books"(如果两个同时出现，则以related_query_name为准) titles = models.Publisher.objects.filter(id=1).values_list("books__title") #<QuerySet [('西瓜物语',), ('香蕉物语',)]> titles = models.Publisher.objects.filter(id=1).values("books__title") #<QuerySet [{'books__title': '西瓜物语'}, {'books__title': '香蕉物语'}]>
ManyToManyField：(使用方式二：通过ManyToManyField自动创建第三张表)
models:
class Book(models.Model): title = models.CharField(max_length=32) publish_date = models.DateField(auto_now_add=True) price = models.DecimalField(max_digits=5, decimal_places=2) memo = models.TextField(null=True) # 创建外键，关联publish publisher = models.ForeignKey(to="Publisher", ) # 创建多对多关联author author = models.ManyToManyField(to="Author") class Author(models.Model): name = models.CharField(max_length=32) age = models.IntegerField() phone = models.CharField(max_length=11) detail = models.OneToOneField(to="AuthorDetail")
class RelatedManager:"关联管理器"是在一对多或者多对多的关联上下文中使用的管理器。
它存在于下面两种情况：外键关系的反向查询、多对多关联关系，简单来说就是当 "."后面的对象 可能存在多个的时候就可以使用以下的方法
create()：创建一个新的对象，保存对象，并将它添加到关联对象集之中，返回新创建的对象。
#models.Book.objects.first().author得到是一个class RelatedManager对象，使用.create操作author表和第三张表 1.正向创建author表数据 ret = models.Book.objects.first().author.create(name="张三",age=16,phone="18012xxxx",detail_id=4) #做了两件事情：1. 创建了一个新的作者，2. 将新创建的作者和第一本书做关联 ret = models.Book.objects.first().author.all().values("id") print(ret)#<QuerySet [{'id': 1}, {'id': 3}, {'id': 15}]> 2.反向创建book表数据 import datetime models.Author.objects.first().book_set.create(title="番茄物语", publish_date=datetime.date.today())
add()：把指定的model对象或对象id添加到关联对象集中
#添加对象 author_objs = models.Author.objects.filter(id__lt=3) models.Book.objects.first().author.add(*author_objs) #添加id models.Book.objects.first().author.add(*[1, 2]) models.Book.objects.first().author.add(1) #第一本书关联的作者id ret = models.Book.objects.first().author.all().values("id") print(ret)#<QuerySet [{'id': 1}, {'id': 3}, {'id': 15}]>
set()：更新model对象的关联对象。
ret=models.Book.objects.first().author.set([2, 3])#设置第三张表的关联关系，给第一个book对象加上id=2和3 ret = models.Book.objects.first().author.all() print(ret)#<QuerySet [<Author: 小仙女>, <Author: 大乌龟>, <Author: 张曌>]> ret = models.Book.objects.first().author.all().values("id") print(ret)#<QuerySet [{'id': 1}, {'id': 3}, {'id': 15}]>
remove()：从关联对象集中移除执行的model对象
models.Book.objects.first().author.remove(3)#找到第一个图书对象所对应的所有作者，到第三张表中删除它与id=3的作者的关联关系#<QuerySet [{'id': 1}, {'id': 15}，{'id'=3}]> ret = models.Book.objects.first().author.all().values("id") print(ret) #<QuerySet [{'id': 1}, {'id': 15}]>
clear():从关联对象集中移除一切对象。
#<QuerySet [{'id': 1}, {'id': 15}]> models.Book.objects.first().author.clear() ret = models.Book.objects.first().author.all().values("id") print(ret)#<QuerySet []>
注意：对于ForeignKey对象，clear()和remove()方法仅在null=True时存在
all()：
ret = models.Book.objects.first().author.all()#得到第一本书对象对应的作者对象集 print(ret) #<QuerySet [Author object,Author object,Author object]>
12.3211 聚合查询
aggregate()是QuerySet 的一个终止子句，它返回一个包含一些键值对的字典。
键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。
from django.db.models import Avg, Sum, Max, Min, Count models.Book.objects.all().aggregate(Avg("price")) #{'price__avg': 13.233333} ret = models.Book.objects.aggregate(Sum("price")) #{'price__sum': Decimal('13.10') ret = models.Book.objects.aggregate(total_price=Sum("price")) #{'total_price': Decimal('13.10')} ret = models.Book.objects.aggregate(avg_price=Avg("price"), max_price=Max("price"), min_price=Min("price")) #{'avg_price': 4.366667, 'max_price': Decimal('12.00'), 'min_price': Decimal('0.10')}
12.3212 分组查询
单表查询分组：按照部门分组求平均工资
select dept,AVG(salary) from employee group by dept;
orm查询：
from django.db.models import Avg models.Employee.objects.values("dept").annotate(avg=Avg("salary") #<QuerySet [{'dept': '教学部', 'avg': 221.0}, {'dept': '销售部', 'avg': 21.0}, {'dept': '人事部', 'avg': 999.0}]> models.Employee.objects.values("dept").annotate(avg=Avg("salary").values('dept', "avg") #<QuerySet [{'dept': '教学部', 'avg': 221.0}, {'dept': '销售部', 'avg': 21.0}, {'dept': '人事部', 'avg': 999.0}]>
连表查询的分组：按照部门分组求平均工资
select dept.name,AVG(salary) from employee inner join dept on (employee.dept_id=dept.id) group by dept_id;
ORM查询：
from django.db.models import Avg models.Dept.objects.annotate(avg=Avg("employee__salary")).values("name", "avg") models.Employee.objects.values("dept__name").annotate(avg=Avg("salary")) #<QuerySet [{'dept__name': '垃圾部', 'avg': 221.0}, {'dept__name': '保安部', 'avg': 21.0}, {'dept__name': '教学部', 'avg': 999.0}]> models.Employee.objects.values("dept__name").annotate(avg=Avg("salary")).values('dept', "avg") #<QuerySet [{'dept__name': '垃圾部', 'avg': 221.0}, {'dept__name': '保安部', 'avg': 21.0}, {'dept__name': '教学部', 'avg': 999.0}]> models.Employee.objects.values("dept__name") #<QuerySet [{'dept__name': '垃圾部'}, {'dept__name': '保安部'}, {'dept__name': '教学部'}]>
作者、图书、出版社表关系：
from django.db import models # 出版社 class Publisher(models.Model): name = models.CharField(max_length=32) city = models.CharField(max_length=32) # 书 class Book(models.Model): title = models.CharField(max_length=32) publish_date = models.DateField(auto_now_add=True) price = models.DecimalField(max_digits=5, decimal_places=2) # 创建外键，关联publish publisher = models.ForeignKey(to="Publisher") # 创建多对多关联author author = models.ManyToManyField(to="Author") # 作者 class Author(models.Model): name = models.CharField(max_length=32)
示例：
1.统计每一本书的作者个数 (1):ret = models.Book.objects.annotate(autor_num=Count("author")).values("title", "autor_num") #<QuerySet [{'title': '西瓜物语', 'autor_num': 0}, {'title': '香蕉物语', 'autor_num': 1}, {'title': '番茄物语', 'autor_num': 1}]> (2):book_list = models.Book.objects.all().annotate(author_num=Count("author")) print(book_list) #<QuerySet [<Book: 西瓜物语>, <Book: 香蕉物语>, <Book: 番茄物语>]> for obj in book_list: print(obj.author_num)#0 1 1 2.统计出每个出版社出版的最便宜的书的价格 (1):ret = models.Publisher.objects.annotate(min_price=Min("book__price")).values("name", "min_price") #<QuerySet [{'name': '清华出版社', 'min_price': Decimal('0.10')}, {'name': '香江出版社', 'min_price': Decimal('12.00')}, {'name': '北大青鸟出版社', 'min_price': None}]> (2):ret=models.Book.objects.values("publisher__name").annotate(min_price=Min("price")) #<QuerySet [{'publisher__name': '清华出版社', 'min_price': Decimal('0.10')}, #{'publisher__name': '香江出版社', 'min_price': Decimal('12.00')}]> (3):publisher_list = models.Publisher.objects.annotate(min_price=Min("book__price")) print(publisher_list) #<QuerySet [<Publisher: 清华出版社>, <Publisher: 香江出版社>, <Publisher: 北大青鸟出版社>]> for obj in publisher_list: print(obj.min_price)#0.10 12.00 None 3.统计不止一个作者的图书 models.Book.objects.annotate(author_num=Count("author")).filter(author_num__gt=1)#<QuerySet []> 4.根据一本图书作者数量的多少对查询集 QuerySet进行排序 models.Book.objects.annotate(author_num=Count("author")).order_by("author_num") #<QuerySet [<Book: 西瓜物语>, <Book: 香蕉物语>, <Book: 番茄物语>]> 5.查询各个作者出的书的总价格 models.Author.objects.annotate(sum_price=Sum("book__price")).values("name", "sum_price") #<QuerySet [{'name': '小仙女', 'sum_price': Decimal('1.00')}, {'name': '小魔女', 'sum_price': Decimal('12.00')}, #{'name': '大乌龟', 'sum_price': None}, {'name': '张san', 'sum_price': None}]>
12.3213 F查询
F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。
商品表结构：
from django.db import models class Product(models.Model): name = models.CharField(max_length=32) price = models.DecimalField(max_digits=6, decimal_places=2) # 库存数 keep = models.IntegerField() # 卖出数 sale = models.IntegerField() def __str__(self): return "{}:{}:{}:{}".format(self.name, self.price, self.keep, self.sale)
示例：
from django.db.models import F 1.查询出卖出数大于库存数的商品 models.Product.objects.filter(sale__gt=F("keep")) #<QuerySet [<Product: 跟哪吒学诗歌:59.00:50:10000>, <Product: 跟苑局学三不:55.00:100:200>]> 2.Django支持F()对象之间以及F()对象和常数之间的加减乘除和取模的操作 models.Product.objects.filter(sale__gt=F('keep')*2) #<QuerySet [<Product: 跟哪吒学诗歌:59.00:50:10000>]> 3.修改操作也可以使用F函数:比如将每个产品的价格提高50元 models.Product.objects.all().update(price=F("price")+50) 4.把所有商品名后面加上"新款" from django.db.models.functions import Concat from django.db.models import Value models.Product.objects.all().update(name=Concat(F("name"), Value("新款")))
12.3214 Q查询
filter() 等方法中的关键字参数查询都是一起进行“AND” 的。 如果需要执行更复杂的查询（例如OR语句），可以使用Q对象
#卖出数大于100 并且 价格大于100块的 models.Product.objects.filter(sale__gt=100, price__gt=100) #<QuerySet [<Product: 跟哪吒学诗歌新款:259.00:50:10000>, <Product: 跟苑局学三不新款:255.00:100:200>]>
示例：
from django.db.models import Q 1.查询卖出数大于100或者价格小于100的 models.Product.objects.filter(Q(sale__gt=100)|Q(price__lt=100)) #|:或 #<QuerySet [<Product: 跟哪吒学诗歌新款:259.00:50:10000>, <Product: 跟苑局学三不新款:255.00:100:200>]> 2.查询库存数是100并且卖出数不是0的产品 models.Product.objects.filter(Q(keep=100)&~Q(sale=0)) #&：与，~：非 models.Product.objects.filter(Q(kucun=100),~Q(maichu=0)) #<QuerySet [<Product: 跟苑局学三不新款:255.00:100:200>]> models.Product.objects.filter(Q(kucun=100)&~Q(maichu=0)).values('name') #<QuerySet [{'name': '跟苑局学三不新款'}]> models.Product.objects.filter(Q(kucun=100)&~Q(maichu=0)).values_list('name') #<QuerySet [('跟苑局学三不新款',)]>
查询函数可以混合使用Q 对象和关键字参数。所有提供给查询函数的参数（关键字参数或Q 对象）都将"AND”在一起。但是，如果出现Q 对象，它必须位于所有关键字参数的前面
#查询产品名包含新款， 并且库存数大于60或者价格小于100的产品 models.Product.objects.filter(Q(keep__gt=60)|Q(price__lt=100), name__contains="新款") #<QuerySet [<Product: 跟苑局学三不新款:255.00:100:200>]>
12.3215 事务
开启一个事务可以包含一些sql语句，这些sql语句要么同时成功，要么都不成功，称之为事务的原子性 作用：事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
import os if __name__ == '__main__': os.environ.setdefault("DJANGO_SETTINGS_MODULE", "BMS.settings") import django django.setup() import datetime from app01 import models try: from django.db import transaction with transaction.atomic(): #开启事务 new_publisher = models.Publisher.objects.create(name="火星出版社") models.Book.objects.create(title="橘子物语", publish_date=datetime.date.today(), publisher_id=10) # 指定一个不存在的出版社id，上一行创建一条出版社数据被回滚，数据库并未创建新数据 except Exception as e: print(str(e))
12.3216 Django ORM执行原生SQL
很多情况下我们不需要将查询结果映射成模型，或者我们需要执行DELETE、 INSERT以及UPDATE操作，在这些情况下，我们可以直接访问数据库，完全避开模型层。我们可以直接从django提供的接口中获取数据库连接，然后像使用pymysql模块一样操作数据库
from django.db import connection, connections cursor = connection.cursor() # cursor = connections['default'].cursor() cursor.execute("""SELECT * from auth_user where id = %s""", [1]) ret = cursor.fetchone()
