Django 使用 ORM 操作数据库详解

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author  : Baimoc
# @Email   : baimoc@163.com
# @Time    : 2020/7/24 9:45

from django.db import models


class Field(models.Model):
    # 自增字段
    Auto = models.AutoField()
    # 自增字段，最大值可拓展到更大
    BigAuto = models.BigAutoField()

    # 二进制数据
    Binary = models.BinaryField()

    # 布尔型
    Boolean = models.BooleanField()
    NullBoolean = models.NullBooleanField() # 允许为空的布尔型

    # 整型
    PositiveSmallInterger = models.PositiveSmallIntegerField()  # 长度为 5字节正整数
    SmallIntegerField = models.SmallIntegerField()   # 长度为 6字节整数
    PositiveIntegerField = models.PositiveIntegerField()   # 长度为 10字节正整数
    IntegerField = models.IntegerField()   # 长度为 11字节正整数
    BigIntegerField = 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()

class A(models.Model):
    one2one = models.OneToOneField(Field)


class B(models.Model):
    one2one = models.ForeignKey(A)


class C(models.Model):
    one2one = models.ManyToManyField(B)

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.CASCADE)  # 写法一
    # pid = models.ForeignKey('AddressInfo', null=True, blank=True, verbose_name='自关联')  # 写法二

    def __str__(self):
        return self.address

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.CASCADE)  # 写法一
    # pid = models.ForeignKey('AddressInfo', null=True, blank=True, verbose_name='自关联')  # 写法二
    note = models.CharField(max_length=200, null=True, blank=True, verbose_name="说明")

    def __str__(self):
        return self.address

    class Meta:
        # 定义数据表名
        db_table='address'
        # 按照哪个字段排序
        # ordering=('pid',)
        # 表别名
        verbose_name='地址信息'
        # 表别名复数，用于英文显示
        verbose_name_plural=verbose_name
        # 设置基类，不生成数据表，作为其他表的基类
        # abstract=False
        # 设置表权限
        # permissions=(('定义好的权限', '权限说明'),)
        # 是否按照Django既定的规则来管理模型类
        # managed=False
        # 联合唯一键
        # unique_together=('address', 'note')  # 另一种写法 ((),())
        # 定义模型类属于哪个应用
        # app_label='myapp1

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author  : Baimoc
# @Email   : baimoc@163.com
# @Time    : 2020/7/24 9:45

from django.db import models


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="课程名")
    # 老师和课程为多对一关系，使用 ForeignKey 来实现
    teacher = models.ForeignKey(Teacher, null=True, on_delete=models.CASCADE, verbose_name="课程讲师")
    type = models.CharField(choices=((1, "实战课"), (2, "免费课"), (0, "其他")), max_length=1, default=0, verbose_name="课程类")
    price = models.PositiveSmallIntegerField(verbose_name="价格")
    volume = models.BigIntegerField(verbose_name="销量")
    online = models.DateField(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):
        # 返回type的描述信息
        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="昵称")
    # 课程与学生是多对多关系
    course = models.ManyToManyField(Course, verbose_name="课程")
    age = models.PositiveSmallIntegerField(verbose_name="年龄")
    gender = models.CharField(choices=((1, "男"), (2, "女"), (0, "保密")), max_length=1, default=0, verbose_name="性别")
    study_time = models.PositiveIntegerField(default="0", verbose_name="学习时长(h)")
    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 TeacherAssisant(models.Model):
    """助教信息表"""
    nickname = models.CharField(max_length=30, primary_key=True, db_index=True, verbose_name="昵称")
    teacher = models.OneToOneField(Teacher, null=True, on_delete=models.SET_NULL, verbose_name="讲师")
    hobby = models.CharField(max_length=100, null=True, blank=True, 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 = "助教信息表"
        db_table = "courses_assistant"
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.nickname

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author  : Baimoc
# @Email   : baimoc@163.com
# @Time    : 2020/7/24 15:29
# @File    : orm_data

import os
import sys
import random
import django
from datetime import date

project_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(project_path)  # 将项目路径添加到系统搜寻路径当中
os.environ['DJANGO_SETTINGS_MODULE'] = 'orm_baimoc.settings'  # 设置项目的配置文件
django.setup()

from ormbaimoc.models import Teacher, Course, Student, TeacherAssisant

def import_data():
    """使用 Django ORM 导入数据"""
    # 讲师数据
    Teacher.objects.create(nickname="Jack", introduction="Python 工程师", fans=123)
    Teacher.objects.create(nickname="Allen", introduction="Java 工程师", fans=321)
    Teacher.objects.create(nickname="Henry", introduction="Golang 工程师", fans=888)

    # 课程数据
    Course.objects.bulk_create([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([Course(title=f"Java 系列教程{i}",
                                       teacher=Teacher.objects.get(nickname="Allen"),
                                       type=random.choice((0, 1, 2)),
                                       price=random.randint(200,300),
                                       volume=random.randint(100, 10000),
                                       online=date(2018, 6, 6))
                                for i in range(1, 4)])
    Course.objects.bulk_create([Course(title=f"Golang 系列教程{i}",
                                       teacher=Teacher.objects.get(nickname="Henry"),
                                       type=random.choice((0, 1, 2)),
                                       price=random.randint(200,300),
                                       volume=random.randint(100, 10000),
                                       online=date(2020, 11, 1))
                                for i in range(1, 3)])

    # 学生数据
    Student.objects.update_or_create(nickname="赵同学", defaults={
        "age":random.randint(18, 58),
        "gender":random.choice((0, 1, 2)),
        "study_time":random.randint(9, 999),
    })
    Student.objects.update_or_create(nickname="吴同学", defaults={
        "age": random.randint(18, 58),
        "gender": random.choice((0, 1, 2)),
        "study_time": random.randint(9, 999),
    })
    Student.objects.update_or_create(nickname="郑同学", defaults={
        "age":random.randint(18, 58),
        "gender":random.choice((0, 1, 2)),
        "study_time":random.randint(9, 999),
    })
    Student.objects.update_or_create(nickname="王同学", defaults={
        "age": random.randint(18, 58),
        "gender": random.choice((0, 1, 2)),
        "study_time": random.randint(9, 999),
    })

    # 正向添加
    # 销量大于等于 1000 的课程
    Student.objects.get(nickname="赵同学").course.add(*Course.objects.filter(volume__gte=1000))
    # 销量大于 5000 的课程
    Student.objects.get(nickname="王同学").course.add(*Course.objects.filter(volume__gt=5000))

    # 反向添加
    # 学习实践大于等于 500 小时的同学
    Course.objects.get(title="Python 系列教程1").student_set.add(*Student.objects.filter(study_time__gte=500))
    # 学习实践小于等于 500 小时的同学
    Course.objects.get(title="Python 系列教程1").student_set.add(*Student.objects.filter(study_time__lte=500))

    # 助教数据 get_or_create
    TeacherAssisant.objects.get_or_create(nickname="助教1",
                                          defaults={"hobby":"慕课网学习", "teacher":Teacher.objects.get(nickname="Jack")})
    TeacherAssisant.objects.get_or_create(nickname="助教2",
                                          defaults={"hobby": "看电影", "teacher": Teacher.objects.get(nickname="Allen")})
    TeacherAssisant.objects.get_or_create(nickname="助教3",
                                          defaults={"hobby": "读书", "teacher": Teacher.objects.get(nickname="Henry")})
    return True


if __name__ == '__main__':
    if import_data():
        print("导入数据成功")

# 其他导入数据方法
# fixtures Django serialization 保存
# 数据库层面的导入数据

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author  : Baimoc
# @Email   : baimoc@163.com
# @Time    : 2020/7/26 18:39
# @File    : view

from django.shortcuts import render
from django.views.generic import View
from django.http import JsonResponse
from .models import AddressInfo, Teacher, Course,Student,TeacherAssisant


class IndexView(View):
    """主页"""

    def get(self, request):
        # 查询全部
        teachers = Teacher.objects.all()
        print(teachers)

        # 获取一个值
        # 只能返回一条结果，多条会报错
        teachers2 = Teacher.objects.get(nickname='Jack')
        print(teachers2, type(teachers2))

        # 查询符合条件的多个值
        # 返回QuerySet，可以是多条结果
        teachers3 = Teacher.objects.filter(fans__gte=500)
        for teacher in teachers3:
            print(f"讲师姓名：{teacher.nickname} --> 粉丝数：{teacher.fans}")

        # 字段数据匹配
        teacher4 = Teacher.objects.filter(fans__in=[888])
        print(teacher4)

        # 结果切片
        print(Teacher.objects.all()[:2])
        # 排序
        teacher6 = Teacher.objects.all().order_by('.fans')
        for t in teacher6:
            print(t.fans)
        # 链式查询
        teacher7 = Teacher.objects.filter(fans__gte=100).order_by('nickname')
        print(teacher7)

        # 查询原生SQL
        print(Teacher.objects.filter(fans__gte=100).order_by('nickname').query)

        return render(request, "address.html")

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author  : Baimoc
# @Email   : baimoc@163.com
# @Time    : 2020/7/26 18:39
# @File    : view
from datetime import date

from django.db.models import Sum, Avg, Max, Min
from django.shortcuts import render
from django.views.generic import View
from django.http import JsonResponse
from .models import AddressInfo, Teacher, Course,Student,TeacherAssisant, GroupConcat


class IndexView(View):
    """主页"""

    def get(self, request):
        # 查询全部
        teachers = Teacher.objects.all()
        print(teachers)

        # 获取一个值
        # 只能返回一条结果，多条会报错
        teachers2 = Teacher.objects.get(nickname='Jack')
        print(teachers2, type(teachers2))

        # 查询符合条件的多个值
        # 返回QuerySet，可以是多条结果
        teachers3 = Teacher.objects.filter(fans__gte=500)
        for teacher in teachers3:
            print(f"讲师姓名：{teacher.nickname} --> 粉丝数：{teacher.fans}")

        # 字段数据匹配
        teacher4 = Teacher.objects.filter(fans__in=[888])
        print(teacher4)

        # 结果切片
        print(Teacher.objects.all()[:2])
        # 排序
        teacher6 = Teacher.objects.all().order_by('.fans')
        for t in teacher6:
            print(t.fans)
        # 链式查询
        teacher7 = Teacher.objects.filter(fans__gte=100).order_by('nickname')
        print(teacher7)

        # 查询原生SQL
        print(Teacher.objects.filter(fans__gte=100).order_by('nickname').query)


        # 返回新 Query API
        # 排除
        print(Student.objects.all().exclude(nickname="王同学"))
        # 排序
        print(Student.objects.all().exclude(nickname="王同学").reverse())
        # 修改返回结果的字段名，将nickname改为name
        students = Student.objects.all().extra(select={"name":"nickname"})
        for student in students:
            print(student.name)

        # 获取字典或元组形式的 QuerySet
        print(TeacherAssisant.objects.values('nickname', 'hobby'))
        # < QuerySet[{'nickname': '助教1', 'hobby': '慕课网学习'}, {'nickname': '助教2', 'hobby': '看电影'}, {'nickname': '助教3', 'hobby': '读书'}] >

        print(TeacherAssisant.objects.values_list('nickname', 'hobby'))
        # <QuerySet [('助教1', '慕课网学习'), ('助教2', '看电影'), ('助教3', '读书')]>

        print(TeacherAssisant.objects.values_list('nickname', flat=True))
        # <QuerySet ['助教2', '助教3', '助教1']>

        # 根据日期获取查询集
        print(Course.objects.dates('created_at', 'day', order='DESC'))
        # <QuerySet [datetime.date(2020, 7, 26)]>

        print(Course.objects.datetimes('created_at', 'second', order='DESC'))
        # <QuerySet [datetime.datetime(2020, 7, 26, 9, 25, 21, tzinfo=<UTC>)]>

        # 并集：union、交集：inersection、差集：difference
        p_240 = Course.objects.filter(price__gte=240)
        p_260 = Course.objects.filter(price__lte=260)
        print(p_240.union(p_260))
        # 引擎不支持会报错
        # print(p_240.inersection(p_260))
        # print(p_240.difference(p_260))

        # 一对多、多对一查询优化：select_related
        # 一对多、多对多查询优化：prefetch_related
        courses = Course.objects.all()
        for c in courses:
            print(f"{c.title} -- {c.teacher.nickname} -- {c.teacher.fans}")
        # (0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
        # (0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
        # (0.000) SELECT `ormbaimoc_course`.`title`, `ormbaimoc_course`.`teacher_id`, `ormbaimoc_course`.`type`, `ormbaimoc_course`.`price`, `ormbaimoc_course`.`volume`, `ormbaimoc_course`.`online`, `ormbaimoc_course`.`created_at`, `ormbaimoc_course`.`updated_at` FROM `ormbaimoc_course`; args=()
        # (0.000) SELECT `ormbaimoc_teacher`.`nickname`, `ormbaimoc_teacher`.`introduction`, `ormbaimoc_teacher`.`fans`, `ormbaimoc_teacher`.`created_at`, `ormbaimoc_teacher`.`updated_at` FROM `ormbaimoc_teacher` WHERE `ormbaimoc_teacher`.`nickname` = 'Henry' LIMIT 21; args=('Henry',)
        # (0.000) SELECT `ormbaimoc_teacher`.`nickname`, `ormbaimoc_teacher`.`introduction`, `ormbaimoc_teacher`.`fans`, `ormbaimoc_teacher`.`created_at`, `ormbaimoc_teacher`.`updated_at` FROM `ormbaimoc_teacher` WHERE `ormbaimoc_teacher`.`nickname` = 'Henry' LIMIT 21; args=('Henry',)
        # (0.000) SELECT `ormbaimoc_teacher`.`nickname`, `ormbaimoc_teacher`.`introduction`, `ormbaimoc_teacher`.`fans`, `ormbaimoc_teacher`.`created_at`, `ormbaimoc_teacher`.`updated_at` FROM `ormbaimoc_teacher` WHERE `ormbaimoc_teacher`.`nickname` = 'Allen' LIMIT 21; args=('Allen',)
        # (0.000) SELECT `ormbaimoc_teacher`.`nickname`, `ormbaimoc_teacher`.`introduction`, `ormbaimoc_teacher`.`fans`, `ormbaimoc_teacher`.`created_at`, `ormbaimoc_teacher`.`updated_at` FROM `ormbaimoc_teacher` WHERE `ormbaimoc_teacher`.`nickname` = 'Allen' LIMIT 21; args=('Allen',)
        # (0.000) SELECT `ormbaimoc_teacher`.`nickname`, `ormbaimoc_teacher`.`introduction`, `ormbaimoc_teacher`.`fans`, `ormbaimoc_teacher`.`created_at`, `ormbaimoc_teacher`.`updated_at` FROM `ormbaimoc_teacher` WHERE `ormbaimoc_teacher`.`nickname` = 'Allen' LIMIT 21; args=('Allen',)
        # (0.000) SELECT `ormbaimoc_teacher`.`nickname`, `ormbaimoc_teacher`.`introduction`, `ormbaimoc_teacher`.`fans`, `ormbaimoc_teacher`.`created_at`, `ormbaimoc_teacher`.`updated_at` FROM `ormbaimoc_teacher` WHERE `ormbaimoc_teacher`.`nickname` = 'Jack' LIMIT 21; args=('Jack',)
        # (0.000) SELECT `ormbaimoc_teacher`.`nickname`, `ormbaimoc_teacher`.`introduction`, `ormbaimoc_teacher`.`fans`, `ormbaimoc_teacher`.`created_at`, `ormbaimoc_teacher`.`updated_at` FROM `ormbaimoc_teacher` WHERE `ormbaimoc_teacher`.`nickname` = 'Jack' LIMIT 21; args=('Jack',)
        # (0.000) SELECT `ormbaimoc_teacher`.`nickname`, `ormbaimoc_teacher`.`introduction`, `ormbaimoc_teacher`.`fans`, `ormbaimoc_teacher`.`created_at`, `ormbaimoc_teacher`.`updated_at` FROM `ormbaimoc_teacher` WHERE `ormbaimoc_teacher`.`nickname` = 'Jack' LIMIT 21; args=('Jack',)
        # (0.000) SELECT `ormbaimoc_teacher`.`nickname`, `ormbaimoc_teacher`.`introduction`, `ormbaimoc_teacher`.`fans`, `ormbaimoc_teacher`.`created_at`, `ormbaimoc_teacher`.`updated_at` FROM `ormbaimoc_teacher` WHERE `ormbaimoc_teacher`.`nickname` = 'Jack' LIMIT 21; args=('Jack',)

        courses = Course.objects.all().select_related('teacher')
        for c in courses:
            print(f"{c.title} -- {c.teacher.nickname} -- {c.teacher.fans}")
        # (0.016) (SELECT `ormbaimoc_course`.`title`, `ormbaimoc_course`.`teacher_id`, `ormbaimoc_course`.`type`, `ormbaimoc_course`.`price`, `ormbaimoc_course`.`volume`, `ormbaimoc_course`.`online`, `ormbaimoc_course`.`created_at`, `ormbaimoc_course`.`updated_at` FROM `ormbaimoc_course` WHERE `ormbaimoc_course`.`price` >= 240) UNION (SELECT `ormbaimoc_course`.`title`, `ormbaimoc_course`.`teacher_id`, `ormbaimoc_course`.`type`, `ormbaimoc_course`.`price`, `ormbaimoc_course`.`volume`, `ormbaimoc_course`.`online`, `ormbaimoc_course`.`created_at`, `ormbaimoc_course`.`updated_at` FROM `ormbaimoc_course` WHERE `ormbaimoc_course`.`price` <= 260) LIMIT 21; args=(240, 260)
        # (0.000) SELECT `ormbaimoc_course`.`title`, `ormbaimoc_course`.`teacher_id`, `ormbaimoc_course`.`type`, `ormbaimoc_course`.`price`, `ormbaimoc_course`.`volume`, `ormbaimoc_course`.`online`, `ormbaimoc_course`.`created_at`, `ormbaimoc_course`.`updated_at`, `ormbaimoc_teacher`.`nickname`, `ormbaimoc_teacher`.`introduction`, `ormbaimoc_teacher`.`fans`, `ormbaimoc_teacher`.`created_at`, `ormbaimoc_teacher`.`updated_at` FROM `ormbaimoc_course` LEFT OUTER JOIN `ormbaimoc_teacher` ON (`ormbaimoc_course`.`teacher_id` = `ormbaimoc_teacher`.`nickname`); args=()

        students = Student.objects.filter(age__lt=30).prefetch_related("course")
        for s in students:
            print(s.course.all())
        # (0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
        # (0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
        # (0.000) SELECT `ormbaimoc_student`.`nickname`, `ormbaimoc_student`.`age`, `ormbaimoc_student`.`gender`, `ormbaimoc_student`.`study_time`, `ormbaimoc_student`.`created_at`, `ormbaimoc_student`.`updated_at` FROM `ormbaimoc_student` WHERE `ormbaimoc_student`.`age` < 30 ORDER BY `ormbaimoc_student`.`age` ASC; args=(30,)

        # 反向查询：父表查询子表
        print(Teacher.objects.get(nickname="Jack").course_set.all())
        # print(Teacher.objects.get(nickname="Jack").teacher.all())

        # annotate(): 使用聚合计数，求和，平均等
        print(Course.objects.values('teacher').annotate(vol=Sum('volume')))
        print(Course.objects.values('teacher').annotate(vol=Avg('volume')))
        print(Course.objects.values('teacher').annotate(vol=Max('volume')))
        print(Course.objects.values('teacher').annotate(vol=Min('volume')))

        # raw(),执行原始SQL


        """不返回 Query API """
        # 获取对象 ---------------------------------
        # get()
        # get_or_create()
        # first()
        print(Course.objects.first())

        # last()
        print(Course.objects.last())

        # latest(), 注意需要设置 get_latest_by = 'created_at'
        print(Course.objects.latest())

        # earliest()
        print(Course.objects.earliest())

        # in_bulk(), 批量插入数据
        print(Course.objects.in_bulk(['Golang 系列教程1', 'Python 系列教程4']))

        # 更新对象 ---------------------------------
        # update()
        Course.objects.filter(title="Golang 系列教程1").update(price=6666)
        # update_or_create()
        Course.objects.filter(title="Java 系列教程5").update_or_create(title="Java 系列教程5", price=888, volume=55, online=date(2018, 6, 6))

        # 删除对象 ----------------------------------
        Course.objects.filter(title='Java 系列教程5').delete()

        # 其他操作 exists()，count(), aggregate()，判断是否存在，统计个数，聚合
        print(Course.objects.filter(title='test').exists())
        print(Course.objects.filter(title='Golang 系列教程1').exists())
        print(Course.objects.count())
        print(Course.objects.aggregate(Max('price'), Min('price'), Avg('price'), Sum('volume')))