Django模型查询方法总结

增删改

# 增删改
    # 增加和改动时要注意save()

    # user=User()
    # user.name='Steve'
    # user.password='123'
    # user.email='456456@qq.com'
    # user.birthday=datetime.today()
    # user.save()

    # user=User.objects.get(name='Steve')
    # user.delete()

    user=User.objects.get(id=3)
    user.birthday=datetime.today()
    user.save()

过滤器查找

过滤器方法:返回queryset

范围查询

	# users=User.objects.all()  # 查询所有
    # data = users.filter(id=5)# id=5
    # data=users.filter(id__gt=5)# id大于5
    # data=users.filter(id__lt=5)# 小于5
    # data=users.filter(id__gte=3).filter(id__lte=6)# 大于等于3 小于等于6
    data=users.filter(id__gte=2,id__lte=6)# 同上 
    # data=users.exclude(id=5)# 不等于5
    # print(data)

排序

	# 排序
    users=User.objects.all()
    # data=users.order_by('id')# 升序排列
    # data=users.order_by('-id')# 降序排列
    # data=users.order_by('id')[:2]# 首元素->id为2的元素
    data=users.order_by('id')[3:5]# 4,5没有3
    # data=users.order_by('id')[-1:3]# 不能用负数下标
    print(data)

users=User.objects.all()
    data=users.values('name','email')# 查找指定列 还会对结果进行排序
    print(data)# 每个对象是字典

users=User.objects.all()
    data=users.values('password').distinct()# 去重查询
    print(data)# 每个对象是字典

非过滤器查找

非过滤器方法:返回模型类或其他类型

	users=User.objects.all()
    # user=users.get(pk=1)# 获取一个对象
    try:
        # user=users.get(id__gt=5)# 获取多个 MultipleObjectsReturned异常
        # user = users.get()  # 获取多个 MultipleObjectsReturned异常
        user=users.get(id__lt=1)# 没有获取到任何数据 DoesNotExist异常
    except MultipleObjectsReturned:
        print("\033[1;31m MultipleObjectsReturned异常发生\033[0m")
        user=users.get(pk=1)
    except User.DoesNotExist:
        print("\033[1;31m DoesNotExist异常发生\033[0m")
        user = users.get(pk=1)
    finally:
        pass

    print(user)
    
    
    
    users=User.objects.all()
    # user=users.first()
    # user=users.last()
    # print(users.filter(id__gt=5).exists())# 只有queryset才能调用exist()
    print(users.filter(id__gt=5).count())# 只有queryset才能调用
    # print(users.exists())

聚合查询

users=User.objects.all()
    # 聚合查询
    # select max(id) from user
    # data=users.aggregate(Max('id'))# 返回dict
    # select avg(id) from user
    # data=users.aggregate(Avg('id'))
    # select sum(id) from user
    # data=users.aggregate(Sum('id'))
    keys=Key.objects.all()
    # select count(*) from keys
    # data=keys.aggregate(Count('keypool'))



    #  select  keypool,count(*) from `key` group by keypool;
    # data=keys.values('keypool').annotate(Count('id'))# 返回QuerySet,可以使用filter()
    # 输出< QuerySet[{'keypool': 0, 'id__count': 4}, {'keypool': 1, 'id__count': 3}, {'keypool': 2, 'id__count': 2}] >
    # data=keys.values('keypool').annotate(Count('id')).filter(keypool=0)
    # 输出<QuerySet [{'keypool': 0, 'id__count': 4}]>
    print(data)

Q与F

	# Q 实现逻辑或与非
    users=User.objects.all()
    # data=users.filter(Q(id__lt=5)|Q(id__gt=5))# 使用逻辑或
    # data=users.filter(~Q(id=5))# 使用逻辑非
    # 逻辑和通过filter就可以实现

    # F 用于调用列名
    keys=Key.objects.all()
    # select * from `key` where id=keypool
    data=keys.filter(id=F('keypool'))

    print(data)

防sql注入

tmp=input('请输入用户名:')
    # users=User.objects.raw("select * from user where name='{}'".format(tmp))# 输入asdf' or '1 可进行sql注入 select * from user where name='asdf' or '1'
    users = User.objects.raw("select * from user where name='%s'" % tmp)# 同样可进行sql注入
    # users=User.objects.raw("select * from user where name=%s",[tmp])# 输入asdf' or '1 查询不到结果 select * from user where name=asdf' or '1 会自动去掉单引号

    print(list(users))# 不用list()会输出sql语句