聚合查询
# MySQL聚合函数有哪些？
max min sum count avg
# MySQL聚合函数使用方式
from django.db.models import Max, Min, Sum, Avg, Count
res = models.Book.objects.aggregate(Min('book_price'))
print(res) # {'book_price__min': Decimal('15.00')}
注意：没有分组使用聚合函数会默认整体就是一组
# MySQL聚合函数混合使用
res = models.Book.objects.aggregate(Max('book_price'),
Min('book_price'),
Sum('book_price'),
Avg('book_price'),
Count('publish_id'))
print(res) # {'book_price__max': Decimal('80.00'), 'book_price__min': Decimal('15.00'), 'book_price__sum': Decimal('427.00'), 'book_price__avg': 47.444444, 'publish_id__count': 9}
分组查询
MySQL分组操作:group by
ORM执行分组操作：annotate
注意：如果报错，可能需修改sql_mode，移除only_full_group_by
一、按照表分组
# 统计每本书的作者个数
from django.db.models import Count
from django.db.models import Count
res = models.Book.objects.annotate(author_amount=Count('authors__pk')).values('book_title', 'author_amount')
print(res) # <QuerySet [{'book_title': '中国地理', 'author_amount': 2},
# {'book_title': '亚洲地理', 'author_amount': 1}, {'book_title': '美洲地理', 'author_amount': 2},
# {'book_title': '欧洲探索', 'author_amount': 1}, {'book_title': '太平洋记录', 'author_amount': 3},
# {'book_title': '大西洋记录', 'author_amount': 2}, {'book_title': '初中英语', 'author_amount': 0},
# {'book_title': '大学物理', 'author_amount': 0}, {'book_title': '大学化学', 'author_amount': 0}]>
# 统计每个出版社价格最便宜的书
res = models.Press.objects.annotate(min_price=Min('book__book_price')).values('press_name', 'min_price')
print(res) # <QuerySet [{'press_name': '南京大学出版社', 'min_price': Decimal('15.00')},
# {'press_name': '江苏大学出版社', 'min_price': Decimal('28.00')},
# {'press_name': '中国矿业大学出版社', 'min_price': Decimal('30.00')},
# {'press_name': '苏州大学出版社', 'min_price': Decimal('80.00')}]>
# 统计不止一个作者的图书
b res=models.Book.objects.annotate(author_amount=Count('authors__pk')).filter(author_amount__gt=1).\
values('book_title', 'author_amount')
print(res) # <QuerySet [{'book_title': '中国地理', 'author_amount': 2},
# {'book_title': '美洲地理', 'author_amount': 2},
# {'book_title': '太平洋记录', 'author_amount': 3},
# {'book_title': '大西洋记录', 'author_amount': 2}]>
# 统计每个作者出的书的总价格
res = models.Author.objects.annotate(book_sum_price=Sum('book__book_price')).values('author_name', 'book_sum_price')
print(res) # <QuerySet [{'author_name': '李伟', 'book_sum_price': Decimal('48.00')},
# {'author_name': '赵国', 'book_sum_price': Decimal('110.00')},
# {'author_name': '赵刚', 'book_sum_price': Decimal('161.00')},
# {'author_name': '徐婷', 'book_sum_price': Decimal('108.00')},
# {'author_name': '王凯', 'book_sum_price': Decimal('51.00')}]>
二、按照表中的某个字段分组
# 统计每个出版社主键值对应的书籍个数
res = models.Book.objects.values('publish_id').annotate(book_amount=Count('pk')).values('publish_id','book_amount')
print(res) # <QuerySet [{'publish_id': 1, 'book_amount': 3},
# {'publish_id': 2, 'book_amount': 2},
# {'publish_id': 3, 'book_amount': 3},
# {'publish_id': 4, 'book_amount': 1}]>
F与Q查询
F查询
# 查询库存大于销量的书籍
# res = models.Book.objects.filter(stock_amount > sale_amount) 语法错误
# res = models.Book.objects.filter(stock_amount__getsale_amount) 语法错误
当查询条件的左右两边的数据都需要表中的数据时建议使用F查询
from django.db.models import F
res = models.Book.objects.filter(stock_amount__gt=F('sale_amount'))
print(res) # <QuerySet [<Book: 美洲地理-图书对象>, <Book: 欧洲探索-图书对象>,
# <Book: 太平洋记录-图书对象>, <Book: 大西洋记录-图书对象>, <Book: 初中英语-图书对象>]>
# 将所有书的价格提升9块
res = models.Book.objects.update(book_price=F('book_price') + 9)
print(res) # 9
# 将所有书的名称后面加上‘-即将售罄’后缀
res = models.Book.objects.update(book_title=F('book_title') + '-即将售罄') # 慎用，会清空书名字段
# 修改char字段的正确操作
from django.db.models.functions import Concat, ConcatPair
from django.db.models import Value
res = models.Book.objects.update(book_title=Concat(F('book_title'), Value('-即将售罄')))
print(res) # 9
res = models.Book.objects.update(book_title=ConcatPair(F('book_title'), Value('-即将售罄')))
print(res) # 9
Q查询
# 查询价格大于30或者卖出大于2500的书籍
res = models.Book.objects.filter(book_price__gt=30,sale_amount__gt=2500)
print(res) # <QuerySet [<Book: 大学物理-图书对象>, <Book: 大学化学-图书对象>]>
print(res.query) # SELECT `app01_book`.`id`, `app01_book`.`book_title`,
# `app01_book`.`book_price`, `app01_book`.`publish_time`, `app01_book`.`publish_id`,
# `app01_book`.`stock_amount`, `app01_book`.`sale_amount` FROM `app01_book`
# WHERE (`app01_book`.`book_price` > 30 AND `app01_book`.`sale_amount` > 2500)
结论：filter括号内多个条件默认是and关系
from django.db.models import Q
'''使用Q对象可以支持逻辑运算符'''
res = models.Book.objects.filter(Q(book_price__gt=30), Q(sale_amount__gt=2500)) # 逗号是and关系
print(res) # <QuerySet [<Book: 大学物理-图书对象>, <Book: 大学化学-图书对象>]>
print(res.query) # SELECT `app01_book`.`id`, `app01_book`.`book_title`, `app01_book`.`book_price`, `app01_book`.`publish_time`, `app01_book`.`publish_id`, `app01_book`.`stock_amount`, `app01_book`.`sale_amount` FROM `app01_book` WHERE (`app01_book`.`book_price` > 30 AND `app01_book`.`sale_amount` > 2500)
res = models.Book.objects.filter(Q(book_price__gt=30) | Q(sale_amount__gt=2500)) # 管道符是or关系
print(res) # <QuerySet [<Book: 中国地理-图书对象>, <Book: 亚洲地理-图书对象>, <Book: 美洲地理-图书对象>, <Book: 欧洲探索-图书对象>, <Book: 太平洋记录-图书对象>, <Book: 初中英语-图书对象>, <Book: 大学物理-图书对象>, <Book: 大学化学-图书对象>]>
print(res.query) # SELECT `app01_book`.`id`, `app01_book`.`book_title`, `app01_book`.`book_price`, `app01_book`.`publish_time`, `app01_book`.`publish_id`, `app01_book`.`stock_amount`, `app01_book`.`sale_amount` FROM `app01_book` WHERE (`app01_book`.`book_price` > 30 OR `app01_book`.`sale_amount` > 2500)
res = models.Book.objects.filter(~Q(book_price__gt=30)) # ~是not操作
print(res) # <QuerySet [<Book: 大西洋记录-图书对象>]>
print(res.query) # SELECT `
# Q对象进阶用法
filter(price=40)
filter('price'=40)
当我们需要编写一个搜索功能,条件是由用户指定, 这种情况左边的数据就是一个字符串
q_obj = Q()
q_obj.connector = 'or' # 默认是and,可改为or
q_obj.children.append(('book_price__gt', 70))
q_obj.children.append(('sale_amount__gt', 3000))
res = models.Book.objects.filter(q_obj)
print(res) # <QuerySet [<Book: 太平洋记录-图书对象>, <Book: 大学物理-图书对象>, <Book: 大学化学-图书对象>]>
print(res.query) # SELECT `app01_book`.`id`, `app01_book`.`book_title`, `app01_book`.`book_price`, `app01_book`.`publish_time`, `app01_book`.`publish_id`, `app01_book`.`stock_amount`, `app01_book`.`sale_amount` FROM `app01_book` WHERE (`app01_book`.`book_price` > 70 or `app01_book`.`sale_amount` > 3000)
ORM查询优化
数据库管理,应当尽量减少与数据库的交互
# ORM查询默认都是惰性查询
只编写ORM语句并不会直接执行SQL语句，只有后续的代码用到了前面的ORM语句才会执行
# ORM查询默认自带分页功能
# only与defer
res = models.Book.objects.values('book_title', 'book_price')
'''需求:单个结果还是以对象的形式展示，可以直接通过句点符操作'''
for i in res:
print(i.get('book_title'))
res = models.Book.objects.only('book_title', 'book_price')
for obj in res:
print(obj.book_title)
print(obj.book_price)
print(obj.publish_time)
注意：only会产生对象结果集,对象点括号内出现的字段不会再走数据库查询
但是如果点击括号内没有的字段也可以获取到数据,但每次执行SQL语句
res = models.Book.objects.defer('book_title', 'book_price')
for obj in res:
print(obj.book_title)
print(obj.book_price)
print(obj.publish_time)
注意：defer与only刚好相反，对象点括号内出现的字段会走数据库查询
如果点括号内没有的字段也可获取到数据，而且不会每次执行SQL语句
# select_related和prefetch_related
res = models.Book.objects.select_related('publish')
for obj in res:
print(obj.book_title)
print(obj.publish.press_name)
print(obj.publish.press_addr)
注意：select_related括号内只能传‘一对一’和‘一对多’字段，不能传‘多对多’字段
效果是内部直接连接表(inner join)，然后将连接之后的大表中所有的数据全部封装到数据对象中
后续对象通过正反向查询跨表，内部不会再执行SQL语句
res = models.Book.objects.prefetch_related('publish')
for obj in res:
print(obj.book_title)
print(obj.publish.press_name)
print(obj.publish.press_addr)
注意：将多次查询之后的结果封装到数据对象中，后续对象通过正反向查询跨表时内部不会再执行SQL语句
ORM字段类型及参数
# 常见字段
AutoField()
int auto_increment
CharField()
注意：必须提供max_length参数，对应的数据库中是varchar类型
IntergerField()
int
DecimalField()
decimal
DateField()
date auto_now auto_now_add
DateTimeField()
datetime auto_now auto_now_add
BigIntergerField()
bigint
BooleanField()
传布尔值 0和1
TextField()
存储大段文本
FileField()
传文件自动保存到指定位置并存文件路径
EmailField()
本质是varchar类型
# 自定义字段类型
class FixedCharField(models.Field):
"""
自定义的char类型的字段类
"""
def __init__(self, max_length, *args, **kwargs):
super().__init__(max_length=max_length, *args, **kwargs)
self.length = max_length
def db_type(self, connection):
"""
限定生成数据库表的字段类型为char，长度为length指定的值
"""
return 'char(%s)' % self.length
class TempTable(models.Model):
id = models.AutoField(primary_key=True)
title = models.CharField(max_length=20)
# 使用上面自定义的char类型的字段
cname = FixedCharField(max_length=20)
重要参数
primary_key
max_length
verbose_name
null
default
max_digits
decimal_places
unique
db_index
auto_now
auto_now_add
to
to_field
db_constraint
# 外键字段中可能还会遇到related_name参数
外键字段中使用related_name参数可以修改正向查询的字段名
# choices
用于可以被列举完全的数据
例如:性别
class User_test(models.Model):
username = models.CharField(max_length=32)
password = models.IntegerField()
gender_choice = (
(1,'男性'),
(2,'女性'),
(3,'其他')
)
gender = models.IntegerField(choices=gender_choice)
user_obj.get_gender_display()
# 有对应关系自动映射，没有就不变
ORM事务操作
MySQL事务:四大特性(ACID)
原子性
一致性
独立性
持久性
start transcation;
rollback;
commit;
from django.db import transaction
try:
with transaction.atomic():
models.User_test.objects.filter(pk=1).update(gender=3)
models.User_test.objects.filter(pk=3).update(gender=1)
except Exception:
pass
ORM执行原生SQL
# 方式1
from django.db import connection, connections
cursor = connection.cursor()
cursor = connections['default'].cursor()
cursor.execute("""SELECT * from user where id = %s""", [1])
cursor.fetchone()
# 方式2
models.UserInfo.objects.extra(
select={'newid':'select count(1) from app01_user where id>%s'},
select_params=[1,],
where = ['age>%s'],
params=[18,],
order_by=['-age'],
tables=['app01_user']
)
多对多三种创建方式
# 全自动(常见)
orm自动创建第三张表,但无法扩展第三张表的字段
authors = models.ManyToManyField(to='Author')
# 全手动(使用频率最低)
优势在于第三张表完全自定义扩展性高;劣势在于无法使用外键方法和正反向
class Book(models.Model):
title = models.CharField(max_length=32)
class Author(models.Model):
name = models.CharField(max_length=32)
class Book2Author(models.Model):
book_id = models.ForeignKey(to='Book')
author_id = models.ForeignKey(to='Author')
# 半自动(常见)
跟全自动意义可以使用正反向,而且第三张表可以扩展，唯一的缺陷是不能用add，set，remove，clear四个方法
class Book(models.Model):
title = models.CharField(max_length=32)
authors = models.ManyToManyField(
to='Author',
through='Book2Author', # 指定表
through_fields=('book','author') # 指定字段
)
class Author(models.Model):
name = models.CharField(max_length=32)
books = models.ManyToManyField(
to='Author',
through='Book2Author', # 指定表
through_fields=('author','book') # 指定字段
) # 多对多可以建在任意一方，如果建在作者表，只需字段顺序互换
class Book2Author(models.Model):
book = models.ForeignKey(to='Book')
author = models.ForeignKey(to='Author')
