2022-05-15
devops
00
请注意,本文编写于 572 天前,最后修改于 572 天前,其中某些信息可能已经过时。

目录

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外键使用

关系类型字段

字段名描述
ForeignKey1个部门,多个员工。多端加外键。
ManyToManyField1个员工多个部门,一个部门多员工。第3表,主键是部门id和员工id联合主键 或 2个1对多,第3个表有3个字段,1个主键,1个是1张表的外键,另一个是另一张表的外键。
OneToOneField1对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__
  1. verbose_name 代码自己看,与数据库无关。
  2. default django自己使用,不是写到数据库
  3. primary_key, max_length, *Field(字段类型) 与数据库一一对应
  4. __repr__ __str__ 调试使用,看着方便。
  5. property用于加工字段,每个实例需要通过类属性, 创建自己的实例属性。

测试代码

t1.py 根目录

  1. Employees.objects django.db.models.manager 管理器
  2. Employees.objects.all() 懒加载; ctrl + q不会出现类定义 django.db.models.query.QuerySet
  3. 查询集中 __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__}')
  1. all() 返回 QuerySet 可迭代对象
  2. QuerySet 可迭代对象是惰性的,你要数据的时候才会查询数据库。
  3. QuerySet 可迭代对象要数据的方法。print或遍历, 序列化, if, 切片, len, repr, bool,会立即求值
  4. QuerySet 可迭代对象遍历的每个对象就是 实例。实例可以访问类属性
  5. e.gender是choices, get_属性名_display() 仅对choices有此方法
  6. Employee.objects 内建管理器,管理增删除改查。不提供,默认提供objects。如果你提供了,则默认就没有了。
  7. 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, 可以链式调用
名称返回值类型描述不存在结果
allQuerySet所有,空集
filterQuerySet过滤空集
excludeQuerySet取反空集
order_byQuerySet排序空集
valuesQuerySetQuerySet调用values,结果集变字典
annotate调用values,结果变字典
空集
annotateQuerySet结果查询 集

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__}')

pk

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__}')
  1. 使用pk, 自动引用主键
  2. 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 1None
last()返回最后一个 逆序LIMIT 1None
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对象
ANDfilter().filter() 或 filter(表达式1,表达式2,) 或 filter() & filter()filter( Q(key=value) & Q(key=value)), Q & Q 实例,魔数方法, 运算符重载即可。
ORproperty__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

image-20220420193753406

而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;
  1. 上面员工表

  2. 下面工资表

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

    员工工资月份
    11001
    11002
    11003
    22001
    22002
    22003
  4. salaries emp_no 依赖主键 employees.emp_no,当删除 employees.emp_no 时,CASCADE表示自己也删除。更新主键,保证不冲突,更新之后,这里salaries没有写UPDATE策略。表示不更新。

准备modal类

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

主键可以使用辅助键。

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

  1. 去掉联合主键,emp_no自增;
  2. 删除外键
  3. 添加id主键,自增
  4. 添加外键 emp_no

image-20220420212934136

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

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()

注意:

  1. 表中有id
  2. 表中仅单个主键
  3. 表中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'

修改表

  1. 去掉联合主键,添加新主键id
  2. 添加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__

本文作者:mykernel

本文链接:

版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!