二 08 django模型系统 _django

1.常用模型字段类型及字段参数
#field-types
常用的字段类型：
1. IntegerField : 整型，映射到数据库中的int类型。2. CharField: 字符类型，映射到数据库中的varchar类型，通过max_length指定最大长度。3. TextField: 文本类型，映射到数据库中的text类型。4. BooleanField: 布尔类型，映射到数据库中的tinyint类型，在使用的时候，传递True/False进去。如果要可以为空，则用NullBooleanField 。5. DateField: 日期类型，没有时间。映射到数据库中是date类型，在使用的时候，可以设置DateField.auto_now每次保存对象时，自动设置该字段为当前时间。设置DateField.auto_now_add当对象第一次被创建时自动设置当前时间。6. DateTimeField: 日期时间类型。映射到数据库中的是datetime类型，在使用的时候，传递datetime.datetime()进去。.
7. AutoField :Int 类型，用于自定义主键的创建，并且具有自增长的属性，再加上primary=True 。就是主键。一般情况下不会自定义，系统会自动创建主键

文章插图
2.field的常用参数
官方文档：#field-
primary_key:指定是否为主键。unique:指定是否唯一。null:指定是否为空，默认为False 。blank: 等于True时form表单验证时可以为空，默认为False 。default:设置默认值。DateField.auto_now:每次修改都会将当前时间更新进去，只有调用， QuerySet.update方法将不会调用。这个参数只是Date和DateTime以及TimModel.save()方法才会调用e类才有的。DateField.auto_now_add:第一次添加进去，都会将当前时间设置进去。以后修改，不会修改这个值
常用查询（一）
通过模型类上的管理器来构造。
- 模型类上的管理器是啥？
模型类.
-表示数据库中对象的集合。通过过滤器查找的结果和范围
等同于语句。惰性的
案例：
获取单个（）
- first() 获取第一条返回的是对象，立即返回
Student.objects.first()
- last() 获取最后一条返回的也是一个对象
res = Student.objects.last()
思考：排序规则？默认通过主键。通过模型中的_meta 设置
- get(**) 根据给定的条件，获取一个对象，如果有多个对象符合，报错
Student.objects.get(name='litao')
- all() 获取所有记录返回的是
Student.objects.create(name='蒸发贵')In [10]: res = Student.objects.all()In [11]: 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`, `teacher_student`.`e_time` FROM `teacher_student`
- (**) 根据给定的条件，获取一个过滤后的，多个条件和sql使用and连接一样。
In [27]: Student.objects.filter(age=0,sex=1) Out[27]: In [28]: res = Student.objects.filter(age=0,sex=1)In [29]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE (`teacher_student`.`age` = 0 AND `teacher_student`.`sex` = 1)
- (**) 跟使用方法一致，作用相反，它是排除。可以传递多个参数，使用and连接
In [30]: res = Student.objects.exclude(sex=0)In [31]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE NOT (`teacher_student`.`sex` = 0)#多个参数：In [32]: res = Student.objects.exclude(sex=0,age=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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE NOT (`teacher_student`.`age` = 1 AND `teacher_student`.`sex` = 0)
常用查询（二）
- 多条件的OR连接用到Q对象，.db..Q
二 08 django模型系统

文章插图
In [34]: from django.db.models import QIn [35]: res = Student.objects.filter(Q(age=0)|Q(age=1)) #查询age=1或者age=0的学生In [36]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE (`teacher_student`.`age` = 0 OR `teacher_student`.`age` = 1)
- (*) 返回一个，返回一个字典列表，而不是数据对象。（指定限制字段的作用）可以写多个参数字段，还可以加入field过滤条件
In [38]: res = Student.objects.values('name')
In [39]: print(res.query) SELECT `teacher_student`.`name` FROM `teacher_student`In [40]: res Out[40]: In [41]: res[0] Out[41]: {'name': '心蓝'}In [42]: res[0]['name'] Out[42]: '心蓝'In [45]: res = Student.objects.values('name','age').filter(age=0)In [46]: print(res.query) SELECT `teacher_student`.`name`, `teacher_student`.`age` FROM `teacher_student` WHERE `teacher_student`.`age` = 0
- only(*fiels) 和value差不多，但是返回，对象列表，注意only一定包含主键字段
In [48]: res = Student.objects.only('name','age').filter(age=0)In [49]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age` FROM `teacher_student` WHERE `teacher_student`.`age` = 0In [50]: res[0] Out[50]: In [51]: res[0].sex Out[51]: 1
- defer(*) 与only用法一样，返回一个，作用和only相反。指定排除中的字段
In [53]: res = Student.objects.defer('c_time','e_time').filter(age=0)In [54]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone` FROM `teacher_student` WHERE `teacher_student`.`age` = 0In [55]: res[0] Out[55]: In [56]: res[0].id Out[56]: 1
- (*) 根据给定的字段来排序默认是顺序，字段名前加上 ‘-’代表反序。中可以有多个条件。条件顺序无所谓，与only顺序无所谓。
In [60]: res = Student.objects.order_by('c_time').only('name') In [62]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name` FROM `teacher_student` ORDER BY `teacher_student`.`c_time` ASCIn [63]: res = Student.objects.order_by('-c_time').only('name')In [64]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name` FROM `teacher_student` ORDER BY `teacher_student`.`c_time` DESC
2. asc desc 的使用场景
In [1]: from teacher.models import StudentIn [2]: Student.objects.all() Out[2]: In [3]: Student.objects.order_by('name') Out[3]:
2.1.实例：按照名字的大写进行排序
In [4]: from django.db.models.functions import LowerIn [5]: res = Student.objects.order_by(Lower('name'))In [6]: 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`, `teacher_student`.`e_time` FROM `teacher_student` ORDER BY LOWER(`teacher_student`.`name`) ASC
2.2 按照名字的大写进行排序，倒叙：
In [7]: res = Student.objects.order_by(Lower('name').desc())In [8]: 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`, `teacher_student`.`e_time` FROM `teacher_student` ORDER BY LOWER(`teacher_student`.`name`) DESC
- 切片和的列表切片用法相似，不支持负索引，数据量大时不用步长
*** 切片过后，不再支持，附加过滤条件与排序
In [68]: res = Student.objects.all()[:5]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`, `teacher_student`.`e_time` FROM `teacher_student` LIMIT 5In [70]: res = Student.objects.all()[2:3]In [71]: 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`, `teacher_student`.`e_time` FROM `teacher_student` LIMIT 1 OFFSET 2
常用查询条件
- 支持，， get
- exact：准确，使用两个下划线（__）
In [79]: Student.objects.get(id__exact=1) Out[79]: In [80]: Student.objects.filter(id__exact=1) Out[80]: In [81]: res = Student.objects.filter(id__exact=1)In [82]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`id` = 1In [83]: res = Student.objects.filter(id=1)In [84]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`id` = 1
- ：不区分大小写
In [85]: res = Student.objects.filter(name = 'LiTao')In [86]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` = LiTaoIn [87]: res = Student.objects.filter(name__iexact = 'LiTao')In [88]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE LiTao
- ：包含(sql语句中有为区分大小写)
In [89]: res = Student.objects.filter(name__contains = 'LiTao')
In [90]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE BINARY %LiTao%
- ：不区分大小写，包含
In [91]: res = Student.objects.filter(name__icontains = 'LiTao')In [92]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE %LiTao%
In [93]: res
Out[93]:
- in：给定一个可迭代对象，列表，元组，
In [96]: res = Student.objects.filter(name__in=['心蓝','litao','aaaa'])In [97]: res Out[97]: In [98]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` IN (心蓝, litao, aaaa)
- range：范围
In [99]: res = Student.objects.filter(age__range=[18,20])In [100]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`age` BETWEEN 18 AND 20
- gt：大于
In [101]: res = Student.objects.filter(age__gt=10)In [102]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`age` > 10
- gte：大于等于
In [103]: res = Student.objects.filter(age__gte=10)In [104]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`age` >= 10
- lt：小于
In [105]: res = Student.objects.filter(age__lt=10)In [106]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`age` < 10
- lte：小于等于
In [107]: res = Student.objects.filter(age__lte=10)In [108]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`age` <= 10
-：大小写敏感，以什么开头
In [109]: res = Student.objects.filter(name__startswith = '新')In [110]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE BINARY 新%
- ：大小写不敏感，以什么开头
In [112]: res = Student.objects.filter(name__istartswith = 'L')In [113]: res Out[113]: In [114]: 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`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE L%
-
-
-True False 对应 IS NULL IS NOT NULL
In [116]: res = Student.objects.filter(qq__isnull=True)In [117]: res Out[117]:
- 聚合
from django.db.models import Count, Avg, Max, Min, Sum#导入统计数量，平均值，最大值，最小值，求和
count 为Count的快捷方式
In [121]: Student.objects.all().count() Out[121]: 5In [122]: Student.objects.filter(name__isnull=True).count() Out[122]: 0In [123]: Student.objects.filter(name__isnull=False).count() Out[123]: 5
通过的方法
..(=Avg('age')) # 计算平均年龄
In [126]: Student.objects.aggregate(age_avg=Avg('age'))#avg_age为自定义的字段名，为字典的keyOut[126]: {'age_avg': 0.0}In [129]: Student.objects.filter(age__gte=0).aggregate(age_avg=Avg('age')) Out[129]: {'age_avg': 0.0}In [130]: Student.objects.filter(age__gte=0).aggregate(age_Max=Max('age')) Out[130]: {'age_Max': 0}In [131]: Student.objects.filter(age__gte=0).aggregate(age_Sum=Sum('age')) Out[131]: {'age_Sum': 0}
- count
- 平均值 Avg
- 分组，聚合
结合 , 和聚合方法一起实现
查询男生有几个，女生有几个
【二08 django模型系统】In [137]: Student.objects.values('sex').annotate(num=Count('sex')) #num为聚合后的字段名称Out[137]: In [138]: res= Student.objects.values('sex').annotate(num=Count('sex'))In [139]: print(res.query)SELECT `teacher_student`.`sex`, COUNT(`teacher_student`.`sex`) AS `num` FROM `teacher_student` GROUP BY `teacher_student`.`sex` ORDER BY NULL