数据库开发-Django ORM的单表查询

　　　　　　　　　　　　　　数据库开发-Django ORM的单表查询

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

版权声明：原创作品，谢绝转载！否则将追究法律责任。

 

 

 

一.查询集

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}