django的orm

朴灿烈づ我的快乐病毒、 2021-10-29 10:36 317阅读 0赞

# django的orm #

### 写业务逻辑 ###

from django.shortcuts import render, HttpResponse Create your views here.  from app01 import models  def test(request):

## 添加数据 ##

models.UserInfo.objects.create(name='lxx',age=23, ut_id=2) dict = { "name":'xxx', 'age':23, 'ut_id':3} models.UserInfo.objects.create(**dict)

## 添加多条数据 ##

info = [
         models.UserInfo(name='root1', age=34, ut_id=1),     models.UserInfo(name='root2', age=35, ut_id=2),     models.UserInfo(name='root3', age=36, ut_id=1),     models.UserInfo(name='root4', age=37, ut_id=3),     models.UserInfo(name='root5', age=32, ut_id=1), ] models.UserInfo.objects.bulk_create(info)

## 删除 ##

models.UserInfo.objects.filter(id=3).delete()

## 更新 ##

models.UserInfo.objects.filter(id=3).update(name='lll', age=23)

## 查询所有数据 ##

res = models.UserInfo.objects.all()
    print(res) ### [ obj, obj, obj...]  # 列表里面放对象
    for obj in res:
        print(obj.name)
         
         
    res = models.UserInfo.objects.values('name', 'age') # 根据字段名查询
    print(res) ### [{"name":'xxx', 'age':44}, ......] # 列表里面放字典,key是字段名,value是对应的值
    
    res = models.UserInfo.objects.values_list('name', 'age') # 根据字段名查询
    print(res) #### [('zekai', '23'), ...... ] # 列表里面放元组,只有值

## 查询语句 ##

# res = models.UserInfo.objects.first()
    # print(res) ### obj
    #
    # res = models.UserInfo.objects.filter(id=3)  ### where id=3
    # res = models.UserInfo.objects.filter(id__gt=3)
    #
    # ##### 正向查询
    # ## 查询所有用户的用户类型
    # res = models.UserInfo.objects.all()
    # for obj in res:
    #     print(obj.name, obj.age, obj.ut.title)
    #
    # ##### 反向查询
    # ### 查询所有类型下面有多少用户
    # res = models.UserType.objects.all()
    # for obj in res:
    #     #### 表名小写_set
    #     # print(obj.title, obj.userinfo_set.all())
    #     #### releate_name的方式
    #     print(obj.title, obj.users.all())
    
    #### orm查询用法大全
    
    ### 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,4,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("id", "name").annotate(s=Sum('age'))
    print(res.query)
    
    ### select id, sum(age) as s, 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')

## or ##

#### 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)

## 原生sql ##

#### 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)

# print(res.query)  ### 查看上述代码生成的sql语句

return HttpResponse('ok')

转载于:https://www.cnblogs.com/fengxuemuyangren/p/11355540.html