1 #!/usr/bin/env python
2 #_*_conding:utf-8_*_
3 #@author :yinzhengjie
4 #blog:http://www.cnblogs.com/yinzhengjie
5
6 import os
7 import django
8
9 #参考salary/wsgi.py文件
10 os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
11 django.setup(set_prefix=False)
12
13 #导入employee应用的models模块中定义的Employee类
14 from employee.models import Employee,Salary
15
16 emps = Employee.objects
17 sals = Salary.objects
18
19 # 查询10004员工所有工资
20 # 方案一、从员工往工资查
21 print(emps.get(pk=10004).salaries.all())
22
23 print("{0} 1 我是分割线 {0}".format("*" * 15))
24
25 # 查询10004员工所有工资
26 emp = emps.get(pk=10004) # 单一员工对象
27 print(emp.salaries.all())
28 print(emp.salaries.values('emp_no', 'from_date', 'salary')) # 投影 # 工资大于55000
29 print(emp.salaries.filter(salary__gt=55000).all())
30
31 print("{0} 2 我是分割线 {0}".format("*" * 15))
32
33 # 查询10004员工所有工资及姓名
34 # 方案二、从工资往员工查
35 slist = list(sals.filter(emp_no=10004))
36
37 for s in slist:
38 print(s.emp_no.first_name, s.emp_no_id, s.salary) # s.emp_no会引发填充对象
39
40 print("{0} 3 我是分割线 {0}".format("*" * 15))
41
42 # ############## 特别注意 #####################
43 # 这种查询会导致列表中的n个Salary实例填充其中emp_no属性,会查n此数据库
44 # 方案二改进(方案二的改进,虽然别扭,但也完成了,查询了较少的次数。)
45 slist = list(sals.filter(emp_no=10004))
46 if slist:
47 first = slist[0]
48 emp = first.emp_no
49 for s in slist:
50 print(emp.pk, emp.first_name, s.emp_no_id, s.salary)
(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` WHERE `employees`.`emp_no` = 10004; args=(10004,)
(0.001) SELECT `salaries`.`id`, `salaries`.`emp_no`, `salaries`.`from_date`, `salaries`.`salary`, `salaries`.`to_date` FROM `salaries` WHERE `salaries`.`emp_no` = 10004 LIMIT 21; args=(10004,)
<QuerySet [<Salary: <Employee: 10004 Chirstian Koblick> 1986-12-01 40054>, <Salary: <Employee: 10004 Chirstian Koblick> 1987-12-01 42283>, <Salary: <Employee: 10004 Chirstian Koblick> 1988-11-30 42542>, <Salary: <Employee: 10004 Chirstian Koblick> 1989-11-30 46065>, <Salary: <Employee: 10004 Chirstian Koblick> 1990-11-30 48271>, <Salary: <Employee: 10004 Chirstian Koblick> 1991-11-30 50594>, <Salary: <Employee: 10004 Chirstian Koblick> 1992-11-29 52119>, <Salary: <Employee: 10004 Chirstian Koblick> 1993-11-29 54693>, <Salary: <Employee: 10004 Chirstian Koblick> 1994-11-29 58326>, <Salary: <Employee: 10004 Chirstian Koblick> 1995-11-29 60770>]>
*************** 1 我是分割线 ***************
<QuerySet [<Salary: <Employee: 10004 Chirstian Koblick> 1986-12-01 40054>, <Salary: <Employee: 10004 Chirstian Koblick> 1987-12-01 42283>, <Salary: <Employee: 10004 Chirstian Koblick> 1988-11-30 42542>, <Salary: <Employee: 10004 Chirstian Koblick> 1989-11-30 46065>, <Salary: <Employee: 10004 Chirstian Koblick> 1990-11-30 48271>, <Salary: <Employee: 10004 Chirstian Koblick> 1991-11-30 50594>, <Salary: <Employee: 10004 Chirstian Koblick> 1992-11-29 52119>, <Salary: <Employee: 10004 Chirstian Koblick> 1993-11-29 54693>, <Salary: <Employee: 10004 Chirstian Koblick> 1994-11-29 58326>, <Salary: <Employee: 10004 Chirstian Koblick> 1995-11-29 60770>]>
(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` = 10004; args=(10004,)
(0.001) SELECT `salaries`.`id`, `salaries`.`emp_no`, `salaries`.`from_date`, `salaries`.`salary`, `salaries`.`to_date` FROM `salaries` WHERE `salaries`.`emp_no` = 10004 LIMIT 21; args=(10004,)
(0.001) SELECT `salaries`.`emp_no`, `salaries`.`from_date`, `salaries`.`salary` FROM `salaries` WHERE `salaries`.`emp_no` = 10004 LIMIT 21; args=(10004,)
<QuerySet [{'emp_no': 10004, 'from_date': datetime.date(1986, 12, 1), 'salary': 40054}, {'emp_no': 10004, 'from_date': datetime.date(1987, 12, 1), 'salary': 42283}, {'emp_no': 10004, 'from_date': datetime.date(1988, 11, 30), 'salary': 42542}, {'emp_no': 10004, 'from_date': datetime.date(1989, 11, 30), 'salary': 46065}, {'emp_no': 10004, 'from_date': datetime.date(1990, 11, 30), 'salary': 48271}, {'emp_no': 10004, 'from_date': datetime.date(1991, 11, 30), 'salary': 50594}, {'emp_no': 10004, 'from_date': datetime.date(1992, 11, 29), 'salary': 52119}, {'emp_no': 10004, 'from_date': datetime.date(1993, 11, 29), 'salary': 54693}, {'emp_no': 10004, 'from_date': datetime.date(1994, 11, 29), 'salary': 58326}, {'emp_no': 10004, 'from_date': datetime.date(1995, 11, 29), 'salary': 60770}]>
<QuerySet [<Salary: <Employee: 10004 Chirstian Koblick> 1994-11-29 58326>, <Salary: <Employee: 10004 Chirstian Koblick> 1995-11-29 60770>]>
*************** 2 我是分割线 ***************
Chirstian 10004 40054
(0.001) SELECT `salaries`.`id`, `salaries`.`emp_no`, `salaries`.`from_date`, `salaries`.`salary`, `salaries`.`to_date` FROM `salaries` WHERE (`salaries`.`emp_no` = 10004 AND `salaries`.`salary` > 55000) LIMIT 21; args=(10004, 55000)
(0.001) SELECT `salaries`.`id`, `salaries`.`emp_no`, `salaries`.`from_date`, `salaries`.`salary`, `salaries`.`to_date` FROM `salaries` WHERE `salaries`.`emp_no` = 10004; args=(10004,)
(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` = 10004; args=(10004,)
(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` = 10004; args=(10004,)
(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` = 10004; args=(10004,)
(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` = 10004; args=(10004,)
Chirstian 10004 42283
Chirstian 10004 42542
Chirstian 10004 46065
Chirstian 10004 48271
Chirstian 10004 50594
(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` = 10004; args=(10004,)
(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` = 10004; args=(10004,)
(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` = 10004; args=(10004,)
(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` = 10004; args=(10004,)
Chirstian 10004 52119
Chirstian 10004 54693
Chirstian 10004 58326
Chirstian 10004 60770
*************** 3 我是分割线 ***************
(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` = 10004; args=(10004,)
(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` = 10004; args=(10004,)
(0.001) SELECT `salaries`.`id`, `salaries`.`emp_no`, `salaries`.`from_date`, `salaries`.`salary`, `salaries`.`to_date` FROM `salaries` WHERE `salaries`.`emp_no` = 10004; args=(10004,)
(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` = 10004; args=(10004,)
10004 Chirstian 10004 40054
10004 Chirstian 10004 42283
10004 Chirstian 10004 42542
10004 Chirstian 10004 46065
10004 Chirstian 10004 48271
10004 Chirstian 10004 50594
10004 Chirstian 10004 52119
10004 Chirstian 10004 54693
10004 Chirstian 10004 58326
10004 Chirstian 10004 60770
<class 'django.db.models.query.QuerySet'>
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
<QuerySet [{'emp_no': 10001}, {'emp_no': 10002}, {'emp_no': 10004}]>
(0.001) SELECT DISTINCT `salaries`.`emp_no` FROM `salaries` WHERE `salaries`.`salary` > 55000 LIMIT 21; args=(55000,)
(0.001) SELECT DISTINCT `salaries`.`emp_no` FROM `salaries` WHERE `salaries`.`salary` > 55000; args=(55000,)
<QuerySet [{'emp_no': 10001}, {'emp_no': 10002}, {'emp_no': 10004}]>
*************** 我是分隔符 ***************
<class 'django.db.models.query.QuerySet'>
<QuerySet [{'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10002}, {'emp_no': 10002}, {'emp_no': 10002}, '...(remaining elements truncated)...']>
(0.001) SELECT DISTINCT `salaries`.`emp_no` FROM `salaries` WHERE (`salaries`.`salary` > 55000 AND `salaries`.`emp_no` IN (10001, 10002, 10004)) LIMIT 21; args=(55000, 10001, 10002, 10004)
(0.001) SELECT `salaries`.`emp_no` FROM `salaries` WHERE `salaries`.`salary` > 55000 LIMIT 21; args=(55000,)
(0.001) SELECT `salaries`.`emp_no` FROM `salaries` WHERE `salaries`.`salary` > 55000; args=(55000,)
<QuerySet [{'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10001}, {'emp_no': 10002}, {'emp_no': 10002}, {'emp_no': 10002}, '...(remaining elements truncated)...']>
(0.001) SELECT `salaries`.`emp_no` FROM `salaries` WHERE (`salaries`.`salary` > 55000 AND `salaries`.`emp_no` IN (10001, 10002, 10004)) LIMIT 21; args=(55000, 10001, 10002, 10004)
1 #!/usr/bin/env python
2 #_*_conding:utf-8_*_
3 #@author :yinzhengjie
4 #blog:http://www.cnblogs.com/yinzhengjie
5
6 import os
7 import django
8
9 #参考salary/wsgi.py文件
10 os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
11 django.setup(set_prefix=False)
12
13 #导入employee应用的models模块中定义的Employee类
14 from employee.models import Employee
15
16 empmgr = Employee.objects
17
18 # 工资大于55000的所有员工的姓名
19 sql = """
20 SELECT
21 DISTINCT e.emp_no, e.first_name, e.last_name
22 FROM
23 employees e
24 JOIN
25 salaries s
26 ON
27 e.emp_no=s.emp_no
28 WHERE
29 s.salary > 55000
30 """
31
32 # DISTINCT 需要,结果会去重
33 emps = empmgr.raw(sql)
34 print(type(emps)) # RawQuerySet
35 print(list(emps))
36
37
38 # 员工工资记录里超过70000的人的工资和姓名
39 sql = """
40 SELECT
41 e.emp_no, e.first_name, e.last_name, s.salary
42 FROM
43 employees e
44 JOIN
45 salaries s
46 ON
47 e.emp_no = s.emp_no
48 where
49 s.salary > 70000
50 """
51
52 #如果查询非常复杂,使用Django不方便,可以直接使用SQL语句
53 for x in empmgr.raw(sql):
54 print(x.__dict__) # 将salary属性注入到当前Employee实例中
55 print(x.first_name, x.salary)
<class 'django.db.models.query.RawQuerySet'>
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT
DISTINCT e.emp_no, e.first_name, e.last_name
FROM
employees e
JOIN
salaries s
ON
e.emp_no=s.emp_no
WHERE
s.salary > 55000
; args=()
(0.001) SELECT
e.emp_no, e.first_name, e.last_name, s.salary
FROM
employees e
JOIN
salaries s
ON
e.emp_no = s.emp_no
where
s.salary > 70000
; args=()
[<Employee: 10001 Georgi Facello>, <Employee: 10002 Bezalel Simmel>, <Employee: 10004 Chirstian Koblick>]
{'_state': <django.db.models.base.ModelState object at 0x104751160>, 'emp_no': 10001, 'first_name': 'Georgi', 'last_name': 'Facello', 'salary': 71046}
Georgi 71046
{'_state': <django.db.models.base.ModelState object at 0x1047511d0>, 'emp_no': 10001, 'first_name': 'Georgi', 'last_name': 'Facello', 'salary': 74333}
Georgi 74333
{'_state': <django.db.models.base.ModelState object at 0x104751240>, 'emp_no': 10001, 'first_name': 'Georgi', 'last_name': 'Facello', 'salary': 75286}
Georgi 75286
{'_state': <django.db.models.base.ModelState object at 0x1047512b0>, 'emp_no': 10001, 'first_name': 'Georgi', 'last_name': 'Facello', 'salary': 75994}
Georgi 75994
{'_state': <django.db.models.base.ModelState object at 0x104751320>, 'emp_no': 10001, 'first_name': 'Georgi', 'last_name': 'Facello', 'salary': 76884}
Georgi 76884
{'_state': <django.db.models.base.ModelState object at 0x104751390>, 'emp_no': 10001, 'first_name': 'Georgi', 'last_name': 'Facello', 'salary': 80013}
Georgi 80013
{'_state': <django.db.models.base.ModelState object at 0x104751400>, 'emp_no': 10001, 'first_name': 'Georgi', 'last_name': 'Facello', 'salary': 81025}
Georgi 81025
{'_state': <django.db.models.base.ModelState object at 0x104751470>, 'emp_no': 10001, 'first_name': 'Georgi', 'last_name': 'Facello', 'salary': 81097}
Georgi 81097
{'_state': <django.db.models.base.ModelState object at 0x1047514e0>, 'emp_no': 10001, 'first_name': 'Georgi', 'last_name': 'Facello', 'salary': 84917}
Georgi 84917
{'_state': <django.db.models.base.ModelState object at 0x104751550>, 'emp_no': 10001, 'first_name': 'Georgi', 'last_name': 'Facello', 'salary': 85112}
Georgi 85112
{'_state': <django.db.models.base.ModelState object at 0x1047515c0>, 'emp_no': 10001, 'first_name': 'Georgi', 'last_name': 'Facello', 'salary': 85097}
Georgi 85097
{'_state': <django.db.models.base.ModelState object at 0x104751630>, 'emp_no': 10001, 'first_name': 'Georgi', 'last_name': 'Facello', 'salary': 88958}
Georgi 88958
{'_state': <django.db.models.base.ModelState object at 0x1047516a0>, 'emp_no': 10002, 'first_name': 'Bezalel', 'last_name': 'Simmel', 'salary': 71963}
Bezalel 71963
{'_state': <django.db.models.base.ModelState object at 0x104751710>, 'emp_no': 10002, 'first_name': 'Bezalel', 'last_name': 'Simmel', 'salary': 72527}
Bezalel 72527