Django-orm数据库查询语句
发布时间:2020-12-20 11:00:10 所属栏目:Python 来源:网络整理
导读:Django-orm数据库查询语句 #### orm查询用法大全 ### 数据库表 class UserInfo(models.Model): ### 主键自增id不用写, 默认会加上 name = models.CharField(max_length=32,null=True) age = models.IntegerField(null=True) ut = models.ForeignKey("UserTy
Django-orm数据库查询语句#### orm查询用法大全 ### 数据库表 class UserInfo(models.Model): ### 主键自增id不用写, 默认会加上 name = models.CharField(max_length=32,null=True) age = models.IntegerField(null=True) ut = models.ForeignKey("UserType",null=True,related_name='users') ### 1. 字段名过滤 # res = models.UserInfo.objects.filter(name='zekai') ### where name='zekai' res = models.UserInfo.objects.filter(id__gt=3) res = models.UserInfo.objects.filter(id__gte=3) res = models.UserInfo.objects.filter(id__lte=3) res = models.UserInfo.objects.filter(id__lt=3) res = models.UserInfo.objects.exclude(id=4) res = models.UserInfo.objects.filter(id=2,name='zekai') ### where id=2 and name='zekai' ### 缺一个 or ####2. in not in res = models.UserInfo.objects.filter(id__in=[2,4,5]) ## where id in (2,5) res = models.UserInfo.objects.exclude(id__in=[1,2]) ### where id not in (1,2) ####3. between...and ### where id between 4 and 8 [4,8] res = models.UserInfo.objects.filter(id__range=[4,8]) ####4. like ##### where name like 'like%' ##### g:全局 global i: ignore (忽略大小写) res = models.UserInfo.objects.filter(name__startswith="ze") res = models.UserInfo.objects.filter(name__istartswith="zekai") ##### where name like '%kk' res = models.UserInfo.objects.filter(name__endswith="kkk") res = models.UserInfo.objects.filter(name__iendswith="jjj") ##### where name like '%hhh%' res = models.UserInfo.objects.filter(name__contains='hhh') res = models.UserInfo.objects.filter(name__icontains='ggg') res = models.UserInfo.objects.filter(name__regex="^zekai$") ### 5.count #### select count(*) from userinfo where id>3; #### select count(id) from userinfo where id>3; res = models.UserInfo.objects.filter(id__gt=3).count() ### 6.order by ##### order by id desc,age asc; #### - :降序 res = models.UserInfo.objects.all().order_by('-id','age') #### 7. group by ###### select id,sum(age) as s,username from userinfo group by username from django.db.models import Count,Min,Max,Sum res = models.UserInfo.objects.values("name").annotate(s=Sum('age')) print(res.query) ### select id,username from userinfo group by username having s > 50; res = models.UserInfo.objects.values("name").annotate(s=Sum('age')).filter(s__gt=50) #### 8. limit ##### limit 0,10 分页 res = models.UserInfo.objects.all()[1:4] # print(res) ##### 9. last res = models.UserInfo.objects.last() ##### 10. only res = models.UserInfo.objects.only('name') print(res) #### 11. defer res = models.UserInfo.objects.defer('id') #### 12. or ##### Q from django.db.models import Q res = models.UserInfo.objects.filter( Q(Q(id__gt=3) | Q(name='zekai')) & Q(age=23) ) #### 13.F from django.db.models import F models.UserInfo.objects.update(name=F('name')+1) #### 14.原生sql 类似pymysql # from django.db import connection,connections # cursor = connection.cursor() # cursor = connections['default'].cursor() # cursor.execute("""SELECT * from auth_user where id = %s""",[1]) # row = cursor.fetchone() # print(row) #### 15.distinct # models.UserInfo.objects.values("name",'age').distinct('name') # print(res.query) ### 查看上述代码生成的sql语句 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |