作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.查询集
1>.查询集相关概述
查询会返回结果的集，它是django.db.models.query.QuerySet类型。 它是惰性求值，和SQLAlchemy一样。结果就是查询的集。 它是可迭代对象。
惰性求值:
创建查询集不会带来任何数据库的访问，直到调用方法使用数据时，才会访问数据库。在迭代、序列 化、if语句中都会立即求值。
缓存:
每一个查询集都包含一个缓存，来最小化对数据库的访问。 新建查询集，缓存为空。首次对查询集求值时，会发生数据库查询，Django会把查询的结果存在这个缓 存中，并返回请求的结果，接下来对查询集求值将使用缓存的结果。
2>.案例一(没有使用缓存，每次都要去查库，查了2次库)
#！/usr/bin/env python
#_*_conding:utf-8_*_
#@author :yinzhengjie
#blog:http://www.cnblogs.com/yinzhengjie import os
import django #参考salary/wsgi.py文件
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False) #导入employee应用的models模块中定义的Employee类
from employee.models import Employee #没有使用缓存，而是每次都要去查库，查了2次
[ emp.first_name for emp in Employee.objects.all()]
[ emp.first_name for emp in Employee.objects.all()]
(0.001) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees`; args=()
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees`; args=()
以上代码执行结果戳这里
3>.案例二(下面的语句使用缓存，因为使用同一个结果集)
#！/usr/bin/env python
#_*_conding:utf-8_*_
#@author :yinzhengjie
#blog:http://www.cnblogs.com/yinzhengjie import os
import django #参考salary/wsgi.py文件
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False) #导入employee应用的models模块中定义的Employee类
from employee.models import Employee #返回一个结果集，本句不发起查询
emps = Employee.objects.all() #下面的语句使用缓存，因为使用同一个结果集
[emp.first_name for emp in emps]
[emp.first_name for emp in emps]
[emp.first_name for emp in emps]
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees`; args=()
以上代码执行结果戳这里
二.限制查询集(切片)
#！/usr/bin/env python
#_*_conding:utf-8_*_
#@author :yinzhengjie
#blog:http://www.cnblogs.com/yinzhengjie import os
import django #参考salary/wsgi.py文件
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False) #导入employee应用的models模块中定义的Employee类
from employee.models import Employee """
分页功能实现，使用限制查询集。 查询集对象可以直接使用索引下标的方式(不支持负索引)，相当于SQL语句中的limit和offset子句。 注意使用索引返回的新的结果集，依然是惰性求值，不会立即查询。
"""
emps = Employee.objects.all()[3:8]
print(*list(emps),sep="\n") print("{0} 我是分割线 {0}".format("*" * 15)) emps = Employee.objects.all()[10:18]
print(*list(emps),sep="\n")
<Employee: 10004 Chirstian Koblick>
<Employee: 10005 Kyoichi Maliniak>
<Employee: 10006 Anneke Preusig>
<Employee: 10007 Tzvetan Zielinski>
<Employee: 10008 Saniya Kalloufi>
*************** 我是分割线 ***************
<Employee: 10011 Mary Sluis>
<Employee: 10012 Patricio Bridgland>
<Employee: 10013 Eberhardt Terkki>
<Employee: 10014 Berni Genin>
<Employee: 10015 Guoxiang Nooteboom>
<Employee: 10016 Kazuhito Cappelletti>
<Employee: 10017 Cristinel Bouloucos>
<Employee: 10018 Kazuhide Peha>
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` LIMIT 5 OFFSET 3; args=()
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` LIMIT 8 OFFSET 10; args=() Process finished with exit code 0
以上代码执行结果戳这里
三.结果集方法
#！/usr/bin/env python
#_*_conding:utf-8_*_
#@author :yinzhengjie
#blog:http://www.cnblogs.com/yinzhengjie import os
import django #参考salary/wsgi.py文件
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False) #导入employee应用的models模块中定义的Employee类
from employee.models import Employee emps = Employee.objects """
all():
返回一个结果集
"""
print(emps.all()) """
values():
返回一个对象字典的列表，列表的元素是字典，字典内是字段和值的键值对
"""
print(emps.values()) """
filter():
过滤，返回满足条件的数据
filter(k1=v1).filter(k2=v2) 等价于 filter(k1=v1, k2=v2)
filter(pk=10010)这里pk指的就是主键， 不用关心主键字段名，当然也可以使用使用主键名filter(emp_no=10010)
"""
print(emps.filter(pk=10010).values) """
exclude():
排除，排除满足条件的数据
"""
print(emps.exclude(emp_no=10010)) """
order_by():
排序，注意参数是字符串
"""
print(emps.exclude(emp_no=10012).order_by("emp_no"))
print(emps.exclude(emp_no=10012).order_by("-pk"))
print(emps.exclude(emp_no=10012).order_by("-pk").values)
<QuerySet [<Employee: 10001 Georgi Facello>, <Employee: 10002 Bezalel Simmel>, <Employee: 10003 Parto Bamford>, <Employee: 10004 Chirstian Koblick>, <Employee: 10005 Kyoichi Maliniak>, <Employee: 10006 Anneke Preusig>, <Employee: 10007 Tzvetan Zielinski>, <Employee: 10008 Saniya Kalloufi>, <Employee: 10009 Sumant Peac>, <Employee: 10010 Duangkaew Piveteau>, <Employee: 10011 Mary Sluis>, <Employee: 10012 Patricio Bridgland>, <Employee: 10013 Eberhardt Terkki>, <Employee: 10014 Berni Genin>, <Employee: 10015 Guoxiang Nooteboom>, <Employee: 10016 Kazuhito Cappelletti>, <Employee: 10017 Cristinel Bouloucos>, <Employee: 10018 Kazuhide Peha>, <Employee: 10019 Lillian Haddadi>, <Employee: 10020 Mayuko Warwick>]>
<QuerySet [{'emp_no': 10001, 'birth_date': datetime.date(1953, 9, 2), 'first_name': 'Georgi', 'last_name': 'Facello', 'gender': 'M', 'hire_date': datetime.date(1986, 6, 26)}, {'emp_no': 10002, 'birth_date': datetime.date(1964, 6, 2), 'first_name': 'Bezalel', 'last_name': 'Simmel', 'gender': 'F', 'hire_date': datetime.date(1985, 11, 21)}, {'emp_no': 10003, 'birth_date': datetime.date(1959, 12, 3), 'first_name': 'Parto', 'last_name': 'Bamford', 'gender': 'M', 'hire_date': datetime.date(1986, 8, 28)}, {'emp_no': 10004, 'birth_date': datetime.date(1954, 5, 1), 'first_name': 'Chirstian', 'last_name': 'Koblick', 'gender': 'M', 'hire_date': datetime.date(1986, 12, 1)}, {'emp_no': 10005, 'birth_date': datetime.date(1955, 1, 21), 'first_name': 'Kyoichi', 'last_name': 'Maliniak', 'gender': 'M', 'hire_date': datetime.date(1989, 9, 12)}, {'emp_no': 10006, 'birth_date': datetime.date(1953, 4, 20), 'first_name': 'Anneke', 'last_name': 'Preusig', 'gender': 'F', 'hire_date': datetime.date(1989, 6, 2)}, {'emp_no': 10007, 'birth_date': datetime.date(1957, 5, 23), 'first_name': 'Tzvetan', 'last_name': 'Zielinski', 'gender': 'F', 'hire_date': datetime.date(1989, 2, 10)}, {'emp_no': 10008, 'birth_date': datetime.date(1958, 2, 19), 'first_name': 'Saniya', 'last_name': 'Kalloufi', 'gender': 'M', 'hire_date': datetime.date(1994, 9, 15)}, {'emp_no': 10009, 'birth_date': datetime.date(1952, 4, 19), 'first_name': 'Sumant', 'last_name': 'Peac', 'gender': 'F', 'hire_date': datetime.date(1985, 2, 18)}, {'emp_no': 10010, 'birth_date': datetime.date(1963, 6, 1), 'first_name': 'Duangkaew', 'last_name': 'Piveteau', 'gender': 'F', 'hire_date': datetime.date(1989, 8, 24)}, {'emp_no': 10011, 'birth_date': datetime.date(1953, 11, 7), 'first_name': 'Mary', 'last_name': 'Sluis', 'gender': 'F', 'hire_date': datetime.date(1990, 1, 22)}, {'emp_no': 10012, 'birth_date': datetime.date(1960, 10, 4), 'first_name': 'Patricio', 'last_name': 'Bridgland', 'gender': 'M', 'hire_date': datetime.date(1992, 12, 18)}, {'emp_no': 10013, 'birth_date': datetime.date(1963, 6, 7), 'first_name': 'Eberhardt', 'last_name': 'Terkki', 'gender': 'M', 'hire_date': datetime.date(1985, 10, 20)}, {'emp_no': 10014, 'birth_date': datetime.date(1956, 2, 12), 'first_name': 'Berni', 'last_name': 'Genin', 'gender': 'M', 'hire_date': datetime.date(1987, 3, 11)}, {'emp_no': 10015, 'birth_date': datetime.date(1959, 8, 19), 'first_name': 'Guoxiang', 'last_name': 'Nooteboom', 'gender': 'M', 'hire_date': datetime.date(1987, 7, 2)}, {'emp_no': 10016, 'birth_date': datetime.date(1961, 5, 2), 'first_name': 'Kazuhito', 'last_name': 'Cappelletti', 'gender': 'M', 'hire_date': datetime.date(1995, 1, 27)}, {'emp_no': 10017, 'birth_date': datetime.date(1958, 7, 6), 'first_name': 'Cristinel', 'last_name': 'Bouloucos', 'gender': 'F', 'hire_date': datetime.date(1993, 8, 3)}, {'emp_no': 10018, 'birth_date': datetime.date(1954, 6, 19), 'first_name': 'Kazuhide', 'last_name': 'Peha', 'gender': 'F', 'hire_date': datetime.date(1987, 4, 3)}, {'emp_no': 10019, 'birth_date': datetime.date(1953, 1, 23), 'first_name': 'Lillian', 'last_name': 'Haddadi', 'gender': 'M', 'hire_date': datetime.date(1999, 4, 30)}, {'emp_no': 10020, 'birth_date': datetime.date(1952, 12, 24), 'first_name': 'Mayuko', 'last_name': 'Warwick', 'gender': 'M', 'hire_date': datetime.date(1991, 1, 26)}]>
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` LIMIT 21; args=()
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` LIMIT 21; args=()
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE `employees`.`emp_no` = 10010 LIMIT 21; args=(10010,)
<bound method QuerySet.values of <QuerySet [<Employee: 10010 Duangkaew Piveteau>]>>
<QuerySet [<Employee: 10001 Georgi Facello>, <Employee: 10002 Bezalel Simmel>, <Employee: 10003 Parto Bamford>, <Employee: 10004 Chirstian Koblick>, <Employee: 10005 Kyoichi Maliniak>, <Employee: 10006 Anneke Preusig>, <Employee: 10007 Tzvetan Zielinski>, <Employee: 10008 Saniya Kalloufi>, <Employee: 10009 Sumant Peac>, <Employee: 10011 Mary Sluis>, <Employee: 10012 Patricio Bridgland>, <Employee: 10013 Eberhardt Terkki>, <Employee: 10014 Berni Genin>, <Employee: 10015 Guoxiang Nooteboom>, <Employee: 10016 Kazuhito Cappelletti>, <Employee: 10017 Cristinel Bouloucos>, <Employee: 10018 Kazuhide Peha>, <Employee: 10019 Lillian Haddadi>, <Employee: 10020 Mayuko Warwick>]>
<QuerySet [<Employee: 10001 Georgi Facello>, <Employee: 10002 Bezalel Simmel>, <Employee: 10003 Parto Bamford>, <Employee: 10004 Chirstian Koblick>, <Employee: 10005 Kyoichi Maliniak>, <Employee: 10006 Anneke Preusig>, <Employee: 10007 Tzvetan Zielinski>, <Employee: 10008 Saniya Kalloufi>, <Employee: 10009 Sumant Peac>, <Employee: 10010 Duangkaew Piveteau>, <Employee: 10011 Mary Sluis>, <Employee: 10013 Eberhardt Terkki>, <Employee: 10014 Berni Genin>, <Employee: 10015 Guoxiang Nooteboom>, <Employee: 10016 Kazuhito Cappelletti>, <Employee: 10017 Cristinel Bouloucos>, <Employee: 10018 Kazuhide Peha>, <Employee: 10019 Lillian Haddadi>, <Employee: 10020 Mayuko Warwick>]>
<QuerySet [<Employee: 10020 Mayuko Warwick>, <Employee: 10019 Lillian Haddadi>, <Employee: 10018 Kazuhide Peha>, <Employee: 10017 Cristinel Bouloucos>, <Employee: 10016 Kazuhito Cappelletti>, <Employee: 10015 Guoxiang Nooteboom>, <Employee: 10014 Berni Genin>, <Employee: 10013 Eberhardt Terkki>, <Employee: 10011 Mary Sluis>, <Employee: 10010 Duangkaew Piveteau>, <Employee: 10009 Sumant Peac>, <Employee: 10008 Saniya Kalloufi>, <Employee: 10007 Tzvetan Zielinski>, <Employee: 10006 Anneke Preusig>, <Employee: 10005 Kyoichi Maliniak>, <Employee: 10004 Chirstian Koblick>, <Employee: 10003 Parto Bamford>, <Employee: 10002 Bezalel Simmel>, <Employee: 10001 Georgi Facello>]>
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE NOT (`employees`.`emp_no` = 10010) LIMIT 21; args=(10010,)
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE NOT (`employees`.`emp_no` = 10012) ORDER BY `employees`.`emp_no` ASC LIMIT 21; args=(10012,)
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE NOT (`employees`.`emp_no` = 10012) ORDER BY `employees`.`emp_no` DESC LIMIT 21; args=(10012,)
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE NOT (`employees`.`emp_no` = 10012) ORDER BY `employees`.`emp_no` DESC LIMIT 21; args=(10012,)
<bound method QuerySet.values of <QuerySet [<Employee: 10020 Mayuko Warwick>, <Employee: 10019 Lillian Haddadi>, <Employee: 10018 Kazuhide Peha>, <Employee: 10017 Cristinel Bouloucos>, <Employee: 10016 Kazuhito Cappelletti>, <Employee: 10015 Guoxiang Nooteboom>, <Employee: 10014 Berni Genin>, <Employee: 10013 Eberhardt Terkki>, <Employee: 10011 Mary Sluis>, <Employee: 10010 Duangkaew Piveteau>, <Employee: 10009 Sumant Peac>, <Employee: 10008 Saniya Kalloufi>, <Employee: 10007 Tzvetan Zielinski>, <Employee: 10006 Anneke Preusig>, <Employee: 10005 Kyoichi Maliniak>, <Employee: 10004 Chirstian Koblick>, <Employee: 10003 Parto Bamford>, <Employee: 10002 Bezalel Simmel>, <Employee: 10001 Georgi Facello>]>>
以上代码执行结果戳这里
四.返回单个值的方法
#！/usr/bin/env python
#_*_conding:utf-8_*_
#@author :yinzhengjie
#blog:http://www.cnblogs.com/yinzhengjie import os
import django #参考salary/wsgi.py文件
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False) #导入employee应用的models模块中定义的Employee类
from employee.models import Employee emps = Employee.objects """
get():
仅返回单个满足条件的对象
如果未能返回对象则抛出DoesNotExist异常;
如果能返回多条，抛出MultipleObjectsReturned异常
"""
print(emps.filter(pk=10010).get())
print(emps.get(pk=10010))
# print(emps.exclude(pk=10010).get()) """
first():
返回第一个对象
"""
print(emps.first())
print(emps.filter(pk=10010,gender=1).first()) """
last():
返回最后一个对象
"""
print(emps.exclude(pk=10010).last()) """
count():
返回当前查询的总条数
"""
print(emps.count())
print(emps.exclude(pk=10010).count()) """
exists():
判断查询集中是否有数据，如果有则返回True
"""
print(emps.exists())
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE `employees`.`emp_no` = 10010; args=(10010,)
<Employee: 10010 Duangkaew Piveteau>
<Employee: 10010 Duangkaew Piveteau>
<Employee: 10001 Georgi Facello>
None
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE `employees`.`emp_no` = 10010; args=(10010,)
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` ORDER BY `employees`.`emp_no` ASC LIMIT 1; args=()
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE (`employees`.`gender` = 1 AND `employees`.`emp_no` = 10010) ORDER BY `employees`.`emp_no` ASC LIMIT 1; args=(1, 10010)
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE NOT (`employees`.`emp_no` = 10010) ORDER BY `employees`.`emp_no` DESC LIMIT 1; args=(10010,)
(0.001) SELECT COUNT(*) AS `__count` FROM `employees`; args=()
<Employee: 10020 Mayuko Warwick>
20
19
True
(0.001) SELECT COUNT(*) AS `__count` FROM `employees` WHERE NOT (`employees`.`emp_no` = 10010); args=(10010,)
(0.001) SELECT (1) AS `a` FROM `employees` LIMIT 1; args=()
以上代码执行结果戳这里
五.字段查询(Field Lookup)表达式
1>.常用的比较运算符
字段查询表达式可以作为filter()、exclude()、get()的参数，实现where子句。
语法如下:
属性名称__比较运算符=值(注意:属性名和运算符之间使用双下划线)
比较运算符如下:
exact:
严格等于，可省略不写。
举例:
filter(isdeleted=False)
filter(isdeleted__exact=False) contains:
是否包含，大小写铭感，等价于like '%天'
举例:
exclude(title__contains='天') startswith,endswith:
以什么开头或者结尾，大小写铭感:
举例:
filter(title__startswith='天') isnull,isnotnull:
是否为null。
举例:
filter(title__isnull=False) iexact,icontains,istartswith,iendswith:
i的意思是忽略大小写 in:
是否在指定范围数据中
举例:
filter(pk__in=[1,2,3,100]) gt,gte,lt,lte
大于，大于等于，小于，小于等于
举例:
filter(id__gt=3)
filter(pub_date__gt=date(2000,1,1))
filter(pk__lte=6) year,month,day,week_day,hour,minute,second:
对日期类型属性处理
举例:
filter(pub_date__year=2000)
2>.比较运算符使用案例
#！/usr/bin/env python
#_*_conding:utf-8_*_
#@author :yinzhengjie
#blog:http://www.cnblogs.com/yinzhengjie import os
import django #参考salary/wsgi.py文件
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False) #导入employee应用的models模块中定义的Employee类
from employee.models import Employee emps = Employee.objects print(emps.filter(emp_no__exact=10010))
print(emps.filter(pk__in=[10010,10009]))
print(emps.filter(last_name__startswith="P"))
print(emps.exclude(pk__gt=10003))
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE `employees`.`emp_no` = 10010 LIMIT 21; args=(10010,)
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE `employees`.`emp_no` IN (10010, 10009) LIMIT 21; args=(10010, 10009)
<QuerySet [<Employee: 10010 Duangkaew Piveteau>]>
<QuerySet [<Employee: 10009 Sumant Peac>, <Employee: 10010 Duangkaew Piveteau>]>
<QuerySet [<Employee: 10006 Anneke Preusig>, <Employee: 10009 Sumant Peac>, <Employee: 10010 Duangkaew Piveteau>, <Employee: 10018 Kazuhide Peha>]>
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE `employees`.`last_name` LIKE BINARY 'P%' LIMIT 21; args=('P%',)
<QuerySet [<Employee: 10001 Georgi Facello>, <Employee: 10002 Bezalel Simmel>, <Employee: 10003 Parto Bamford>]>
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE NOT (`employees`.`emp_no` > 10003) LIMIT 21; args=(10003,)
以上代码执行结果戳这里
六.Q对象
#！/usr/bin/env python
#_*_conding:utf-8_*_
#@author :yinzhengjie
#blog:http://www.cnblogs.com/yinzhengjie import os
import django
from django.db.models import Q
#参考salary/wsgi.py文件
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False) #导入employee应用的models模块中定义的Employee类
from employee.models import Employee emps = Employee.objects """
虽然Django提供传入条件的方式，但是不方便，它还提供了Q对象来解决。 Q对象是django.db.models.Q，可以使用&、|操作符来组成逻辑表达式。 ~ 表示not。 可使用&|和Q对象来构造复杂的逻辑表达式，可以使用一个或多个Q对象。 如果混用关键字参数和Q对象，那么Q对象必须位于关键字参数的前面。
""" #下面两句效果一样
print(emps.filter(Q(pk__lt=10006)))
print(emps.filter(pk__lt=10006)) #下面三句效果一样,都是在表达"与"这个逻辑效果
print(emps.filter(pk__gt=10003).filter(pk__lt=10006))
print(emps.filter(pk__gt=10003,pk__lt=10006))
print(emps.filter(Q(pk__gt=10003) & Q(pk__lt=10006))) #下面两句等价,都是在表达"或"这个逻辑效果
print(emps.filter(pk__in=[10003,10006]))
print(emps.filter(Q(pk=10003) | Q(pk=10006))) #表达"非"这个逻辑效果
print(emps.filter(~Q(pk__gt=10003)))
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE `employees`.`emp_no` < 10006 LIMIT 21; args=(10006,)
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE `employees`.`emp_no` < 10006 LIMIT 21; args=(10006,)
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE (`employees`.`emp_no` > 10003 AND `employees`.`emp_no` < 10006) LIMIT 21; args=(10003, 10006)
<QuerySet [<Employee: 10001 Georgi Facello>, <Employee: 10002 Bezalel Simmel>, <Employee: 10003 Parto Bamford>, <Employee: 10004 Chirstian Koblick>, <Employee: 10005 Kyoichi Maliniak>]>
<QuerySet [<Employee: 10001 Georgi Facello>, <Employee: 10002 Bezalel Simmel>, <Employee: 10003 Parto Bamford>, <Employee: 10004 Chirstian Koblick>, <Employee: 10005 Kyoichi Maliniak>]>
<QuerySet [<Employee: 10004 Chirstian Koblick>, <Employee: 10005 Kyoichi Maliniak>]>
<QuerySet [<Employee: 10004 Chirstian Koblick>, <Employee: 10005 Kyoichi Maliniak>]>
<QuerySet [<Employee: 10004 Chirstian Koblick>, <Employee: 10005 Kyoichi Maliniak>]>
<QuerySet [<Employee: 10003 Parto Bamford>, <Employee: 10006 Anneke Preusig>]>
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE (`employees`.`emp_no` > 10003 AND `employees`.`emp_no` < 10006) LIMIT 21; args=(10003, 10006)
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE (`employees`.`emp_no` > 10003 AND `employees`.`emp_no` < 10006) LIMIT 21; args=(10003, 10006)
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE `employees`.`emp_no` IN (10003, 10006) LIMIT 21; args=(10003, 10006)
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE (`employees`.`emp_no` = 10003 OR `employees`.`emp_no` = 10006) LIMIT 21; args=(10003, 10006)
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` WHERE NOT (`employees`.`emp_no` > 10003) LIMIT 21; args=(10003,)
<QuerySet [<Employee: 10003 Parto Bamford>, <Employee: 10006 Anneke Preusig>]>
<QuerySet [<Employee: 10001 Georgi Facello>, <Employee: 10002 Bezalel Simmel>, <Employee: 10003 Parto Bamford>]>
以上代码执行结果戳这里
七.聚合，分组
1>.aggregate() 返回字典，方便使用
#！/usr/bin/env python
#_*_conding:utf-8_*_
#@author :yinzhengjie
#blog:http://www.cnblogs.com/yinzhengjie import os
import django
from django.db.models import Avg,Sum,Max,Min,Count
#参考salary/wsgi.py文件
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False) #导入employee应用的models模块中定义的Employee类
from employee.models import Employee emps = Employee.objects print(emps.filter(pk__gt=10010).count()) #单值
print(emps.filter(pk__gt=10010).aggregate(Count("pk"))) #字典
print(emps.filter(pk__lte=10010).aggregate(Avg("pk")))
print(emps.aggregate(Max("pk"),min=Min("pk"))) #起别名
10
{'pk__count': 10}
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT COUNT(*) AS `__count` FROM `employees` WHERE `employees`.`emp_no` > 10010; args=(10010,)
(0.001) SELECT COUNT(`employees`.`emp_no`) AS `pk__count` FROM `employees` WHERE `employees`.`emp_no` > 10010; args=(10010,)
{'pk__avg': 10005.5}
(0.001) SELECT AVG(`employees`.`emp_no`) AS `pk__avg` FROM `employees` WHERE `employees`.`emp_no` <= 10010; args=(10010,)
{'min': 10001, 'pk__max': 10020}
(0.000) SELECT MIN(`employees`.`emp_no`) AS `min`, MAX(`employees`.`emp_no`) AS `pk__max` FROM `employees`; args=()
以上代码执行结果戳这里
2>. annotate()方法用来分组聚合，返回查询集
#！/usr/bin/env python
#_*_conding:utf-8_*_
#@author :yinzhengjie
#blog:http://www.cnblogs.com/yinzhengjie import os
import django
from django.db.models import Avg,Sum,Max,Min,Count
#参考salary/wsgi.py文件
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False) #导入employee应用的models模块中定义的Employee类
from employee.models import Employee emps = Employee.objects print(emps.filter(pk__gt=10010).aggregate(Count("pk"))) #字典
res = emps.filter(pk__gt=10010).annotate(Count("pk")) #返回查询集，没指定使用主键分组 for i in res:
print(i)
print(i.__dict__) #里面多放了了一个属性pk__count
{'pk__count': 10}
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT COUNT(`employees`.`emp_no`) AS `pk__count` FROM `employees` WHERE `employees`.`emp_no` > 10010; args=(10010,)
(0.001) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date`, COUNT(`employees`.`emp_no`) AS `pk__count` FROM `employees` WHERE `employees`.`emp_no` > 10010 GROUP BY `employees`.`emp_no` ORDER BY NULL; args=(10010,)
<Employee: 10011 Mary Sluis>
{'_state': <django.db.models.base.ModelState object at 0x104647550>, 'emp_no': 10011, 'birth_date': datetime.date(1953, 11, 7), 'first_name': 'Mary', 'last_name': 'Sluis', 'gender': 'F', 'hire_date': datetime.date(1990, 1, 22), 'pk__count': 1}
<Employee: 10012 Patricio Bridgland>
{'_state': <django.db.models.base.ModelState object at 0x1046475f8>, 'emp_no': 10012, 'birth_date': datetime.date(1960, 10, 4), 'first_name': 'Patricio', 'last_name': 'Bridgland', 'gender': 'M', 'hire_date': datetime.date(1992, 12, 18), 'pk__count': 1}
<Employee: 10013 Eberhardt Terkki>
{'_state': <django.db.models.base.ModelState object at 0x1046475c0>, 'emp_no': 10013, 'birth_date': datetime.date(1963, 6, 7), 'first_name': 'Eberhardt', 'last_name': 'Terkki', 'gender': 'M', 'hire_date': datetime.date(1985, 10, 20), 'pk__count': 1}
<Employee: 10014 Berni Genin>
{'_state': <django.db.models.base.ModelState object at 0x104647668>, 'emp_no': 10014, 'birth_date': datetime.date(1956, 2, 12), 'first_name': 'Berni', 'last_name': 'Genin', 'gender': 'M', 'hire_date': datetime.date(1987, 3, 11), 'pk__count': 1}
<Employee: 10015 Guoxiang Nooteboom>
{'_state': <django.db.models.base.ModelState object at 0x104647710>, 'emp_no': 10015, 'birth_date': datetime.date(1959, 8, 19), 'first_name': 'Guoxiang', 'last_name': 'Nooteboom', 'gender': 'M', 'hire_date': datetime.date(1987, 7, 2), 'pk__count': 1}
<Employee: 10016 Kazuhito Cappelletti>
{'_state': <django.db.models.base.ModelState object at 0x1046470f0>, 'emp_no': 10016, 'birth_date': datetime.date(1961, 5, 2), 'first_name': 'Kazuhito', 'last_name': 'Cappelletti', 'gender': 'M', 'hire_date': datetime.date(1995, 1, 27), 'pk__count': 1}
<Employee: 10017 Cristinel Bouloucos>
{'_state': <django.db.models.base.ModelState object at 0x104647780>, 'emp_no': 10017, 'birth_date': datetime.date(1958, 7, 6), 'first_name': 'Cristinel', 'last_name': 'Bouloucos', 'gender': 'F', 'hire_date': datetime.date(1993, 8, 3), 'pk__count': 1}
<Employee: 10018 Kazuhide Peha>
{'_state': <django.db.models.base.ModelState object at 0x1046477f0>, 'emp_no': 10018, 'birth_date': datetime.date(1954, 6, 19), 'first_name': 'Kazuhide', 'last_name': 'Peha', 'gender': 'F', 'hire_date': datetime.date(1987, 4, 3), 'pk__count': 1}
<Employee: 10019 Lillian Haddadi>
{'_state': <django.db.models.base.ModelState object at 0x104647860>, 'emp_no': 10019, 'birth_date': datetime.date(1953, 1, 23), 'first_name': 'Lillian', 'last_name': 'Haddadi', 'gender': 'M', 'hire_date': datetime.date(1999, 4, 30), 'pk__count': 1}
<Employee: 10020 Mayuko Warwick>
{'_state': <django.db.models.base.ModelState object at 0x1046478d0>, 'emp_no': 10020, 'birth_date': datetime.date(1952, 12, 24), 'first_name': 'Mayuko', 'last_name': 'Warwick', 'gender': 'M', 'hire_date': datetime.date(1991, 1, 26), 'pk__count': 1}
以上代码执行结果戳这里
3>.values()方法，放在annotate前就是指定分组字段，之后就是取结果中的字段
#！/usr/bin/env python
#_*_conding:utf-8_*_
#@author :yinzhengjie
#blog:http://www.cnblogs.com/yinzhengjie import os
import django
from django.db.models import Avg,Sum,Max,Min,Count
#参考salary/wsgi.py文件
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False) #导入employee应用的models模块中定义的Employee类
from employee.models import Employee emps = Employee.objects res = emps.filter(pk__gt=10010).values("gender").annotate(Count("pk")) #查询集
print(res) for x in res:
print(x) print("{0} 1 我是分隔符 {0}".format("*" * 15)) res2 = emps.filter(pk__gt=10010).values("gender").annotate(c=Count("pk")).order_by("-c") #查询集
print(res2) for x in res2:
print(x) print("{0} 2 我是分隔符 {0}".format("*" * 15)) res3 = emps.filter(pk__gt=10010).values('gender').annotate(Avg('pk'), c=Count('pk')).order_by('-c').values('pk__avg', 'c') #查询集,但后面的values过滤了每个对象字典的key print(s) for x in res3:
print(x) # 字典
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT `employees`.`gender`, COUNT(`employees`.`emp_no`) AS `pk__count` FROM `employees` WHERE `employees`.`emp_no` > 10010 GROUP BY `employees`.`gender` ORDER BY NULL LIMIT 21; args=(10010,)
(0.001) SELECT `employees`.`gender`, COUNT(`employees`.`emp_no`) AS `pk__count` FROM `employees` WHERE `employees`.`emp_no` > 10010 GROUP BY `employees`.`gender` ORDER BY NULL; args=(10010,)
(0.001) SELECT `employees`.`gender`, COUNT(`employees`.`emp_no`) AS `c` FROM `employees` WHERE `employees`.`emp_no` > 10010 GROUP BY `employees`.`gender` ORDER BY `c` DESC LIMIT 21; args=(10010,)
<QuerySet [{'gender': 'F', 'pk__count': 3}, {'gender': 'M', 'pk__count': 7}]>
{'gender': 'F', 'pk__count': 3}
{'gender': 'M', 'pk__count': 7}
*************** 1 我是分隔符 ***************
<QuerySet [{'gender': 'M', 'c': 7}, {'gender': 'F', 'c': 3}]>
(0.001) SELECT `employees`.`gender`, COUNT(`employees`.`emp_no`) AS `c` FROM `employees` WHERE `employees`.`emp_no` > 10010 GROUP BY `employees`.`gender` ORDER BY `c` DESC; args=(10010,)
{'gender': 'M', 'c': 7}
{'gender': 'F', 'c': 3}
*************** 2 我是分隔符 ***************
(0.001) SELECT AVG(`employees`.`emp_no`) AS `pk__avg`, COUNT(`employees`.`emp_no`) AS `c` FROM `employees` WHERE `employees`.`emp_no` > 10010 GROUP BY `employees`.`gender` ORDER BY `c` DESC; args=(10010,)
{'pk__avg': 10015.5714, 'c': 7}
{'pk__avg': 10015.3333, 'c': 3}
以上代码执行结果戳这里
