django 第六课 模型第二讲
1.常用的字段类型
官方文档地址
https://docs.djangoproject.com/en/2.1/ref/models/fields/#field-types
2.字段的常用参数
https://docs.djangoproject.com/en/2.1/ref/models/fields/#field-options
3.常用查询
通过模型类上的管理器来构造QuerySet
模型类上的管理是啥？
模型类型.objects
QuerySet 表示数据库种对象的集合，等同于Select 语句 惰性的
案例：
-all（） 获取所有记录,返回都是QuerySet
In [17]: Student.objects.all() Out[17]: <QuerySet [<Student: xinlan-0>, <Student: donghao-0>, <Student: liuyifei-0>, <Student: liutao-0>, <Student: liudehua-0>, <Student: zhangtianbao-0>]>
first() 获取第一条 返回的是对象
last() 获取最后一条返回的对象
In [19]: s = Student.objects.first() In [20]: s Out[20]: <Student: xinlan-0> In [21]: s = Student.objects.last() In [22]: s Out[22]: <Student: zhangtianbao-0>
get（**kwargs） 根据给定的条件获取一个对象
In [25]: s = Student.objects.get(sex = 0) --------------------------------------------------------------------------- DoesNotExist Traceback (most recent call last) <ipython-input-25-68f9926d8462> in <module> ----> 1 s = Student.objects.get(sex = 0) ~/.virtualenvs/django/lib/python3.6/site-packages/django/db/models/manager.py in manager_method(self, *args, **kwargs) 80 def create_method(name, method): 81 def manager_method(self, *args, **kwargs): ---> 82 return getattr(self.get_queryset(), name)(*args, **kwargs) 83 manager_method.__name__ = method.__name__ 84 manager_method.__doc__ = method.__doc__ ~/.virtualenvs/django/lib/python3.6/site-packages/django/db/models/query.py in get(self, *args, **kwargs) 397 raise self.model.DoesNotExist( 398 "%s matching query does not exist." % --> 399 self.model._meta.object_name 400 ) 401 raise self.model.MultipleObjectsReturned( DoesNotExist: Student matching query does not exist.
- filter(**kwargs) 根据给定的条件，获取一个过滤后的queryset，多个条件使用and连接。
- exclude(**kwargs) 跟filter使用方法一致，作用想反，它是排除。
In [32]: res =Student.objects.filter(age=0,sex=1) In [33]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` WHERE (`teacher_student`.`age` = 0 AND `teacher_student`.`sex` = 1) In [34]: res =Student.objects.exclude(age=0,sex=1) In [35]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` WHERE NOT (`teacher_student`.`age` = 0 AND `teacher_student`.`sex` = 1)
- 多条件的OR连接 用到Q对象，django.db.models.Q
In [36]: from django.db.models import Q In [37]: res = Student.objects.filter(Q(age=0)|Q(age=1)) In [38]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` WHERE (`teacher_student`.`age` = 0 OR `teacher_student`.`age` = 1)
- values(*fields) 返回一个queryset，返回一个字典列表，而不是数据对象。
- only(*fiels) 返回querySet ，对象列表，注意only一定包含主键字段
- defer(*fields) 返回一个QuerySet，作用和only相反
In [39]: res = Student.objects.values('name') In [40]: print(res) <QuerySet [{'name': 'xinlan'}, {'name': 'donghao'}, {'name': 'liuyifei'}, {'name': 'liutao'}, {'name': 'liudehua'}, {'name': 'zhangtianbao'}]> In [41]: res Out[41]: <QuerySet [{'name': 'xinlan'}, {'name': 'donghao'}, {'name': 'liuyifei'}, {'name': 'liutao'}, {'name': 'liudehua'}, {'name': 'zhangtianbao'}]> In [42]: res[0]['name'] Out[42]: 'xinlan' In [43]: res = Student.objects.values('name','age').filter(age=0) In [44]: print(res.query) SELECT `teacher_student`.`name`, `teacher_student`.`age` FROM `teacher_student` WHERE `teacher_student`.`age` = 0 In [45]: res = Student.objects.only('name','age').filter(age=0) In [46]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age` FROM `teacher_student` WHERE `teacher_student`.`age` = 0 In [47]: res[0] Out[47]: <Student: xinlan-0> In [48]: res[0].c_time Out[48]: datetime.datetime(2019, 2, 25, 14, 55, 14, 24220, tzinfo=<UTC>) In [49]: res = Student.objects.defer('name','age') In [50]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student`
order_by(*fields) 根据给定的字段来排序 默认是顺序，字段名前加上 ‘-’代表反序
1 In [51]: res = Student.objects.order_by('c_time') 2 3 In [52]: res = Student.objects.order_by('c_time').only('name') 4 5 In [53]: print(res.query) 6 SELECT `teacher_student`.`id`, `teacher_student`.`name` FROM `teacher_student` ORDER BY `teacher_student`.`c_time` ASC 7 8 In [54]: res = Student.objects.order_by('-c_time').only('name') 9 10 In [55]: print(res.query) 11 SELECT `teacher_student`.`id`, `teacher_student`.`name` FROM `teacher_student` ORDER BY `teacher_student`.`c_time` DESC
- 切片 和python的列表切片用法相似，不支持负索引，数据量大时不用步长
*** 切片过后，不再支持，附加过滤条件与排序
In [56]: res = Student.objects.all()[:5] In [57]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` LIMIT 5 In [58]: res = Student.objects.all()[2:4] In [59]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` LIMIT 2 OFFSET 2
- 常用查询条件 filter，exclude， get
- exact
- iexact
In [65]: Student.objects.get(id__exact=10) Out[65]: <Student: liudehua-0> In [66]: Student.objects.filter(id__exact=10) Out[66]: <QuerySet [<Student: liudehua-0>]> In [67]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` LIMIT 2 OFFSET 2 In [68]: Student.objects.filter(id__iexact=10) Out[68]: <QuerySet [<Student: liudehua-0>]> In [69]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` LIMIT 2 OFFSET 2
- contains
- icontains
In [72]: res = Student.objects.filter(name__contains='xinlan') In [73]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE BINARY %xinlan% In [74]: res = Student.objects.filter(name__icontains='xinlan') In [75]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE %xinlan%
- in
In [76]: res =Student.objects.filter(name__in=['xinlan','donghao','asd']) In [77]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` WHERE `teacher_student`.`name` IN (xinlan, donghao, asd)
- range
In [90]: res = Student.objects.filter(age__range=(0,4)) In [91]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` WHERE `teacher_student`.`age` BETWEEN 0 AND 4
- gt
- gte
- lt
- lte
In [93]: Student.objects.filter(age__gt=18) Out[93]: <QuerySet []> In [94]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` WHERE `teacher_student`.`age` BETWEEN 0 AND 4 In [95]: Student.objects.filter(age__gte=18) Out[95]: <QuerySet []> In [96]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` WHERE `teacher_student`.`age` BETWEEN 0 AND 4 In [97]: Student.objects.filter(age__lt=18) Out[97]: <QuerySet [<Student: xinlan-0>, <Student: donghao-0>, <Student: liuyifei-0>, <Student: liutao-0>, <Student: liudehua-0>, <Student: zhangtianbao-0>]> In [98]: Student.objects.filter(age__lte=18) Out[98]: <QuerySet [<Student: xinlan-0>, <Student: donghao-0>, <Student: liuyifei-0>, <Student: liutao-0>, <Student: liudehua-0>, <Student: zhangtianbao-0>]>
- startswith
- istartswith
- endswith
- iendswith
- isnull True False 对应 IS NULL IS NOT NULL
- 聚合
from django.db.models import Count, Avg, Max, Min, Sum
通过queryset的aggregate方法
Student.objects.aggregate(age_avg=Avg('age')) # 计算平均年龄
- count
- 平均值 Avg
In [101]: from django.db.models import Avg,Max,Min,Sum In [102]: Student.objects.aggregate(age__avg=Avg('age')) Out[102]: {'age__avg': 0.0} In [103]: Student.objects.all().count() Out[103]: 6 In [104]: Student.objects.filter(name__startswith='xin').count() Out[104]: 1 In [105]: Student.objects.aggregate(agg__max=Max('age')) Out[105]: {'agg__max': 0} In [106]: Student.objects.aggregate(agg__min=Mix('age')) --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-106-ade266492921> in <module> ----> 1 Student.objects.aggregate(agg__min=Mix('age')) NameError: name 'Mix' is not defined In [107]: Student.objects.aggregate(agg__min=Min('age')) Out[107]: {'agg__min': 0} In [108]: Student.objects.aggregate(agg__sum=Sum('age')) Out[108]: {'agg__sum': 0}
- 分组，聚合
结合 Values,annotate 和聚合方法一起实现
查询男生有几个，女生有几个
In [109]: from django.db.models import Count In [110]: Student.objects.values('sex').annotate(num=Count('sex')) Out[110]: <QuerySet [{'sex': 1, 'num': 6}]> In [111]: res = Student.objects.values('sex').annotate(Count('sex')) In [112]: print(res.query) SELECT `teacher_student`.`sex`, COUNT(`teacher_student`.`sex`) AS `sex__count` FROM `teacher_student` GROUP BY `teacher_student`.`sex` ORDER BY NULL
