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) 根据给定的字段来排序 默认是顺序，字段名前加上 ‘-’代表反序

In [51]: res = Student.objects.order_by('c_time')                                                          

In [52]: res = Student.objects.order_by('c_time').only('name')                                             

In [53]: print(res.query)                                                                                  
SELECT `teacher_student`.`id`, `teacher_student`.`name` FROM `teacher_student` ORDER BY `teacher_student`.`c_time` ASC

In [54]: res = Student.objects.order_by('-c_time').only('name')                                            

In [55]: print(res.query)                                                                                  
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