77.Q表达式详解

Q表达式可以包裹查询条件,可以在多个条件之间进行操作:与或非等。Q表达式一般会放在filter()中进行使用,F表达式一般是放在update()中进行使用。

定义模型的models.py文件中,示例代码如下:
from django.db import models


# 定义作者模型
class Author(models.Model):
    name = models.CharField(max_length=100, unique=True)
    age = models.IntegerField()
    email = models.EmailField()

    class Meta:
        db_table = 'author'

    def __str__(self):
        return "%s,%s,%s" % (self.name,self.age, self.email)


# 定义出版社模型
class Publisher(models.Model):
    name = models.CharField(max_length=100,unique=True)

    class Meta:
        db_table = 'publisher'

    def __str__(self):
        return " ->:%s" % self.name


# 定义图书模型
class Book(models.Model):
    name = models.CharField(max_length=100, unique=True)
    pages = models.IntegerField()
    price = models.FloatField()
    rating = models.FloatField()
    author = models.ForeignKey('Author', on_delete=models.CASCADE)
    publisher = models.ForeignKey('Publisher', on_delete=models.CASCADE)
    score = models.FloatField(null=True)

    class Meta:
        db_table = 'book'

    def __str__(self):
        return "(书名:%s,页数:%s,价格:%s,打折:%s,作者:%s,出版社:%s)" % (self.name, self.pages, self.price, self.rating, self.author, self.publisher)


# 定义预定图书的模型
class BookOrder(models.Model):
    book = models.ForeignKey('Book', on_delete=models.CASCADE)
    price = models.FloatField()
    time = models.DateTimeField(auto_now_add=True, null=True)

    class Meta:
        db_table = 'book_order'
mysql数据库中book表中数据如下:

在这里插入图片描述

views.py文件中示例代码如下:
from .models import Book
from djang.db import connection
from django.http import HttpResponse
from django.db.models import Q


# Q表达式的详解
def index3(request):
    # 1. 首先先数据库中添加几条评分的数据
    # book = Book.objects.get(pk=1)
    # book.score = 4.48
    # book.save()

    # 2. 查找book表中价格大于150,评分score大于4.50并且折扣rating大于0.8的图书信息
    # 不使用Q表达式
    books = Book.objects.filter(price__gte=150, score__gte=4.50, rating__gte=0.8)
    for book in books:
        print("%s, %s, %s, %s"%(book.name, book.price, book.score, book.rating))
    # 注意:如果前面的%s的个数和后面的参数的个数不相对应的话,
    #     就会出现:not all arguments converted during string formatting,不是所有的参数在字符串格式化期间都进行了转化
    # 打印出结果:
    # 红楼梦, 199.0, 4.95, 0.85


    print(connection.queries)
    # [{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'},
    #  {'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'}, {
    #      'sql': 'SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, `book`.`score` FROM `book` WHERE (`book`.`price` >= 150.0e0 AND `book`.`rating` >= 0.8e0 AND `book`.`score` >= 4.5e0)',
    #      'time': '0.000'}]

    # 3. 查找book表中折扣小于0.8,或者是评分小于4.5的图书信息,其中在使用&,|时,两边一定是Q()表达式的值
    # 使用Q表达式,多个条件之间进行:或操作
    books = Book.objects.filter(Q(rating__lt=0.8)|Q(score__lt=4.5))
    for book in books:
        print("%s,评分:%s,折扣: %s"%(book.name, book.score, book.rating))
    # 打印出结果:
    # 三国演义,评分:4.48,折扣: 0.8
    # 水浒传,评分:4.83,折扣: 0.75
    # 西游记,评分:4.56,折扣: 0.75

    print(connection.queries[-1])
    #{'sql': 'SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, `book`.`score` FROM `book` WHERE (`book`.`rating` < 0.8e0 OR `book`.`score` < 4.5e0)', 'time': '0.000'}

    # 4.获取价格大于150,并且名字中不包含“记”的图书信息
    # 使用Q表达式,多个条件之间进行与非操作
    books = Book.objects.filter(Q(price__gte=150) & ~Q(name__icontains="记"))
    for book in books:
        print("%s, %s"%(book.name, book.price))
    # 水浒传, 159.0
    # 红楼梦, 199.0

    print(connection.queries[-1])
    # {'sql': "SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, `book`.`score` FROM `book` WHERE (`book`.`price` >= 150.0e0 AND NOT (`book`.`name` LIKE '%记%'))", 'time': '0.000'}
    return HttpResponse("success !")
补充:django ORM中大小比较的符号怎么表示:
  1. __gt: 表示大于
  2. __gte: 表示大于等于
  3. __lt: 表示小于
  4. __lte: 表示小于等于
  5. exclude:表示不等于
始于才华,忠于颜值;每件事情在成功之前,看起来都是天方夜谭。一无所有,就是无所不能。
原文地址:https://www.cnblogs.com/guyan-2020/p/12259908.html