Django框架之ORM-阿里云开发者社区

ORM

Object-Relational Mapping

对应关系

ORM   DB
类    数据表
对象  数据行
属性  字段

ORM提高开发效率，降低了执行效率

Flask - Sqlalchemy

字段类型和参数

1、字段类型

# 自增长 默认int
Auto = models.AutoField(primary_key=True)
BigAuto = models.BigAutoField()
# 二进制
Binary = models.BinaryField()
# 布尔
Boolean = models.BooleanField()
NullBoolean = models.NullBooleanField()
# 整型
PositiveSmallInteger = models.PositiveSmallIntegerField()  # 5字节
SmallInteger = models.SmallIntegerField()  # 6字节
PositiveInteger = models.PositiveIntegerField()  # 10字节
Integer = models.IntegerField()  # 11字节
BigInteger = models.BigIntegerField()  # 20字节
# 字符串
Char = models.CharField()  # varchar
Text = models.TextField()  # longtext
# 时间日期
Date = models.DateField()
DateTime = models.DateTimeField()
Duration = models.DurationField()  # int python timedelta实现
# 浮点型
Float = models.FloatField()
Decimal = models.DecimalField()
# 其他
Email = models.EmailField()  # 邮箱
Image = models.ImageField()
File = models.FileField()
FilePath = models.FilePathField()
URL = models.URLField()
UUID = models.UUIDField()
GenericIPAddress = models.GenericIPAddressField()

2、关系型字段

一对一 OneToOneField

多对一 ForeignKey

多对对 ManyToManyField 默认或自定义中间表

3、字段类型参数

默认表名

应用_模型类名小写

eg: app_student

（1）所有字段都有的参数

db_column="name"  指定数据库字段名
verbose_name="别名"
help_text="表单说明"
primary_key=True  设置主键
unique=True       唯一键
null=True         数据库允许为空，默认都是不能为null的
blank=True    前端提交表单允许为空
db_index=True 建立索引
editable=False 不允许编辑字段

（2）个别字段的参数

CharField
    max_length=100 最大长度
DateField、DateTime
    unique_for_date=True 日期必须唯一
    unique_for_month=True 月份唯一
    auto_now=True       自动更新记录时间
    auto_now_add=True   自动增加记录时间
DecimalField
    max_digits=4   总位数
    decimal_places=2 小数点数
    eg: 11.22 16.34

（3）关系型字段参数

OneToOneField
    related_name="one"  父表查字表数据
ForeignKey
    on_delete=""  外键所关联的对象被删除时进行的操作
        CASCADE： 级联删除。将定义有外键的模型对象同时删除， 默认操作
        PROTECT： 阻止删除。会报完整性错误。
        DO_NOTHING：什么也不做
        SET_NULL：外键设置为 null，前提是 null=True, blank=True。
        SET_DEFAULT：设置为外键的默认值，前提是设置了default参数。
        SET()：会调用外面的值，可以是一个函数。一般情况下使用 CASCADE 就可以了。
eg:
```python
class B(models.Model):
    foreign = models.ForeignKey(A, on_delete=models.CASCADE)
    foreign = models.ForeignKey(A, on_delete=models.PROTECT)
    foreign = models.ForeignKey(A, on_delete=models.DO_NOTHING)
    foreign = models.ForeignKey(A, on_delete=models.SET_NULL, null=True, blank=True)
    foreign = models.ForeignKey(A, on_delete=models.SET_DEFAULT, default=0)
    foreign = models.ForeignKey(A, on_delete=models.SET)

4、自关联

示例：地址省-市-县级联

class AddressInfo(models.Model):
    address = models.CharField(max_length=200, null=True, blank=True, verbose_name="地址")
    pid = models.ForeignKey("self", null=True, blank=True, verbose_name="自关联", on_delete=models.SET_NULL)
    def __str__(self):
        return self.address

元数据Meta

修改应用名称

apps.py

class OrmdemoConfig(AppConfig):
    name = 'ormdemo'
    verbose_name = "应用名称"

修改model元数据

class AddressInfo(models.Model):
    address = models.CharField(max_length=200, null=True, blank=True, verbose_name="地址")
    note = models.CharField(max_length=200, null=True, blank=True, verbose_name="说明")
    pid = models.ForeignKey("self", null=True, blank=True, verbose_name="自关联", on_delete=models.SET_NULL)
    def __str__(self):
        return self.address
    class Meta:
        db_table = "address"  # 自定义表名
        ordering = ["pid_id"]  # 指定排序字段
        verbose_name = "地址"  # 单数
        verbose_name_plural = verbose_name  # 复数
        abstract = True  # 设置为基类
        permissions = (("定义好的权限", "权限说明"),)
        managed = False  # 按照Django默认方式管理数据表
        unique_together = ("address", "note")  # 联合唯一键，单元组或多元组
        app_label = "ormdemo"  # 定义模型类属于哪个应用
        db_tablespace = "" # 定义数据库表空间

模型类开发实例

d24.1.jpg

"""
讲师 - 助教 一对一
讲师 - 课程表 一对多
课程表 - 学生 多对多
"""
class Teacher(models.Model):
    """讲师信息表"""
    nickname = models.CharField(max_length=30, primary_key=True, db_index=True, verbose_name="昵称")
    introduction = models.TextField(default="这位同学很懒，木有签名的说~", verbose_name="简介")
    fans = models.PositiveIntegerField(default=0, verbose_name="粉丝数")
    created_at = models.DateTimeField(auto_now_add=True, verbose_name="创建时间")
    updated_at = models.DateTimeField(auto_now=True, verbose_name="创建时间")
    class Meta:
        verbose_name = "讲师信息表"
        verbose_name_plural = verbose_name
    def __str__(self):
        return self.nickname
class Course(models.Model):
    """课程信息表"""
    title = models.CharField(max_length=100, primary_key=True, db_index=True, verbose_name="课程名")
    type = models.CharField(choices=((0, "其他"), (1, "实战课"), (2, "免费课")), max_length=1, default=0, verbose_name="课程类型")
    price = models.PositiveSmallIntegerField(verbose_name="价格")
    volume = models.BigIntegerField(verbose_name="销量")
    online = models.DateField(verbose_name="上线时间")
    # 删除级联
    teacher = models.ForeignKey(Teacher, null=True, blank=True, on_delete=models.CASCADE, verbose_name="讲师")
    created_at = models.DateTimeField(auto_now_add=True, verbose_name="创建时间")
    updated_at = models.DateTimeField(auto_now=True, verbose_name="创建时间")
    class Meta:
        verbose_name = "课程信息表"
        verbose_name_plural = verbose_name
    def __str__(self):
        return f"{self.get_type_display()}-{self.title}"
class Student(models.Model):
    """学生信息表"""
    nickname = models.CharField(max_length=30, primary_key=True, db_index=True, verbose_name="学生姓名")
    age = models.PositiveSmallIntegerField(verbose_name="年龄")
    gender = models.CharField(choices=((0, "保密"), (1, "男"), (2, "女")), max_length=1, default=0, verbose_name="性别")
    study_time = models.PositiveIntegerField(default=0, verbose_name="学习时长(h)")
    course = models.ManyToManyField(Course, verbose_name="课程")
    created_at = models.DateTimeField(auto_now_add=True, verbose_name="创建时间")
    updated_at = models.DateTimeField(auto_now=True, verbose_name="创建时间")
    class Meta:
        verbose_name = "学生信息表"
        verbose_name_plural = verbose_name
    def __str__(self):
        return self.nickname
class TeacherAssistant(models.Model):
    """助教信息表"""
    nickname = models.CharField(max_length=30, primary_key=True, db_index=True, verbose_name="昵称")
    hobby = models.CharField(max_length=10, null=True, blank=True, verbose_name="爱好")
    # 删除置空
    teacher = models.OneToOneField(Teacher, null=True, blank=True, on_delete=models.SET_NULL, verbose_name="讲师")
    created_at = models.DateTimeField(auto_now_add=True, verbose_name="创建时间")
    updated_at = models.DateTimeField(auto_now=True, verbose_name="创建时间")
    class Meta:
        verbose_name = "助教信息表"
        verbose_name_plural = verbose_name
    def __str__(self):
        return self.nickname

生成数据表

python manage.py makemigrations   # 生成迁移文件
python manage.py migrate          # 执行迁移文件

删除某个模型类的完整操作:

（1）在已创建的app下, 首先删除models.py中需要删除的模型类

（2）删除该模型类在迁移脚本migrations中的对应文件

（3）删除该项目在django_migrations中的对应记录

（4）删除数据库中对应的数据表

导入数据

（1）django shell

$ python manage.py shell

from app.models import Mode
model = Model()
model.name = "name"
model.save()

（2）脚本导入

# -*- coding: utf-8 -*-
import os
import random
from datetime import date
import django
import sys
# 导入项目配置
project_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(project_path)
os.environ['DJANGO_SETTINGS_MODULE'] = 'mysite.settings'
django.setup()
from ormdemo.models import Teacher, Course, Student, TeacherAssistant
def import_data():
    # 讲师数据 create
    Teacher.objects.create(nickname="Jack", introduction="Python工程师", fans=666)
    # 课程数据 bulk_create
    data = [
        Course(title=f"Python系列教程{i}",
               teacher=Teacher.objects.get(nickname="Jack"),
               type=random.choice([0, 1, 2]),
               price=random.randint(200, 300),
               volume=random.randint(100, 10000),
               online=date(2018, 10, 1)
               )
        for i in range(1, 5)
    ]
    Course.objects.bulk_create(data)
    # 学生数据 update_or_create
    Student.objects.update_or_create(
        nickname="A同学",
        defaults={
            "age": random.randint(18, 58),
            "gender": random.choice([0, 1, 2]),
            "study_time": random.randint(9, 999)
        }
    )
    # 正向添加
    # 销量大于等于1000的课程
    Student.objects.get(nickname="A同学").course.add(*Course.objects.filter(volume__gte=1000))
    # 反向添加
    # 学习时间大于等于500小时的同学
    Course.objects.get(title="Python系列教程1").student_set.add(*Student.objects.filter(
        study_time__gte=500
    ))
    # 助教数据 get_or_create()
    TeacherAssistant.objects.get_or_create(
        nickname="助教1",
        defaults={
            "hobby": "慕课网学习",
            "teacher": Teacher.objects.get(nickname="Jack")
        }
    )
if __name__ == '__main__':
    import_data()

（3）fixtures

Django serialization -> model保存

python manage.py dumpdata > data.json # 导出数据

python manage.py loaddata data.json # 导入数据

（4）mysqldump

MySQL数据库导入导出数据

(5)PyCharm

PyCharm自带的导入导出工具

ModelAPI

1、查询，检索，过滤

Teacher.objects.all()  # 返回多个结果
Teacher.objects.get(id=1)  返回一条结果
Teacher.objects.filter(fans__gte=500) # 返回多个结果

2、数据匹配大小写敏感

# 双下划线开头`__`
Teacher.objects.filter(fans__in=[500, 600])
# i开头表示大小写敏感
Teacher.objects.filter(nickname__icontains="A")

3、结果切片、排序、链式查询

Teacher.objects.all()[:1]
Teacher.objects.all().order_by("-age")  # 负号表示降序
Teacher.objects.filter(fans__gte=500).order_by("nickname")  # 查询集可以继续使用排序

4、查看执行的原生SQL

Teacher.objects.all().order_by("-nickname").query
"""
SELECT `ormdemo_teacher`.`nickname`, 
FROM `ormdemo_teacher` 
ORDER BY `ormdemo_teacher`.`nickname` DESC
"""

返回新的QuerySet的API

读操作大部分都返回

1、第一类

all 全部

filter 过滤

order_by 排序

exclude 排除

reverse 逆序需要在元数据中设置ordering

distinct 去重

Student.objects.all().exclude(nickname="A同学")
# SELECT `ormdemo_student`.`nickname` 
# FROM `ormdemo_student` 
# WHERE NOT (`ormdemo_student`.`nickname` = A同学)
Student.objects.all().exclude(nickname="A同学").reverse()
"""
ELECT `ormdemo_student`.`nickname`
FROM `ormdemo_student` 
WHERE NOT (`ormdemo_student`.`nickname` = A同学)
"""

2、第二类

extra 别名

defer 排除一些字段

only 选择一些字段

Student.objects.all().extra(select={"name": "nickname"})
"""
SELECT (nickname) AS `name`, `ormdemo_student`.`nickname`, `ormdemo_student`.`age`
FROM `ormdemo_student`
"""
Student.objects.all().only("nickname", "age")
"""
SELECT `ormdemo_student`.`nickname`, `ormdemo_student`.`age` FROM `ormdemo_student`
"""

3、第三类

values 获取字典形式

values_list 获取元组形式

Student.objects.values("nickname", "age")
# <QuerySet [{'nickname': 'A同学', 'age': 54}, {'nickname': 'B同学', 'age': 36}]>
Student.objects.values_list("nickname", "age")
# <QuerySet [('A同学', 54), ('B同学', 36)]>
Student.objects.values_list("nickname", flat=True) # 单个字段返回列表
# <QuerySet ['A同学', 'B同学']>

4、第四类

dates、datatimes根据时间日期获取查询集

Student.objects.dates("created_at", "month")
"""
SELECT DISTINCT CAST(DATE_FORMAT(`ormdemo_student`.`created_at`, '%Y-%m-01') AS DATE) AS `datefield` 
FROM `ormdemo_student` 
WHERE `ormdemo_student`.`created_at` IS NOT NULL 
ORDER BY `datefield` ASC
"""

5、第五类

union 并集

intersection 交集需要数据库支持

difference 差集需要数据库支持

ret1 = Student.objects.filter(nickname="同学A")
ret2 = Student.objects.filter(nickname="同学B")
ret1.union(ret2)
"""
(SELECT `ormdemo_student`.`nickname
FROM `ormdemo_student` 
WHERE `ormdemo_student`.`nickname` = '同学A') 
UNION 
(SELECT `ormdemo_student`.`nickname`
FROM `ormdemo_student` 
WHERE `ormdemo_student`.`nickname` = '同学B')  
LIMIT 2

6、第六类

select_related 一对一，多对一查询优化

prefetch_related 一对多，多对多查询优化

Course.objects.all().select_related("teacher")
"""
 SELECT `ormdemo_course`.`title`,  `ormdemo_teacher`.`nickname`
 FROM `ormdemo_course` 
 LEFT OUTER JOIN `ormdemo_teacher`
 ON (`ormdemo_course`.`teacher_id` = `ormdemo_teacher`.`nickname`)  
 LIMIT 21;
"""
Student.objects.all().prefetch_related("course")
"""
SELECT `ormdemo_student`.`nickname`
FROM `ormdemo_student`  LIMIT 21;
SELECT (`ormdemo_student_course`.`student_id`) AS `_prefetch_related_val_student_id`, `ormdemo_course`.`title`
FROM `ormdemo_course` 
INNER JOIN `ormdemo_student_course` 
ON (`ormdemo_course`.`title` = `ormdemo_student_course`.`course_id`) 
WHERE `ormdemo_student_course`.`student_id` IN ('A同学', 'B同学', 'C同学', 'D同学');
"""

反向查询

Teacher.objects.get(nickname="Jack").course_set.all()
"""
SELECT `ormdemo_teacher`.`nickname`
FROM `ormdemo_teacher` 
WHERE `ormdemo_teacher`.`nickname` = 'Jack'
SELECT `ormdemo_course`.`title`
FROM `ormdemo_course` 
WHERE `ormdemo_course`.`teacher_id` = 'Jack'  
LIMIT 21;
"""

7、第七类

annotate 聚合计数，求和，平均数，执行原生SQL（分组后的数据进行统计）

from django.db.models import Count, Avg, Max, Min, Sum
Course.objects.values("teacher").annotate(vol=Sum("volume"))
"""
SELECT `ormdemo_course`.`teacher_id`, SUM(`ormdemo_course`.`volume`) AS `vol` 
FROM `ormdemo_course` 
GROUP BY `ormdemo_course`.`teacher_id` 
ORDER BY NULL  LIMIT 21;
"""
Course.objects.values("teacher").annotate(pri=Avg("price"))
"""
SELECT `ormdemo_course`.`teacher_id`, AVG(`ormdemo_course`.`price`) AS `pri` 
FROM `ormdemo_course` 
GROUP BY `ormdemo_course`.`teacher_id` 
ORDER BY NULL  LIMIT 21;
# order by null用途是强制对查询结果禁用排序
"""

不返回QuerySet的API

写操作都不返回结果集

1、获取对象

get, get_or_create, first, last, in_bulk

latest最近记录, earliest 最早记录需要设置元数据get_latest_by

Student.objects.get(nickname="A同学")  # 返回多个会报错
"""
SELECT `ormdemo_student`.`nickname`
FROM `ormdemo_student` 
WHERE `ormdemo_student`.`nickname` = 'A同学'
"""
Student.objects.first()
"""
SELECT `ormdemo_student`.`nickname`
FROM `ormdemo_student` 
ORDER BY `ormdemo_student`.`nickname` ASC  
LIMIT 1
"""
Student.objects.last()
"""
SELECT `ormdemo_student`.`nickname`
FROM `ormdemo_student` 
ORDER BY `ormdemo_student`.`nickname` DESC  
LIMIT 1;
"""
Student.objects.in_bulk(["A同学"])
"""
SELECT `ormdemo_student`.`nickname`
FROM `ormdemo_student` 
WHERE `ormdemo_student`.`nickname` IN ('A同学');
"""
Student.objects.latest()
"""
SELECT `ormdemo_student`.`nickname
FROM `ormdemo_student` 
ORDER BY `ormdemo_student`.`created_at` DESC  
LIMIT 1;
"""
Student.objects.earliest()
"""
SELECT `ormdemo_student`.`nickname
FROM `ormdemo_student` 
ORDER BY `ormdemo_student`.`created_at` ASC  
LIMIT 1;

2、创建对象

create, bulk_create, update_or_create

3、更新对象

update, update_or_create

Student.objects.filter(nickname="A同学").update(age=13)
"""
UPDATE `ormdemo_student` 
SET `age` = 13 
WHERE `ormdemo_student`.`nickname` = 'A同学'; 
"""

4、删除对象

delete 使用filter过滤

Student.objects.filter(nickname="A同学").delete()
"""
SELECT `ormdemo_student`.`nickname 
FROM `ormdemo_student` 
WHERE `ormdemo_student`.`nickname` = 'A同学';
DELETE FROM `ormdemo_student_course` 
WHERE `ormdemo_student_course`.`student_id` IN ('A同学');
DELETE FROM `ormdemo_student` 
WHERE `ormdemo_student`.`nickname` IN ('A同学');
(13, {'ormdemo.Student_course': 12, 'ormdemo.Student': 1})
"""

5、其他操作

exists, count, aggregate(整个数据表统计)

Student.objects.filter(nickname="B同学").exists()
"""
SELECT (1) AS `a` 
FROM `ormdemo_student` 
WHERE `ormdemo_student`.`nickname` = 'B同学'  
LIMIT 1
"""
Student.objects.filter(nickname="B同学").count()
"""
SELECT COUNT(*) AS `__count` 
FROM `ormdemo_student` 
WHERE `ormdemo_student`.`nickname` = 'B同学'; 
"""
Course.objects.aggregate(Max("price"), Min("price"), Avg("price"), Sum("volum"))
"""
SELECT 
MAX(`ormdemo_course`.`price`) AS `price__max`, 
MIN(`ormdemo_course`.`price`) AS `price__min`, 
AVG(`ormdemo_course`.`price`) AS `price__avg`, 
SUM(`ormdemo_course`.`volume`) AS `volume__sum` 
FROM `ormdemo_course`;
{'price__max': 295, 'price__min': 210, 'price__avg': 250.75, 'volume__sum': 60559}
"""

自定义聚合查询（略）

group_contact

F对象和Q对象

F对象操作字段数据

Q对象结合AND OR NOT | ~ & 实现复杂查询

from django.db.models import F, Q
Student.objects.update(age=F("age") + 3)
"""
UPDATE `ormdemo_student` SET `age` = (`ormdemo_student`.`age` + 3);
"""
Student.objects.filter(Q(age__gte=1) & Q(age__lte=10))
"""
SELECT `ormdemo_student`.`nickname`
FROM `ormdemo_student`
WHERE (`ormdemo_student`.`age` >= 1 AND `ormdemo_student`.`age` <= 10) 
LIMIT 21;
"""

Django框架之ORM

ORM

字段类型和参数

元数据Meta

模型类开发实例

返回新的QuerySet的API

不返回QuerySet的API

F对象和Q对象

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Django框架之ORM

ORM

字段类型和参数

元数据Meta

模型类开发实例

返回新的QuerySet的API

不返回QuerySet的API

F对象和Q对象

热门文章

最新文章

相关课程

相关电子书

相关实验场景