执行查询

一旦创建 数据模型 后,Django 自动给予你一套数据库抽象 API,允许你创建,检索,更新和删除对象。本页介绍如何使用这些 API。参考 数据模型参考 获取所有查询选项的完整细节。

在本指南中(以及在参考资料中),我们将提及以下模型,它们构成了一个博客应用程序:

from datetime import date

from django.db import models


class Blog(models.Model):
    name = models.CharField(max_length=100)
    tagline = models.TextField()

    def __str__(self):
        return self.name


class Author(models.Model):
    name = models.CharField(max_length=200)
    email = models.EmailField()

    def __str__(self):
        return self.name


class Entry(models.Model):
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
    headline = models.CharField(max_length=255)
    body_text = models.TextField()
    pub_date = models.DateField()
    mod_date = models.DateField(default=date.today)
    authors = models.ManyToManyField(Author)
    number_of_comments = models.IntegerField(default=0)
    number_of_pingbacks = models.IntegerField(default=0)
    rating = models.IntegerField(default=5)

    def __str__(self):
        return self.headline

创建对象

为了用 Python 对象展示数据表对象,Django 使用了一套直观的系统:一个模型类代表一张数据表,一个模型类的实例代表数据库表中的一行记录。

要创建一个对象,用关键字参数初始化它,然后调用 save() 将其存入数据库。

Assuming models live in a file mysite/blog/models.py, here's an example:

>>> from blog.models import Blog
>>> b = Blog(name="Beatles Blog", tagline="All the latest Beatles news.")
>>> b.save()

这在幕后执行了 INSERT SQL 语句。Django 在你显式调用 save() 才操作数据库。

save() 方法没有返回值。

参见

save() 接受很多此处未介绍的高级选项。参考文档 save() 获取完整细节。

要一步创建并保存一个对象,使用 create() 方法。

将修改保存至对象

要将修改保存至数据库中已有的某个对象,使用 save()

Given a Blog instance b5 that has already been saved to the database, this example changes its name and updates its record in the database:

>>> b5.name = "New name"
>>> b5.save()

这在幕后执行了 UPDATE SQL 语句。Django 在你显示调用 save() 后才操作数据库。

保存 ForeignKeyManyToManyField 字段

Updating a ForeignKey field works exactly the same way as saving a normal field -- assign an object of the right type to the field in question. This example updates the blog attribute of an Entry instance entry, assuming appropriate instances of Entry and Blog are already saved to the database (so we can retrieve them below):

>>> from blog.models import Blog, Entry
>>> entry = Entry.objects.get(pk=1)
>>> cheese_blog = Blog.objects.get(name="Cheddar Talk")
>>> entry.blog = cheese_blog
>>> entry.save()

Updating a ManyToManyField works a little differently -- use the add() method on the field to add a record to the relation. This example adds the Author instance joe to the entry object:

>>> from blog.models import Author
>>> joe = Author.objects.create(name="Joe")
>>> entry.authors.add(joe)

To add multiple records to a ManyToManyField in one go, include multiple arguments in the call to add(), like this:

>>> john = Author.objects.create(name="John")
>>> paul = Author.objects.create(name="Paul")
>>> george = Author.objects.create(name="George")
>>> ringo = Author.objects.create(name="Ringo")
>>> entry.authors.add(john, paul, george, ringo)

Django 会在添加或指定错误类型的对象时报错。

检索对象

要从数据库检索对象,要通过模型类的 Manager 构建一个 QuerySet

一个 QuerySet 代表来自数据库中对象的一个集合。它可以有 0 个,1 个或者多个 filters. Filters,可以根据给定参数缩小查询结果量。在 SQL 的层面上, QuerySet 对应 SELECT 语句,而*filters*对应类似 WHERELIMIT 的限制子句。

You get a QuerySet by using your model's Manager. Each model has at least one Manager, and it's called objects by default. Access it directly via the model class, like so:

>>> Blog.objects
<django.db.models.manager.Manager object at ...>
>>> b = Blog(name="Foo", tagline="Bar")
>>> b.objects
Traceback:
    ...
AttributeError: "Manager isn't accessible via Blog instances."

备注

Managers 只能通过模型类访问,而不是通过模型实例,目的是强制分离 “表级” 操作和 “行级” 操作。

Manager 是模型的 QuerySets 主要来源。例如 Blog.objects.all() 返回了一个 QuerySet,后者包含了数据库中所有的 Blog 对象。

检索全部对象

The simplest way to retrieve objects from a table is to get all of them. To do this, use the all() method on a Manager:

>>> all_entries = Entry.objects.all()

方法 all() 返回了一个包含数据库中所有对象的 QuerySet 对象。

通过过滤器检索指定对象

all() 返回的 QuerySet 包含了数据表中所有的对象。虽然,大多数情况下,你只需要完整对象集合的一个子集。

要创建一个这样的子集,你需要通过添加过滤条件精炼原始 QuerySet。两种最常见的精炼 QuerySet 的方式是:

filter(**kwargs)
返回一个新的 QuerySet,包含的对象满足给定查询参数。
exclude(**kwargs)
返回一个新的 QuerySet,包含的对象 满足给定查询参数。

查询参数(**kwargs)应该符合下面的 Field lookups 的要求。

例如,要包含获取 2006 年的博客条目(entries blog)的 QuerySet,像这样使用 filter():

Entry.objects.filter(pub_date__year=2006)

通过默认管理器类也一样:

Entry.objects.all().filter(pub_date__year=2006)

链式过滤器

The result of refining a QuerySet is itself a QuerySet, so it's possible to chain refinements together. For example:

>>> Entry.objects.filter(headline__startswith="What").exclude(
...     pub_date__gte=datetime.date.today()
... ).filter(pub_date__gte=datetime.date(2005, 1, 30))

这个先获取包含数据库所有条目(entry)的 QuerySet,然后排除一些,再进入另一个过滤器。最终的 QuerySet 包含标题以 "What" 开头的,发布日期介于 2005 年 1 月 30 日与今天之间的所有条目。

每个 QuerySet 都是唯一的

每次精炼一个 QuerySet,你就会获得一个全新的 QuerySet,后者与前者毫无关联。每次精炼都会创建一个单独的、不同的 QuerySet,能被存储,使用和复用。

Example:

>>> q1 = Entry.objects.filter(headline__startswith="What")
>>> q2 = q1.exclude(pub_date__gte=datetime.date.today())
>>> q3 = q1.filter(pub_date__gte=datetime.date.today())

这三个 QuerySets 是独立的。第一个是基础 QuerySet,包含了所有标题以 "What" 开头的条目。第二个是第一个的子集,带有额外条件,排除了 pub_date 是今天和今天之后的所有记录。第三个是第一个的子集,带有额外条件,只筛选 pub_date 是今天或未来的所有记录。最初的 QuerySet (q1) 不受筛选操作影响。

QuerySet 是惰性的

QuerySets are lazy -- the act of creating a QuerySet doesn't involve any database activity. You can stack filters together all day long, and Django won't actually run the query until the QuerySet is evaluated. Take a look at this example:

>>> q = Entry.objects.filter(headline__startswith="What")
>>> q = q.filter(pub_date__lte=datetime.date.today())
>>> q = q.exclude(body_text__icontains="food")
>>> print(q)

虽然这看起来像是三次数据库操作,实际上只在最后一行 (print(q)) 做了一次。一般来说, QuerySet 的结果直到你 “要使用” 时才会从数据库中拿出。当你要用时,才通过数据库 计算QuerySet。关于何时才真的执行计算的更多细节,参考 什么时候 QuerySet 被执行

get() 检索单个对象

filter() 总是返回一个 QuerySet,即便只有一个对象满足查询条件 —— 这种情况下, QuerySet 只包含了一个元素。

If you know there is only one object that matches your query, you can use the get() method on a Manager which returns the object directly:

>>> one_entry = Entry.objects.get(pk=1)

你可以对 get() 使用与 filter() 类似的所有查询表达式 —— 同样的,参考下面的 Field lookups

注意, 使用切片 [0] 时的 get()filter() 有点不同。如果没有满足查询条件的结果, get() 会抛出一个 DoesNotExist 异常。该异常是执行查询的模型类的一个属性 —— 所有,上述代码中,若没有哪个 Entry 对象的主键是 1,Django 会抛出 Entry.DoesNotExist

类似了,Django 会在有不止一个记录满足 get() 查询条件时发出警告。这时,Django 会抛出 MultipleObjectsReturned,这同样也是模型类的一个属性。

其它 QuerySet 方法

大多数情况下,你会在需要从数据库中检索对象时使用 all()get()filter()exclude()。然而,这样远远不够;完整的各种 QuerySet 方法请参阅 QuerySet API 参考

限制 QuerySet 条目数

利用 Python 的数组切片语法将 QuerySet 切成指定长度。这等价于 SQL 的 LIMITOFFSET 子句。

For example, this returns the first 5 objects (LIMIT 5):

>>> Entry.objects.all()[:5]

This returns the sixth through tenth objects (OFFSET 5 LIMIT 5):

>>> Entry.objects.all()[5:10]

不支持负索引 (例如 Entry.objects.all()[-1])

Generally, slicing a QuerySet returns a new QuerySet -- it doesn't evaluate the query. An exception is if you use the "step" parameter of Python slice syntax. For example, this would actually execute the query in order to return a list of every second object of the first 10:

>>> Entry.objects.all()[:10:2]

由于对 queryset 切片工作方式的模糊性,禁止对其进行进一步的排序或过滤。

To retrieve a single object rather than a list (e.g. SELECT foo FROM bar LIMIT 1), use an index instead of a slice. For example, this returns the first Entry in the database, after ordering entries alphabetically by headline:

>>> Entry.objects.order_by("headline")[0]

This is roughly equivalent to:

>>> Entry.objects.order_by("headline")[0:1].get()

然而,注意一下,若没有对象满足给定条件,前者会抛出 IndexError,而后者会抛出 DoesNotExist。参考 get() 获取更多细节。

字段查询

字段查询即你如何制定 SQL WHERE 子句。它们以关键字参数的形式传递给 QuerySet 方法 filter()exclude()get()

Basic lookups keyword arguments take the form field__lookuptype=value. (That's a double-underscore). For example:

>>> Entry.objects.filter(pub_date__lte="2006-01-01")

转换为 SQL 语句大致如下:

SELECT * FROM blog_entry WHERE pub_date <= '2006-01-01';

这是怎么做到的

Python 能定义可接受任意数量 name-value 参数的函数,参数名和值均在运行时计算。更多信息,请参考官方 Python 教程中的 Keyword Arguments

查询子句中指定的字段必须是模型的一个字段名。不过也有个例外,在 ForeignKey 中,你可以指定以 _id 为后缀的字段名。这种情况下,value 参数需要包含 foreign 模型的主键的原始值。例子:

>>> Entry.objects.filter(blog_id=4)

若你传入了无效的关键字参数,查询函数会抛出 TypeError

数据库 API 支持两套查询类型;完整参考文档位于 字段查询参考。为了让你了解能干啥,以下是一些常见的查询:

exact

An "exact" match. For example:

>>> Entry.objects.get(headline__exact="Cat bites dog")

会生成这些 SQL:

SELECT ... WHERE headline = 'Cat bites dog';

若你为提供查询类型 —— 也就说,若关键字参数未包含双下划线 —— 查询类型会被指定为 exact

For example, the following two statements are equivalent:

>>> Blog.objects.get(id__exact=14)  # Explicit form
>>> Blog.objects.get(id=14)  # __exact is implied

这是为了方便,因为 exact 查询是最常见的。

iexact

A case-insensitive match. So, the query:

>>> Blog.objects.get(name__iexact="beatles blog")

会匹配标题为 "Beatles Blog""beatles blog", 甚至 "BeAtlES blOG"Blog

contains

大小写敏感的包含测试。例子:

Entry.objects.get(headline__contains="Lennon")

粗略地转为 SQL:

SELECT ... WHERE headline LIKE '%Lennon%';

注意这将匹配标题 'Today Lennon honored',而不是 'today lennon honored'

这也有个大小写不敏感的版本, icontains

startswith, endswith
以……开头和以……结尾的查找。当然也有大小写不敏感的版本,名为 istartswithiendswith

同样,这只介绍了皮毛。完整的参考能在 field 查询参考 找到。

跨关系查询

Django 提供了一种强大而直观的方式来“追踪”查询中的关系,在幕后自动为你处理 SQL JOIN 关系。为了跨越关系,跨模型使用关联字段名,字段名由双下划线分割,直到拿到想要的字段。

This example retrieves all Entry objects with a Blog whose name is 'Beatles Blog':

>>> Entry.objects.filter(blog__name="Beatles Blog")

跨域的深度随你所想。

它也可以反向工作。虽然它 可以自定义,默认情况下,你在查找中使用模型的小写名称来引用一个 “反向” 关系。

This example retrieves all Blog objects which have at least one Entry whose headline contains 'Lennon':

>>> Blog.objects.filter(entry__headline__contains="Lennon")

如果你在跨多个关系进行筛选,而某个中间模型的没有满足筛选条件的值,Django 会将它当做一个空的(所有值都是 NULL)但是有效的对象。这样就意味着不会抛出错误。例如,在这个过滤器中:

Blog.objects.filter(entry__authors__name="Lennon")

(假设有个关联的 Author 模型),若某项条目没有任何关联的 author,它会被视作没有关联的 name,而不是因为缺失 author 而抛出错误。大多数情况下,这就是你期望的。唯一可能使你迷惑的场景是在使用 isnull 时。因此:

Blog.objects.filter(entry__authors__name__isnull=True)

将会返回 Blog 对象,包含 authorname 为空的对象,以及那些 entryauthor 为空的对象。若你不想要后面的对象,你可以这样写:

Blog.objects.filter(entry__authors__isnull=False, entry__authors__name__isnull=True)

跨多值关联

当跨越 ManyToManyField 或反查 ForeignKey (例如从 BlogEntry )时,对多个属性进行过滤会产生这样的问题:是否要求每个属性都在同一个相关对象中重合。我们可能会寻找那些在标题中含有 “Lennon” 的 2008 年的博客,或者我们可能会寻找那些仅有 2008 年的任何条目以及一些在标题中含有 “Lennon” 的较新或较早的条目。

要选择所有包含 2008 年至少一个标题中有 "Lennon" 的条目的博客(满足两个条件的同一条目),我们要写:

Blog.objects.filter(entry__headline__contains="Lennon", entry__pub_date__year=2008)

否则,如果要执行一个更为宽松的查询,选择任何只在标题中带有 "Lennon" 的条目和 2008 年的条目的博客,我们将写:

Blog.objects.filter(entry__headline__contains="Lennon").filter(
    entry__pub_date__year=2008
)

假设只有一个博客既有包含 "Lennon" 的条目又有 2008 年的条目,但 2008 年的条目中没有包含 "Lennon" 。第一个查询不会返回任何博客,但第二个查询会返回那一个博客。(这是因为第二个过滤器选择的条目可能与第一个过滤器中的条目相同,也可能不相同)。我们是用每个过滤器语句来过滤 Blog 项,而不是 Entry 项)。简而言之,如果每个条件需要匹配相同的相关对象,那么每个条件应该包含在一个 filter() 调用中。

备注

由于第二个(更宽松的)查询链接了多个过滤器,它对主模型进行了多次连接,可能会产生重复的结果。

>>> from datetime import date
>>> beatles = Blog.objects.create(name="Beatles Blog")
>>> pop = Blog.objects.create(name="Pop Music Blog")
>>> Entry.objects.create(
...     blog=beatles,
...     headline="New Lennon Biography",
...     pub_date=date(2008, 6, 1),
... )
<Entry: New Lennon Biography>
>>> Entry.objects.create(
...     blog=beatles,
...     headline="New Lennon Biography in Paperback",
...     pub_date=date(2009, 6, 1),
... )
<Entry: New Lennon Biography in Paperback>
>>> Entry.objects.create(
...     blog=pop,
...     headline="Best Albums of 2008",
...     pub_date=date(2008, 12, 15),
... )
<Entry: Best Albums of 2008>
>>> Entry.objects.create(
...     blog=pop,
...     headline="Lennon Would Have Loved Hip Hop",
...     pub_date=date(2020, 4, 1),
... )
<Entry: Lennon Would Have Loved Hip Hop>
>>> Blog.objects.filter(
...     entry__headline__contains="Lennon",
...     entry__pub_date__year=2008,
... )
<QuerySet [<Blog: Beatles Blog>]>
>>> Blog.objects.filter(
...     entry__headline__contains="Lennon",
... ).filter(
...     entry__pub_date__year=2008,
... )
<QuerySet [<Blog: Beatles Blog>, <Blog: Beatles Blog>, <Blog: Pop Music Blog]>

备注

filter() 的查询行为会跨越多值关联,就像前文说的那样,并不与 exclude() 相同。相反,一次 exclude() 调用的条件并不需要指向同一项目。

例如,以下查询会排除那些关联条目标题包含 "Lennon" 且发布于 2008 年的博客:

Blog.objects.exclude(
    entry__headline__contains="Lennon",
    entry__pub_date__year=2008,
)

但是,与 filter() 的行为不同,其并不会限制博客同时满足这两种条件。要这么做的话,也就是筛选出所有条目标题不带 "Lennon" 且发布年不是 2008 的博客,你需要做两次查询:

Blog.objects.exclude(
    entry__in=Entry.objects.filter(
        headline__contains="Lennon",
        pub_date__year=2008,
    ),
)

过滤器可以为模型指定字段

在之前的例子中,我们已经构建过的 filter 都是将模型字段值与常量做比较。但是,要怎么做才能将模型字段值与同一模型中的另一字段做比较呢?

Django 提供了 F 表达式 实现这种比较。 F() 的实例充当查询中的模型字段的引用。这些引用可在查询过滤器中用于在同一模型实例中比较两个不同的字段。

For example, to find a list of all blog entries that have had more comments than pingbacks, we construct an F() object to reference the pingback count, and use that F() object in the query:

>>> from django.db.models import F
>>> Entry.objects.filter(number_of_comments__gt=F("number_of_pingbacks"))

Django supports the use of addition, subtraction, multiplication, division, modulo, and power arithmetic with F() objects, both with constants and with other F() objects. To find all the blog entries with more than twice as many comments as pingbacks, we modify the query:

>>> Entry.objects.filter(number_of_comments__gt=F("number_of_pingbacks") * 2)

To find all the entries where the rating of the entry is less than the sum of the pingback count and comment count, we would issue the query:

>>> Entry.objects.filter(rating__lt=F("number_of_comments") + F("number_of_pingbacks"))

You can also use the double underscore notation to span relationships in an F() object. An F() object with a double underscore will introduce any joins needed to access the related object. For example, to retrieve all the entries where the author's name is the same as the blog name, we could issue the query:

>>> Entry.objects.filter(authors__name=F("blog__name"))

For date and date/time fields, you can add or subtract a timedelta object. The following would return all entries that were modified more than 3 days after they were published:

>>> from datetime import timedelta
>>> Entry.objects.filter(mod_date__gt=F("pub_date") + timedelta(days=3))

The F() objects support bitwise operations by .bitand(), .bitor(), .bitxor(), .bitrightshift(), and .bitleftshift(). For example:

>>> F("somefield").bitand(16)

Oracle

Oracle 不支持按位 XOR 操作。

Expressions can reference transforms

Django supports using transforms in expressions.

For example, to find all Entry objects published in the same year as they were last modified:

>>> from django.db.models import F
>>> Entry.objects.filter(pub_date__year=F("mod_date__year"))

To find the earliest year an entry was published, we can issue the query:

>>> from django.db.models import Min
>>> Entry.objects.aggregate(first_published_year=Min("pub_date__year"))

This example finds the value of the highest rated entry and the total number of comments on all entries for each year:

>>> from django.db.models import OuterRef, Subquery, Sum
>>> Entry.objects.values("pub_date__year").annotate(
...     top_rating=Subquery(
...         Entry.objects.filter(
...             pub_date__year=OuterRef("pub_date__year"),
...         )
...         .order_by("-rating")
...         .values("rating")[:1]
...     ),
...     total_comments=Sum("number_of_comments"),
... )

主键 (pk) 查询快捷方式

出于方便的目的,Django 提供了一种 pk 查询快捷方式, pk 表示主键 "primary key"。

In the example Blog model, the primary key is the id field, so these three statements are equivalent:

>>> Blog.objects.get(id__exact=14)  # Explicit form
>>> Blog.objects.get(id=14)  # __exact is implied
>>> Blog.objects.get(pk=14)  # pk implies id__exact

The use of pk isn't limited to __exact queries -- any query term can be combined with pk to perform a query on the primary key of a model:

# Get blogs entries with id 1, 4 and 7
>>> Blog.objects.filter(pk__in=[1, 4, 7])

# Get all blog entries with id > 14
>>> Blog.objects.filter(pk__gt=14)

pk lookups also work across joins. For example, these three statements are equivalent:

>>> Entry.objects.filter(blog__id__exact=3)  # Explicit form
>>> Entry.objects.filter(blog__id=3)  # __exact is implied
>>> Entry.objects.filter(blog__pk=3)  # __pk implies __id__exact

LIKE 语句中转义百分号和下划线

等效于 LIKE SQL 语句的字段查询子句 (iexactcontainsicontainsstartswithistartswithendswithiendswith) 会将 LIKE 语句中有特殊用途的两个符号,即百分号和下划线自动转义。(在 LIKE 语句中,百分号匹配多个任意字符,而下划线匹配一个任意字符。)

This means things should work intuitively, so the abstraction doesn't leak. For example, to retrieve all the entries that contain a percent sign, use the percent sign as any other character:

>>> Entry.objects.filter(headline__contains="%")

Django 为你小心处理了引号;生成的 SQL 语句看起来像这样:

SELECT ... WHERE headline LIKE '%\%%';

同样的处理也包括下划线。百分号和下划线都为你自动处理,你无需担心。

缓存和 QuerySet

每个 QuerySet 都带有缓存,尽量减少数据库访问。理解它是如何工作的能让你编写更高效的代码。

新创建的 QuerySet 缓存是空的。一旦要计算 QuerySet 的值,就会执行数据查询,随后,Django 就会将查询结果保存在 QuerySet 的缓存中,并返回这些显式请求的缓存(例如,下一个元素,若 QuerySet 正在被迭代)。后续针对 QuerySet 的计算会复用缓存结果。

Keep this caching behavior in mind, because it may bite you if you don't use your QuerySets correctly. For example, the following will create two QuerySets, evaluate them, and throw them away:

>>> print([e.headline for e in Entry.objects.all()])
>>> print([e.pub_date for e in Entry.objects.all()])

这意味着同样的数据库查询会被执行两次,实际加倍了数据库负载。同时,有可能这两个列表不包含同样的记录,因为在两次请求间,可能有 Entry 被添加或删除了。

To avoid this problem, save the QuerySet and reuse it:

>>> queryset = Entry.objects.all()
>>> print([p.headline for p in queryset])  # Evaluate the query set.
>>> print([p.pub_date for p in queryset])  # Reuse the cache from the evaluation.

QuerySet 未被缓存时

查询结果集并不总是缓存结果。当仅计算查询结果集的 部分 时,会校验缓存,若没有填充缓存,则后续查询返回的项目不会被缓存。特别地说,这意味着使用数组切片或索引的 限制查询结果集 不会填充缓存。

For example, repeatedly getting a certain index in a queryset object will query the database each time:

>>> queryset = Entry.objects.all()
>>> print(queryset[5])  # Queries the database
>>> print(queryset[5])  # Queries the database again

However, if the entire queryset has already been evaluated, the cache will be checked instead:

>>> queryset = Entry.objects.all()
>>> [entry for entry in queryset]  # Queries the database
>>> print(queryset[5])  # Uses cache
>>> print(queryset[5])  # Uses cache

Here are some examples of other actions that will result in the entire queryset being evaluated and therefore populate the cache:

>>> [entry for entry in queryset]
>>> bool(queryset)
>>> entry in queryset
>>> list(queryset)

备注

只是打印查询结果集不会填充缓存。因为调用 __repr__() 仅返回了完整结果集的一个切片。

Asynchronous queries

If you are writing asynchronous views or code, you cannot use the ORM for queries in quite the way we have described above, as you cannot call blocking synchronous code from asynchronous code - it will block up the event loop (or, more likely, Django will notice and raise a SynchronousOnlyOperation to stop that from happening).

Fortunately, you can do many queries using Django's asynchronous query APIs. Every method that might block - such as get() or delete() - has an asynchronous variant (aget() or adelete()), and when you iterate over results, you can use asynchronous iteration (async for) instead.

Query iteration

The default way of iterating over a query - with for - will result in a blocking database query behind the scenes as Django loads the results at iteration time. To fix this, you can swap to async for:

async for entry in Authors.objects.filter(name__startswith="A"):
    ...

Be aware that you also can't do other things that might iterate over the queryset, such as wrapping list() around it to force its evaluation (you can use async for in a comprehension, if you want it).

Because QuerySet methods like filter() and exclude() do not actually run the query - they set up the queryset to run when it's iterated over - you can use those freely in asynchronous code. For a guide to which methods can keep being used like this, and which have asynchronous versions, read the next section.

QuerySet and manager methods

Some methods on managers and querysets - like get() and first() - force execution of the queryset and are blocking. Some, like filter() and exclude(), don't force execution and so are safe to run from asynchronous code. But how are you supposed to tell the difference?

While you could poke around and see if there is an a-prefixed version of the method (for example, we have aget() but not afilter()), there is a more logical way - look up what kind of method it is in the QuerySet reference.

In there, you'll find the methods on QuerySets grouped into two sections:

  • Methods that return new querysets: These are the non-blocking ones, and don't have asynchronous versions. You're free to use these in any situation, though read the notes on defer() and only() before you use them.
  • Methods that do not return querysets: These are the blocking ones, and have asynchronous versions - the asynchronous name for each is noted in its documentation, though our standard pattern is to add an a prefix.

Using this distinction, you can work out when you need to use asynchronous versions, and when you don't. For example, here's a valid asynchronous query:

user = await User.objects.filter(username=my_input).afirst()

filter() returns a queryset, and so it's fine to keep chaining it inside an asynchronous environment, whereas first() evaluates and returns a model instance - thus, we change to afirst(), and use await at the front of the whole expression in order to call it in an asynchronous-friendly way.

备注

If you forget to put the await part in, you may see errors like "coroutine object has no attribute x" or "<coroutine …>" strings in place of your model instances. If you ever see these, you are missing an await somewhere to turn that coroutine into a real value.

事务

Transactions are not currently supported with asynchronous queries and updates. You will find that trying to use one raises SynchronousOnlyOperation.

If you wish to use a transaction, we suggest you write your ORM code inside a separate, synchronous function and then call that using sync_to_async - see 异步支持 for more.

查询 JSONField

JSONField 里的查找实现是不一样的,主要因为存在键转换。为了演示,我们将使用下面这个例子:

from django.db import models


class Dog(models.Model):
    name = models.CharField(max_length=200)
    data = models.JSONField(null=True)

    def __str__(self):
        return self.name

保存和查询 None

As with other fields, storing None as the field's value will store it as SQL NULL. While not recommended, it is possible to store JSON scalar null instead of SQL NULL by using Value(None, JSONField()).

无论存储哪种值,当从数据库检索时,JSON 标量 null 的 Python 表示法与 SQL 的 NULL 相同,即 None。因此,可能很难区分它们。

这只适用于 None 值作为字段的顶级值。如果 None 被保存在列表或字典中,它将始终被解释为 JSON 的 null 值。

When querying, None value will always be interpreted as JSON null. To query for SQL NULL, use isnull:

>>> Dog.objects.create(name="Max", data=None)  # SQL NULL.
<Dog: Max>
>>> Dog.objects.create(name="Archie", data=Value(None, JSONField()))  # JSON null.
<Dog: Archie>
>>> Dog.objects.filter(data=None)
<QuerySet [<Dog: Archie>]>
>>> Dog.objects.filter(data=Value(None, JSONField()))
<QuerySet [<Dog: Archie>]>
>>> Dog.objects.filter(data__isnull=True)
<QuerySet [<Dog: Max>]>
>>> Dog.objects.filter(data__isnull=False)
<QuerySet [<Dog: Archie>]>

除非你确定要使用 SQL 的 NULL 值,否则请考虑设置 null=False 并为空值提供合适的默认值,例如 default=dict

备注

保存 JSON 的 null 值不违反 Django 的 null=False

Changed in Django 4.2:

Support for expressing JSON null using Value(None, JSONField()) was added.

4.2 版后已移除: Passing Value("null") to express JSON null is deprecated.

键、索引和路径转换

To query based on a given dictionary key, use that key as the lookup name:

>>> Dog.objects.create(
...     name="Rufus",
...     data={
...         "breed": "labrador",
...         "owner": {
...             "name": "Bob",
...             "other_pets": [
...                 {
...                     "name": "Fishy",
...                 }
...             ],
...         },
...     },
... )
<Dog: Rufus>
>>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": None})
<Dog: Meg>
>>> Dog.objects.filter(data__breed="collie")
<QuerySet [<Dog: Meg>]>

Multiple keys can be chained together to form a path lookup:

>>> Dog.objects.filter(data__owner__name="Bob")
<QuerySet [<Dog: Rufus>]>

If the key is an integer, it will be interpreted as an index transform in an array:

>>> Dog.objects.filter(data__owner__other_pets__0__name="Fishy")
<QuerySet [<Dog: Rufus>]>

如果要查询的键与另一个查询的键名冲突,请改用 contains 来查询。

To query for missing keys, use the isnull lookup:

>>> Dog.objects.create(name="Shep", data={"breed": "collie"})
<Dog: Shep>
>>> Dog.objects.filter(data__owner__isnull=True)
<QuerySet [<Dog: Shep>]>

备注

上面给出的例子隐式地使用了 exact 查找。Key,索引和路径转换也可以用:icontainsendswithiendswithiexactregexiregexstartswithistartswithltltegtgte 以及 包含与键查找

KT() expressions

New in Django 4.2.
class KT(lookup)

Represents the text value of a key, index, or path transform of JSONField. You can use the double underscore notation in lookup to chain dictionary key and index transforms.

例如:

>>> from django.db.models.fields.json import KT
>>> Dog.objects.create(
...     name="Shep",
...     data={
...         "owner": {"name": "Bob"},
...         "breed": ["collie", "lhasa apso"],
...     },
... )
<Dog: Shep>
>>> Dogs.objects.annotate(
...     first_breed=KT("data__breed__1"), owner_name=KT("data__owner__name")
... ).filter(first_breed__startswith="lhasa", owner_name="Bob")
<QuerySet [<Dog: Shep>]>

备注

由于键-路径查询的工作方式,exclude()filter() 不能保证产生详尽的集合。如果你想包含没有路径的对象,请添加 isnull 查找。

警告

由于任何字符串都可以成为 JSON 对象中的一个键,除了下面列出的那些之外,任何查询都将被解释为一个键查询。不会出现错误。要格外小心打字错误,并经常检查你的查询是否按你的意图进行。

MariaDB 和 Oracle 用户

在键、索引或路径转换上使用 order_by() 将使用值的字符串表示法对对象进行排序。这是因为 MariaDB 和 Oracle 数据库没有提供将 JSON 值转换为其等价的 SQL 值的函数。

Oracle 用户

在 Oracle 数据库中,在 exclude() 查询中使用 None 作为查询值,将返回没有 null 作为指定路径的对象,包括没有路径的对象。在其他数据库后端,该查询将返回具有该路径且其值不是 null 的对象。

PostgreSQL 用户

在 PostgreSQL 上,如果只使用一个键或索引,那么会使用 SQL 运算符 -> 。如果使用多个操作符,则会使用 #> 运算符。

SQLite 用户

On SQLite, "true", "false", and "null" string values will always be interpreted as True, False, and JSON null respectively.

包含与键查找

contains

The contains lookup is overridden on JSONField. The returned objects are those where the given dict of key-value pairs are all contained in the top-level of the field. For example:

>>> Dog.objects.create(name="Rufus", data={"breed": "labrador", "owner": "Bob"})
<Dog: Rufus>
>>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
<Dog: Meg>
>>> Dog.objects.create(name="Fred", data={})
<Dog: Fred>
>>> Dog.objects.filter(data__contains={"owner": "Bob"})
<QuerySet [<Dog: Rufus>, <Dog: Meg>]>
>>> Dog.objects.filter(data__contains={"breed": "collie"})
<QuerySet [<Dog: Meg>]>

Oracle 和 SQLite

Oracle 和 SQLite 不支持 contains

contained_by

This is the inverse of the contains lookup - the objects returned will be those where the key-value pairs on the object are a subset of those in the value passed. For example:

>>> Dog.objects.create(name="Rufus", data={"breed": "labrador", "owner": "Bob"})
<Dog: Rufus>
>>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
<Dog: Meg>
>>> Dog.objects.create(name="Fred", data={})
<Dog: Fred>
>>> Dog.objects.filter(data__contained_by={"breed": "collie", "owner": "Bob"})
<QuerySet [<Dog: Meg>, <Dog: Fred>]>
>>> Dog.objects.filter(data__contained_by={"breed": "collie"})
<QuerySet [<Dog: Fred>]>

Oracle 和 SQLite

Oracle 和 SQLite 不支持 contained_by

has_key

Returns objects where the given key is in the top-level of the data. For example:

>>> Dog.objects.create(name="Rufus", data={"breed": "labrador"})
<Dog: Rufus>
>>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
<Dog: Meg>
>>> Dog.objects.filter(data__has_key="owner")
<QuerySet [<Dog: Meg>]>

has_keys

Returns objects where all of the given keys are in the top-level of the data. For example:

>>> Dog.objects.create(name="Rufus", data={"breed": "labrador"})
<Dog: Rufus>
>>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
<Dog: Meg>
>>> Dog.objects.filter(data__has_keys=["breed", "owner"])
<QuerySet [<Dog: Meg>]>

has_any_keys

Returns objects where any of the given keys are in the top-level of the data. For example:

>>> Dog.objects.create(name="Rufus", data={"breed": "labrador"})
<Dog: Rufus>
>>> Dog.objects.create(name="Meg", data={"owner": "Bob"})
<Dog: Meg>
>>> Dog.objects.filter(data__has_any_keys=["owner", "breed"])
<QuerySet [<Dog: Rufus>, <Dog: Meg>]>

通过 Q 对象完成复杂查询

在类似 filter() 中,查询使用的关键字参数是通过 "AND" 连接起来的。如果你要执行更复杂的查询(例如,由 OR 语句连接的查询),你可以使用 Q 对象

一个 Q 对象 (django.db.models.Q) 用于压缩关键字参数集合。这些关键字参数由前文 "Field lookups" 指定。

例如,该 Q 对象压缩了一个 LIKE 查询:

from django.db.models import Q

Q(question__startswith="What")

Q objects can be combined using the &, |, and ^ operators. When an operator is used on two Q objects, it yields a new Q object.

例如,该语句生成一个 Q 对象,表示两个 "question_startswith" 查询语句之间的 "OR" 关系:

Q(question__startswith="Who") | Q(question__startswith="What")

This is equivalent to the following SQL WHERE clause:

WHERE question LIKE 'Who%' OR question LIKE 'What%'

You can compose statements of arbitrary complexity by combining Q objects with the &, |, and ^ operators and use parenthetical grouping. Also, Q objects can be negated using the ~ operator, allowing for combined lookups that combine both a normal query and a negated (NOT) query:

Q(question__startswith="Who") | ~Q(pub_date__year=2005)

每个接受关键字参数的查询函数 (例如 filter()exclude()get()) 也同时接受一个或多个 Q 对象作为位置(未命名的)参数。若你为查询函数提供了多个 Q 对象参数,这些参数会通过 "AND" 连接。例子:

Poll.objects.get(
    Q(question__startswith="Who"),
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
)

...粗略地转为 SQL:

SELECT * from polls WHERE question LIKE 'Who%'
    AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')

查询函数能混合使用 Q 对象和关键字参数。所有提供给查询函数的参数(即关键字参数或 Q 对象)均通过 "AND" 连接。然而,若提供了 Q 对象,那么它必须位于所有关键字参数之前。例子:

Poll.objects.get(
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
    question__startswith="Who",
)

……会是一个有效的查询,等效于前文的例子;但是:

# INVALID QUERY
Poll.objects.get(
    question__startswith="Who",
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
)

……却是无效的。

参见

Django 单元测试中的 OR 查询实例 展示了 Q 的用法。

比较对象

要比较两个模型实例,使用标准的 Python 比较操作符,两个等号: ==。实际上,这比较了两个模型实例的主键值。

Using the Entry example above, the following two statements are equivalent:

>>> some_entry == other_entry
>>> some_entry.id == other_entry.id

If a model's primary key isn't called id, no problem. Comparisons will always use the primary key, whatever it's called. For example, if a model's primary key field is called name, these two statements are equivalent:

>>> some_obj == other_obj
>>> some_obj.name == other_obj.name

删除对象

The delete method, conveniently, is named delete(). This method immediately deletes the object and returns the number of objects deleted and a dictionary with the number of deletions per object type. Example:

>>> e.delete()
(1, {'blog.Entry': 1})

你也能批量删除对象。所有 QuerySet 都有个 delete() 方法,它会删除 QuerySet 中的所有成员。

For example, this deletes all Entry objects with a pub_date year of 2005:

>>> Entry.objects.filter(pub_date__year=2005).delete()
(5, {'webapp.Entry': 5})

请记住,只要有机会的话,这会通过纯 SQL 语句执行,所以就无需在过程中调用每个对象的删除方法了。若你为模型类提供了自定义的 delete() 方法,且希望确保调用了该方法,你需要 “手动” 删除该模型的实例(例如,如,遍历 QuerySet,在每个对象上分别调用 delete() 方法),而不是使用 QuerySet 的批量删除方法 delete()

当 Django 删除某个对象时,默认会模仿 SQL 约束 ON DELETE CASCADE 的行为——换而言之,某个对象被删除时,关联对象也会被删除。例子:

b = Blog.objects.get(pk=1)
# This will delete the Blog and all of its Entry objects.
b.delete()

这种约束行为由 ForeignKeyon_delete 参数指定。

注意 delete() 是唯一未在 Manager 上暴漏的 QuerySet 方法。这是一种安全机制,避免你不小心调用了 Entry.objects.delete(),删除了 所有的 条目。若你 确实 想要删除所有对象,你必须显示请求完整结果集合:

Entry.objects.all().delete()

复制模型实例

Although there is no built-in method for copying model instances, it is possible to easily create new instance with all fields' values copied. In the simplest case, you can set pk to None and _state.adding to True. Using our blog example:

blog = Blog(name="My blog", tagline="Blogging is easy")
blog.save()  # blog.pk == 1

blog.pk = None
blog._state.adding = True
blog.save()  # blog.pk == 2

若你使用了集成,事情会更复杂。考虑下 Blog 的一个子类:

class ThemeBlog(Blog):
    theme = models.CharField(max_length=200)


django_blog = ThemeBlog(name="Django", tagline="Django is easy", theme="python")
django_blog.save()  # django_blog.pk == 3

Due to how inheritance works, you have to set both pk and id to None, and _state.adding to True:

django_blog.pk = None
django_blog.id = None
django_blog._state.adding = True
django_blog.save()  # django_blog.pk == 4

该方法不会拷贝不是模型数据表中的关联关系。例如, Entry 有一个对 AuthorManyToManyField 关联关系。在复制条目后,你必须为新条目设置多对多关联关系。

entry = Entry.objects.all()[0]  # some previous entry
old_authors = entry.authors.all()
entry.pk = None
entry._state.adding = True
entry.save()
entry.authors.set(old_authors)

对于 OneToOneField 关联,你必须拷贝关联对象,并将其指定给新对象的关联字段,避免违反一对一唯一性约束。例如,指定前文复制的 entry:

detail = EntryDetail.objects.all()[0]
detail.pk = None
detail._state.adding = True
detail.entry = entry
detail.save()

一次修改多个对象

有时候,你想统一设置 QuerySet 中的所有对象的某个字段。你可以通过 update() 达到目的。例子:

# Update all the headlines with pub_date in 2007.
Entry.objects.filter(pub_date__year=2007).update(headline="Everything is the same")

You can only set non-relation fields and ForeignKey fields using this method. To update a non-relation field, provide the new value as a constant. To update ForeignKey fields, set the new value to be the new model instance you want to point to. For example:

>>> b = Blog.objects.get(pk=1)

# Change every Entry so that it belongs to this Blog.
>>> Entry.objects.update(blog=b)

The update() method is applied instantly and returns the number of rows matched by the query (which may not be equal to the number of rows updated if some rows already have the new value). The only restriction on the QuerySet being updated is that it can only access one database table: the model's main table. You can filter based on related fields, but you can only update columns in the model's main table. Example:

>>> b = Blog.objects.get(pk=1)

# Update all the headlines belonging to this Blog.
>>> Entry.objects.filter(blog=b).update(headline="Everything is the same")

要认识到 update() 方法是直接转为 SQL 语句的。这是一种用于直接更新的批量操作。它并不会调用模型的 save() 方法,或发射 pre_savepost_save 信号(调用 save() 会触发信号),或使用 auto_now 字段选项。若想保存 QuerySet 中的每项,并确保调用了每个实例的 save() 方法,你并不需要任何特殊的函数来处理此问题。迭代它们,并调用它们的 save() 方法:

for item in my_queryset:
    item.save()

Calls to update can also use F expressions to update one field based on the value of another field in the model. This is especially useful for incrementing counters based upon their current value. For example, to increment the pingback count for every entry in the blog:

>>> Entry.objects.update(number_of_pingbacks=F("number_of_pingbacks") + 1)

However, unlike F() objects in filter and exclude clauses, you can't introduce joins when you use F() objects in an update -- you can only reference fields local to the model being updated. If you attempt to introduce a join with an F() object, a FieldError will be raised:

# This will raise a FieldError
>>> Entry.objects.update(headline=F("blog__name"))

回归原生 SQL

若你发现需要编写的 SQL 查询语句太过复杂,以至于 Django 的数据库映射无法处理,你可以回归手动编写 SQL。Django 针对编写原生 SQL 有几个选项;参考 执行原生 SQL 查询

最后,Django 数据库层只是一种访问数据库的接口,理解这点非常重要。你也可以通过其它工具,编程语言或数据库框架访问数据库;Django 并没有对数据库数据库做啥独有的操作。