ANSHENG'S BLOG
Django模型常见问题
# djangopublishDate · 2019-05-09 / lastEditedTime · 2022-04-06 04:49:00

如何查看queryset的SQL语句

有时你想知道Django ORM如何执行我们的查询或者你正在编写的代码的对应SQL是什么,你可以通过queryset.querystr以获取执行SQL语句

  • 示例1

获取Event所有的记录

>>> from events.models import Event >>> queryset = Event.objects.all() >>> str(queryset.query) 'SELECT "events_event"."id", "events_event"."epic_id", "events_event"."details", "events_event"."years_ago" FROM "events_event"'
  • 示例2

获取Event years_ago大于5的记录

>>> queryset = Event.objects.filter(years_ago__gt=5) >>> str(queryset.query) 'SELECT "events_event"."id", "events_event"."epic_id", "events_event"."details", "events_event"."years_ago" FROM "events_event" WHERE "events_event"."years_ago" > 5'
  • 实例3

获取非Queryset的query

>>> from django.db import connection >>> e = Event(epic_id='12', years_ago='2019') >>> e.save() >>> print(connection.queries)

如何在Django ORM中进行OR查询

  • 添加测试数据
>>> from django.contrib.auth import get_user_model >>> User = get_user_model() >>> User.objects.create_user(username='yash', first_name='Yash', last_name='Rastogi') <User: yash> >>> User.objects.create_user(username='John', first_name='John', last_name='Kumar') <User: John> >>> User.objects.create_user(username='Ricky', first_name='Ricky', last_name='Dayal') <User: Ricky> >>> User.objects.create_user(username='sharukh', first_name='Sharukh', last_name='Misra') <User: sharukh> >>> User.objects.create_user(username='Ritesh', first_name='Ritesh', last_name='Deshmukh') <User: Ritesh> >>> User.objects.create_user(username='Billy', first_name='Billy', last_name='Sharma') <User: Billy> >>> User.objects.create_user(username='Radha', first_name='Radha', last_name='George') <User: Radha> >>> User.objects.create_user(username='sohan', first_name='Sohan', last_name='Upadhyay') <User: sohan> >>> User.objects.create_user(username='Raghu', first_name='Raghu', last_name='Khan') <User: Raghu> >>> User.objects.create_user(username='rishab', first_name='Rishab', last_name='Deol') <User: rishab>
  • 需求

假设要查找first_name以P开头或者last_name以D开头的用户

Django提供两种方案:

  • queryset_1 | queryset_2

  • filter(Q(<condition_1>)|Q(<condition_2>)

  • 方案1

>>> queryset = User.objects.filter(first_name__startswith='R') | User.objects.filter(last_name__startswith='D') >>> queryset <QuerySet [<User: Ricky>, <User: Ritesh>, <User: Radha>, <User: Raghu>, <User: rishab>]>
  • 方案2

通过Q进行查询

>>> from django.db.models import Q >>> qs = User.objects.filter(Q(first_name__startswith='R')|Q(last_name__startswith='D')) >>> qs <QuerySet [<User: Ricky>, <User: Ritesh>, <User: Radha>, <User: Raghu>, <User: rishab>]>

如何在Django ORM中进行AND查询

  • 需求

假设要查找first_name以P开头并且last_name以D开头的用户

Django提供三种方案:

  • filter(<condition_1>, <condition_2>)

  • queryset_1 & queryset_2

  • filter(Q(<condition_1>) & Q(<condition_2>))

  • 方案1

组合多个条件的默认filter方式是AND,所以你可以这样做

>>> queryset_1 = User.objects.filter(first_name__startswith='R', last_name__startswith='D') >>> queryset_1 <QuerySet [<User: Ricky>, <User: Ritesh>, <User: rishab>]>
  • 方案2

使用运算符

>>> queryset_2 = User.objects.filter(first_name__startswith='R') & User.objects.filter(last_name__startswith='D') >>> queryset_2 <QuerySet [<User: Ricky>, <User: Ritesh>, <User: rishab>]>
  • 方案3

使用Q对象

>>> queryset_3 = User.objects.filter(Q(first_name__startswith='R') & Q(last_name__startswith='D')) >>> queryset_3 <QuerySet [<User: Ricky>, <User: Ritesh>, <User: rishab>]>
  • 查看生成查询的SQL语句是否完全相等
>>> str(queryset_1.query) == str(queryset_2.query) == str(queryset_3.query) True

如何在Django queryset中进行NOT查询

  • 需求

使用NOT操作获取 id < 5的所有用户

Django提供两种方案:

  • exclude(<condition>)

  • filter(~Q(<condition>))

  • 方案2

>>> queryset = User.objects.filter(~Q(id__lt=5)) >>> queryset <QuerySet [<User: Ritesh>, <User: Billy>, <User: Radha>, <User: sohan>, <User: Raghu>, <User: rishab>]>

如何从相同或不同的Model中联合两个查询集

UNION运算符用于组合两个或多个查询集的结果集,查询集可以来自相同或不同的模型,当它们的查询集来自不同的模型时,字段及其数据类型应该匹配。

使用User模型生成2个查询集来执行联合操作

>>> q1 = User.objects.filter(id__gte=5) >>> q2 = User.objects.filter(id__lte=9) >>> q1.union(q2) <QuerySet [<User: yash>, <User: John>, <User: Ricky>, <User: sharukh>, <User: Ritesh>, <User: Billy>, <User: Radha>, <User: sohan>, <User: Raghu>, <User: rishab>]> >>> q2.union(q1) <QuerySet [<User: yash>, <User: John>, <User: Ricky>, <User: sharukh>, <User: Ritesh>, <User: Billy>, <User: Radha>, <User: sohan>, <User: Raghu>, <User: rishab>]>

由于HeroVillain都有namegender,我们可以values_list用来限制选定的字段然后做一个联合

Hero.objects.all().values_list( "name", "gender" ).union( Villain.objects.all().values_list( "name", "gender" ))

这会给你所有HeroVillain对象的name和gender。

如何仅在查询集中选择某些字段

  • 需求

我们想要查询first_name以R开头所有用户的first_name和last_name

Django提供了两种方法来实现这一点:

  • values(输出字典) and values_list(输出列表) methods on queryset.

  • only_method

  • 方案1

>>> queryset = User.objects.filter(first_name__startswith='R').values('first_name', 'last_name') >>> queryset <QuerySet [{'first_name': 'Ricky', 'last_name': 'Dayal'}, {'first_name': 'Ritesh', 'last_name': 'Deshmukh'}, {'first_name': 'Radha', 'last_name': 'George'}, {'first_name': 'Raghu', 'last_name': 'Khan'}, {'first_name': 'Rishab', 'last_name': 'Deol'}]>

可以查看生成的SQL语句

>>> str(queryset.query) 'SELECT "auth_user"."first_name", "auth_user"."last_name" FROM "auth_user" WHERE "auth_user"."first_name" LIKE R% ESCAPE \\'\\\\\\''
  • 方案2
>>> queryset = User.objects.filter(first_name__startswith='R').only("first_name", "last_name") >>> queryset <QuerySet [<User: Ricky>, <User: Ritesh>, <User: Radha>, <User: Raghu>, <User: rishab>]>

查看生成的SQL语句

>>> str(queryset.query) 'SELECT "auth_user"."id", "auth_user"."first_name", "auth_user"."last_name" FROM "auth_user" WHERE "auth_user"."first_name" LIKE R% ESCAPE \\'\\\\\\''

方案1和方案2唯一的区别是only会输出id而方案1却不会。

如何在Django中执行子查询表达式

Django允许使用SQL子查询,有一个与User表相关联的UserParent Model。所有的UserParent都有UserParent

>>> from django.db.models import Subquery >>> users = User.objects.all() >>> UserParent.objects.filter(user_id__in=Subquery(users.values('id'))) <QuerySet [<UserParent: UserParent object (2)>, <UserParent: UserParent object (5)>, <UserParent: UserParent object (8)>]>

如何使用基于比较字段值的条件过滤查询集

  • 创建测试数据
>>> User.objects.create_user(email="shabda@example.com", username="shabda", first_name="Shabda", last_name="Raaj") <User: shabda> >>> User.objects.create_user(email="guido@example.com", username="Guido", first_name="Guido", last_name="Guido") <User: Guido>
  • 需求

查询first_namelast_last相等的用户

>>> from django.db.models import F >>> User.objects.filter(last_name=F("first_name")) <QuerySet [<User: Guido>]>
>>> User.objects.create_user(email="guido@example.com", username="Tim", first_name="Tim", last_name="Teters") <User: Tim> >>> from django.db.models.functions import Substr >>> User.objects.annotate(first=Substr("first_name", 1, 1), last=Substr("last_name", 1, 1)).filter(first=F("last")) <QuerySet [<User: Guido>, <User: Tim>]>

如何在没有任何文件的情况下过滤FileField

FileFieldImageField存储文件或图像的路径,在DB级别,它们与CharField相同。

因此,要找到没有任何文件的FileField,我们可以查询。

no_files_objects = MyModel.objects.filter( Q(file='')|Q(file=None) )

如何使用Django ORM找到第二条记录

可以使用切片运算符从查询中找到第N条记录

>>> user = User.objects.order_by('-last_login')[1] # 第二最高记录 >>> user.first_name 'John' >>> user = User.objects.order_by('-last_login')[2] # 第三最高记录 >>> user.first_name 'Ricky'

如果你想查看到底执行了什么SQL语句,其实使用的就是LIMIT ... OFFSET

SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" ORDER BY "auth_user"."last_login" DESC LIMIT 1 OFFSET 2

如何查找具有重复字段值的行

  • 修改测试数据
>>> User.objects.filter(pk__in=[2,11,13]).update(first_name='John') 3

可以使用以下技术找到重复记录

>>> from django.db.models import Count >>> duplicates = User.objects.values('first_name').annotate(name_count=Count('first_name')).filter(name_count__gt=1) >>> duplicates <QuerySet [{'first_name': 'John', 'name_count': 3}]>

如果你想到所有的记录,可以这样做

>>> records = User.objects.filter(first_name__in=[item['first_name'] for item in duplicates]) >>> print([item.id for item in records]) [2, 11, 13]

如何从queryset中查找不同的字段值

您想要查找名称未重复的用户。你可以这样做