ORM 增删改查、聚合分组、F、Q

创建项目

django-admin  startproject django_orm 

进入项目,创建应用

cd django_orm
python manage.py startapp orm_test

编辑项目配置文件,添加应用,和日志设置

INSTALLED_APPS = [
    '...',
    'orm_test.apps.OrmTestConfig',
]
# 日志配置
LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console':{
            'level':'DEBUG',
            'class':'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level':'DEBUG',
        },
    }
}

进入应用目录,编辑models.py,设计表结构

from django.db import models

# Create your models here.
class Student(models.Model):
    name = models.CharField(max_length=32, verbose_name="姓名")
    age = models.IntegerField(verbose_name="年龄")
    classes = models.ForeignKey("Class", verbose_name="班级", on_delete=models.CASCADE)

    class Meta():
        db_table = "s_student"  #自定义表名

    def __str__(self):
        return self.name

class Class(models.Model):
    name = models.CharField(max_length=32, verbose_name="班级")
    teacher = models.ManyToManyField("Teacher", verbose_name="教师")

    class Meta():
        db_table = "s_class"

    def __str__(self):
        return self.name

class Teacher(models.Model):
    name = models.CharField(max_length=32, verbose_name="姓名")
    course = models.ForeignKey("Course", verbose_name="课程", on_delete=models.CASCADE)   # 级联删除(删除对象时,会连带删除外键关联的对象)

    class Meta():
        db_table = "s_teacher"

    def __str__(self):
        return self.name


class Course(models.Model):
    name = models.CharField(max_length=32, verbose_name="课程")

    class Meta():
        db_table = "s_course"

    def __str__(self):
        return self.name

class Achievement(models.Model):
    student = models.ForeignKey("Student", verbose_name="学生", on_delete=models.CASCADE)
    course = models.ForeignKey("Course", verbose_name="课程", on_delete=models.CASCADE)
    source = models.DecimalField(max_digits=5, decimal_places=2, verbose_name="成绩")

    class Meta():
        db_table = "s_achievement"
        unique_together = ("student", "course")     # 联合唯一约束

将设计的表结构同步到数据库

python manage.py makemigrations
python manage.py migrate

进入项目终端环境

python manage.py shell

导入数据模型对象,进行ORM演示

from orm_test import models

根据表模型设计,先创建课程(增)

单表添加数据

  • 第一种创建对象的方式
# 先实例化对象,再保存
course_obj = models.Course(name='语文')
course_obj.save()
  • 第二种创建对象的方式
# 直接创建
models.Course.objects.create(name='语文')

一对多的添加数据(例如,可能有多个语文老师)

#通过外键传入对象添加
models.Teacher.objects.create(name='wangchenxi', course=course_obj)
#通过外键pk添加
models.Teacher.objects.create(name='xiaopengyou', course_id=1)
#反向通过set创建添加
course_obj.teacher_set.create(name='yunshou')

多对多的添加数据(例如,一个班级有多个老师任教)

#添加数学课程
cobj = models.Course(name='数学')
cobj.save()
#添加数学老师
cobj.teacher_set.create(name='mathTeacher')
#创建班级
class_obj = models.Class.objects.create(name='19级')
#分配数学老师、语文老师到创建的班级
class_obj.teacher.add(1,4)
ts = models.Teacher.objects.values_list('name', 'pk')
#>>> ts
#(0.000) SELECT "s_teacher"."name", "s_teacher"."id" FROM "s_teacher" LIMIT 21; args=()
#<QuerySet [('陈婷', 1), ('胡令能', 5), ('好男人', 6), ('mathTeacher', 7), ('healthTeacher', 8)]>
ts = models.Teacher.objects.all()
#(0.000) SELECT "s_teacher"."id", "s_teacher"."name", "s_teacher"."course_id" FROM "s_teacher" #INNER JOIN "s_class_teacher" ON ("s_teacher"."id" = "s_class_teacher"."teacher_id") WHERE #"s_class_teacher"."class_id" = 1 LIMIT 21; args=(1,)
#<QuerySet [<Teacher: 陈婷>, <Teacher: 好男人>]>
#通过对象添加
 class_1911.teacher.add(ts[1], ts[3], ts[4])
#(0.000) SELECT "s_teacher"."id", "s_teacher"."name", "s_teacher"."course_id" FROM "s_teacher" LIMIT 1 OFFSET 1; args=()
#(0.000) SELECT "s_teacher"."id", "s_teacher"."name", "s_teacher"."course_id" FROM "s_teacher" LIMIT 1 OFFSET 3; args=()
#(0.000) SELECT "s_teacher"."id", "s_teacher"."name", "s_teacher"."course_id" FROM "s_teacher" LIMIT 1 OFFSET 4; args=()
#(0.000) BEGIN; args=None
#(0.000) SELECT "s_class_teacher"."teacher_id" FROM "s_class_teacher" WHERE ("s_class_teacher"."class_id" = 1 AND "s_class_teacher"."teacher_id" IN (8, 5, 7)); args=(1, 8, 5, 7)
#(0.000) INSERT INTO "s_class_teacher" ("class_id", "teacher_id") SELECT 1, 8 UNION ALL SELECT 1, 5 UNION ALL SELECT 1, 7; args=(1, 8, 1, 5, 1, 7)

一种:基本上都是通过传入对象或pk(id)来创建对象的;

二种:通过关联字段外键的反向创建添加

ORM常用查询

方法 说明
all() 查询所有结果
filter(**kwargs) 查询所有符合筛选条件的对象,多个值是搜索条件为and,如果想用or或非需要使用Q查询
get(**kwargs) 查询符合筛选条件的对象,但返回值只能有一个,超过一个则报错(get() returned more than one)
exclude(**kwargs) 返回 筛选条件不符合的数据(反选)
order_by(*field) 对结果进行排序
reverse() 对返回结果反选
count() 计数
first() 返回第一条数据
last() 返回最后一条数据
exists() 判断查询数据是否为空,有数据返回True,空则返回False
values(*field) 返回一个QuerySet,列表中每条数据为一个字典
values_list(*field) 返回一个QuerySet,列表中每条数据为一个元组
distinct() 对结果去重

双下划线处理

# 双下划线查询
# 查询所有学生的数学成绩
models.Course.objects.filter(name='数学').values('achievement__student__name', 'achievement__source')
# SELECT "s_student"."name", "s_achievement"."source" FROM "s_course" LEFT OUTER JOIN "s_achievement" ON ("s_course"."id" = "s_achievement"."course_id") LEFT OUTER JOIN "s_student" ON ("s_achievement"."student_id" = "s_student"."id") WHERE "s_course"."name" = '数学'  LIMIT 21; 
# 

# 查询二哈同学所有学科的成绩
models.Student.objects.filter(name='二哈').values('achievement__course__name','achievement__source')
# SELECT "s_course"."name", "s_achievement"."source" FROM "s_student" LEFT OUTER JOIN "s_achievement" ON ("s_student"."id" = "s_achievement"."student_id") LEFT OUTER JOIN "s_course" ON ("s_achievement"."course_id" = "s_course"."id") WHERE "s_student"."name" = '二哈'  LIMIT 21;

聚合查询

聚合查询是使用aggreate(args,*kwargs)方法,对QuerySet进行计算。

使用前导入

from django.db.models import Sum, Avg, Max, MinCount

# 求学生总数
# pk 即主键ID
models.Student.objects.aggregate(s_num=Count('pk'))
# SELECT COUNT("s_student"."id") AS "s_num" FROM "s_student";



# 求1506班学生总和
# 1. LEFT JOIN
models.Class.objects.filter(name='1506').aggregate(Count('student__pk'))
# SELECT COUNT("s_student"."id") AS "student__pk__count" FROM "s_class" LEFT OUTER JOIN "s_student" ON ("s_class"."id" = "s_student"."classes_id") WHERE "s_class"."name" = '1506';
# 2. INNER JOIN
models.Student.objects.filter(classes__name='1506').aggregate(Count('pk'))
# SELECT COUNT("s_student"."id") AS "pk__count" FROM "s_student" INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE "s_class"."name" = '1506';


# 查询1506班的老师总数
# 1. LEFT JOIN
 models.Class.objects.filter(name='1506').aggregate(t_count = Count('teacher__pk'))
 # SELECT COUNT("s_class_teacher"."teacher_id") AS "t_count" FROM "s_class" LEFT OUTER JOIN "s_class_teacher" ON ("s_class"."id" = "s_class_teacher"."class_id") WHERE "s_class"."name" = '1506';
 # 2. INNER JOIN
 models.Teacher.objects.filter(class__name='1506').aggregate(t_count = Count('pk'))
# SELECT COUNT("s_teacher"."id") AS "t_count" FROM "s_teacher" INNER JOIN "s_class_teacher" ON ("s_teacher"."id" = "s_class_teacher"."teacher_id") INNER JOIN "s_class" ON ("s_class_teacher"."class_id" = "s_class"."id") WHERE "s_class"."name" = '1506'; 


# 查询二哈同学考试学科总数
# 当Count中字段为id时可以省略
models.Student.objects.filter(name='二哈').aggregate(co_count = Count('achievement'))
# SELECT COUNT("s_achievement"."id") AS "co_count" FROM "s_student" LEFT OUTER JOIN "s_achievement" ON ("s_student"."id" = "s_achievement"."student_id") WHERE "s_student"."name" = '二哈';


# 查询二哈同学的总成绩
models.Student.objects.filter(name='二哈').aggregate(co_sum = Sum('achievement__source'))
# SELECT CAST(SUM("s_achievement"."source") AS NUMERIC) AS "co_sum" FROM "s_student" LEFT OUTER JOIN "s_achievement" ON ("s_student"."id" = "s_achievement"."student_id") WHERE "s_student"."name" = '二哈';


# 求1506班物理平均成绩
models.Course.objects.filter(name='物理',achievement__student__classes__name='1506').aggregate(Avg('achievement__source'))
# SELECT CAST(AVG("s_achievement"."source") AS NUMERIC) AS "achievement__source__avg" FROM "s_course" INNER JOIN "s_achievement" ON ("s_course"."id" = "s_achievement"."course_id") INNER JOIN "s_student" ON ("s_achievement"."student_id" = "s_student"."id") INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE ("s_class"."name" = '1506' AND "s_course"."name" = '物理');

# 求1506班物理成绩最高是多少
models.Course.objects.filter(name='物理',achievement__student__classes__name='1506').aggregate(Max('achievement__source'))
# SELECT CAST(MAX("s_achievement"."source") AS NUMERIC) AS "achievement__source__max" FROM "s_course" INNER JOIN "s_achievement" ON ("s_course"."id" = "s_achievement"."course_id") INNER JOIN "s_student" ON ("s_achievement"."student_id" = "s_student"."id") INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE ("s_class"."name" = '1506' AND "s_course"."name" = '物理');


# 求1506班物理成绩最低是多少
models.Course.objects.filter(name='物理',achievement__student__classes__name='1506').aggregate(Min('achievement__source'))

分组查询

models.表.objects.values(‘分组字段’).annotate(统计字段)

# 查看每门学科的最低分
models.Course.objects.values('name').annotate(Min('achievement__source'))
# SELECT "s_course"."name", CAST(MIN("s_achievement"."source") AS NUMERIC) AS "achievement__source__min" FROM "s_course" LEFT OUTER JOIN "s_achievement" ON ("s_course"."id" = "s_achievement"."course_id") GROUP BY "s_course"."name"  LIMIT 21;

常用双下划线方法

方法 说明
field__lt 小于
field__ gt 大于
field__ lte 小于等于
field__ gte 大于等于
field__ in [1,2,4]在列表中
field__ range (2,6)在范围内
field__contains 获取字段中包含指定字符串的数据
field__icontains 获取字段中包含指定字符串的数据,大小写不敏感
field__startswith 获取字段中以指定字符串开头的数据
field__istartswith 获取字段中以指定字符串开头的数据,大小写不敏感
field__endswith 获取字段中以指定字符串结尾的数据
field__iendswith 获取字段中以指定字符串结尾的数据,大小写不敏感

F查询

对查询结果进行引用,用于两个不同字段的对比 需要导入模块

from django.db.models import F
# F()对象之间,F()与常数之间可以进行加减乘除和取模操作
# 查询分数比年龄*2还低的学生
models.Achievement.objects.filter(student__age__gt=F('source')*2).values('student__name', 'student__age', 'source')
# SELECT "s_student"."name", "s_student"."age", "s_achievement"."source" FROM "s_achievement" INNER JOIN "s_student" ON ("s_achievement"."student_id" = "s_student"."id") WHERE "s_student"."age" > ("s_achievement"."source")  LIMIT 21; 

Q查询

filter()方法默认是and,并没有提供or和not方法,无法使用无法使用复杂的查询方法,可以使用Q方法封装字段进行复杂的查询,可以组合使用 &(and), |(or),~(not)操作符对Q对象进行操作。

需要导入模块

from django.db import Q

# 查询年龄15岁,并且在1506班上课的同学
models.Student.objects.filter(Q(age=15) & Q(classes__name='1508'))
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE ("s_student"."age" = 15 AND "s_class"."name" = '1508')  LIMIT 21;

# 查询年龄12岁或在1508班上课的同学
models.Student.objects.filter(Q(age=12) | Q(classes__name='1508'))
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE ("s_student"."age" = 12 OR "s_class"."name" = '1508')  LIMIT 21;

# 查找1506班以外的同学
models.Student.objects.filter(~Q(classes__name='1506'))
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE NOT ("s_class"."name" = '1506')  LIMIT 21;

# 优先级 NOT >AND>OR
# 查找年龄大于25岁或者年龄等于12岁并且不在1506班的学生
models.Student.objects.filter(Q(age__gt=25) | Q(age=12) & ~Q(classes__name='1506'))
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE ("s_student"."age" > 25 OR ("s_student"."age" = 12 AND NOT ("s_class"."name" = '1506')))  LIMIT 21;

更新

# 修改二哈同学的物理成绩为50分
models.Achievement.objects.filter(student__name='二哈', course__name='物理').update(source=50)
# UPDATE "s_achievement" SET "source" = '50.00' WHERE "s_achievement"."id" IN (SELECT U0."id" FROM "s_achievement" U0 INNER JOIN "s_course" U1 ON (U0."course_id" = U1."id") INNER JOIN "s_student" U2 ON (U0."student_id" = U2."id") WHERE (U1."name" = '物理' AND U2."name" = '二哈'));

# 修改二哈同学的物理成绩为70分
erha_obj = models.Achievement.objects.filter(student__name='二哈', course__name='物理').first()
# SELECT "s_achievement"."id", "s_achievement"."student_id", "s_achievement"."course_id", "s_achievement"."source" FROM "s_achievement" INNER JOIN "s_course" ON ("s_achievement"."course_id" = "s_course"."id") INNER JOIN "s_student" ON ("s_achievement"."student_id" = "s_student"."id") WHERE ("s_course"."name" = '物理' AND "s_student"."name" = '二哈') ORDER BY "s_achievement"."id" ASC  LIMIT 1; 
erha_obj.source = '70
erha_obj.save()
# UPDATE "s_achievement" SET "student_id" = 2, "course_id" = 3, "source" = '70.00' WHERE "s_achievement"."id" = 6; args=(2, 3, '70.00', 6)

删除

# 删除tom老师
models.Teacher.objects.filter(name='tom').delete()
# SELECT "s_teacher"."id", "s_teacher"."name", "s_teacher"."course_id" FROM "s_teacher" WHERE "s_teacher"."name" = 'tom'; args=('tom',)
# BEGIN; args=None
# DELETE FROM "s_class_teacher" WHERE "s_class_teacher"."teacher_id" IN (3); args=(3,)
# DELETE FROM "s_teacher" WHERE "s_teacher"."id" IN (3); args=(3,)
# 返回值:(3, {'student_info.Class_teacher': 2, 'student_info.Teacher': 1})

引用链接:https://blog.csdn.net/weixin_37038498/article/details/99177422

引用链接:https://www.cnblogs.com/maple-shaw/articles/9403501.html

版权声明:除特别注明外,本站所有文章均为王晨曦个人站点原创

转载请注明:出处来自王晨曦个人站点 » ORM 增删改查、聚合分组、F、Q

点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注