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

点赞

发表评论

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

  1. supreme clothing 说道:

    I and my buddies have been examining the nice recommendations on your web page and then suddenly got an awful suspicion I had not expressed respect to the website owner for those tips. All the women ended up as a result warmed to see them and have in effect seriously been enjoying these things. Thanks for simply being considerably thoughtful and then for deciding on such marvelous resources most people are really desirous to be informed on. My honest regret for not expressing gratitude to you sooner.

  2. curry shoes 说道:

    I precisely needed to thank you very much once again. I am not sure what I might have accomplished without these opinions contributed by you about such a theme. It seemed to be a very horrifying condition in my view, nevertheless understanding the professional approach you resolved the issue took me to leap over delight. Now i'm grateful for this support and in addition believe you comprehend what an amazing job you were undertaking educating many others using your blog. Probably you haven't encountered any of us.

  3. jordan shoes 说道:

    A lot of thanks for your entire hard work on this website. My daughter enjoys setting aside time for investigations and it's easy to understand why. Most of us learn all relating to the lively tactic you produce very helpful guidelines through the web blog and even increase participation from some other people about this concern so our own child is actually starting to learn so much. Have fun with the remaining portion of the new year. You are performing a powerful job.

  4. kyrie 7 说道:

    Thanks a lot for giving everyone a very remarkable chance to read from this web site. It is often very superb and jam-packed with amusement for me and my office fellow workers to visit the blog nearly thrice in one week to read the newest items you have. And definitely, I am just usually motivated with the superb hints you give. Selected 2 areas in this post are completely the finest I've ever had.

  5. off white jordan 1 说道:

    A lot of thanks for each of your efforts on this site. My mum really likes working on research and it's really easy to understand why. We all know all regarding the compelling form you provide insightful things through your website and as well recommend contribution from other ones on this concept while our simple princess is really understanding a great deal. Have fun with the rest of the new year. Your conducting a fantastic job.