查询

查询方法 Methods that return new QuerySets

查询条件 Field lookups

跨表查询

ForeignKey 一对多查询

class Publisher(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32, unique=True)

    def __str__(self):
        return "我是一个出版社对象:{}".format(self.name)


class Book(models.Model):
    id = models.AutoField(primary_key=True)
    price = models.DecimalField(max_digits=5, decimal_places=2, default=99.99)
    # 库存数
    kucun = models.IntegerField(default=1000)
    # 卖出数
    maichu = models.IntegerField(default=0)

    title = models.CharField(max_length=32)
    # 外键
    # related_name="books" 反向查询是用来代替 book_set的
    publisher = models.ForeignKey(
        to="Publisher",
        on_delete=models.CASCADE)

    def __str__(self):
        return self.title

book_obj = models.Book.objects.all().first()
ret = book_obj.publisher  # 和我这本书关联的出版社对象
print(ret, type(ret))

ret = models.Book.objects.filter(id=1).values_list("publisher__name")
print(ret)

publisher_obj = models.Publisher.objects.first()  # 找到第一个出版社对象
books = publisher_obj.book_set.all()  # 找到第一个出版社出版的所有书
titles = books.values_list("title")

books = publisher_obj.allbook.all()

ret = models.Publisher.objects.filter(id=1).values_list("book__title")
print(ret)

ManyToManyField 多对多查询

models.Author.objects.first().book_set.create(title="番茄物语", publish_date=datetime.date.today())

new_book = models.book.objects.create(name= '新的书').save()  # 在book表内建立一本新书
models.Author.objects.first().book_set.add(new_book)  # 在多对多表内添加一行,记录第一个作者和这本新书的对应关系.

book_obj = models.Book.objects.first()
book_obj.authors.set([2, 3])

book_obj = models.Book.objects.first()
book_obj.authors.remove(3)

book_obj = models.Book.objects.first()
book_obj.authors.clear()

一对一查询

使用聚合函数与分组查询

聚合函数

from django.db.models import Avg, Sum, Max, Min, Count

models.Book.objects.all().aggregate(Avg("price"))
# 结果是{'price__avg': 13.233333},其中的"price__avg"由Django自动生成

models.Book.objects.aggregate(average_price=Avg('price'))
# 结果是{'average_price': 13.233333}, 其中的"average_price"是自定义的名称.

models.Book.objects.all().aggregate(Avg("price"), Max("price"), Min("price"))
# 同时使用多个聚合函数,可以用逗号分割,结果字典中会包括全部结果.

分组

ret = models.Items.objects.all().annotate(num_balance=Sum("moves__in_num") - Sum("moves__out_num"))

ret = models.Moves.objects.values("item_id").annotate(insum = Sum("in_num"))

select dept,AVG(salary) from employee group by dept;

SQL语句与ORM的对应关系:

models.Employee.objects.values("dept,"平均数的别名")

models.Employee.objects.values("dept,"平均数的别名").annotate()

F查询和Q查询

F查询

ret = ret_temp = models.Moves.objects.filter(in_num__gte=F("out_num"))

models.Moves.objects.update(in_num = F("in_num") + 10)

Q查询

ret_temp = models.Moves.objects.filter(in_num__gte=F("out_num"), in_num__gt=50)

from django.db.models import F,Q
ret_temp = models.Moves.objects.filter(Q(in_num__lte=F("out_num")) | Q(out_num__gt=20))