Django - ORM基础数据查询

  • 作者:lwj
  • 分类:Django
  • 发表日期:2021-10-31 15:02:18
  • 阅读(57)
  • 评论(0)

定义的Django的模型对象

from django.db import models

# 自定义图书类
class BookInfo(models.Model):
    """图书模型"""
    title = models.CharField(max_length=200, verbose_name="书名")
    date = models.DateField(verbose_name="发布日期")
    bread = models.IntegerField(default=0, verbose_name="阅读量")
    comment = models.IntegerField(default=0, verbose_name="评论数")
    is_delete = models.BooleanField(default=False, verbose_name="逻辑删除")

    class Meta:
        db_table = "tb_books"      # 自定义表的名字
        verbose_name = "图书"
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.title

# 自定义英雄模型类
class HeroInfo(models.Model):
    GENDER_CHOICES = (  # 枚举
        (0, 'female'),
        (1, 'male')
    )
    hero_name = models.CharField(max_length=20, verbose_name="英雄名称")
    # 默认值为0,只取元组GENDER_CHOICES中枚举的值
    gender = models.SmallIntegerField(choices=GENDER_CHOICES, default=0, verbose_name="性别")
    comment = models.CharField(max_length=200, null=True, verbose_name="描述信息")              # 可以为null值
    book = models.ForeignKey(to=BookInfo, on_delete=models.CASCADE, verbose_name="图书")       # 外键
    is_delete = models.BooleanField(default=False, verbose_name="逻辑删除")

    class Meta:
        db_table = "tb_heros"
        verbose_name = "英雄"
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.hero_name

模板文件

<!--index.html-->
<h5>查询结果</h5>
<h5>
    <ul>书籍
        {% if results_list|length > 0 %}
            {% for results_list in results_list %}
                {% include 'books/book.html' %}
            {% endfor %}
        {% elif results_list %}
            {% include 'books/book.html' %}
        {% else %}
            <h5>查询结果为空</h5>
        {% endif %}
    </ul>
<!--    <ul>书中的角色</ul>-->
</h5>


<!--book.html-->
<li>书名:{{ results_list.title }}</li>
<li>时间: {{ results_list.date.year}}/{{ results_list.date.month }}/{{ results_list.date.day }}</li>
<li>阅读量:{{ results_list.bread }}</li>
<li>评论数:{{ results_list.comment }}</li>
<li>角色数量:{{ results_list.hero_count }}</li>
<br>

数据库中的数据,如下图:

导入的包

from django.shortcuts import render, HttpResponse
from django.http import JsonResponse
from django.core import serializers
from django.db.models import Q, F
from django.db.models import Avg, Count, Sum, Max, Min

from .models import BookInfo, HeroInfo

1. all(), 查询tb_books全表数据

def views_book_info_all(request):
    results = BookInfo.objects.all()
    print(results)
    return render(request, 'books/index.html', {"results_list": results})

2.  get(), 查询单条数据,为空或者多条数据会报错

def views_book_info_get(request):
    results = BookInfo.objects.get(id=1)
    print(results)
    return render(request, 'books/index.html', {"results_list": results})

3. filter(), 根据条件获取一组数据(注意:这种方法获取到的是一个可迭代的对象, 要想在html中渲染出来,需要for循环)

def views_book_info_filter(request):
    results = BookInfo.objects.filter(title="西游记", is_delete=0)
    print(results)
    return render(request, 'books/index.html', {"results_list": results})

4. exclude(), 获取除设置条件之外的数据, 即查询条件取反

def views_book_info_exclude(request):
    results = BookInfo.objects.exclude(title="西游记")
    print(results)
    return render(request, 'books/index.html', {"results_list": results})

5. values(), 查询指定字段,返回一个列表 每个元素为一个字典

def views_books_info_values(request):
    results = BookInfo.objects.values("title", "bread", "date")
    print(results)
    return render(request, 'books/index.html', {"results_list": results})

6. order_by(), 排序(默认正序, 倒序在排序字段名之前加"-"), 如下优先comment字段倒叙,同值则按bread正序

def views_book_info_order_by(request):
    results = BookInfo.objects.all().order_by("-comment", "bread")
    print(results)
    return render(request, 'books/index.html', {"results_list": results})

7. reverse(),反转排序的结果

def views_book_info_reverse(request):
    # 返回order_by的排序结果
    results = BookInfo.objects.all().order_by("-comment", "bread").reverse()
    print(results)
    return render(request, 'books/index.html', {"results_list": results})

8. aggregate(), 查询数据聚合

def views_book_info_aggregate(request):
    results = BookInfo.objects.all()
    _count = results.aggregate(Count("id"))
    _sum = results.aggregate(Sum("bread"))
    _avg = results.aggregate(Avg("comment"))
    _max = results.aggregate(Max("id"))
    _min = results.aggregate(Min("id"))
    return HttpResponse(content=f"<h5>查询符合条件的书籍计数(count)为:{_count}</h5>"
                                f"<h5>查询符合条件的书籍阅读总次数为:{_sum}</h5>"
                                f"<h5>查询符合条件的书籍评论平均数为:{_avg}</h5>"
                                f"<h5>查询符合条件的书籍评论数最多的为:{_max}</h5>"
                                f"<h5>查询符合条件的书籍评论数最少的为:{_min}</h5>"
                        )

9. annotate(), 查询数据分组

def views_book_info_annotate(request):
    from django.db.models import Count, Sum
    # 根据书名进行分组,统计同名书籍的阅读总数
    results = BookInfo.objects.values("title").annotate(hero_count=Sum("bread"))
    # 根据书名进行分组,统计同名书下的角色数量
    results = BookInfo.objects.values("title").annotate(hero_count=Count("heroinfo"))
    # 根据书籍表的ID分组查询角色表关联的角色数量
    results = BookInfo.objects.annotate(hero_count=Count("heroinfo"))
    print(results)
    return render(request, 'books/index.html', {"results_list": results})

10. firtst(), 返回查询到的第一条数据

def views_book_info_first(request):
    results = BookInfo.objects.all().first()
    print(results)
    return HttpResponse(content=f"<h3>查询结果的第一本书籍为:{results}</h3>")

11. last(), 返回查询到的最后一条数据

def views_book_info_last(request):
    results = BookInfo.objects.all().last()
    print(results)
    return HttpResponse(content=f"<h3>查询结果的最后一本书籍为:{results}</h3>")

12. exists(), 判断查询的数据是否存在,存在为True否则为False

def views_book_info_exists(request):
    results = BookInfo.objects.filter(title="东游记").exists()
    print(results)
    return HttpResponse(content=f"<h3>查询数据存在的结果为:{results}</h3>")

13. update_or_create(), 数据是否存在, 不存在则插入新的一条并返回True,存在则返回False

def views_book_info_update_or_create(request):
    results = BookInfo.objects.update_or_create(title="东游记", date="2001-10-09")
    print(results)
    return HttpResponse(content=f"<h3>查询结果为:{results}</h3>")

14. __year、__month__day获取日期字段的年月日(注意双下划线)

def views_book_info_search(request):
    results = BookInfo.objects.filter(date__year="2001")  # __year: 日期字段的年份
    print(f"date__year查询结果为:{results}")
    results = BookInfo.objects.filter(date__month="11")  # __month: 日期字段的月份
    print(f"date__year查询结果为:{results}")
    results = BookInfo.objects.filter(date__day="24")  # __day: 日期字段的日期
    print(f"date__year查询结果为:{results}")
    return JsonResponse(data={"result": 0, "msg": "成功"}, charset='utf-8')

15. __isnull, 判断是否为null值,传参True和False

def views_book_info_search(request):
    results = BookInfo.objects.filter(comment__isnull=True)  # __isnull: 字段为null值,传参True和False
    print(f"comment__isnull查询结果为:{results}")
    return JsonResponse(data={"result": 0, "msg": "成功"}, charset='utf-8')

16. __exact, 精准匹配字段数据

def views_book_info_search(request):
    results = BookInfo.objects.filter(title__exact="东游记")  # __exact: 精准匹配字段数据
    print(f"title__exact查询结果为:{results}")
    return JsonResponse(data={"result": 0, "msg": "成功"}, charset='utf-8')

17. __iexact, 字段值精准匹配忽略大小写

def views_book_info_search(request):
    results = BookInfo.objects.filter(title__iexact="dame de paris")  # __iexact: 字段值精准匹配但忽略大小写
    print(f"title__iexact查询结果为:{results}")
    return JsonResponse(data={"result": 0, "msg": "成功"}, charset='utf-8')

18. __contains__icontains, 字段值模糊匹配(__icontains忽略大小写)

def views_book_info_search(request):
    results = BookInfo.objects.filter(title__icontains="游记")  # __contains: 字段值模糊匹配
    print(f"title__icontains查询结果为:{results}")
    results = BookInfo.objects.filter(title__icontains="paris")  # __icontains: 字段值模糊匹配忽略大小写
    print(f"title__icontains查询结果为:{results}")
    return JsonResponse(data={"result": 0, "msg": "成功"}, charset='utf-8')

19. __gt__gte__lt__lte,比较运算符

def views_book_info_search(request):
    results = BookInfo.objects.filter(bread__gt="12")  # __gt: 字段值大于
    print(f"bread__gt查询结果为:{results}")
    results = BookInfo.objects.filter(bread__gte="12")  # __gt: 字段值大于等于
    print(f"bread__gte查询结果为:{results}")
    results = BookInfo.objects.filter(bread__lt="12")  # __lt: 字段值小于
    print(f"bread__lt查询结果为:{results}")
    results = BookInfo.objects.filter(bread__lte="12")  # __lt: 字段值小于等于
    print(f"bread__lte查询结果为:{results}")
    return JsonResponse(data={"result": 0, "msg": "成功"}, charset='utf-8')

20. __in: 字段值为容器中的元素

def views_book_info_search(request):
    results = BookInfo.objects.filter(bread__in=[12, 20])  # __in: 字段值为容器的元素
    print(f"bread__gt查询结果为:{results}")
    return JsonResponse(data={"result": 0, "msg": "成功"}, charset='utf-8')

21. __startswith__endswith,字段以xx开头或者以xx结尾

def views_book_info_search(request):
    results = BookInfo.objects.filter(title__startswith="dame")  # __startswith: 字段值以xx开头
    print(f"title__istartswith查询结果为:{results}")
    results = BookInfo.objects.filter(title__endswith="paris")  # __endswith: 字段值以xx结尾
    print(f"title__iendswith:{results}")
    return JsonResponse(data={"result": 0, "msg": "成功"}, charset='utf-8')

22. __istartswith__iendswith,以xx看头或者xx结尾且忽略大小写

def views_book_info_search(request):
    results = BookInfo.objects.filter(title__istartswith="dame")  # __istartswith: 字段值以xx开头忽略大小写
    print(f"title__istartswith查询结果为:{results}")
    results = BookInfo.objects.filter(title__iendswith="paris")  # __iendswith: 字段值以xx结尾忽略大小写
    print(f"title__iendswith:{results}")
    return JsonResponse(data={"result": 0, "msg": "成功"}, charset='utf-8')

23. __range, 字段值在xx和yy范围的

def views_book_info_search(request):
    results = BookInfo.objects.filter(date__year__range=[1986, 1990])  # __range: 字段值在xx和yy范围的数据
    print(f"date_year__range查询结果为:{results}")
    return JsonResponse(data={"result": 0, "msg": "成功"}, charset='utf-8')

24. distinct(), 对查询结果数据去重, 如下根据title和id组合去重

def views_book_info_search(request):
    results = BookInfo.objects.filter(id__gt=18).values("title", "id").distinct("title")
    print(f"distinct查询结果为:{results}")
    return JsonResponse(data={"result": 0, "msg": "成功"}, charset='utf-8')

25. Q对象, 增强各个条件之间的关系,这种写法用在你不知道用户到底传入了多少个参数的时候很方便

def views_book_info_q_object(request):
    results = BookInfo.objects.filter(Q(title="西游记") | Q(title="东游记"))          # | 表示 或
    print(f"Q对象‘或’操作语句|查询结果为:{results}")
    results = BookInfo.objects.filter(Q(title="西游记") & Q(bread=1))                # & 表示 且
    print(f"Q对象‘且’操作语句|查询结果为:{results}")
    results = BookInfo.objects.filter(~Q(title="西游记"))                            # ~Q 表示 非 取反
    print(f"Q对象’非‘操作语句|查询结果为:{results}")
    return JsonResponse(data={"result": 0, "msg": "成功"}, charset='utf-8')

26. F对象, 可以对两字段进行比较,如果A写在了等号的左边,则B出现在等号的右边,B需要通过F对象构造

def views_book_info_f_object(request):
    results = BookInfo.objects.filter(bread__gt=F("comment")) # 查询书籍表中,bread字段大于comment字段的数据
    print(f"F对象->bread__lt=F('comment')查询结果为:{results}")
    return JsonResponse(data={"result": 0, "msg": "成功"}, charset='utf-8')

27.  raw(), 原生sql查询

select 
tb_books.id, tb_books.title, tb_books.bread, tb_books.comment, 
tb_heros.hero_name, tb_heros.comment, tb_heros.gender  
from tb_books inner join tb_heros
on tb_books.id = tb_heros.book_id 
where tb_books.id < 3
def views_book_info_raw(request):
    book_hero = BookInfo.objects.raw(sql)
    print(f"查询结果为:{list(book_hero)}")
    return HttpResponse(book_hero)

 

觉得不错,支持一下!

提交评论

您尚未登录,登录之后方可评论~ 登录 or 注册

评论列表

暂无评论
返回顶部

建议反馈

1. 可在博文底部留言评论

2. 发邮件到i_suichuan@163.com