django中ORM查询高级操作5

    xiaoxiao2022-07-12  157

    一、filter和exclude

    filter选取符合条件,exclude表示不选取。

    查看models转换的语句可以在运行语句的结尾加上       .query.__str__()

    1.大于,小于,大于等于,小于等于,大于且小于

    models.UserInfo.objects.filter(id__gt=5) # 获取id大于5的值 models.UserInfo.objects.filter(id__gte=5) # 获取id大于等于5的值 models.UserInfo.objects.filter(id__lt=5) # 获取id小于5的值 models.UserInfo.objects.filter(id__lte=5) # 获取id小于5的值 models.UserInfo.objects.filter(id__lt=5, id__gt=1) # 获取id大于1且 小于5的值

    2.in,在所给的列表中

    models.UserInfo.objects.filter(id__in=[4,8,12]) # 获取id等于4、8、12的数据 models.UserInfo.objects.exclude(id__in=[11, 22, 33]) # not in

    3.isnull

    models.UserInfo.objects.filter(username__isnull=True) #username为空的数据

    4.contains

    models.UserInfo.objects.filter(username__contains="a") # contains models.UserInfo.objects.filter(username__icontains="a") # icontains大小写不敏感 models.UserInfo.objects.exclude(username__icontains="a") #不包含"a"

    5.range

    models.UserInfo.objects.filter(id__range=[1,5]) # 范围bettwen and

    6.其他类似

    startswith #以...开头 istartswith #忽略大小写,以...开头 endswith #以...结尾 iendswith #忽略大小写,以...结尾 exact #等于... iexact #忽略大小写,等于...

    7.order by

    models.UserInfo.objects.filter(username='a').order_by('id') # asc,递增 models.UserInfo.objects.filter(username='a').order_by('-id') # desc,递减

    8.group by

    from django.db.models import Count, Min, Max, Sum #例子1 models.UserInfo.objects.filter(password=1234).values('username').annotate(count=Count('id')) #原生sql语句 SELECT "app01_UserInfo"."username", COUNT("app01_UserInfo"."id") AS "count" FROM "app01_UserInfo" WHERE "app01_UserInfo"."password" = 1234 GROUP BY "app01_UserInfo"."id" #例子2 models.UserInfo.objects.all().values('username').annotate(count=Count('id')).values('username','count') #原生sql语句 SELECT "app01_userinfo"."username", COUNT("app01_userinfo"."id") AS "count" FROM "app01_userinfo" GROUP BY "app01_userinfo"."username"

    9.limit offset

    models.UserInfo.objects.all()[2:5] #同python切片

    10.regx正则

    models.UserInfo.objects.filter(name__regex=r'(\w)\1\1') #regex正则匹配 models.UserInfo.objects.filter(name__iregex=r'(\w)\1\1') #iregex 不区分大小写

    11.日期

    # date #查询time字段为2019-5-22的数据 models.UserInfo.objects.filter(time__date__gt=datetime.date(2019,5,22)) #查询与user_group关联的外键ctime字段为2019-5-22以后的数据 models.UserInfo.objects.filter(user_group__ctime__date__gte=datetime.date(2019,5,22)) # year models.UserInfo.objects.filter(time__year=2019) #查询与user_group关联的外键的创建时间为2019年的数据 models.UserInfo.objects.filter(user_group__ctime__year__gte=2019) # month models.UserInfo.objects.filter(time__month=5) models.UserInfo.objects.filter(user_group__ctime__month__gte=5) # day models.UserInfo.objects.filter(time__day=22) models.UserInfo.objects.filter(user_group__ctime__day__gte=22) # week_day models.UserInfo.objects.filter(time__week_day=2) #查询工作日为第二天的数据,指的是周一,这里是1-7(周日-周六) models.UserInfo.objects.filter(user_group__ctime__week_day__gte=2) # hour models.UserInfo.objects.filter(time__hour=15) #sqlite默认用的utc时间,查询时请按照当地时间,在网页上显示的是按照当地时间 models.UserInfo.objects.filter(user_group__ctime__hour__gte=15) models.UserInfo.objects.filterr(timestamp__hour__gte=15) # minute models.UserInfo.objects.filter(time__minute=15) models.UserInfo.objects.filter(user_group__ctime__minute__gte=15) models.UserInfo.objects.filterr(timestamp__minute__gte=15) # second models.UserInfo.objects.filter(time__second=37) models.UserInfo.objects.filter(user_group__ctime__second__gte=37) models.UserInfo.objects.filterr(timestamp__second__gte=37)

    二、其他操作

    1.extra

    extra中可实现别名,条件,排序等,后面连个用filter,exclude一般都能实现,排序用order_by也能实现。

    extra源码:

    def extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None): """Add extra SQL fragments to the query.""" assert self.query.can_filter(), \ "Cannot change a query once a slice has been taken" clone = self._chain() clone.query.add_extra(select, select_params, where, params, tables, order_by) return clone

    extra在传递参数时,要从select,where,tables中选择,select接受的参数形式是字典形式,而where和table支持的是列表形式的参数,params和select_params是为了传递值,oder_by排序。

    1.1 select和select_params

    #select #查询结果中会增加is_adult字段,以布尔值显示结果 models.UserInfo.objects.all().extra(select={'is_adult':"age>18" }) #在sqlite3中格式化时间: #str_time自定义的字段名称,strftime格式化时间,time是UserInfo中的字段,"%s"占位符 models.UserInfo.objects.all() .extra(select={'str_time':"strftime(%s,time)" },select_params=['%Y-%m-%d']) #select_paramas可以传递列表 .values("str_time",'name') #例子3:输出单一的结果 models.UserInfo.objects.all() .extra(select={'group':'select count(id) from app01_userinfo where age>%s'},select_params=['20'])

    1.2 where和params

    #where和params #可以用OR进行连接,传递值是可以写成元组形式也可以是列表形式 v7=models.UserInfo.objects.all() .extra(where=["age>%s OR age<%s","password='1234'"],params=(20,27),order_by=['age']) .values('username','age','password') print(v7)

    1.3 tables

    #tables models.UserInfo.objects.all() .extra(tables=['app01_usergroup'],where=['uid=user_group_id']) .values('username')

    2.F

    UserInfo表中的年龄都自加1:

    from django.db.models import F models.UserInfo.objects.update(age=F('age')+1)

    3.Q

    用来执行比较复杂的where条件查询,可以用来表示or和and关系

    #方式一: Q(id__gt=10) Q(id=8) | Q(id__gt=10) Q(Q(id=8) | Q(id__gt=10)) & Q(username='root') # 方式二: con = Q() q1 = Q() q1.connector = 'OR' q1.children.append(('id', 1)) q1.children.append(('id', 10)) q1.children.append(('id', 9)) q2 = Q() q2.connector = 'OR' q2.children.append(('uid', 2)) q2.children.append(('uid', 4)) q2.children.append(('uid', 8)) con.add(q1, 'AND') con.add(q2, 'AND') models.UserInfo.objects.filter(con) #条件为: (id=1 or id=10 or id=9) and (uid=2 or uid=4 or uid=8)

     

    最新回复(0)