使用 Django Q F 对象构建复杂的查询

分手后的思念是犯贱 2022-05-28 02:05 199阅读 0赞

from django.db import models class Question(models.Model): text = models.CharField(max_length=200) pub_date = models.DateTimeField('published_date') def __unicode__(self): return self.text

先在 `settings.py` 中进行设置：

TIME_ZONE = 'Asia/Shanghai'
    
    LANGUAGE_CODE = 'zh-CN'

然后创建一些数据：

>>> Question.objects.create( text='what are you doing', pub_date=datetime.datetime(2015, 11, 7) ) >>> Question.objects.create( text='what is wrong with you', pub_date=datetime.datetime(2014, 11, 7) ) >>> Question.objects.create( text='who are you', pub_date=datetime.datetime(2015, 10, 7) ) >>> Question.objects.create( text='who am i', pub_date=datetime.datetime(2014, 10, 7) ) >>> Question.objects.all() [<Question: what are you doing>, <Question: what is wrong with you>, <Question: who are you>, <Question: who am i>]

##### AND #####

将多个 `Q` 对象作为非关键字参数或使用 `&` 联结即可实现 `AND` 查询：

>>> from django.db.models import Q # Q(...) >>> Question.objects.filter(Q(text__contains = 'you')) [<Question: what are you doing>, <Question: what is wrong with you>, <Question: who are you>] # Q(...), Q(...) >>> Question.objects.filter(Q(text__contains = 'you'), Q(text__contains = 'what')) [<Question: what are you doing>, <Question: what is wrong with you>] # Q(...) & Q(...) >>> Question.objects.filter(Q(text__contains = 'you') & Q(text__contains = 'what')) [<Question: what are you doing>, <Question: what is wrong with you>]

##### OR #####

使用 `|` 联结两个 `Q` 对象即可实现 `OR` 查询：

# Q(...) | Q(...) >>> Question.objects.filter(Q(text__contains = 'you') | Q(text__contains = 'who')) [<Question: what are you doing>, <Question: what is wrong with you>, <Question: who are you>, <Question: who am i>]

##### NOT #####

使用 `~` 即可实现 `NOT` 查询：

# ~Q(...) >>> Question.objects.filter(~Q(text__contains = 'you')) [<Question: who am i>]

扩展：  
想按条件过滤掉某些数据，如何表示“不等于”这个概念呢？

>>> Question.objects.filter(text != '')

上面这种写法是错误的，正确的写法是：

# exclude >>> Question.objects.exclude(text = '') [<Question: what are you doing>, <Question: what is wrong with you>, <Question: who are you>, <Question: who am i>] # ~Q(...) >>> Question.objects.filter(~Q(text = '')) [<Question: what are you doing>, <Question: what is wrong with you>, <Question: who are you>, <Question: who am i>]

##### Q 对象与关键字参数共用 #####

`Q` 对象可以结合关键字参数一起传递给查询函数，不过需要注意的是要将`Q` 对象放在关键字参数的前面：

# Q(...), key=value >>> Question.objects.filter(Q(text__contains = 'you'), text__contains = 'who') [<Question: who are you>]

##### OR，AND，NOT 多条件自由组合 #####

# (A OR B) AND C AND (NOT D) >>> Question.objects.filter((Q(text__contains = 'you') | Q(text__contains = 'who')) & Q(text__contains = 'what') & ~Q(text__contains = 'are')) [<Question: what is wrong with you>]

##### 动态构建查询条件 #####

比如定义了一个包含一些 `Q` 对象的列表，那么如何使用这个列表构建 `AND` 或 `OR` 查询呢？ 可以使用 `operator` 和 `reduce`：

>>> import operator >>> q_list = [Q(text__contains = 'you'), Q(text__contains = 'who')] # OR >>> Question.objects.filter(reduce(operator.or_, q_list)) [<Question: what are you doing>, <Question: what is wrong with you>, <Question: who are you>, <Question: who am i>] # AND >>> Question.objects.filter(reduce(operator.and_, q_list)) [<Question: who are you>]

这个列表也可能是根据用户的输入来构建的，比如简单的搜索功能（搜索一个文章的标题或内容或作者包含某个关键字）：

q = request.GET.get('q', '').strip() q_list = [] if q: for key in ['title__contains', 'content__contains', 'author__contains']: q_list.append(Q(**{ key: q})) queryset = Entry.objects.filter(reduce(operator.or_, q_list))

1、all() ： 查询所有结果
    2、filter()
    3、get(**kwargs)
    4、exclude(**kwargs)
    5、value(*field)
    6、values_list(*field)
    7、order_by(*field)
    8、reverse():
    9、distinct():
    10、count()
    11、first():
    12: last():
    13: exists():

For more details, please check out the link below.

link [https://docs.djangoproject.com/en/2.0/ref/models/querysets/\#django.db.models.query.QuerySet][https_docs.djangoproject.com_en_2.0_ref_models_querysets_django.db.models.query.QuerySet]

F 查询  
Advantages of F():  
link [https://docs.djangoproject.com/en/2.0/ref/models/expressions/][https_docs.djangoproject.com_en_2.0_ref_models_expressions]

An F() object represents the value of a model field or annotated column. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory.

getting the database, rather than Python, to do work.  
reducing the number of queries some operations require.

Instead, Django uses the F() object to generate an SQL expression that describes the required operation at the database level.

This is easiest to understand through an example. Normally, one might do something like this:

# Tintin filed a news story!
    reporter = Reporters.objects.get(name='Tintin')
    reporter.stories_filed += 1
    reporter.save()

Here, we have pulled the value of reporter.stories\_filed from the database into memory and manipulated it using familiar Python operators, and then saved the object back to the database. But instead we could also have done:

from django.db.models import F reporter = Reporters.objects.get(name='Tintin') reporter.stories_filed = F('stories_filed') + 1 reporter.save()

Although reporter.stories\_filed = F('stories\_filed') + 1 looks like a normal Python assignment of value to an instance attribute, in fact it’s an SQL construct describing an operation on the database.

When Django encounters an instance of F(), it overrides the standard Python operators to create an encapsulated SQL expression; in this case, one which instructs the database to increment the database field represented by reporter.stories\_filed.

Whatever value is or was on reporter.stories\_filed, Python never gets to know about it - it is dealt with entirely by the database. All Python does, through Django’s F() class, is create the SQL syntax to refer to the field and describe the operation.

To access the new value saved this way, the object must be reloaded:

reporter = Reporters.objects.get(pk=reporter.pk)
    # Or, more succinctly:
    reporter.refresh_from_db()

As well as being used in operations on single instances as above, `F()` can be used on `QuerySets` of object instances, with `update()`. This reduces the two queries we were using above - the `get()` and the [`save()`][save] \- to just one:

reporter = Reporters.objects.filter(name='Tintin')
    reporter.update(stories_filed=F('stories_filed') + 1)

We can also use [`update()`][update] to increment the field value on multiple objects - which could be very much faster than pulling them all into Python from the database, looping over them, incrementing the field value of each one, and saving each one back to the database:

Reporter.objects.all().update(stories_filed=F('stories_filed') + 1)

F() therefore can offer performance advantages by:

getting the database, rather than Python, to do work  
reducing the number of queries some operations require

[https_docs.djangoproject.com_en_2.0_ref_models_querysets_django.db.models.query.QuerySet]: https://link.jianshu.com?t=https%3A%2F%2Fdocs.djangoproject.com%2Fen%2F2.0%2Fref%2Fmodels%2Fquerysets%2F%23django.db.models.query.QuerySet
[https_docs.djangoproject.com_en_2.0_ref_models_expressions]: https://link.jianshu.com?t=https%3A%2F%2Fdocs.djangoproject.com%2Fen%2F2.0%2Fref%2Fmodels%2Fexpressions%2F
[save]: https://link.jianshu.com?t=https%3A%2F%2Fdocs.djangoproject.com%2Fen%2F2.0%2Fref%2Fmodels%2Finstances%2F%23django.db.models.Model.save
[update]: https://link.jianshu.com?t=https%3A%2F%2Fdocs.djangoproject.com%2Fen%2F2.0%2Fref%2Fmodels%2Fquerysets%2F%23django.db.models.query.QuerySet.update