天天看點
傳回

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 = "" # 定義資料庫表空間

模型類開發執行個體

Django架構之ORM
"""
講師 - 助教 一對一
講師 - 課程表 一對多
課程表 - 學生 多對多
"""


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;
"""
sql 關系型資料庫 mysql shell api go 資料庫 Python orm django字段 Django架構 orm django功能 django orm批量 django代碼架構
上一篇: How to create CDS view to return Service order item detail data
下一篇: 使用instr 函數優化替換Like條件子句提高資料檢索性能案例總結

繼續閱讀