Django: 使用 Q 对象构建复杂的查询语句

官方文档： https://docs.djangoproject.com/en/2.2/topics/db/queries/

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():
 

F 查询:  https://docs.djangoproject.com/en/2.0/ref/models/expressions/

注意红色部分加上括号

             tabledata = Incident.objects.filter((Q(customer=acc) & Q(group=self.dict["AIATWUNIXIN"]) | Q(group=self.dict["AIATWWININ"]) | Q(group=self.dict["AIACNIN"]) |Q(group=self.dict["AIACNDBA"])| Q(group=self.dict["AIATWDATA"])| Q(group=self.dict["AIATWMW"])| Q(group=self.dict["AIATWSTOR"])| Q(group=self.dict["AIATSSTECH"])) | (Q(customer=acc) & (Q(iownergroup=self.dict["AIATWUNIXIN"]) | Q(iownergroup=self.dict["AIATWWININ"]) | Q(iownergroup=self.dict["AIACNIN"]) |Q(iownergroup=self.dict["AIACNDBA"])| Q(iownergroup=self.dict["AIATWDATA"])| Q(iownergroup=self.dict["AIATWMW"])| Q(iownergroup=self.dict["AIATWSTOR"])| Q(iownergroup=self.dict["AIATSSTECH"]) & Q(group="")))).filter(Q(inature="Request")).values()
               

             tabledata = Incident.objects.filter((Q(customer=acc) & Q(inature="Request") & (Q(group=self.dict["AIATWUNIXIN"]) | Q(group=self.dict["AIATWWININ"]) | Q(group=self.dict["AIACNIN"]) |Q(group=self.dict["AIACNDBA"])| Q(group=self.dict["AIATWDATA"])| Q(group=self.dict["AIATWMW"])| Q(group=self.dict["AIATWSTOR"])| Q(group=self.dict["AIATSSTECH"]))) | ((Q(customer=acc) & Q(inature="Request")) & (Q(group="") & Q(iownergroup=self.dict["AIATWUNIXIN"]) | Q(iownergroup=self.dict["AIATWWININ"]) | Q(iownergroup=self.dict["AIACNIN"]) |Q(iownergroup=self.dict["AIACNDBA"])| Q(iownergroup=self.dict["AIATWDATA"])| Q(iownergroup=self.dict["AIATWMW"])| Q(iownergroup=self.dict["AIATWSTOR"])| Q(iownergroup=self.dict["AIATSSTECH"])))).values()

ids = user.objects.filter(NAME=username).values('CNUM') 返回  a QuerySet

可以遍历：

        username = user.objects.get(CNUM=userid).NAME
        ids = user.objects.filter(NAME=username).values('CNUM') 
        for unid in ids:
          uid = unid['CNUM']
          #checkstat.objects.filter(checkerid=checkerid, userid=userid).update(stats="Y")
          checkstat.objects.filter(checkerid=checkerid, userid=uid).update(stats="Y")
        p = checker.objects.get(ID=checkerid)
        usere = user.objects.get(CNUM=userid).Email

但是， usere = user.objects.get(CNUM=userid).Email,  使用get()只能返回一个对象， 如果有多个对象，会raise MultipleObjectsReturned

      userid = request.POST.get("uid")
      if confirmreply == "y":
        checkstat.objects.filter(checkerid=checkerid, userid=userid).update(stats="Y")
        p = checker.objects.get(ID=checkerid)
        usere = user.objects.get(CNUM=userid).Email
        to_mail = []
        to_mail.append(usere)
        body = "Congratulations!! You have completed " + p.Type + ": " + p.Name

   D=Changes.objects.all().values("chnum")
   print(D)        #返回的itorable对象，可以用循环遍历,且只返回查询的key和value

    for chdata in D:  #chdata为 {'chnum': 'CHG0072026'}
        if chdata['chnum'] not in CHNUM:
            Changes.objects.filter(chnum=chdata['chnum']).delete()

    D33=Changes.objects.values("chnum")
    print(D33)
    for chdata in D:
        if chdata['chnum'] not in CHNUM:
            Changes.objects.filter(chnum=chdata['chnum']).delete()

[root@oc1152622168 aia]# /bin/python3 /home/cictest/jhpengsz/icdreport-0429.19/manage.py crontab run 46ece4623bb3e00f02ffaae493c65c52

得到的结果是一样的
<QuerySet [{'chnum': 'CHG0072026'}, {'chnum': 'CHG0088573'}, {'chnum': 'CHG0134103'}, '...(remaining elements truncated)...']>
<QuerySet [{'chnum': 'CHG0072026'}, {'chnum': 'CHG0088573'}, {'chnum': 'CHG0134103'}, '...(remaining elements truncated)...']>
 

    acciter = icdaccount.objects.all().values()
    print(acciter)


而icdaccount.objects.all().values() 可以得到以下iterable:
<QuerySet [{'id': 1, 'account': 'ea', 'intraaddr': 'shensh@cn.ibm.com'}]>

AND 查询
OR 查询
NOT 查询
与关键字参数共用
OR, AND, NOT 多条件查询
动态构建查询条件
参考资料
本文将讲述如何在 Django 项目中使用 Q 对象构建复杂的查询条件。 假设有如下的 model:

    class Question(models.Model):
        question_text = models.CharField(max_length=200)
        pub_date = models.DateTimeField('date published')

然后我们创建了一些数据：

    Question.objects.create(
        question_text='what are you doing',
        pub_date=datetime.datetime(2015, 11,7)
    )
    Question.objects.create(
        question_text='what is wrong with you',
        pub_date=datetime.datetime(2014, 11, 7)
    )
    Question.objects.create(
        question_text='who are you',
        pub_date=datetime.datetime(2015, 10, 7)
    )
    Question.objects.create(
        question_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(question_text__contains='you'))
    [<Question: what are you doing>, <Question: what is wrong with you>, <Question: who are you>]

# Q(...), Q(...)

    >>> Question.objects.filter(Q(question_text__contains='you'), Q(question_text__contains='what'))
    [<Question: what are you doing>, <Question: what is wrong with you>]

# Q(...) & Q(...)

    >>> Question.objects.filter(Q(question_text__contains='you') & Q(question_text__contains='what'))
    [<Question: what are you doing>, <Question: what is wrong with you>]

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

# Q(...) | Q(...)

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

NOT 查询
使用 ~Q(...) 客户实现 NOT 查询:

# ~Q(...)

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

与关键字参数共用
记得要把 Q 对象放前面:

# Q(...), key=value

    >>> Question.objects.filter(Q(question_text__contains='you'), question_text__contains='who')
    [<Question: who are you>]

OR, AND, NOT 多条件查询
这几个条件可以自由组合使用:

# (A OR B) AND C AND (NOT D)

    >>> Question.objects.filter((Q(question_text__contains='you') | Q(question_text__contains='who')) & Q(question_text__contains='what') & ~Q(question_text__contains='are'))
    [<Question: what is wrong with you>]

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

    >>> lst = [Q(question_text__contains='you'), Q(question_text__contains='who')]

# OR

    >>> Question.objects.filter(reduce(operator.or_, lst))
    [<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_, lst))
    [<Question: who are you>]

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

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