Django框架之ORM

# 自增长 默认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()

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

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)

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

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

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`
"""

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同学']>

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
"""

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

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同学');
"""

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用途是强制对查询结果禁用排序
"""

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;

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

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})
"""

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;
"""