ORM的常用查询方法
app01/models.py
from django.db import models
# 扮演角色
class Role(models.Model):
# 角色名称
role_name = models.CharField(max_length=32, unique=True)
class Meta:
db_table = "xc_role"
# 班级信息
class Classroom(models.Model):
# 班级名称
class_name = models.CharField(max_length=32, verbose_name="班级名称", unique=True)
# 班级地址
address = models.CharField(max_length=32, null=True)
class Meta:
db_table = "xc_class"
def __str__(self):
return self.class_name
# 用户名称
class User(models.Model):
# 用户名称
username = models.CharField(max_length=32, verbose_name="姓名")
# 用户年龄
age = models.IntegerField(verbose_name="年龄")
# 用户地址
home = models.CharField(verbose_name="家乡", null=True, max_length=32)
# 用户身高
hight = models.IntegerField(verbose_name="身高", null=True)
# 多对多
roles = models.ManyToManyField(Role)
# 一对多
classrooms = models.ForeignKey(Classroom, on_delete=models.CASCADE, null=True)
class Meta:
db_table = "xc_user"
迁移命令
# 生成迁移文件
python manage.py makemigrations
# 迁移数据库
python manage.py migrate
app01/serializer.py
from app01.models import *
from rest_framework import serializers
class UserSer(serializers.ModelSerializer):
class Meta:
model = User
fields = "__all__"
# depth = 1
class ClassSer(serializers.ModelSerializer):
class Meta:
model = Classroom
fields = "__all__"
# depth = 1
class RoleSer(serializers.ModelSerializer):
class Meta:
model = Role
fields = "__all__"
# depth = 1
app01/urls.py
from django.urls import path
from app01.views import *
urlpatterns = [
path('info/', InfoView.as_view()),
path('manytomany/', ManytoManyTest.as_view()),
]
app01/views.py
# 导入相应的模块
from django.db.models import F, Q, Sum
from app01.models import *
from rest_framework.views import APIView
from rest_framework.response import Response
from app01.serializer import *
class InfoView(APIView):
def get(self, request):
# 基本查询 ============================
# 单个查询,不存在抛出异常
# user = User.objects.get(username="tim")
# ser = UserSer(user)
# return Response(ser.data)
# 查询多个值,有说多少返回多少
# user = User.objects.all() # all不可以接参数,否则会报错
# ser = UserSer(user, many=True)
# return Response(ser.data)
# 查询结果的数量
# user = User.objects.filter(age=19).count()
# return Response(user)
# 查询结果取反
# user = User.objects.exclude(username="tim")
# print(user)
# ser = UserSer(user, many=True)
# return Response(ser.data)
# 模糊查询 ======================
# contains 是否包含 查询结果包含某个值
# user = User.objects.filter(home__contains="北京")
# ser = UserSer(user, many=True)
# return Response(ser.data)
# startswith/endswith 以指定值开头/结尾
# user = User.objects.filter(home__startswith="上")
# ser = UserSer(user, many=True)
# return Response(ser.data)
# ==========================
# user = User.objects.filter(home__endswith="平")
# ser = UserSer(user, many=True)
# return Response(ser.data)
# 空查询 =====================
# isnull 是否为空
# user = User.objects.filter(home__isnull=False)
# ser = UserSer(user, many=True)
# return Response(ser.data)
# 范围查询 =========================
# in 在范围内
# user = User.objects.filter(id__in=[1, 3, 5])
# ser = UserSer(user, many=True)
# return Response(ser.data)
# range 相当于detween...and...
# user = User.objects.filter(id__range=[3, 6])
# ser = UserSer(user, many=True)
# return Response(ser.data)
# 比较查询 =========================
# gt 大于 >
# 查询id⼤于5的
# user = User.objects.filter(id__gt=5)
# ser = UserSer(user, many=True)
# return Response(ser.data)
# gte 大/等于 >=
# 查询id⼤于等于5的
# user = User.objects.filter(id__gte=5)
# ser = UserSer(user, many=True)
# return Response(ser.data)
# lt 小于 <
# 查询id小于3的
# user = User.objects.filter(id__lt=3)
# ser = UserSer(user, many=True)
# return Response(ser.data)
# lte 小/等于 <=
# 查询id小于等于3的
# user = User.objects.filter(id__lte=3)
# ser = UserSer(user, many=True)
# return Response(ser.data)
# exclude 不等于 !=
# 查询id不等于的5的
# user = User.objects.exclude(id=5)
# ser = UserSer(user, many=True)
# return Response(ser.data)
# F对象和Q对象 =====================
# 比较两个字段之间的关系用F对象(F可以进行运算)
# user = User.objects.filter(age__gte=F('hight'))
# ser = UserSer(user, many=True)
# return Response(ser.data)
# =============================================
# (F可以进行运算)
# user = User.objects.filter(age__gte=F('hight')*2)
# ser = UserSer(user, many=True)
# return Response(ser.data)
# 与逻辑运算符连⽤使⽤Q对象。 或（ | ） 与（ & ） ⾮（ ~ ）
# 查询年龄为19或者id为3的对象
# user = User.objects.filter(Q(age__gte=19) | Q(pk__lt=3))
# ser = UserSer(user, many=True)
# return Response(ser.data)
# -查询年龄为19与&id为3的对象--------------------------------
# user = User.objects.filter(Q(age__gte=19) & Q(pk__lt=3))
# ser = UserSer(user, many=True)
# return Response(ser.data)
# -查询年龄为非~20的对象-------------------------------------
# user = User.objects.filter(~Q(age__gte=20))
# ser = UserSer(user, many=True)
# return Response(ser.data)
# 聚合函数 ========================================================
# 使⽤aggregate()过滤器调⽤聚合函数。
# 聚合函数包括：Avg平均，Count数量，Max最⼤，Min最⼩，Sum求和
# user = User.objects.aggregate(Sum('age')) # 求库中所有年龄的和
# print(user)
# return Response(user)
# 排序
# 使⽤order_by对结果进⾏排序
# user = User.objects.all().order_by('age') # 按库中的年龄的升序排列
# ser = UserSer(user, many=True)
# return Response(ser.data)
# ============================================================
# user = User.objects.all().order_by('-age') # 按库中的年龄的降序排列
# ser = UserSer(user, many=True)
# return Response(ser.data)
pass
manytomany的操作
app01/views.py
from django.shortcuts import render
from rest_framework.response import Response
from rest_framework.views import APIView
from app01.models import *
from app01.serializer import *
class ManytoManyTest(APIView):
# 增加方法
def post(self, request):
# 方法一: 建立在manytomany的models里添加数据(一条,一个对象)
user_obj = User.objects.filter(id=1).first()
role_obj = Role.objects.filter(id=2).first()
data = user_obj.roles.add(role_obj)
return Response({
"code": 1000,
"data": data
})
# ⽅法⼆：在未建⽴manytomany的models⾥添加数据，（⼀条，⼀个对象）
# user_obj = User.objects.all()
# role_obj = Role.objects.filter(id=2).first()
# role_obj.user_set.add(*user_obj)
# return Response({
# "code": 1000,
# })
pass
# 删除方法
def delete(self, request):
# ⽅法⼀：在建⽴manytomany的models⾥删除数据，（⼀条，⼀个对象）
user_obj = User.objects.filter(id=5).first()
role_obj = Role.objects.filter(id=3).first()
user_obj.roles.remove(role_obj)
return Response({
"code": 1000
})
# ⽅法⼆：在未建⽴manytomany的models⾥删除数据，（多条，可迭代对象）
# user_obj = User.objects.all()
# role_obj = Role.objects.filter(id=2).first()
# role_obj.user_set.remove(*user_obj)
# return Response({
# "code": 1000
# })
pass
# 修改数据
def put(self, request):
# ⽅法⼀：在建⽴manytomany的models⾥修改数据，参数只能是可迭代对象
user_obj = User.objects.filter(id=1).first()
role_obj = Role.objects.filter(id=3)
user_obj.roles.set(role_obj)
return Response({
"code": 1000
})
# ⽅法⼆：在未建⽴manytomany的models⾥修改数据，参数只能是可迭代对象
# user_obj = User.objects.all()
# role_obj = Role.objects.filter(id=1).first()
# role_obj.user_set.set(user_obj)
# return Response({
# "code": 1000,
# })
pass
# 查看数据
def get(self, request):
# ⽅法⼀：在建⽴manytomany的models⾥查数据
user_obj = User.objects.get(id=2)
data = user_obj.roles.all()
data_list = []
for item in data:
data_dic = {
"role_name": item.role_name,
"username": user_obj.username
}
data_list.append(data_dic)
return Response(data_list)
# ⽅法⼆：在未建⽴manytomany的models⾥查数据
# role_obj = Role.objects.get(id=2)
# data = role_obj.user_set.all()
# data_list = []
# for item in data:
# data_dic = {
# "role_name": role_obj.role_name,
# "username": item.username
# }
# data_list.append(data_dic)
# return Response(data_list)
pass