数据库开发-Django ORM的一对多查询

　　　　　　　　　　　　　　数据库开发-Django ORM的一对多查询

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

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

 

 

 

一.联合主键问题

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` smallint(6) NOT NULL DEFAULT '1' COMMENT 'M=1, F=2',
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees`
(`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
员工和工资表是一对多关系
SQLAlchemy提供了联合主键支持，但是Django至今都没有支持。 

Django只支持单一主键，这也是我提倡的。但对于本次基于Django测试的表就只能增加一个单一主键了。具体原因，请参看 https://code.djangoproject.com/wiki/MultipleColumnPrimaryKeys 。

Django 到目前为止也没有提供这种Composite primary key Django不能直接添加自己的2个字段的联合主键，我们手动为表创建一个自增id主键。操作顺序如下:
　　1. 取消表所有联合主键，并删除所有外键约束后保存，成功再继续
　　2. 为表增加一个id字段，自增、主键。保存，如果成功，它会自动填充数据 
　　3. 重建原来的外键约束即可

 

二.构建模型

 1 from django.db import models
 2 
 3 # Create your models here.
 4 from django.db import models
 5 
 6 class Employee(models.Model):
 7    class Meta:
 8        db_table = 'employees'
 9 
10    emp_no = models.IntegerField(primary_key=True)
11    birth_date = models.DateField(null=False)
12    first_name = models.CharField(null=False, max_length=14)
13    last_name = models.CharField(null=False, max_length=16)
14    gender = models.SmallIntegerField(null=False)
15    hire_date = models.DateField(null=False)
16 
17    def __repr__(self):
18        return "<Employee: {} {} {}>".format(
19            self.emp_no,
20            self.first_name,
21            self.last_name
22        )
23 
24    __str__ = __repr__
25 
26 
27 class Salary(models.Model):
28     class Meta:
29         db_table = 'salaries'
30 
31     id = models.AutoField(primary_key=True) # 额外增加的，Django不支持联合主键
32     # 候选键(emp_no, from_date)
33     emp_no = models.ForeignKey('Employee', on_delete=models.CASCADE, null=False, db_column='emp_no')        #Django习惯给外键默认起名为xxx_id(例如:emp_no_id),推荐显式增加db_column来指定字段名称。
34     from_date = models.DateField(null=False)
35     salary = models.IntegerField(null=False)
36     to_date = models.DateField(null=False)
37 
38     def __repr__(self):
39         return "<Salary: {} {} {}>".format(
40             self.emp_no,
41             self.from_date,
42             self.salary
43         )
44 
45     __str__ = __repr__

 

三.特殊属性 

 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 from django.db.models import Avg,Sum,Max,Min,Count
 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 """
17     如果增加类外键后，Django会对一端和多端增加一些新的类属性。
18         从一端往多端查 <Employee_instance>.salary_set 
19         从多端往一端查 <Salary_instance>.emp_no
20 """
21 emps = Employee.objects
22 
23 """
24     一端,Employee类中多了一个类属性,即"('salary_set', <django.db.models.fields.related_descriptors.ReverseManyToOneDescriptor object at 0x1045ae550>)"
25 """
26 print(*Employee.__dict__.items(),sep="\n")
27 
28 print("{0}  我是分隔符 {0}".format("*" * 15))
29 
30 """
31     多端，Salary类中也多了一个类属性,即"('emp_no_id', <django.db.models.query_utils.DeferredAttribute object at 0x1045ae240>)"和"('emp_no', <django.db.models.fields.related_descriptors.ForwardManyToOneDescriptor object at 0x1045ae278>)"
32 """
33 print(*Salary.__dict__.items(),sep="\n")
('__module__', 'employee.models')
('__repr__', <function Employee.__repr__ at 0x1045c7b70>)
('__str__', <function Employee.__repr__ at 0x1045c7b70>)
('__doc__', 'Employee(emp_no, birth_date, first_name, last_name, gender, hire_date)')
('_meta', <Options for Employee>)
('DoesNotExist', <class 'employee.models.Employee.DoesNotExist'>)
('MultipleObjectsReturned', <class 'employee.models.Employee.MultipleObjectsReturned'>)
('emp_no', <django.db.models.query_utils.DeferredAttribute object at 0x1045cbc88>)
('birth_date', <django.db.models.query_utils.DeferredAttribute object at 0x1045cbcc0>)
('get_next_by_birth_date', functools.partialmethod(<function Model._get_next_or_previous_by_FIELD at 0x10379d620>, , field=<django.db.models.fields.DateField: birth_date>, is_next=True))
('get_previous_by_birth_date', functools.partialmethod(<function Model._get_next_or_previous_by_FIELD at 0x10379d620>, , field=<django.db.models.fields.DateField: birth_date>, is_next=False))
('first_name', <django.db.models.query_utils.DeferredAttribute object at 0x1045b1d30>)
('last_name', <django.db.models.query_utils.DeferredAttribute object at 0x1045cbd30>)
('gender', <django.db.models.query_utils.DeferredAttribute object at 0x1045cbd68>)
('hire_date', <django.db.models.query_utils.DeferredAttribute object at 0x1045cbda0>)
('get_next_by_hire_date', functools.partialmethod(<function Model._get_next_or_previous_by_FIELD at 0x10379d620>, , field=<django.db.models.fields.DateField: hire_date>, is_next=True))
('get_previous_by_hire_date', functools.partialmethod(<function Model._get_next_or_previous_by_FIELD at 0x10379d620>, , field=<django.db.models.fields.DateField: hire_date>, is_next=False))
('objects', <django.db.models.manager.ManagerDescriptor object at 0x1045cbeb8>)
('salary_set', <django.db.models.fields.related_descriptors.ReverseManyToOneDescriptor object at 0x1045d44a8>)
***************  我是分隔符 ***************
('__module__', 'employee.models')
('__repr__', <function Salary.__repr__ at 0x1045c2158>)
('__str__', <function Salary.__repr__ at 0x1045c2158>)
('__doc__', 'Salary(id, emp_no, from_date, salary, to_date)')
('_meta', <Options for Salary>)
('DoesNotExist', <class 'employee.models.Salary.DoesNotExist'>)
('MultipleObjectsReturned', <class 'employee.models.Salary.MultipleObjectsReturned'>)
('id', <django.db.models.query_utils.DeferredAttribute object at 0x1045d40f0>)
('emp_no_id', <django.db.models.query_utils.DeferredAttribute object at 0x1045d4198>)
('emp_no', <django.db.models.fields.related_descriptors.ForwardManyToOneDescriptor object at 0x1045d41d0>)
('from_date', <django.db.models.query_utils.DeferredAttribute object at 0x1045d4208>)
('get_next_by_from_date', functools.partialmethod(<function Model._get_next_or_previous_by_FIELD at 0x10379d620>, , field=<django.db.models.fields.DateField: from_date>, is_next=True))
('get_previous_by_from_date', functools.partialmethod(<function Model._get_next_or_previous_by_FIELD at 0x10379d620>, , field=<django.db.models.fields.DateField: from_date>, is_next=False))
('salary', <django.db.models.query_utils.DeferredAttribute object at 0x1045d42b0>)
('to_date', <django.db.models.query_utils.DeferredAttribute object at 0x1045d4320>)
('get_next_by_to_date', functools.partialmethod(<function Model._get_next_or_previous_by_FIELD at 0x10379d620>, , field=<django.db.models.fields.DateField: to_date>, is_next=True))
('get_previous_by_to_date', functools.partialmethod(<function Model._get_next_or_previous_by_FIELD at 0x10379d620>, , field=<django.db.models.fields.DateField: to_date>, is_next=False))
('objects', <django.db.models.manager.ManagerDescriptor object at 0x1045d4438>)
以上代码执行结果戳这里

 

四.查询

1>.使用salary_set查询

 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 
18 
19 #查询10004员工所有工资
20 print(emps.get(pk=10004).salary_set.all())
(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` = 10004; 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>]>
(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,)
以上代码执行结果戳这里

2>.如果觉得salary_set不好用，可以使用related_name  

from django.db import models

# Create your models here.
from django.db import models

class Employee(models.Model):
   class Meta:
       db_table = 'employees'

   emp_no = models.IntegerField(primary_key=True)
   birth_date = models.DateField(null=False)
   first_name = models.CharField(null=False, max_length=14)
   last_name = models.CharField(null=False, max_length=16)
   gender = models.SmallIntegerField(null=False)
   hire_date = models.DateField(null=False)

   def __repr__(self):
       return "<Employee: {} {} {}>".format(
           self.emp_no,
           self.first_name,
           self.last_name
       )

   __str__ = __repr__


class Salary(models.Model):
    class Meta:
        db_table = 'salaries'

    id = models.AutoField(primary_key=True) # 额外增加的，Django不支持联合主键
    # 候选键(emp_no, from_date)
    #emp_no = models.ForeignKey('Employee', on_delete=models.CASCADE, null=False, db_column='emp_no')        #Django习惯给外键默认起名为xxx_id(例如:emp_no_id),推荐显式增加db_column来指定字段名称。
    emp_no = models.ForeignKey('Employee', on_delete=models.CASCADE, null=False,db_column='emp_no', related_name='salaries')
    from_date = models.DateField(null=False)
    salary = models.IntegerField(null=False)
    to_date = models.DateField(null=False)

    def __repr__(self):
        return "<Salary: {} {} {}>".format(
            self.emp_no,
            self.from_date,
            self.salary
        )

    __str__ = __repr__
需要修改models.py的文件内容
 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 
18 
19 #查询10004员工所有工资
20 #print(emps.get(pk=10004).salary_set.all())
21 print(emps.get(pk=10004).salaries.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` 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>]>
以上代码执行结果戳这里

3>.完整代码

 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
以上代码执行结果戳这里

  

五.distinct

 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 # 所有发了工资的员工 print(salarymgr.values('emp_no').distinct())
20 # 工资大于55000的所有员工的姓名
21 emps = sals.filter(salary__gt=55000).values('emp_no').distinct()
22 print(type(emps))
23 
24 print(emps)
25 print(emps.filter(emp_no__in=[d.get('emp_no') for d in emps]))
26 
27 print("{0}  我是分隔符 {0}".format("*" * 15))        #不使用上面的distinct()，观察输出的结果。
28 
29 emps = sals.filter(salary__gt=55000).values('emp_no')
30 print(type(emps))
31 
32 print(emps)
33 print(emps.filter(emp_no__in=[d.get('emp_no') for d in emps]))
<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)
以上代码执行结果戳这里

 

六.raw的使用(如果查询非常复杂，使用Django不方便，可以直接使用SQL语句 )

 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
以上代码执行结果戳这里

 

posted @ 2019-11-28 06:03  尹正杰  阅读(583)  评论(0)  编辑  收藏  举报