django orm

无论前后端，还是模板, 均需要ORM

Object Relationship Map, 对象和关系映射。

table表，定义一次。class
column表的字段，定义一次。class property
row表中有N多行记录, 实体。实体与实体间1对1，1对多，多对多。instance/object

mysql引擎，就是将对象转换成关系, 并在数据库上执行。


# https://docs.djangoproject.com/en/3.2/ref/settings/#databases

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
    }
}

mysql引擎缺点，转换的语句性能可能不高，看代码水平。

字段类型(类属性值)

https://docs.djangoproject.com/en/3.2/ref/models/fields/#field-types

字段类	说明
autofield	自增整数段，不指定，django会自动添加主键字段
booleanfield
nullbooleanfield
charfield

缺省主键

默认，如果不定义主键时，会自动生成主键。如果定义了主键，将不会自动生成主键。

python
id = models.AutoField(primary_key=True)

python之禅，显式优于隐式，有必要尽量自己定义主键。

3.2增加DEFAULT_AUTO_FIELD定义自增主键

字段选项

https://docs.djangoproject.com/en/3.2/ref/models/fields/#field-options

选项	描述
primary_key	主键
unique	惟一
null	字段可为null, 默认False, 默认不可以空。
default	不是数据库字段缺省值，而是django填充的默认值
blank	与数据库无关，admin页面可以不填
db_index	字段是否有索引
db_column	外键使用

关系类型字段

字段名	描述
ForeignKey	1个部门，多个员工。多端加外键。
ManyToManyField	1个员工多个部门，一个部门多员工。第3表，主键是部门id和员工id联合主键或 2个1对多，第3个表有3个字段，1个主键，1个是1张表的外键，另一个是另一张表的外键。
OneToOneField	1对1，随便哪个表加外键，从表加外键。

定义表

django.db.models.Model 元类，创建类的时候，背后完成类属性初始化。



class Meta:
    db_table = 'employee' # 默认<appname>_<model_name>
    unique_toger #联合惟一，新版本使用uniqueConstraint
    ordering=['-pk'] # 排序
    
字段，字段属性, 如果类只读数据库，就可以不与数据库一致。一旦迁移就一致。

示例数据


/*
Create By Wayne

Source Server         : python
Source Server Version : 50545
Source Host           : 192.168.142.135:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50545
File Encoding         : 65001

Date: 2017-10-01 20:27:47

http://www.magedu.com

DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE test;
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for departments
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES ('d009', 'Customer Service');
INSERT INTO `departments` VALUES ('d005', 'Development');
INSERT INTO `departments` VALUES ('d002', 'Finance');
INSERT INTO `departments` VALUES ('d003', 'Human Resources');
INSERT INTO `departments` VALUES ('d001', 'Marketing');
INSERT INTO `departments` VALUES ('d004', 'Production');
INSERT INTO `departments` VALUES ('d006', 'Quality Management');
INSERT INTO `departments` VALUES ('d008', 'Research');
INSERT INTO `departments` VALUES ('d007', 'Sales');

-- ----------------------------
-- Table structure for dept_emp
-- ----------------------------
DROP TABLE IF EXISTS `dept_emp`;
CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept_emp
-- ----------------------------
INSERT INTO `dept_emp` VALUES ('10001', 'd005', '1986-06-26', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10002', 'd007', '1996-08-03', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10003', 'd004', '1995-12-03', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10004', 'd004', '1986-12-01', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10005', 'd003', '1989-09-12', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10006', 'd005', '1990-08-05', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10007', 'd008', '1989-02-10', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10008', 'd005', '1998-03-11', '2000-07-31');
INSERT INTO `dept_emp` VALUES ('10009', 'd006', '1985-02-18', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10010', 'd004', '1996-11-24', '2000-06-26');
INSERT INTO `dept_emp` VALUES ('10010', 'd006', '2000-06-26', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10011', 'd009', '1990-01-22', '1996-11-09');
INSERT INTO `dept_emp` VALUES ('10012', 'd005', '1992-12-18', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10013', 'd003', '1985-10-20', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10014', 'd005', '1993-12-29', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10015', 'd008', '1992-09-19', '1993-08-22');
INSERT INTO `dept_emp` VALUES ('10016', 'd007', '1998-02-11', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10017', 'd001', '1993-08-03', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10018', 'd004', '1992-07-29', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10018', 'd005', '1987-04-03', '1992-07-29');
INSERT INTO `dept_emp` VALUES ('10019', 'd008', '1999-04-30', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10020', 'd004', '1997-12-30', '9999-01-01');

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
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;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES ('10001', '1953-09-02', 'Georgi', 'Facello', 1, '1986-06-26');
INSERT INTO `employees` VALUES ('10002', '1964-06-02', 'Bezalel', 'Simmel', 2, '1985-11-21');
INSERT INTO `employees` VALUES ('10003', '1959-12-03', 'Parto', 'Bamford', 1, '1986-08-28');
INSERT INTO `employees` VALUES ('10004', '1954-05-01', 'Chirstian', 'Koblick', 1, '1986-12-01');
INSERT INTO `employees` VALUES ('10005', '1955-01-21', 'Kyoichi', 'Maliniak', 1, '1989-09-12');
INSERT INTO `employees` VALUES ('10006', '1953-04-20', 'Anneke', 'Preusig', 2, '1989-06-02');
INSERT INTO `employees` VALUES ('10007', '1957-05-23', 'Tzvetan', 'Zielinski', 2, '1989-02-10');
INSERT INTO `employees` VALUES ('10008', '1958-02-19', 'Saniya', 'Kalloufi', 1, '1994-09-15');
INSERT INTO `employees` VALUES ('10009', '1952-04-19', 'Sumant', 'Peac', 2, '1985-02-18');
INSERT INTO `employees` VALUES ('10010', '1963-06-01', 'Duangkaew', 'Piveteau', 2, '1989-08-24');
INSERT INTO `employees` VALUES ('10011', '1953-11-07', 'Mary', 'Sluis', 2, '1990-01-22');
INSERT INTO `employees` VALUES ('10012', '1960-10-04', 'Patricio', 'Bridgland', 1, '1992-12-18');
INSERT INTO `employees` VALUES ('10013', '1963-06-07', 'Eberhardt', 'Terkki', 1, '1985-10-20');
INSERT INTO `employees` VALUES ('10014', '1956-02-12', 'Berni', 'Genin', 1, '1987-03-11');
INSERT INTO `employees` VALUES ('10015', '1959-08-19', 'Guoxiang', 'Nooteboom', 1, '1987-07-02');
INSERT INTO `employees` VALUES ('10016', '1961-05-02', 'Kazuhito', 'Cappelletti', 1, '1995-01-27');
INSERT INTO `employees` VALUES ('10017', '1958-07-06', 'Cristinel', 'Bouloucos', 2, '1993-08-03');
INSERT INTO `employees` VALUES ('10018', '1954-06-19', 'Kazuhide', 'Peha', 2, '1987-04-03');
INSERT INTO `employees` VALUES ('10019', '1953-01-23', 'Lillian', 'Haddadi', 1, '1999-04-30');
INSERT INTO `employees` VALUES ('10020', '1952-12-24', 'Mayuko', 'Warwick', 1, '1991-01-26');

-- ----------------------------
-- Table structure for salaries
-- ----------------------------
DROP TABLE IF EXISTS `salaries`;
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;

-- ----------------------------
-- Records of salaries
-- ----------------------------
INSERT INTO `salaries` VALUES ('10001', '60117', '1986-06-26', '1987-06-26');
INSERT INTO `salaries` VALUES ('10001', '62102', '1987-06-26', '1988-06-25');
INSERT INTO `salaries` VALUES ('10001', '66074', '1988-06-25', '1989-06-25');
INSERT INTO `salaries` VALUES ('10001', '66596', '1989-06-25', '1990-06-25');
INSERT INTO `salaries` VALUES ('10001', '66961', '1990-06-25', '1991-06-25');
INSERT INTO `salaries` VALUES ('10001', '71046', '1991-06-25', '1992-06-24');
INSERT INTO `salaries` VALUES ('10001', '74333', '1992-06-24', '1993-06-24');
INSERT INTO `salaries` VALUES ('10001', '75286', '1993-06-24', '1994-06-24');
INSERT INTO `salaries` VALUES ('10001', '75994', '1994-06-24', '1995-06-24');
INSERT INTO `salaries` VALUES ('10001', '76884', '1995-06-24', '1996-06-23');
INSERT INTO `salaries` VALUES ('10001', '80013', '1996-06-23', '1997-06-23');
INSERT INTO `salaries` VALUES ('10001', '81025', '1997-06-23', '1998-06-23');
INSERT INTO `salaries` VALUES ('10001', '81097', '1998-06-23', '1999-06-23');
INSERT INTO `salaries` VALUES ('10001', '84917', '1999-06-23', '2000-06-22');
INSERT INTO `salaries` VALUES ('10001', '85112', '2000-06-22', '2001-06-22');
INSERT INTO `salaries` VALUES ('10001', '85097', '2001-06-22', '2002-06-22');
INSERT INTO `salaries` VALUES ('10001', '88958', '2002-06-22', '9999-01-01');
INSERT INTO `salaries` VALUES ('10002', '65828', '1996-08-03', '1997-08-03');
INSERT INTO `salaries` VALUES ('10002', '65909', '1997-08-03', '1998-08-03');
INSERT INTO `salaries` VALUES ('10002', '67534', '1998-08-03', '1999-08-03');
INSERT INTO `salaries` VALUES ('10002', '69366', '1999-08-03', '2000-08-02');
INSERT INTO `salaries` VALUES ('10002', '71963', '2000-08-02', '2001-08-02');
INSERT INTO `salaries` VALUES ('10002', '72527', '2001-08-02', '9999-01-01');
INSERT INTO `salaries` VALUES ('10003', '40006', '1995-12-03', '1996-12-02');
INSERT INTO `salaries` VALUES ('10003', '43616', '1996-12-02', '1997-12-02');
INSERT INTO `salaries` VALUES ('10003', '43466', '1997-12-02', '1998-12-02');
INSERT INTO `salaries` VALUES ('10003', '43636', '1998-12-02', '1999-12-02');
INSERT INTO `salaries` VALUES ('10003', '43478', '1999-12-02', '2000-12-01');
INSERT INTO `salaries` VALUES ('10003', '43699', '2000-12-01', '2001-12-01');
INSERT INTO `salaries` VALUES ('10003', '43311', '2001-12-01', '9999-01-01');
INSERT INTO `salaries` VALUES ('10004', '40054', '1986-12-01', '1987-12-01');
INSERT INTO `salaries` VALUES ('10004', '42283', '1987-12-01', '1988-11-30');
INSERT INTO `salaries` VALUES ('10004', '42542', '1988-11-30', '1989-11-30');
INSERT INTO `salaries` VALUES ('10004', '46065', '1989-11-30', '1990-11-30');
INSERT INTO `salaries` VALUES ('10004', '48271', '1990-11-30', '1991-11-30');
INSERT INTO `salaries` VALUES ('10004', '50594', '1991-11-30', '1992-11-29');
INSERT INTO `salaries` VALUES ('10004', '52119', '1992-11-29', '1993-11-29');
INSERT INTO `salaries` VALUES ('10004', '54693', '1993-11-29', '1994-11-29');
INSERT INTO `salaries` VALUES ('10004', '58326', '1994-11-29', '1995-11-29');
INSERT INTO `salaries` VALUES ('10004', '60770', '1995-11-29', '1996-11-28');

-- ----------------------------
-- Table structure for titles
-- ----------------------------
DROP TABLE IF EXISTS `titles`;
CREATE TABLE `titles` (
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`title`,`from_date`),
  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of titles
-- ----------------------------
INSERT INTO `titles` VALUES ('10001', 'Senior Engineer', '1986-06-26', '9999-01-01');
INSERT INTO `titles` VALUES ('10002', 'Staff', '1996-08-03', '9999-01-01');
INSERT INTO `titles` VALUES ('10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
INSERT INTO `titles` VALUES ('10004', 'Engineer', '1986-12-01', '1995-12-01');
INSERT INTO `titles` VALUES ('10004', 'Senior Engineer', '1995-12-01', '9999-01-01');
INSERT INTO `titles` VALUES ('10005', 'Senior Staff', '1996-09-12', '9999-01-01');
INSERT INTO `titles` VALUES ('10005', 'Staff', '1989-09-12', '1996-09-12');
INSERT INTO `titles` VALUES ('10006', 'Senior Engineer', '1990-08-05', '9999-01-01');
INSERT INTO `titles` VALUES ('10007', 'Senior Staff', '1996-02-11', '9999-01-01');
INSERT INTO `titles` VALUES ('10007', 'Staff', '1989-02-10', '1996-02-11');
INSERT INTO `titles` VALUES ('10008', 'Assistant Engineer', '1998-03-11', '2000-07-31');
INSERT INTO `titles` VALUES ('10009', 'Assistant Engineer', '1985-02-18', '1990-02-18');
INSERT INTO `titles` VALUES ('10009', 'Engineer', '1990-02-18', '1995-02-18');
INSERT INTO `titles` VALUES ('10009', 'Senior Engineer', '1995-02-18', '9999-01-01');
INSERT INTO `titles` VALUES ('10010', 'Engineer', '1996-11-24', '9999-01-01');
INSERT INTO `titles` VALUES ('10011', 'Staff', '1990-01-22', '1996-11-09');
INSERT INTO `titles` VALUES ('10012', 'Engineer', '1992-12-18', '2000-12-18');
INSERT INTO `titles` VALUES ('10012', 'Senior Engineer', '2000-12-18', '9999-01-01');
INSERT INTO `titles` VALUES ('10013', 'Senior Staff', '1985-10-20', '9999-01-01');
INSERT INTO `titles` VALUES ('10014', 'Engineer', '1993-12-29', '9999-01-01');
INSERT INTO `titles` VALUES ('10015', 'Senior Staff', '1992-09-19', '1993-08-22');

基于表完成类（表), 类属性(字段名)

employee/models.py

python
from django.db import models

# Create your models here.
"""
| employees | CREATE TABLE `employees` (
  `emp_no` int NOT NULL, --- 不自增, pycharm 2021.2.3 默认提示null=False, 所以省略。verbose_name 后台管理展示的。实际没有用。
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,        ----  varchar，只对应 CharField
  `last_name` varchar(16) NOT NULL,
  `gender` smallint NOT NULL DEFAULT '1' COMMENT 'M=1, F=2', # M 男, F女
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`) --- --- 主键
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
"""


class Gender(models.TextChoices):
    # 数据库中写的值, 描述, 描述用来展示使用
    MALE = 'M', '男'  # get_gender_display() 网页获取展示名
    FEMALE = 'F', '女'


class Employee(models.Model):  # Model基类，元编程, 自动添加objects 管理器, 可以完成增删改查。一类返回结果集合, 链式编程；一类返回单个数据, 不支持链式编程；
    class Meta:
        db_table = 'employees'  # 数据库中对应的表名称，不写默认<app>_<module>, employee_employee

    emp_no = models.BigIntegerField(primary_key=True,
                                    verbose_name='工号')  # 一般字段名和属性名一致; 主键，你定义了主键，django不会自动添加自增id。否则 会自动添加
    birth_date = models.DateField(verbose_name='生日')
    first_name = models.CharField(max_length=14, verbose_name='名')
    last_name = models.CharField(max_length=16, verbose_name='姓')
    gender = models.CharField(choices=Gender.choices, verbose_name='性别', default=Gender.MALE)  # 与上面的enum不一样，这里只是查询
    # hire_date = models.DateField(verbose_name='雇佣时间')
    
    @property
    def name(self):
        return f'[{self.last_name} {self.first_name}]'

    def __repr__(self):
        return f'<Employee {self.emp_no} {self.name}>'

    __str__ = __repr__

verbose_name 代码自己看，与数据库无关。
default django自己使用，不是写到数据库
primary_key, max_length, *Field(字段类型) 与数据库一一对应
__repr__ __str__ 调试使用，看着方便。
property用于加工字段，每个实例需要通过类属性, 创建自己的实例属性。

测试代码

t1.py 根目录

Employees.objects django.db.models.manager 管理器
Employees.objects.all() 懒加载; ctrl + q不会出现类定义 django.db.models.query.QuerySet
查询集中 __iter__

python
print(f'加载到 {__name__}')

import os
import django

# 1. 加载环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')

# 2. 启动django
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects # Manager 管增删改查
    # 不提供，默认提供objects。如果你提供了，则默认就没有了。

    # 黄色，是动态挂载的。自己记
    # print(Employee.objects.all())  # => QuerySet 可迭代对象 <Employee: Employee object (10001)>
    # emps = Employee.objects.all()
    emps:QuerySet = mgr.all()
    # emps. 通过变量注释查看

    print(type(emps)) # <class 'django.db.models.query.QuerySet'> 没有查询数据库的语句，说明以上操作没有触发查询数据库。是惰性的，你要数据的时候才会查询数据库。
    # print(emps)
    for e in emps: # __iter__
        print(type(e),e, e.emp_no,e.name) # employee.models.Employee
        print(e.gender,e.get_gender_display()) # get_属性名_display() 仅对choices有此方法
print(f'加载结束 {__name__}')

all() 返回 QuerySet 可迭代对象
QuerySet 可迭代对象是惰性的，你要数据的时候才会查询数据库。
QuerySet 可迭代对象要数据的方法。print或遍历, 序列化, if, 切片, len, repr, bool，会立即求值
QuerySet 可迭代对象遍历的每个对象就是实例。实例可以访问类属性
e.gender是choices, get_属性名_display() 仅对choices有此方法
Employee.objects 内建管理器，管理增删除改查。不提供，默认提供objects。如果你提供了，则默认就没有了。
emps:QuerySet 变量注释，方便变量补全

django 管理器，完成查询

pymysql与数据库交互，ORM需要打印sql日志。

django 缓存

list缓存

python
print(f'加载到 {__name__}')

import os
import django

# 1. 加载环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')

# 2. 启动django
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects # Manager 管增删改查
    # 不提供，默认提供objects。如果你提供了，则默认就没有了。

    # 黄色，是动态挂载的。自己记
    # print(Employee.objects.all())  # => QuerySet 可迭代对象 <Employee: Employee object (10001)>
    # emps = Employee.objects.all()
    emps:QuerySet = mgr.all()
    # emps. 通过变量注释查看

    print(type(emps)) # <class 'django.db.models.query.QuerySet'> 没有查询数据库的语句，说明以上操作没有触发查询数据库。是惰性的，你要数据的时候才会查询数据库。
    print(emps) # 查看，不会缓存。默认LIMIT 21
    print(emps._result_cache)
    print(list(emps)) # 把迭代对象变成列表之后，将使用缓存。不会LIMIT
    print(emps._result_cache)
    print(emps)
print(f'加载结束 {__name__}')

for e in emps 遍历缓存


....
    print(emps) # 查看，不会缓存。默认LIMIT 21
    print(emps._result_cache)
    # print(list(emps)) # 把迭代对象变成列表之后，将使用缓存。不会LIMIT
    for e in emps:
        pass 
    print(emps._result_cache)
    print(emps)
print(f'加载结束 {__name__}')


print(*emps) # 同样是遍历，之后使用emps将使用缓存。

反复遍历的方法

python
x = list(emps)
for e in x:
	pass

切片左闭右开,0索引起始

分页使用，对应limit, offset

queryset[10:], OFFSET 10，索引从0开始, 不支持负索引。这个括号是左闭右开的区间。

queryset[10:15], LIMIT 5 OFFSET 10; 起始是偏移量，差为限制，这个括号是左闭右开的区间。


print(f'加载到 {__name__}')

import os
import django

# 1. 加载环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')

# 2. 启动django
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects # Manager 管增删改查
    # 不提供，默认提供objects。如果你提供了，则默认就没有了。

    # 黄色，是动态挂载的。自己记
    # print(Employee.objects.all())  # => QuerySet 可迭代对象 <Employee: Employee object (10001)>
    # emps = Employee.objects.all()
    emps:QuerySet = mgr.all()
    # emps. 通过变量注释查看

    print(type(emps)) # <class 'django.db.models.query.QuerySet'> 没有查询数据库的语句，说明以上操作没有触发查询数据库。是惰性的，你要数据的时候才会查询数据库。

    print(emps[10:]) # <QuerySet [<Employee 10011 [Sluis Mary]> # ` FROM `employees` LIMIT 21 OFFSET 10;
    # print(mgr[10:]) # Manager对象不能切片，只能对查询集切片
print(f'加载结束 {__name__}')

返回查询集方法

以下方法可以通过manager调用返回结果集，结果集还可以调用以下方法

manager调用queryset
queryset调用queryset, 可以链式调用

名称	返回值类型	描述	不存在结果
all	QuerySet	所有，	空集
filter	QuerySet	过滤	空集
exclude	QuerySet	取反	空集
order_by	QuerySet	排序	空集
values	QuerySet	QuerySet调用values，结果集变字典 annotate调用values,结果变字典	空集
annotate	QuerySet	结果查询集

QuerySet 是惰性, 可迭代对象

filter

python
print(f'加载到 {__name__}')

import os
import django

# 1. 加载环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')

# 2. 启动django
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects # Manager 管增删改查
    print(mgr.filter(emp_no=10010)) # 关键字传参 <QuerySet [<Employee 10010 [Piveteau Duangkaew]>]>

print(f'加载结束 {__name__}')

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    emps:QuerySet = mgr.filter(pk=10010)
    print(emps)


print(f'加载结束 {__name__}')

exclude 关键字传参，不支持!=

python
print(f'加载到 {__name__}')

import os
import django

# 1. 加载环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')

# 2. 启动django
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects # Manager 管增删改查
    # print(mgr.filter(emp_no=10010)) # 关键字传参 <QuerySet [<Employee 10010 [Piveteau Duangkaew]>]>
    print(mgr.exclude(emp_no=10010)) # 关键字传参 <QuerySet [<Employee 10010 [Piveteau Duangkaew]>]>

print(f'加载结束 {__name__}')


WHERE NOT (`employees`.`emp_no` = 10010)

filter

python
print(f'加载到 {__name__}')

import os
import django

# 1. 加载环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')

# 2. 启动django
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects # Manager 管增删改查
    # print(mgr.filter(emp_no=10010)) # 关键字传参 <QuerySet [<Employee 10010 [Piveteau Duangkaew]>]>
    print(mgr.exclude(emp_no=10010).filter(pk=10020)) # 关键字传参 <QuerySet [<Employee 10010 [Piveteau Duangkaew]>]>

print(f'加载结束 {__name__}')

WHERE (NOT (`employees`.`emp_no` = 10010) AND `employees`.`emp_no` = 10020)
这个举例链示，直接filter即可

order_by

python
print(f'加载到 {__name__}')

import os
import django

# 1. 加载环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')

# 2. 启动django
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects # Manager 管增删改查
    # print(mgr.exclude(emp_no=10010).order_by('emp_no')) # 默认主键升序 ORDER BY `employees`.`emp_no`  ASC
    #print(mgr.exclude(emp_no=10010).order_by('-emp_no')) # 默认主键升序 ORDER BY `employees`.`emp_no` DESC
    #print(mgr.order_by('-emp_no')) # 主键降序
    emps:QuerySet = mgr.order_by('-pk') # 记不得主键，直接使用pk
    print(emps)
print(f'加载结束 {__name__}')

使用pk, 自动引用主键
order_by直接对manager使用。

values

list(emps.values()) 和 [emps.values()] 是有区别的。

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    emps:QuerySet = mgr.filter(emp_no=10010).order_by('-pk')
    print(emps.values()) # <QuerySet [{'emp_no': 10010, 'birth_date': datetime.date(1963, 6, 1), 'first_name': 'Duangkaew', 'last_name': 'Piveteau', 'gender': 'F'}]>
    print(list(emps.values())) # 返回列表套字典
    print([emps.values()]) # 返回列表套 列表套对象

print(f'加载结束 {__name__}')


WHERE `employees`.`emp_no` = 10010 ORDER BY `employees`.`emp_no` DESC

返回单个实例的方法

mgr调用，返回单个实例
queryset调用，返回单个实例

方法	描述	不存在结果
first()	返回首个顺序LIMIT 1	None
last()	返回最后一个逆序LIMIT 1	None
get()	严格只能有1条结果，多了就异常。所以一般使用在主键查询	异常
count()	统计	0
exist()	只要结果>=1行，就返回True.	False

first

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    emps:QuerySet = mgr.first()
    print(emps)


print(f'加载结束 {__name__}')

last


print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    emps:QuerySet = mgr.last()
    print(emps)


print(f'加载结束 {__name__}')


FROM `employees` ORDER BY `employees`.`emp_no` DESC LIMIT 1

get

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    # emps:QuerySet = mgr.filter(pk=10010).get() # 只返回1个
    emps:QuerySet = mgr.get(pk=10010)
    # emps:QuerySet = mgr.filter(pk=10050).get() # 异常
    # emps:QuerySet = mgr.exclude(pk=10050).get() # 异常
    print(emps)


print(f'加载结束 {__name__}')

count

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    # emps:QuerySet = mgr.count() # SELECT COUNT(*)
    # emps:QuerySet = mgr.filter(pk=10010).count() # SELECT COUNT(*) WHERE
    # emps:QuerySet = mgr.exclude(pk=10010).count() # SELECT COUNT(*) WHERE
    emps:QuerySet = mgr.filter(pk=10050).count() # SELECT COUNT(*) WHERE
    print(emps)


print(f'加载结束 {__name__}')

exists

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    # emps:QuerySet = mgr.exists() # 拿到1行就True  SELECT (1) AS `a` FROM `employees` LIMIT 1; args=() 不返回字段, 不浪费流量
    emps:QuerySet = mgr.filter(pk=10050).exists()
    print(emps)


print(f'加载结束 {__name__}')

lookup表达式

https://docs.djangoproject.com/en/3.2/ref/models/querysets/#field-lookups-1

lookup表达式 属性名__比较运算符 = 值

2个下划线

方法	描述	SQL
property__exact	等于
property__gt	大于
property__lt	小于
property__gte	大于等于	>=
property__lte	小于等于	<=
property__contains	包含, 前后百分号, 不要使用	`LIKE BINARY '%P%'`
property__startswith	前缀	`LIKE BINARY 'P%'`
property__endswith	后缀	`LIKE BINARY '%P'`
property__iexact	精确, 忽略大小写	`LIKE 'p'`
property__icontains	忽略大小写	`LIKE '%P%'`
property__istartswith	忽略大小写	`LIKE 'P%'`
property__iendswith	忽略大小写	`LIKE '%P'`
property__in=[x,x,x]	in 集合, 1端返回多端是一个id列表，使用多端的manager加上lookup表达式可以取出多端。	`WHERE ( x IN (10010, 10020, 10030)`

查询数据库要快，就需要使用索引，没有索引就全表扫描。

contains, 后缀匹配不会使用索引, 使用了效率低。

前缀才会使用索引。主键严格匹配最OK。

索引越多，对增删改, 均有影响。

exact

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    emps:QuerySet = mgr.filter(pk__exact=10010)
    print(emps)


print(f'加载结束 {__name__}')

gt

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    emps:QuerySet = mgr.filter(pk__gt=10010)
    print(emps)


print(f'加载结束 {__name__}')

AND1 filter(pk__gt=10010).filter(pk__lt=10015)


print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    emps:QuerySet = mgr.filter(pk__gt=10010).filter(pk__lt=10015)
    print(emps)


print(f'加载结束 {__name__}')

AND2 filter(pk__gt=10010,pk__lt=10015)


print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    emps:QuerySet = mgr.filter(pk__gt=10010,pk__lt=10015)
    print(emps)


print(f'加载结束 {__name__}')

contains

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    emps:QuerySet = mgr.filter(last_name__contains='P',pk__lt=10015)
    print(emps)


print(f'加载结束 {__name__}')

startswith

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    emps:QuerySet = mgr.filter(last_name__startswith='P',pk__lt=10015)
    print(emps)


print(f'加载结束 {__name__}')

istartswith

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    emps:QuerySet = mgr.filter(last_name__istartswith='p',pk__lt=10015)
    print(emps)


print(f'加载结束 {__name__}')

in

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    emps:QuerySet = mgr.filter(pk__in=[10010,10020,10030],pk__lt=10015)
    print(emps)


print(f'加载结束 {__name__}')

Q对象 AND/OR操作

语法	通用方法	Q对象
AND	filter().filter() 或 filter(表达式1,表达式2,) 或 filter() & filter()	filter( Q(key=value) & Q(key=value)), Q & Q 实例，魔数方法, 运算符重载即可。
OR	property__in=[] 或 filter() \| filter()	filter( Q(key=value) \| Q(key=value))
not		~ Q(key=value), ~ (Q(key=value) & Q(key=value)) 相当于 ~ Q(key=value) & ~ Q(key=value)
混合		only Q对象

AND

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))
    emps: QuerySet = mgr.filter(pk__in=[10010, 10020, 10030], pk__lt=10015)  # 2个关键字传参, 常用推荐
    print(emps)
    emps: QuerySet = mgr.filter(pk__in=[10010, 10020, 10030]).filter(pk__lt=10015)  # 2个关键字传参, 常用推荐
    print(emps)
    emps: QuerySet = mgr.filter(pk__in=[10010, 10020, 10030]) & mgr.filter(pk__lt=10015)  # 2个查询集与
    print(emps)
    emps: QuerySet = mgr.filter(Q(pk__in=[10010, 10020, 10030]) & Q(pk__lt=10015))  # 实例与运算，位置传参，啰嗦
    print(emps)
    emps: QuerySet = mgr.filter(pk__in=[10010, 10020, 10030]).filter(Q(pk__lt=10015))  # Q实例按位置传递参数, 不建议这样写
    print(emps)
print(f'加载结束 {__name__}')

所有结果相同

OR操作

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))

    emps: QuerySet = mgr.filter(Q(pk__in=[10010, 10020, 10030]) | Q(pk__lt=10015))
    print(emps)
    emps: QuerySet = mgr.filter(pk__in=[10010, 10020, 10030]) | mgr.filter(pk__lt=10015)
    print(emps)
    
print(f'加载结束 {__name__}')

NOT

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    mgr = Employee.objects

    print(type(mgr))
    print(type(mgr.all()))

    emps: QuerySet = mgr.filter(~(Q(pk__in=[10010, 10020, 10030]) | Q(pk__lt=10015)))
    print(emps)

    emps: QuerySet = mgr.filter(~ Q(pk__in=[10010, 10020, 10030]) & ~ Q(pk__lt=10015))
    print(emps)
print(f'加载结束 {__name__}')

聚合、分组

gruop aggregate() 返回字典，方便使用

count(), 最常用

python
from django.db.models import Count

print(f'加载到 {__name__}')

import os
import django

# 1. 加载环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')

# 2. 启动django
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee
from django.db.models.query import QuerySet
if __name__ == '__main__':
    mgr = Employee.objects # Manager 管增删改查
    print(mgr.filter(pk__gte=10010).count())                  # 11
    """
    聚合. 不分组，即过滤的结果，当成1组，1组出1个结果。。
    """
    print(mgr.filter(pk__gte=10010).aggregate(Count('pk'))) # {'pk__count': 11} 返回 pk__count, pk是后面的pk, __ 固定，count是Count                      按位传参

    print(mgr.filter(pk__gte=10010).aggregate(Count('emp_no'))) # {'emp_no__count': 11} 返回 emp_no__count, emp_no是后面的emp_no, __ 固定，count是Count  按位传参

    print(mgr.filter(pk__gte=10010).aggregate(c=Count('pk'))) # 上面的key太长了，换key {'c': 11}                                                        关键字传参

    from django.db.models import Q, Count,Max,Min,Sum,Avg
    # 整个表取合，1组出1个结果。最大；最小；总和；平均；
    print(mgr.aggregate(Max('pk'), Min('pk'), Sum('pk'), Avg('pk'))) # {'pk__max': 10020, 'pk__min': 10001, 'pk__sum': 200210, 'pk__avg': 10010.5}

    """
    分组聚合
    按工号分组，即20个分组。
    按性别分组，2个组。
    """
    print(mgr.filter(pk__gte=10010).aggregate(Count('pk'))) #  {'pk__count': 11}
    # 返回1个值，返回字典的values
    print(mgr.filter(pk__gte=10010).aggregate(Count('pk')).values()) #  dict_values([11])

    # 分组
    print(mgr.filter(pk__gte=10010).values() ) #  转字典
    print(mgr.filter(pk__gte=10010).values('pk') ) #  仅保留

    # 分组，values在aggregate之前, 结果 是聚合
    print(mgr.filter(pk__gte=10010).values('pk').aggregate(Count('pk')) ,'--------') # {'pk__count': 11} -------- 对保留的pk进行聚合。
    # 分组，values在annotate之前
    print(mgr.filter(pk__gte=10010).values('pk').annotate(Count('pk')) ) #  基于 pk分组 <QuerySet [{'pk': 10010, 'pk__count': 1}, {'pk': 10011, 'pk__count': 1}, {'pk': 10012, 'pk__count': 1}, {'pk': 10013, 'pk__count': 1}, {'pk': 10014, 'pk__count': 1}, {'pk': 10015, 'pk__count': 1}, {'pk': 10016, 'pk__count': 1}, {'pk': 10017, 'pk__count': 1}, {'pk': 10018, 'pk__count': 1}, {'pk': 10019, 'pk__count': 1}, {'pk': 10020, 'pk__count': 1}]>
    print(mgr.filter(pk__gte=10010).values('gender').annotate(Count('pk')),'-->' ) #  基于 gender分组 <QuerySet [{'gender': 'F', 'pk__count': 4}, {'gender': 'M', 'pk__count': 7}]>

    # 分组聚合
    for x in     mgr.filter(pk__gte=10010).values('gender').annotate(Count('pk')):
        print(x,type(x)) # {'gender': 'M', 'pk__count': 7} <class 'dict'>

    # 仅annotate聚合; 以主键分组，非分组字段也拿出来
    """ 默认主键分组
        SELECT
        `employees`.`emp_no`,
        `employees`.`birth_date`,
        `employees`.`first_name`,
        `employees`.`last_name`,
        `employees`.`gender`,
        COUNT( `employees`.`emp_no` ) AS `pk__count` 
        FROM
            `employees` 
        WHERE
            `employees`.`emp_no` >= 10010 
        GROUP BY
            `employees`.`emp_no`;
    """
    # annotate 获取实例
    for x in     mgr.filter(pk__gte=10010).annotate(Count('pk')):
        print(x,type(x)) # <Employee 10010 [Piveteau Duangkaew]> <class 'employee.models.Employee'>
    # annotate 获取实例 修改字典key.
    for x in     mgr.filter(pk__gte=10010).annotate(c=Count('pk')):
        print(x,type(x)) # <Employee 10010 [Piveteau Duangkaew]> <class 'employee.models.Employee'>
        print(x.__dict__,type(x),x.c) # <Employee 10010 [Piveteau Duangkaew]> <class 'employee.models.Employee'>

    # annotate 后，转字典
    for x in     mgr.filter(pk__gte=10010).annotate(c=Count('pk')).values():
        print(x,type(x)) # {'emp_no': 10020, 'birth_date': datetime.date(1952, 12, 24), 'first_name': 'Mayuko', 'last_name': 'Warwick', 'gender': 'M', 'c': 1} <class 'dict'>

    # annotate 后过滤
    for x in     mgr.filter(pk__gte=10010).annotate(c=Count('pk')).values('first_name','pk'):
        print(x,type(x)) # {'emp_no': 10020, 'birth_date': datetime.date(1952, 12, 24), 'first_name': 'Mayuko', 'last_name': 'Warwick', 'gender': 'M', 'c': 1} <class 'dict'>

print(f'加载结束 {__name__}')

默认主键分组 annotate

而values().annotate 前面可以指定基于哪个来分组

以上的字段属性就封闭在Employee实例中，获取属性 print(x.__dict__,type(x),x.c)

分组聚合简写

python
    # print(emps.filter(emp_no__lte=10010).aggregate(c=Count('emp_no'))) # select count(*) # 不分组聚合 select count(*)
    print(emps.filter(emp_no__lte=10010).values('gender').annotate(c=Count('pk'))) #  分组聚合 select count(*) group by gender

理解表结构

当我们拿到旧的项目如下结构

sql
CREATE TABLE `employees` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `salaries` (
  `emp_no` int NOT NULL,
  `salary` int 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

上面员工表
下面工资表
主键需要惟一, 所以下表，只有emp_no,from_date 员工和时间联合才可以惟一标识此行。

员工工资月份
1 100 1
1 100 2
1 100 3
2 200 1
2 200 2
2 200 3
salaries emp_no 依赖主键 employees.emp_no，当删除 employees.emp_no 时，CASCADE表示自己也删除。更新主键，保证不冲突，更新之后，这里salaries没有写UPDATE策略。表示不更新。

员工	工资	月份
1	100	1
1	100	2
1	100	3
2	200	1
2	200	2
2	200	3

准备modal类

联合主键, django不支持。主键仅支持单列。

主键可以使用辅助键。

数据库改造，去掉联合主键，并添加新字段。

去掉联合主键，emp_no自增；
删除外键
添加id主键，自增
添加外键 emp_no

测试环境可以修改, 生产环境不可以

python
from django.db import models

class Gender(models.TextChoices):  # get_gender_display() 网页获取展示名
    MALE = 'M', '男'
    FEMALE = 'F', '女'


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

    emp_no = models.BigIntegerField(primary_key=True, verbose_name='工号')
    birth_date = models.DateField(verbose_name='生日')
    first_name = models.CharField(max_length=14, verbose_name='名')
    last_name = models.CharField(max_length=16, verbose_name='姓')
    gender = models.CharField(choices=Gender.choices, verbose_name='性别')

    # hire_date = models.DateField(verbose_name='雇佣时间')

    @property
    def name(self):
        return f'[{self.last_name} {self.first_name}]'

    def __repr__(self):
        return f'<Employ {self.emp_no} {self.name}>'

    __str__ = __repr__


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

    # id = models.AutoField(primary_key=True)
    
    """
    外键。django仅支持单字段的主键。
    默认没有字段定义 primary_key, 自动添加自增id的主键。当前类就是使用这个方式
    有字段定义 primary_key, 就是默认的主键。
    """
    # emp_no = models.IntegerField() # 需要外键
    # emp_no = models.ForeignKey(Employee) # 同项目，直接写类
    # emp_no = models.ForeignKey('a_module.Employee') # 跨项目, 模块，类
    emp_no = models.ForeignKey('Employee', on_delete=models.CASCADE)  # 同项目的类名
    salary = models.IntegerField(verbose_name='工资')
    from_date = models.DateField()
    to_date = models.DateField()

注意：

表中有id
表中仅单个主键
表中emp_no是E.emp_no外键

bash
mysql> DESC salaries;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| emp_no    | int  | NO   | PRI | NULL    |       |
| salary    | int  | NO   |     | NULL    |       |
| from_date | date | NO   | PRI | NULL    |       |
| to_date   | date | NO   |     | NULL    |       |
+-----------+------+------+-----+---------+-------+
4 rows in set (0.01 sec)


mysql> SHOW CREATE TABLE salaries;
| salaries | CREATE TABLE `salaries` (
  `emp_no` int NOT NULL,
  `salary` int NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`) USING BTREE,
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC |


#删除外键
mysql> ALTER TABLE salaries DROP FOREIGN KEY salaries_ibfk_1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

#删除主键
mysql> ALTER TABLE salaries DROP PRIMARY KEY;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

#查看
mysql> DESC salaries;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| emp_no    | int  | NO   |     | NULL    |       |
| salary    | int  | NO   |     | NULL    |       |
| from_date | date | NO   |     | NULL    |       |
| to_date   | date | NO   |     | NULL    |       |
+-----------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)

#添加自增ID
ALTER TABLE salaries ADD id  BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '默认自增id';

# 添加数据
INSERT INTO `salaries`(emp_no,salary,from_date,to_date) VALUES 
(10001,60117,'1986-06-26','1987-06-26'),
(10001,62102,'1987-06-26','1988-06-25'),
(10001,66074,'1988-06-25','1989-06-25'),
(10001,66596,'1989-06-25','1990-06-25'),
(10001,66961,'1990-06-25','1991-06-25'),
(10001,71046,'1991-06-25','1992-06-24'),
(10001,74333,'1992-06-24','1993-06-24'),
(10001,75286,'1993-06-24','1994-06-24'),
(10001,75994,'1994-06-24','1995-06-24'),
(10001,76884,'1995-06-24','1996-06-23'),
(10001,80013,'1996-06-23','1997-06-23'),
(10001,81025,'1997-06-23','1998-06-23'),
(10001,81097,'1998-06-23','1999-06-23'),
(10001,84917,'1999-06-23','2000-06-22'),
(10001,85112,'2000-06-22','2001-06-22'),
(10001,85097,'2001-06-22','2002-06-22'),
(10001,88958,'2002-06-22','9999-01-01'),
(10002,65828,'1996-08-03','1997-08-03'),
(10002,65909,'1997-08-03','1998-08-03'),
(10002,67534,'1998-08-03','1999-08-03'),
(10002,69366,'1999-08-03','2000-08-02'),
(10002,71963,'2000-08-02','2001-08-02'),
(10002,72527,'2001-08-02','9999-01-01'),
(10003,40006,'1995-12-03','1996-12-02'),
(10003,43616,'1996-12-02','1997-12-02'),
(10003,43466,'1997-12-02','1998-12-02'),
(10003,43636,'1998-12-02','1999-12-02'),
(10003,43478,'1999-12-02','2000-12-01'),
(10003,43699,'2000-12-01','2001-12-01'),
(10003,43311,'2001-12-01','9999-01-01'),
(10004,40054,'1986-12-01','1987-12-01'),
(10004,42283,'1987-12-01','1988-11-30'),
(10004,42542,'1988-11-30','1989-11-30'),
(10004,46065,'1989-11-30','1990-11-30'),
(10004,48271,'1990-11-30','1991-11-30'),
(10004,50594,'1991-11-30','1992-11-29'),
(10004,52119,'1992-11-29','1993-11-29'),
(10004,54693,'1993-11-29','1994-11-29'),
(10004,58326,'1994-11-29','1995-11-29'),
(10004,60770,'1995-11-29','1996-11-28'),
(10004,62566,'1996-11-28','1997-11-28'),
(10004,64340,'1997-11-28','1998-11-28'),
(10004,67096,'1998-11-28','1999-11-28'),
(10004,69722,'1999-11-28','2000-11-27'),
(10004,70698,'2000-11-27','2001-11-27'),
(10004,74057,'2001-11-27','9999-01-01'),
(10005,78228,'1989-09-12','1990-09-12'),
(10005,82621,'1990-09-12','1991-09-12'),
(10005,83735,'1991-09-12','1992-09-11'),
(10005,85572,'1992-09-11','1993-09-11'),
(10005,85076,'1993-09-11','1994-09-11'),
(10005,86050,'1994-09-11','1995-09-11'),
(10005,88448,'1995-09-11','1996-09-10'),
(10005,88063,'1996-09-10','1997-09-10'),
(10005,89724,'1997-09-10','1998-09-10'),
(10005,90392,'1998-09-10','1999-09-10'),
(10005,90531,'1999-09-10','2000-09-09'),
(10005,91453,'2000-09-09','2001-09-09'),
(10005,94692,'2001-09-09','9999-01-01'),
(10006,40000,'1990-08-05','1991-08-05'),
(10006,42085,'1991-08-05','1992-08-04'),
(10006,42629,'1992-08-04','1993-08-04'),
(10006,45844,'1993-08-04','1994-08-04'),
(10006,47518,'1994-08-04','1995-08-04'),
(10006,47917,'1995-08-04','1996-08-03'),
(10006,52255,'1996-08-03','1997-08-03'),
(10006,53747,'1997-08-03','1998-08-03'),
(10006,56032,'1998-08-03','1999-08-03'),
(10006,58299,'1999-08-03','2000-08-02'),
(10006,60098,'2000-08-02','2001-08-02'),
(10006,59755,'2001-08-02','9999-01-01'),
(10007,56724,'1989-02-10','1990-02-10'),
(10007,60740,'1990-02-10','1991-02-10'),
(10007,62745,'1991-02-10','1992-02-10'),
(10007,63475,'1992-02-10','1993-02-09'),
(10007,63208,'1993-02-09','1994-02-09'),
(10007,64563,'1994-02-09','1995-02-09'),
(10007,68833,'1995-02-09','1996-02-09'),
(10007,70220,'1996-02-09','1997-02-08'),
(10007,73362,'1997-02-08','1998-02-08'),
(10007,75582,'1998-02-08','1999-02-08'),
(10007,79513,'1999-02-08','2000-02-08'),
(10007,80083,'2000-02-08','2001-02-07'),
(10007,84456,'2001-02-07','2002-02-07'),
(10007,88070,'2002-02-07','9999-01-01'),
(10008,46671,'1998-03-11','1999-03-11'),
(10008,48584,'1999-03-11','2000-03-10'),
(10008,52668,'2000-03-10','2000-07-31'),
(10009,60929,'1985-02-18','1986-02-18'),
(10009,64604,'1986-02-18','1987-02-18'),
(10009,64780,'1987-02-18','1988-02-18'),
(10009,66302,'1988-02-18','1989-02-17'),
(10009,69042,'1989-02-17','1990-02-17'),
(10009,70889,'1990-02-17','1991-02-17'),
(10009,71434,'1991-02-17','1992-02-17'),
(10009,74612,'1992-02-17','1993-02-16'),
(10009,76518,'1993-02-16','1994-02-16'),
(10009,78335,'1994-02-16','1995-02-16'),
(10009,80944,'1995-02-16','1996-02-16'),
(10009,82507,'1996-02-16','1997-02-15'),
(10009,85875,'1997-02-15','1998-02-15'),
(10009,89324,'1998-02-15','1999-02-15'),
(10009,90668,'1999-02-15','2000-02-15'),
(10009,93507,'2000-02-15','2001-02-14'),
(10009,94443,'2001-02-14','2002-02-14'),
(10009,94409,'2002-02-14','9999-01-01'),
(10010,72488,'1996-11-24','1997-11-24'),
(10010,74347,'1997-11-24','1998-11-24'),
(10010,75405,'1998-11-24','1999-11-24'),
(10010,78194,'1999-11-24','2000-11-23'),
(10010,79580,'2000-11-23','2001-11-23'),
(10010,80324,'2001-11-23','9999-01-01'),
(10011,42365,'1990-01-22','1991-01-22'),
(10011,44200,'1991-01-22','1992-01-22'),
(10011,48214,'1992-01-22','1993-01-21'),
(10011,50927,'1993-01-21','1994-01-21'),
(10011,51470,'1994-01-21','1995-01-21'),
(10011,54545,'1995-01-21','1996-01-21'),
(10011,56753,'1996-01-21','1996-11-09'),
(10012,40000,'1992-12-18','1993-12-18'),
(10012,41867,'1993-12-18','1994-12-18'),
(10012,42318,'1994-12-18','1995-12-18'),
(10012,44195,'1995-12-18','1996-12-17'),
(10012,46460,'1996-12-17','1997-12-17'),
(10012,46485,'1997-12-17','1998-12-17'),
(10012,47364,'1998-12-17','1999-12-17'),
(10012,51122,'1999-12-17','2000-12-16'),
(10012,54794,'2000-12-16','2001-12-16'),
(10012,54423,'2001-12-16','9999-01-01'),
(10013,40000,'1985-10-20','1986-10-20'),
(10013,40623,'1986-10-20','1987-10-20'),
(10013,40561,'1987-10-20','1988-10-19'),
(10013,40306,'1988-10-19','1989-10-19'),
(10013,43569,'1989-10-19','1990-10-19'),
(10013,46305,'1990-10-19','1991-10-19'),
(10013,47118,'1991-10-19','1992-10-18'),
(10013,50351,'1992-10-18','1993-10-18'),
(10013,49887,'1993-10-18','1994-10-18'),
(10013,53957,'1994-10-18','1995-10-18'),
(10013,57590,'1995-10-18','1996-10-17'),
(10013,59228,'1996-10-17','1997-10-17'),
(10013,59571,'1997-10-17','1998-10-17'),
(10013,63274,'1998-10-17','1999-10-17'),
(10013,63352,'1999-10-17','2000-10-16'),
(10013,66744,'2000-10-16','2001-10-16'),
(10013,68901,'2001-10-16','9999-01-01'),
(10014,46168,'1993-12-29','1994-12-29'),
(10014,48242,'1994-12-29','1995-12-29'),
(10014,47921,'1995-12-29','1996-12-28'),
(10014,50715,'1996-12-28','1997-12-28'),
(10014,53228,'1997-12-28','1998-12-28'),
(10014,53962,'1998-12-28','1999-12-28'),
(10014,56937,'1999-12-28','2000-12-27'),
(10014,59142,'2000-12-27','2001-12-27'),
(10014,60598,'2001-12-27','9999-01-01'),
(10015,40000,'1992-09-19','1993-08-22'),
(10016,70889,'1998-02-11','1999-02-11'),
(10016,72946,'1999-02-11','2000-02-11'),
(10016,76826,'2000-02-11','2001-02-10'),
(10016,76381,'2001-02-10','2002-02-10'),
(10016,77935,'2002-02-10','9999-01-01'),
(10017,71380,'1993-08-03','1994-08-03'),
(10017,75538,'1994-08-03','1995-08-03'),
(10017,79510,'1995-08-03','1996-08-02'),
(10017,82163,'1996-08-02','1997-08-02'),
(10017,86157,'1997-08-02','1998-08-02'),
(10017,89619,'1998-08-02','1999-08-02'),
(10017,91985,'1999-08-02','2000-08-01'),
(10017,96122,'2000-08-01','2001-08-01'),
(10017,98522,'2001-08-01','2002-08-01'),
(10017,99651,'2002-08-01','9999-01-01'),
(10018,55881,'1987-04-03','1988-04-02'),
(10018,59206,'1988-04-02','1989-04-02'),
(10018,61361,'1989-04-02','1990-04-02'),
(10018,61648,'1990-04-02','1991-04-02'),
(10018,61217,'1991-04-02','1992-04-01'),
(10018,61244,'1992-04-01','1993-04-01'),
(10018,63286,'1993-04-01','1994-04-01'),
(10018,65739,'1994-04-01','1995-04-01'),
(10018,67519,'1995-04-01','1996-03-31'),
(10018,69276,'1996-03-31','1997-03-31'),
(10018,72585,'1997-03-31','1998-03-31'),
(10018,72804,'1998-03-31','1999-03-31'),
(10018,76957,'1999-03-31','2000-03-30'),
(10018,80305,'2000-03-30','2001-03-30'),
(10018,84541,'2001-03-30','2002-03-30'),
(10018,84672,'2002-03-30','9999-01-01'),
(10019,44276,'1999-04-30','2000-04-29'),
(10019,46946,'2000-04-29','2001-04-29'),
(10019,46775,'2001-04-29','2002-04-29'),
(10019,50032,'2002-04-29','9999-01-01'),
(10020,40000,'1997-12-30','1998-12-30'),
(10020,40647,'1998-12-30','1999-12-30'),
(10020,43800,'1999-12-30','2000-12-29'),
(10020,44927,'2000-12-29','2001-12-29'),
(10020,47017,'2001-12-29','9999-01-01'),
(10021,55025,'1988-02-10','1989-02-09'),
(10021,56399,'1989-02-09','1990-02-09'),
(10021,59700,'1990-02-09','1991-02-09'),
(10021,60851,'1991-02-09','1992-02-09'),
(10021,61117,'1992-02-09','1993-02-08');

#查看
mysql> SELECT * FROM salaries WHERE id < 10;
+--------+--------+------------+------------+----+
| emp_no | salary | from_date  | to_date    | id |
+--------+--------+------------+------------+----+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |  1 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |  2 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |  3 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |  4 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |  5 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |  6 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |  7 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |  8 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |  9 |
+--------+--------+------------+------------+----+
9 rows in set (0.00 sec)

# employee准备
INSERT INTO `employees` VALUES (10001,'1953-09-02','Georgi','Facello','M','1986-06-26'),
(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'),
(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'),
(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'),
(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12'),
(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02'),
(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10'),
(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15'),
(10009,'1952-04-19','Sumant','Peac','F','1985-02-18'),
(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24'),
(10011,'1953-11-07','Mary','Sluis','F','1990-01-22'),
(10012,'1960-10-04','Patricio','Bridgland','M','1992-12-18'),
(10013,'1963-06-07','Eberhardt','Terkki','M','1985-10-20'),
(10014,'1956-02-12','Berni','Genin','M','1987-03-11'),
(10015,'1959-08-19','Guoxiang','Nooteboom','M','1987-07-02'),
(10016,'1961-05-02','Kazuhito','Cappelletti','M','1995-01-27'),
(10017,'1958-07-06','Cristinel','Bouloucos','F','1993-08-03'),
(10018,'1954-06-19','Kazuhide','Peha','F','1987-04-03'),
(10019,'1953-01-23','Lillian','Haddadi','M','1999-04-30'),
(10020,'1952-12-24','Mayuko','Warwick','M','1991-01-26');

注意：外键 emp_no 在数据库不加时, 代码定义了模型，也可以。

数据库加了更双保险。

此处不会加外键，下面多对多时，将会在数据库中，添加。以示区别。

外键引用的真实数据库字段

问题

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects

    print(smgr.all())
print(f'加载结束 {__name__}')

python
django.db.utils.OperationalError: (1054, "Unknown column 'salaries.emp_no_id' in 'field list'")
# 字段列表中没有emp_no_id

修改数据库字段

问题：数据库字段和类属性不一样，不方便看。

由于模型定义了外键, 会将外键的字段emp_no，自动关联一个对象。表示关系。关联Employee对象。数据库中会使用字段_id

bash
# 修改字段名
ALTER TABLE salaries  RENAME COLUMN emp_no TO emp_no_id;
 
# 执行结果
<QuerySet [<Salary: Salary object (1)>, <Salary: Salary object (2)>, <Salary: Salary object (3)>, <Salary: Salary object (4)>, <Salary: Salary object (5)>, <Salary: Salary object (6)>, <Salary: Salary object (7)>, <Salary: Salary object (8)>, <Salary: Salary object (9)>, <Salary: Salary object (10)>, <Salary: Salary object (11)>, <Salary: Salary object (12)>, <Salary: Salary object (13)>, <Salary: Salary object (14)>, <Salary: Salary object (15)>, <Salary: Salary object (16)>, <Salary: Salary object (17)>, <Salary: Salary object (18)>, <Salary: Salary object (19)>, <Salary: Salary object (20)>, '...(remaining elements truncated)...']>
加载结束 __main__

现在如果不修改字段

python
# 修改字段名
ALTER TABLE salaries  RENAME COLUMN emp_no_id TO emp_no;

# 代码中，指定字段名
class Salary(models.Model):
    class Meta:
        db_table = 'salaries'

    # id = models.AutoField(primary_key=True)

    """
    外键。django仅支持单字段的主键。
    默认没有字段定义 primary_key, 自动添加自增id的主键。当前类就是使用这个方式
    有字段定义 primary_key, 就是默认的主键。
    """
    # emp_no = models.IntegerField() # 需要外键
    # emp_no = models.ForeignKey(Employee) # 同项目，直接写类
    # emp_no = models.ForeignKey('a_module.Employee') # 跨项目, 模块，类

    # 由于模型定义了外键, 会将外键的字段emp_no，自动关联一个对象。表示关系。关联Employee对象。
    emp_no = models.ForeignKey('Employee', on_delete=models.CASCADE, db_column='emp_no')  # 同项目的类名

db_column='emp_no'

元编程原理


print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    print('Employee')
    print(*Employee.__dict__.items(),sep='\n')
    print()
    print('Salary')
    print(*Salary.__dict__.items(),sep='\n')



print(f'加载结束 {__name__}')

结果

python
加载到 __main__
Employee
('__module__', 'employee.models')
('name', <property object at 0x0000028E502E5D10>)
('__repr__', <function Employee.__repr__ at 0x0000028E502EB160>)
('__str__', <function Employee.__repr__ at 0x0000028E502EB160>)
('__doc__', 'Employee(emp_no, birth_date, first_name, last_name, gender)')
('_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 0x0000028E502ED1F0>)
('birth_date', <django.db.models.query_utils.DeferredAttribute object at 0x0000028E502ED250>)
('get_next_by_birth_date', functools.partialmethod(<function Model._get_next_or_previous_by_FIELD at 0x0000028E4FCA4E50>, , 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 0x0000028E4FCA4E50>, , field=<django.db.models.fields.DateField: birth_date>, is_next=False))
('first_name', <django.db.models.query_utils.DeferredAttribute object at 0x0000028E502ED3A0>)
('last_name', <django.db.models.query_utils.DeferredAttribute object at 0x0000028E502ED400>)
('gender', <django.db.models.query_utils.DeferredAttribute object at 0x0000028E502ED460>)
"""
gender获取名字
"""
('get_gender_display', functools.partialmethod(<function Model._get_FIELD_display at 0x0000028E4FCA4DC0>, , field=<django.db.models.fields.CharField: gender>))
('objects', <django.db.models.manager.ManagerDescriptor object at 0x0000028E502DDD30>)

"""
默认在1端加了一个 ReverseManyToOneDescriptor 反向多对1，即多端给1端添加的，用于1端关联多端。
可以通过在多端通过relationship 定义此属性名。
"""
('salary_set', <django.db.models.fields.related_descriptors.ReverseManyToOneDescriptor object at 0x0000028E502B9970>)

Salary
('__module__', 'employee.models')
('__str__', <function Salary.__str__ at 0x0000028E502CCF70>)
('__doc__', 'Salary(id, emp_no, salary, from_date, to_date)')
('_meta', <Options for Salary>)
('DoesNotExist', <class 'employee.models.Salary.DoesNotExist'>)
('MultipleObjectsReturned', <class 'employee.models.Salary.MultipleObjectsReturned'>)
"""
加了2个属性。
"""
# Attribute 对应字段
('emp_no_id', <django.db.models.fields.related_descriptors.ForeignKeyDeferredAttribute object at 0x0000028E502DDBE0>)

# ManyToOneDescriptor 多对1的描述器，关联实例
('emp_no', <django.db.models.fields.related_descriptors.ForwardManyToOneDescriptor object at 0x0000028E502DDA30>)
('salary', <django.db.models.query_utils.DeferredAttribute object at 0x0000028E502DD700>)
('from_date', <django.db.models.query_utils.DeferredAttribute object at 0x0000028E502DD760>)
('get_next_by_from_date', functools.partialmethod(<function Model._get_next_or_previous_by_FIELD at 0x0000028E4FCA4E50>, , 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 0x0000028E4FCA4E50>, , field=<django.db.models.fields.DateField: from_date>, is_next=False))
('to_date', <django.db.models.query_utils.DeferredAttribute object at 0x0000028E502DD460>)
('get_next_by_to_date', functools.partialmethod(<function Model._get_next_or_previous_by_FIELD at 0x0000028E4FCA4E50>, , 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 0x0000028E4FCA4E50>, , field=<django.db.models.fields.DateField: to_date>, is_next=False))
('id', <django.db.models.query_utils.DeferredAttribute object at 0x0000028E502D2D60>)
('objects', <django.db.models.manager.ManagerDescriptor object at 0x0000028E502D2FA0>)
加载结束 __main__

多查1 [查n回]

10002号员工的工资和姓名

查询多端实例

如果知道员工id 10002，从多端查询 id


print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects

    # print(smgr.all())
    print(smgr.filter(emp_no=10002))
print(f'加载结束 {__name__}')

结果是, 1个语句，1个结果。

bash
加载到 __main__
(0.000) 
                SELECT VERSION(),
                       @@sql_mode,
                       @@default_storage_engine,
                       @@sql_auto_is_null,
                       @@lower_case_table_names,
                       CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
            ; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.016) SELECT `salaries`.`id`, `salaries`.`emp_no`, `salaries`.`salary`, `salaries`.`from_date`, `salaries`.`to_date` FROM `salaries` WHERE `salaries`.`emp_no` = 10002 LIMIT 21; args=(10002,)
<QuerySet [<Salary: Salary object (18)>, <Salary: Salary object (19)>, <Salary: Salary object (20)>, <Salary: Salary object (21)>, <Salary: Salary object (22)>, <Salary: Salary object (23)>]>
加载结束 __main__


    def __str__(self):
        return "<Salary {},{}>".format(self.pk,  self.salary)

1次查询


(0.000) SELECT `salaries`.`id`, `salaries`.`emp_no`, `salaries`.`salary`, `salaries`.`from_date`, `salaries`.`to_date` FROM `salaries` WHERE `salaries`.`emp_no` = 10002 LIMIT 21; args=(10002,)
<QuerySet [<Salary: <Salary 18,65828>>, <Salary: <Salary 19,65909>>, <Salary: <Salary 20,67534>>, <Salary: <Salary 21,69366>>, <Salary: <Salary 22,71963>>, <Salary: <Salary 23,72527>>]>

多端打印emp_no时

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

    # id = models.AutoField(primary_key=True)

    """
    外键。django仅支持单字段的主键。
    默认没有字段定义 primary_key, 自动添加自增id的主键。当前类就是使用这个方式
    有字段定义 primary_key, 就是默认的主键。
    """
    # emp_no = models.IntegerField() # 需要外键
    # emp_no = models.ForeignKey(Employee) # 同项目，直接写类
    # emp_no = models.ForeignKey('a_module.Employee') # 跨项目, 模块，类

    # 由于模型定义了外键, 会将外键的字段emp_no，自动关联一个对象。表示关系。关联Employee对象。
    emp_no = models.ForeignKey('Employee', on_delete=models.CASCADE, db_column='emp_no')  # 同项目的类名
    salary = models.IntegerField(verbose_name='工资')
    from_date = models.DateField()
    to_date = models.DateField()

+    def __str__(self):
+        return "<Salary {},{},{}>".format(self.pk, self.emp_no, self.salary)

输出结果

python
加载到 __main__
(0.000) 
                SELECT VERSION(),
                       @@sql_mode,
                       @@default_storage_engine,
                       @@sql_auto_is_null,
                       @@lower_case_table_names,
                       CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
            ; args=None
(0.016) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.000) SELECT `salaries`.`id`, `salaries`.`emp_no`, `salaries`.`salary`, `salaries`.`from_date`, `salaries`.`to_date` FROM `salaries` WHERE `salaries`.`emp_no` = 10002 LIMIT 21; args=(10002,)
#1 employee
(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
(0.016) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
#6 employee
(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
# 18-23 6个
<QuerySet [<Salary: <Salary 18,<Employ 10002 [Simmel Bezalel]>,65828>>, <Salary: <Salary 19,<Employ 10002 [Simmel Bezalel]>,65909>>, <Salary: <Salary 20,<Employ 10002 [Simmel Bezalel]>,67534>>, <Salary: <Salary 21,<Employ 10002 [Simmel Bezalel]>,69366>>, <Salary: <Salary 22,<Employ 10002 [Simmel Bezalel]>,71963>>, <Salary: <Salary 23,<Employ 10002 [Simmel Bezalel]>,72527>>]>
加载结束 __main__

注意：每个结果对应一次查询, 刚好6次查询

pk, salary 正常

中间的emp_no是 <Employ 10002 [Simmel Bezalel]> 说明是实例，employee实例表达是这样的。

这样的问题，相同的查询重复了6次。

现在打印每个用户


if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects

    # print(smgr.all())
    # print(smgr.filter(emp_no=10002))
    for x in smgr.filter(emp_no=10002):
        print(x.emp_no.name)

执行结果

python
(0.000) SELECT `salaries`.`id`, `salaries`.`emp_no`, `salaries`.`salary`, `salaries`.`from_date`, `salaries`.`to_date` FROM `salaries` WHERE `salaries`.`emp_no` = 10002; args=(10002,)
[Simmel Bezalel]
[Simmel Bezalel]
[Simmel Bezalel]
[Simmel Bezalel]
[Simmel Bezalel]
[Simmel Bezalel]
加载结束 __main__
(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
(0.016) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)

如果不打印日志，你以为速度好，你查询了n次才打开了数据。

1查多 [查1回]

10002号员工的工资和姓名

查1个

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects

    emp = emgr.get(pk=10002)
    print(emp.pk, emp.name)


print(f'加载结束 {__name__}')

结果


(0.016) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
10002 [Simmel Bezalel]
加载结束 __main__

借助关联获取多个


print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects

    emp = emgr.get(pk=10002)
    print(emp.salary_set) #惰性
    print(emp.salary_set.all()) #惰性


print(f'加载结束 {__name__}')

结果


(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
(0.000) SELECT `salaries`.`id`, `salaries`.`emp_no`, `salaries`.`salary`, `salaries`.`from_date`, `salaries`.`to_date` FROM `salaries` WHERE `salaries`.`emp_no` = 10002 LIMIT 21; args=(10002,)
employee.Salary.None
<QuerySet [<Salary: <Salary 18,65828>>, <Salary: <Salary 19,65909>>, <Salary: <Salary 20,67534>>, <Salary: <Salary 21,69366>>, <Salary: <Salary 22,71963>>, <Salary: <Salary 23,72527>>]>
加载结束 __main__

修改反向多对1的键

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

    # id = models.AutoField(primary_key=True)

    """
    外键。django仅支持单字段的主键。
    默认没有字段定义 primary_key, 自动添加自增id的主键。当前类就是使用这个方式
    有字段定义 primary_key, 就是默认的主键。
    """
    # emp_no = models.IntegerField() # 需要外键
    # emp_no = models.ForeignKey(Employee) # 同项目，直接写类
    # emp_no = models.ForeignKey('a_module.Employee') # 跨项目, 模块，类

    # 由于模型定义了外键, 会将外键的字段emp_no，自动关联一个对象。表示关系。关联Employee对象。
+    emp_no = models.ForeignKey('Employee', on_delete=models.CASCADE, db_column='emp_no',related_name="ss")  # 同项目的类名
    salary = models.IntegerField(verbose_name='工资')
    from_date = models.DateField()
    to_date = models.DateField()

    def __str__(self):
        return "<Salary {},{}>".format(self.pk,  self.salary)

现在打印

diff
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects

    emp = emgr.get(pk=10002)
+    print(emp.ss) #惰性
+    print(emp.ss.all()) #惰性


print(f'加载结束 {__name__}')

一般见名知义salaries


# 修改名
emp_no = models.ForeignKey('Employee', on_delete=models.CASCADE, db_column='emp_no',related_name="salaries")  # 同项目的类名


# 打印
if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects

    emp = emgr.get(pk=10002)
    print(emp.salaries) #惰性
    print(emp.salaries.all()) #惰性

现在获取用户名

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

    # id = models.AutoField(primary_key=True)

    """
    外键。django仅支持单字段的主键。
    默认没有字段定义 primary_key, 自动添加自增id的主键。当前类就是使用这个方式
    有字段定义 primary_key, 就是默认的主键。
    """
    # emp_no = models.IntegerField() # 需要外键
    # emp_no = models.ForeignKey(Employee) # 同项目，直接写类
    # emp_no = models.ForeignKey('a_module.Employee') # 跨项目, 模块，类

    # 由于模型定义了外键, 会将外键的字段emp_no，自动关联一个对象。表示关系。关联Employee对象。
    emp_no = models.ForeignKey('Employee', on_delete=models.CASCADE, db_column='emp_no',related_name="salaries")  # 同项目的类名
    salary = models.IntegerField(verbose_name='工资')
    from_date = models.DateField()
    to_date = models.DateField()

    def __str__(self):
+        return "<Salary {},{}, {}>".format(self.pk,  self.salary, self.emp_no.name)

测试

diff
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects

    emp = emgr.get(pk=10002)
+    print(emp.salaries) #惰性
+    print(emp.salaries.all()) #惰性


print(f'加载结束 {__name__}')

结果

python
(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10002 LIMIT 21; args=(10002,)
(0.000) SELECT `salaries`.`id`, `salaries`.`emp_no`, `salaries`.`salary`, `salaries`.`from_date`, `salaries`.`to_date` FROM `salaries` WHERE `salaries`.`emp_no` = 10002 LIMIT 21; args=(10002,)
employee.Salary.None
<QuerySet [<Salary: <Salary 18,65828, [Simmel Bezalel]>>, <Salary: <Salary 19,65909, [Simmel Bezalel]>>, <Salary: <Salary 20,67534, [Simmel Bezalel]>>, <Salary: <Salary 21,69366, [Simmel Bezalel]>>, <Salary: <Salary 22,71963, [Simmel Bezalel]>>, <Salary: <Salary 23,72527, [Simmel Bezalel]>>]>
加载结束 __main__

现在的SELECT 只有2句，因为已经有Employee实例。

实践注意

一般修改反向多对1添加的键名。外键定义 related_name

外键名是实例，数据库中字段默认是属性_id，这个需要和属性名统一，也需要修改。外键定义 db_column

distinct


print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects

    x = smgr.all()  # FROM `salaries`
    print(x)
    x = smgr.all().values('pk').distinct()  # SELECT DISTINCT `salaries`.`id` FROM `salaries` LIMIT 21; args=()
    print(x)
    x = smgr.all().values('emp_no').distinct()  # SELECT DISTINCT `salaries`.`id` FROM `salaries` LIMIT 21; args=()
    print(x) # <QuerySet [{'emp_no': 10001}, {'emp_no': 10002}, {'emp_no': 10003}, {'emp_no': 10004}, 
print(f'加载结束 {__name__}')

IN


print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects

    # 工资过9万的员工
    x = smgr.filter(salary__gte=90000).values('emp_no').distinct() # <QuerySet [{'emp_no': 10005}, {'emp_no': 10009}, {'emp_no': 10017}]>
    print(x) # <QuerySet [{'emp_no': 10005}, {'emp_no': 10009}, {'emp_no': 10017}]>

    print(emgr.filter(pk__in=x)) # WHERE `employees`.`emp_no` IN (SELECT DISTINCT U0.`emp_no` FROM `salaries` U0 WHERE U0.`salary` >= 90000)
    print(emgr.filter(pk__in=map(lambda x: x.get('emp_no'),x))) # FROM `employees` WHERE `employees`.`emp_no` IN (10005, 10009, 10017)
    print(emgr.filter(pk__in=[ i.get('emp_no') for i in x])) # FROM `employees` WHERE `employees`.`emp_no` IN (10005, 10009, 10017)
print(f'加载结束 {__name__}')

连接

原生的语句

太复杂，难得调试。就写RAW sql

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects

    """
    效率低的情况，使用原生的SQL 
    
    FROM 起始谁就返回谁。从employees查询 ，就返回employees
    """
    x = emgr.raw('''
        SELECT employees.emp_no, employees.first_name, employees.last_name, salaries.salary  FROM employees LEFT JOIN salaries ON salaries.emp_no = employees.emp_no
        WHERE salaries.salary > 90000
    ''')
    print(type(x)) # <class 'django.db.models.query.RawQuerySet'>
    # print(x.all()) # AttributeError: 'RawQuerySet' object has no attribute 'all'
    print(*x) # <Employ 10005 [Maliniak Kyoichi]> <Employ 10005 [Maliniak Kyoichi]>
    for i in x:
        print(i, type(i),i.__dict__) # <Employ 10017 [Bouloucos Cristinel]> <class 'employee.models.Employee'> {'_............ucos', 'salary': 99651}
        print(i, type(i),i.salary)


print(f'加载结束 {__name__}')

如果过滤没有的字段，去获取，查询N次

上面已经获取员工的编号，名字，但是没有姓别。

diff
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects

    """
    效率低的情况，使用原生的SQL 
    
    FROM 起始谁就返回谁。从employees查询 ，就返回employees
    """
    x = emgr.raw('''
        SELECT employees.emp_no, employees.first_name, employees.last_name, salaries.salary  FROM employees LEFT JOIN salaries ON salaries.emp_no = employees.emp_no
        WHERE salaries.salary > 90000
    ''')
    print(type(x)) # <class 'django.db.models.query.RawQuerySet'>
    # print(x.all()) # AttributeError: 'RawQuerySet' object has no attribute 'all'
    print(*x) # <Employ 10005 [Maliniak Kyoichi]> <Employ 10005 [Maliniak Kyoichi]>
    for i in x:
        print(i, type(i),i.__dict__) # <Employ 10017 [Bouloucos Cristinel]> <class 'employee.models.Employee'> {'_............ucos', 'salary': 99651}
+        print(i, type(i),i.salary, i.gender)


print(f'加载结束 {__name__}')

注意：i可以获取到性别，但是ORM替你去查询了N次。


(0.000) SELECT `employees`.`emp_no`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10005 LIMIT 21; args=(10005,)
(0.015) SELECT `employees`.`emp_no`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10005 LIMIT 21; args=(10005,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10005 LIMIT 21; args=(10005,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10005 LIMIT 21; args=(10005,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10009 LIMIT 21; args=(10009,)
(0.016) SELECT `employees`.`emp_no`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10009 LIMIT 21; args=(10009,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10009 LIMIT 21; args=(10009,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10009 LIMIT 21; args=(10009,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10017 LIMIT 21; args=(10017,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10017 LIMIT 21; args=(10017,)
(0.000) SELECT `employees`.`emp_no`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10017 LIMIT 21; args=(10017,)
(0.016) SELECT `employees`.`emp_no`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10017 LIMIT 21; args=(10017,)
<Employ 10017 [Bouloucos Cristinel]> <class 'employee.models.Employee'> 91985 F
<Employ 10017 [Bouloucos Cristinel]> <class 'employee.models.Employee'> {'_state': <django.db.models.base.ModelState object at 0x000001C7EDEB1670>, 'emp_no': 10017, 'first_name': 'Cristinel', 'last_name': 'Bouloucos', 'salary': 96122}

过滤字段加一个gender, 查1次


        SELECT employees.emp_no, employees.first_name, employees.last_name, employees.gender, salaries.salary  FROM employees LEFT JOIN salaries ON salaries.emp_no = employees.emp_no
        WHERE salaries.salary > 90000

实践注意

在打印的未SELECT的字段, 也会触发多次查询 , 例如上面的gender

在打印实例时，如果SELECT没有字段, 也会触发多次查询。所以在生产中，会将repr/str删除，避免多次查询


    def __repr__(self):
        return f'<Employ {self.emp_no} {self.name}>'

而语句是

diff
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects

    """
    效率低的情况，使用原生的SQL 
    
    FROM 起始谁就返回谁。从employees查询 ，就返回employees
    """
    x = emgr.raw('''
+        SELECT employees.emp_no, employees.gender, salaries.salary  FROM employees LEFT JOIN salaries ON salaries.emp_no = employees.emp_no
        WHERE salaries.salary > 90000
    ''')
    print(type(x)) # <class 'django.db.models.query.RawQuerySet'>
    # print(x.all()) # AttributeError: 'RawQuerySet' object has no attribute 'all'
    print(*x) # <Employ 10005 [Maliniak Kyoichi]> <Employ 10005 [Maliniak Kyoichi]>
    for i in x:
        print(i, type(i),i.__dict__) # <Employ 10017 [Bouloucos Cristinel]> <class 'employee.models.Employee'> {'_............ucos', 'salary': 99651}
        print(i, type(i),i.salary, i.gender)


print(f'加载结束 {__name__}')

就会触发查询 N次

多对多

SQL结构

员工、部门、员工部门表

sql
mysql> DESC departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no   | char(4)     | NO   | PRI | NULL    |       |
| dept_name | varchar(40) | NO   | UNI | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
 CREATE TABLE `departments` (
  `dept_no` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `dept_name` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`dept_no`) USING BTREE,
  UNIQUE KEY `dept_name` (`dept_name`) USING BTREE # 部门名不可以重复
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC

 
mysql> DESC employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
 CREATE TABLE `employees` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `last_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `gender` enum('M','F') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC

mysql> DESC dept_emp;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no    | int     | NO   | PRI | NULL    |       |
| dept_no   | char(4) | NO   | PRI | NULL    |       |
| from_date | date    | NO   |     | NULL    |       |
| to_date   | date    | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 CREATE TABLE `dept_emp` (
  `emp_no` int NOT NULL,
  `dept_no` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`) USING BTREE,
  KEY `dept_no` (`dept_no`) USING BTREE,
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC

第3个表使用联合主键，且是另2个表的外键。这样django不支持，所以去掉联合主键，只保留id主键。

django中，第3个表只有2个外键, 没有其他字段，可以使用manytomany. 如果有其他字段，就需要第3个表。

modal类

python

class Department(models.Model):
    class Meta:
        # Department.Meta.db_table
        db_table = 'departments'

    dept_no =models.CharField(max_length=4,primary_key=True)
    dept_name = models.CharField(max_length=40,unique=True)

    def __str__(self):
        return f"<D {self.pk}, {self.dept_name}>"

    __repr__ = __str__

class Dept_emp(models.Model):
    # 没有主键，默认ID
    emp_no = models.ForeignKey(Employee,on_delete=models.CASCADE,
                                db_column='emp_no')
    dept_no = models.ForeignKey(Department,on_delete=models.CASCADE,
                                db_column='dept_no')
    from_date = models.DateField()
    to_date = models.DateField()

    class Meta:
        db_table = 'dept_emp'

修改表

去掉联合主键，添加新主键id
添加2个外键。

sql
# 删除外键
mysql> ALTER TABLE dept_emp DROP FOREIGN KEY dept_emp_ibfk_1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE dept_emp DROP FOREIGN KEY dept_emp_ibfk_2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#删除联合主键
ALTER TABLE dept_emp DROP PRIMARY KEY;

#添加新主键
ALTER TABLE dept_emp ADD id UNSIGNED  BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '默认自增id';


# 结果
mysql> SELECT * FROM dept_emp;
+--------+---------+------------+------------+----+
| emp_no | dept_no | from_date  | to_date    | id |
+--------+---------+------------+------------+----+
|  10001 | d005    | 1986-06-26 | 9999-01-01 |  1 |
|  10002 | d007    | 1996-08-03 | 9999-01-01 |  2 |
|  10003 | d004    | 1995-12-03 | 9999-01-01 |  3 |
|  10004 | d004    | 1986-12-01 | 9999-01-01 |  4 |
|  10005 | d003    | 1989-09-12 | 9999-01-01 |  5 |
|  10006 | d005    | 1990-08-05 | 9999-01-01 |  6 |
|  10007 | d008    | 1989-02-10 | 9999-01-01 |  7 |
|  10008 | d005    | 1998-03-11 | 2000-07-31 |  8 |
|  10009 | d006    | 1985-02-18 | 9999-01-01 |  9 |
|  10010 | d004    | 1996-11-24 | 2000-06-26 | 10 |
|  10010 | d006    | 2000-06-26 | 9999-01-01 | 11 |
|  10011 | d009    | 1990-01-22 | 1996-11-09 | 12 |
|  10012 | d005    | 1992-12-18 | 9999-01-01 | 13 |
|  10013 | d003    | 1985-10-20 | 9999-01-01 | 14 |
|  10014 | d005    | 1993-12-29 | 9999-01-01 | 15 |
|  10015 | d008    | 1992-09-19 | 1993-08-22 | 16 |
|  10016 | d007    | 1998-02-11 | 9999-01-01 | 17 |
|  10017 | d001    | 1993-08-03 | 9999-01-01 | 18 |
|  10018 | d004    | 1992-07-29 | 9999-01-01 | 19 |
|  10018 | d005    | 1987-04-03 | 1992-07-29 | 20 |
|  10019 | d008    | 1999-04-30 | 9999-01-01 | 21 |
|  10020 | d004    | 1997-12-30 | 9999-01-01 | 22 |
+--------+---------+------------+------------+----+
22 rows in set (0.00 sec)

# 添加外键 
ALTER TABLE dept_emp ADD FOREIGN KEY  (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE dept_emp ADD FOREIGN KEY  (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE ON UPDATE RESTRICT;

# 
DESC dept_emp;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| emp_no    | int     | NO   | MUL | NULL    |                |
| dept_no   | char(4) | NO   | MUL | NULL    |                |
| from_date | date    | NO   |     | NULL    |                |
| to_date   | date    | NO   |     | NULL    |                |
| id        | bigint  | NO   | PRI | NULL    | auto_increment |
+-----------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


CREATE TABLE `dept_emp` (
  `emp_no` int NOT NULL,
  `dept_no` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  PRIMARY KEY (`id`),
  KEY `dept_no` (`dept_no`) USING BTREE,
  KEY `emp_no` (`emp_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC

注意：外键在数据库不加时, 代码定义了模型，也可以。

数据库加了更双保险。上面 5.7.7.2 中就没有添加外键

外键用来保证完整性和一致性。如果不加，就需要代码保证。保证不了，就别写代码了。

主键修改，在设计时修改

添加外键之后的元编程原理

python
print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary, Dept_emp, Department
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    dmgr = Department.objects
    smgr = Salary.objects
    demgr = Dept_emp.objects
    print()
    print('Employee')
    print(*Employee.__dict__.items(),sep='\n')
    """
    ('salaries', <django.db.models.fields.related_descriptors.ReverseManyToOneDescriptor object at 0x0000020CE17C8970>)
('dept_emp_set', <django.db.models.fields.related_descriptors.ReverseManyToOneDescriptor object at 0x0000020CE18062B0>)
    """


    print()
    print('Department')
    print(*Department.__dict__.items(),sep='\n')
    """
    ('dept_emp_set', <django.db.models.fields.related_descriptors.ReverseManyToOneDescriptor object at 0x000001FBA97963D0>)
    """
        
    
    print()
    print('Dept_emp')
    print(*Dept_emp.__dict__.items(),sep='\n')
print(f'加载结束 {__name__}')

员工表，有2个反向多对1添加的属性。

部门表有1个

完成查询

100010员工信息

100010员工信息


print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary, Dept_emp
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects
    demgr = Dept_emp.objects

    # 10010员工 信息和部门编号
    emp = emgr.get(pk=10010)
    print(emp )

print(f'加载结束 {__name__}')

10010员工所在部门的名

10010员工所在部门的编号

Employee --> DE --> Department

员工对应的中间表


print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary, Dept_emp
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    smgr = Salary.objects
    demgr = Dept_emp.objects

    # 10010员工 信息和部门编号
    emp = emgr.get(pk=10010)
    print(emp.dept_emp_set.all())

print(f'加载结束 {__name__}')

结果


加载到 __main__
<QuerySet [<Dept_emp: Dept_emp object (10)>, <Dept_emp: Dept_emp object (11)>]>
加载结束 __main__
(0.000) 
                SELECT VERSION(),
                       @@sql_mode,
                       @@default_storage_engine,
                       @@sql_auto_is_null,
                       @@lower_case_table_names,
                       CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
            ; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.016) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10010 LIMIT 21; args=(10010,)
(0.000) SELECT `dept_emp`.`id`, `dept_emp`.`emp_no`, `dept_emp`.`dept_no`, `dept_emp`.`from_date`, `dept_emp`.`to_date` FROM `dept_emp` WHERE `dept_emp`.`emp_no` = 10010 LIMIT 21; args=(10010,)

中间表到部门


print(f'加载到 {__name__}')

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup(set_prefix=False)

###################### 所有测试在之下 ######################

from employee.models import Employee, Salary, Dept_emp, Department
from django.db.models.query import QuerySet, Q

if __name__ == '__main__':
    emgr = Employee.objects
    dmgr = Department.objects
    demgr = Dept_emp.objects

    # 10010员工 信息和部门编号
    emp = emgr.get(pk=10010)
    x = emp.dept_emp_set.values('dept_no')
    print(dmgr.filter(pk__in=map(lambda x:x.get('dept_no'),x)))
    for i in dmgr.filter(pk__in=map(lambda x:x.get('dept_no'),x)):
        print(i.dept_name)


print(f'加载结束 {__name__}')

结果


加载到 __main__
(0.000) 
                SELECT VERSION(),
                       @@sql_mode,
                       @@default_storage_engine,
                       @@sql_auto_is_null,
                       @@lower_case_table_names,
                       CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
            ; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.000) SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender` FROM `employees` WHERE `employees`.`emp_no` = 10010 LIMIT 21; args=(10010,)
(0.000) SELECT `dept_emp`.`dept_no` FROM `dept_emp` WHERE `dept_emp`.`emp_no` = 10010; args=(10010,)
(0.000) SELECT `departments`.`dept_no`, `departments`.`dept_name` FROM `departments` WHERE `departments`.`dept_no` IN ('d004', 'd006') LIMIT 21; args=('d004', 'd006')
(0.000) SELECT `departments`.`dept_no`, `departments`.`dept_name` FROM `departments` WHERE `departments`.`dept_no` IN ('d004', 'd006'); args=('d004', 'd006')
<QuerySet [<D d004, Production>, <D d006, Quality Management>]>
Production
Quality Management
加载结束 __main__

目录

django orm

字段类型(类属性值)

缺省主键

字段选项

关系类型字段

定义表

示例数据

基于表完成类（表), 类属性(字段名)

测试代码

django 管理器，完成查询

django 缓存

切片 左闭右开,0索引起始

返回查询集方法

filter

exclude 关键字传参，不支持!=

order_by

values

返回单个实例的方法

first

last

get

count

exists

lookup表达式

exact

gt

contains

startswith

istartswith

in

Q对象 AND/OR操作

AND

OR操作

NOT

聚合、分组

分组聚合简写

理解表结构

准备modal类

外键引用的真实数据库字段

问题

修改数据库字段

现在如果不修改字段

元编程原理

多查1 [查n回]

查询多端实例

多端打印emp_no时

1查多 [查1回]

查1个

借助关联获取多个

修改反向多对1的键

现在获取用户名

实践注意

distinct

IN

连接

原生的语句

如果过滤没有的字段，去获取，查询N次

过滤字段加一个gender, 查1次

实践注意

多对多

SQL结构

modal类

添加外键之后的元编程原理

完成查询

100010员工信息

10010员工所在部门的名

员工对应的中间表

中间表到部门

切片左闭右开,0索引起始