第四十八篇 ORM查询操作,外键查询,QuerySet用法

心得:你是你,你一直都在,必须得相信自己,没有过不去的坎,时间在一分一秒的流逝,而你在一点一滴的成长。

一、ORM查询

在 ORM 层面,这 些查询条件都是使用
field _ _ condition 的方式来使用的。以下将那些常用的查询条件来一 一解释。(两个下划线)

models.py

class User(models.Model):
    username = models.CharField(max_length=100)
    password = models.CharField(max_length=100)

exact:
使用精确查找对应到SQL语句里,相当于=,如果在python中提供的是None在SQL语句中将被翻译为NULL.

user = User.objects.filter(id__exact=2)
user2 = User.objects.filter(id__exact=None)
print(user.query)
print(user2.query)

exact将被翻译为=条件,往往在ORM中会将__exact省略。

SELECT `rear_user`.`id`, `rear_user`.`username`, `rear_user`.`password` FROM `rear_user` WHERE `rear_user`.`id` = 2
SELECT `rear_user`.`id`, `rear_user`.`username`, `rear_user`.`password` FROM `rear_user` WHERE `rear_user`.`id` IS NULL

iexact:

使用like 进行查找:

user = User.objects.filter(id__iexact=2)

SQL

WHERE `rear_user`.`id` LIKE 2

所以 exact 和 iexact 的 区别实际上就是 LIKE 和 = 的区别

contains:
大小写敏感,判断某个字段是否包含了某个数据

user = User.objects.filter(username__contains="to")
print(user.query)

SQL

WHERE `rear_user`.`username` LIKE BINARY %to%

要注意的是,在使用 contains 的时候,翻译成的 sql 语句左右两边是有百分号的,意味着使用 的是模糊查询。而 exact 翻译成 sql 语句左右两边是没有百分号的,意味着使用的是精确的查询。LIKE BINARY在SQL中意味着大小写敏感。

icontains:
大小写不敏感的匹配查询

user = User.objects.filter(username__icontains="to")
print(user.query)

SQL

WHERE `rear_user`.`username` LIKE %to%

in:
提取那些给定的 field 的值是否在给定的容器中。容器可以为 list 、 tuple 或者任何一个可以 迭代的对象,包括 QuerySet 对象

user = User.objects.filter(id__in=[1,2,3])
print(user.query)

SQL

WHERE `rear_user`.`id` IN (1, 2, 3)

gt
great than
某个 field 的值要大于给定的值:

user = User.objects.filter(id__gt=2)
print(user.query)

SQL

WHERE `rear_user`.`id` > 2

gte:
great than equal

lt:
less than

lte:
less than equal

startswith:
判断某个字段的值是否是以某个值开始的。大小写敏感,翻译之后存在LIKE BINARY

user = User.objects.filter(username__startswith="to")

SQL

WHERE `rear_user`.`username` LIKE BINARY to%

istartwith:
判断某个字段的值是否是以某个值开始的。大小写不敏感

endswith
判断某个字段值是否以某个值结束的,大小写敏感

iendswith:
类似于 endswith ,只不过大小写不敏感。

date:

import datetime
def find(request):
    user = User.objects.filter(date__date=datetime.date(2020,6,1))
    print(user.quer)

SQL

WHERE DATE(`rear_user`.`date`) = 2020-06-01

或者使用链式调用,获取两天的数据:

user = User.objects.filter(date__date=datetime.date(2020,6,1)).filter(date__date=datetime.date(2020,6,2))

SQL

WHERE (DATE(`rear_user`.`date`) = 2020-06-01 AND DATE(`rear_user`.`date`) = 2020-06-02)

range

start_date = datetime.date(2005, 1, 1)
end_date = datetime.date(2005, 3, 31)
Entry.objects.filter(pub_date__range=(start_date, end_date))

year
查询某年:
Entry.objects.filter(pub_date__year=2005)\

month
查询某月:
Entry.objects.filter(pub_date__month=12)

day
某天
Entry.objects.filter(pub_date__day=3)

week_day
星期几
Entry.objects.filter(pub_date__week_day=2)

isnull:
根据值是否为空进行查找:
获取所有不为空的内容

user = User.objects.filter(id__isnull=False)

SQL

WHERE `rear_user`.`id` IS NOT NULL

二、聚合函数

1.所有的聚合函数都是放在django.db.models下面
2.聚合函数需要放在aggregate方法中执行
3. 聚合函数执行完成后,会给聚合的值取名,规则为filed__聚合函数名
4. aggregate返回的是一个字典,不可以用query去查询SQL语句。
5. 查询SQL语句可以用以下方法:
from django.db import connection print(connection.queries)
Avg :求平均值

user = User.objects.aggregate(Avg('id'))
结果为:
{'id__avg': 2.0}

Count :获取指定的对象的个数

user = User.objects.aggregate(Count('id'))
结果:
{'id__count': 3}

Max 和 Min :获取指定对象的最大值和最小值

user = User.objects.aggregate(Max('id'),Min('id'))
结果:
{'id__max': 3, 'id__min': 1}

Sum :求指定对象的总和

user = User.objects.aggregate(Sum('id'))

SQL

{'id__sum': Decimal('6')}

python中如何提取这个值

Decimal('320.00').to_eng_string()

三、QuerySet和F,Q查询

filter :
将满足条件的数据提取出来,返回一个新的 QuerySet

exclude :
排除满足条件的数据,返回一个新的 QuerySet

annotate :
给 QuerySet 中的每个对象都添加一个使用查询表达式(聚合函数、F表达式、Q 表达式、Func表达式等)的新字段,相当于给表加入附加字段。

order_by :
指定将查询的结果根据某个字段进行排序。如果要倒叙排序,那么可以在这个字 段的前面加一个负号

正序:
ASC

user = User.objects.order_by("id","date")
SQL:
ORDER BY `rear_user`.`id` ASC, `rear_user`.`date` ASC

倒叙加负号:

DESC

user = User.objects.order_by("-id","date")
SQL:
ORDER BY `rear_user`.`id` DESC, `rear_user`.`date` ASC

也可以在模型Meta类中定义ordering来指定默认的排序方式:

class Meta:
	db_table = 'user'
	ordering = ['-id','date']

F查询
如果我们要对两个字段的值做比较,Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段,来比较同一个 model 实例中两个不同字段的值。
Django 支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。

from django.db.models import F, Q
models.Book.objects.filter(commnet_num__lt=F('keep_num')*2)

Q查询
filter() 等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询(例如OR语句),你可以使用Q对象。

查询作者名是小仙女或小魔女的

from django.db.models import F, Q
models.Book.objects.filter(Q(authors__name="小仙女")|Q(authors__name="小魔女"))

你可以组合& 和| 操作符以及使用括号进行分组来编写任意复杂的Q 对象。同时,Q 对象可以使用~ 操作符取反,这允许组合正常的查询和取反(NOT) 查询。
示例:查询作者名字是小仙女并且不是2018年出版的书的书名。

models.Book.objects.filter(Q(author__name="小仙女") & ~Q(publish_date__year=2018)).values_list("title")
<QuerySet [('番茄物语',)]

values
返回的结果为字典
用来指定在提取数据出来,需要提取哪些字段。当需要跨表提取表字段时需要加入双下滑线,比如values('book__title') 原表类名为Book,提取字段时需要将其小写。

user = User.objects.all().values('id','username')
for i in user:
	print(i)

结果

{'id': 3, 'username': 'tom'}
{'id': 2, 'username': 'tom'}
{'id': 1, 'username': 'tom'}

values_list

将返回的QuerySet类型转换为元组类型,再通过list方法转换为列表。

user = User.objects.all().values_list()
print(list(user))

结果:

[(3, 'tom', 'tom123', datetime.datetime(2020, 1, 6, 13, 27, 8, 86819, tzinfo=<UTC>)), (2, 'tom', 'tom123', datetime.datetime(2020, 1, 6, 13, 27, 8, 86819, tzinfo=<UTC>)), (1, 'tom', 'tom123', datetime.datetime(2020, 1, 6, 13, 27, 8, 86819, tzinfo=<UTC>))]

分组:
在SQL语句中分组需要使用group_by指令去进行分组操作,而在ORM查询中,分组操作为:需要将谁分组用那个表去查询,下列例子需要用出版社去分组。

# 查找每个出版社出版价格最高的书籍价格
from django.db.models import *
ret = models.Publisher.objects.annotate(Max('book__price')).values()

十三条:

返回对象列表的方法
all()
filter()
exclude()
order_by()
reverse()
distinct()
values()  {}
values_list()   ()

返回对象的方法
get()
first()
last()
create()

返回布尔值的
exists()

返回数字的
count()

四、外键查询

1 正向查找
从有ForeignKey的表查起
对象查找(跨表)

  • 一对一或多对一

语法:
对象.关联字段.字段
示例:先获取书本对象,然后点外键就可以拿到出版社对象,不能使用QuerySet方法。

book_obj = models.Book.objects.first()  # 第一本书对象
print(book_obj.publisher)  # 得到这本书关联的出版社对象
print(book_obj.publisher.name)  # 得到出版社对象的名称
  • 多对多

先获取书本对象,书本对象对应多个作者,所以点出来的是作者的QuerySet对象,所以可以使用filter,all,values,values_list等方法来筛选。如果需要查询字段则基字段为作者。

查询名为python开天辟地的这本书的作者的年龄
book_obj = models.Book.objects.filter(title="python开天辟地").first()
for author in book_obj.author.all():
    print(author.name,author.age)

字段查找(跨表)
语法:
关联字段__字段(字段加__要查询的属性)
示例:

print(models.Book.objects.values_list("publisher__name"))

2 反向操作
对象查找,多对多或一对多
语法:
obj.表名_set
示例:
先获取出版社的对象,再获取书本的集合,然后筛选所需要的书本,基字段为书

publisher_obj = models.Publisher.objects.first()  # 找到第一个出版社对象
books = publisher_obj.book_set.all()  # 找到第一个出版社出版的所有书
books = publisher_obj.books.all()  # 在Foreikey中设置relate_name=books时,反向查找可以用books来查找对象
titles = books.values_list("title")  # 找到第一个出版社出版的所有书的书名

字段查找
语法:
表名__字段(表名小写加__要查询的属性)
示例:

titles = models.Publisher.objects.values_list("book__title")

五、数据库查询性能相关

查看SQL语句配置settings文件

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level': 'DEBUG',
        },
    }
}
  1. [{} ]
    all_users = models.User.objects.all().values(‘name’,‘age’,‘role__name’)

  2. [ 对象 ]
    all_users = models.User.objects.all()
    用的时候注意,只拿自己表中的字段,别跨表

  3. select_related (外键、一对一)
    all_users = models.User.objects.all().select_related(‘role’)

  4. prefetch_related (role)
    all_users = models.User.objects.all().prefetch_related(‘role’)

  5. only
    all_users = models.User.objects.all().only(‘name’)
    用的时候注意,只拿自己指定的字段

  6. defer
    all_users = models.User.objects.all().defer(‘name’)

六、练习题:

from django.db import models


class Student(models.Model):
    """学生表"""

    name = models.CharField(max_length=100)
    gender = models.SmallIntegerField()

    class Meta:
        db_table = 'student'


class Course(models.Model):
    """课程表"""

    name = models.CharField(max_length=100)
    teacher = models.ForeignKey("Teacher", on_delete=models.SET_NULL, null=True)

    class Meta:
        db_table = 'course'


class Score(models.Model):
    """分数表"""

    student = models.ForeignKey("Student", on_delete=models.CASCADE)
    course = models.ForeignKey("Course", on_delete=models.CASCADE)
    number = models.FloatField()

    class Meta:
        db_table = 'score'


class Teacher(models.Model):
    """老师表"""

    name = models.CharField(max_length=100)

    class Meta:
        db_table = 'teacher'

练习题:

  1. 查询平均成绩大于60分的同学的id和平均成绩;
rows = Student.objects.annotate(avg=Avg("score__number")).filter(avg__gte=60).valu es("id","avg")
  1. 查询所有同学的id、姓名、选课的数、总成绩;
 rows = Student.objects.annotate(course_nums=Count("score__course"),total_score=Sum( "score__number")) .values("id","name","course_nums","total_score") for row in rows:     print(row)
  1. 查询姓“李”的老师的个数;
 teacher_nums = Teacher.objects.filter(name__startswith="李").count() print(teacher_nums)
  1. 查询没学过“黄老师”课的同学的id、姓名;
rows = Student.objects.exclude(score__course__teacher__name="黄老师").values('id', 'name') for row in rows:     print(row)
  1. 查询学过课程id为1和2的所有同学的id、姓名;
 rows = Student.objects.filter(score__course__in=[1,2]).distinct().values('id','nam e') for row in rows:     print(row)
  1. 查询学过“黄老师”所教的所有课的同学的学号、姓名;
 rows = Student.objects.annotate(nums=Count("score__course",filter=Q(score__course_ _teacher__name='黄老师'))) .filter(nums=Course.objects.filter(teacher__name='黄老师').count()).values('id','n ame') for row in rows:     print(row)
  1. 查询所有课程成绩小于60分的同学的id和姓名;
 students = Student.objects.exclude(score__number__gt=60) for student in students:     print(student)
  1. 查询没有学全所有课的同学的id、姓名;
 students = Student.objects.annotate(num=Count(F("score__course"))).filter(num__lt= Course.objects.count()).values('id','name') for student in students:     print(student)
  1. 查询所有学生的姓名、平均分,并且按照平均分从高到低排序;
 students = Student.objects.annotate(avg=Avg("score__number")).order_by("-avg").val ues('name','avg') for student in students:     print(student)
  1. 查询各科成绩的最高和最低分,以如下形式显示:课程ID,课程名称,最高分,最低分:
courses = Course.objects.annotate(min=Min("score__number"),max=Max("score__number") ).values("id",'name','min','max') for course in courses:    print(course)
  1. 查询每门课程的平均成绩,按照平均成绩进行排序;
courses = Course.objects.annotate(avg=Avg("score__number")).order_by('avg').values( 'id','name','avg') for course in courses:    print(course)
  1. 统计总共有多少女生,多少男生;
rows = Student.objects.aggregate(male_num=Count("gender",filter=Q(gender=1)),female _num=Count("gender",filter=Q(gender=2))) print(rows)
  1. 将“黄老师”的每一门课程都在原来的基础之上加5分;
rows = Score.objects.filter(course__teacher__name='黄老师').update(number=F("number" )+5) print(rows)
  1. 查询两门以上不及格的同学的id、姓名、以及不及格课程数;
students = Student.objects.annotate(bad_count=Count("score__number",filter=Q(score_ _number__lt=60))).filter(bad_count__gte=2).values('id','name','bad_count') for student in students:    print(student)
  1. 查询每门课的选课人数;
courses = Course.objects.annotate(student_nums=Count("score__student")).values('id', 'name','student_nums') for course in courses:    print(course)

ORM联表查询练习
在项目下可以创建py文件并导入django环境,可以使用脚本直接执行ORM语句
练习题

import os

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "orm_homework.settings")
import django

django.setup()

from app01 import models

ret = models.Book.objects.all()

# 查找所有书名里包含金老板的书
ret = models.Book.objects.filter(title__contains='金')
# 查找出版日期是2018年的书
ret = models.Book.objects.filter(publish_date__year='2018').values()
# 查找出版日期是2017年的书名
ret = models.Book.objects.filter(publish_date__year='2017').values()
# 查找价格大于10元的书
ret = models.Book.objects.filter(price__gt='10').values('price')
# 查找价格大于10元的书名和价格
ret = models.Book.objects.filter(price__gt='10').values('title','price')
# 查找memo字段是空的书
ret = models.Book.objects.filter(memo__isnull=True)
# 查找在北京的出版社
ret = models.Publisher.objects.filter(city='北京')
# 查找名字以沙河开头的出版社startswith
ret = models.Publisher.objects.filter(name__startswith='沙河')
# 查找“沙河出版社”出版的所有书籍
ret = models.Book.objects.filter(publisher__name='沙河出版社')
ret = models.Publisher.objects.filter(name='沙河出版社').values('book__title')
# 查找每个出版社出版价格最高的书籍价格
from django.db.models import *
ret = models.Publisher.objects.annotate(Max('book__price')).values()
# 查找每个出版社的名字以及出的书籍数量
ret = models.Publisher.objects.annotate(count=Count('book__id')).values('name','count')
#
# 查找作者名字里面带“小”字的作者
ret = models.Author.objects.filter(name__contains='小')
# 查找年龄大于30岁的作者
ret = models.Author.objects.filter(age__gt='30')
# 查找手机号是155开头的作者
ret = models.Author.objects.filter(phone__startswith='155')
# 查找手机号是155开头的作者的姓名和年龄
ret = models.Author.objects.filter(phone__startswith='155').values('name','age')
# 查找每个作者写的价格最高的书籍价格
ret = models.Author.objects.annotate(max=Max('book__price')).values('name','max')
# 查找每个作者的姓名以及出的书籍数量
ret = models.Author.objects.annotate(count=Count('book__id')).values('name','count')
#
# 查找书名是“跟金老板学开车”的书的出版社
ret = models.Publisher.objects.filter(book__title='跟金老板学开车')
# 查找书名是“跟金老板学开车”的书的出版社所在的城市
ret = models.Publisher.objects.filter(book__title='跟金老板学开车').values('book__title','city')
# 查找书名是“跟金老板学开车”的书的出版社的名称
ret = models.Publisher.objects.filter(book__title='跟金老板学开车').values('name')
# 查找书名是“跟金老板学开车”的书的出版社出版的其他书籍的名字和价格
r = models.Publisher.objects.filter(book__title='跟金老板学开车').first()
res = models.Book.objects.filter(publisher=r).exclude(title='跟金老板学开车')
print(res)
#
# 查找书名是“跟金老板学开车”的书的所有作者
ret = models.Author.objects.filter(book__title='跟金老板学开车').values('name')
# 查找书名是“跟金老板学开车”的书的作者的年龄
ret = models.Author.objects.filter(book__title='跟金老板学开车').values('name','age')
# 查找书名是“跟金老板学开车”的书的作者的手机号码
ret = models.Author.objects.filter(book__title='跟金老板学开车').values('name','age','phone')
# 查找书名是“跟金老板学开车”的书的作者们的姓名以及出版的所有书籍名称和价钱
ret = models.Author.objects.filter(book__title='跟金老板学开车').values('name','book__title','book__price')


print(ret)
print(ret.query)

  • 0
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值