ORM的增删改查

昨天回顾

一、路由系统

a.创建app的方式

pycharm

命令行创建：python manage.py startapp app02

b.路由的分组

c.路由的分发

(\w+)所有非空，+代表至少1个

?P<X>(\W+)精准匹配 ，如果位置第一个的话，第一个捕捉到的值传给X

二、ORM

# 添加数据(2种办法)
models.UserInfo.objects.create(name='XXX',age=22,ut_id=2)
dicts = {"name":'XXX','age':22,'ut_id':2}
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}, ......]
​
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)  ##where id>3
​
# 正常查询
#查询所有用户的用户类型
res = models.UserInfo.objects.all()
for obj in res:
    print(obj.name,obj.age,obj.ut.title)
    
#反向查询
#查询所有类型下面有多少用户
res = models.UserInfo.objects.all()
for obj in res:
    #表名小写_set
    print(obj.title,obj.userinfo_set.all())
    # relate_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.filter(id=3) 
res = models.UserInfo.objects.filter(id=2,name='zekai') #,等同于and 

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.UseInfo.objects.filter(id__get=3).count()
   ​

6. order by

   order by id desc,age asc
   - :降序
   res = models.UserInfo.objects.all().order_by('-id','age')# 先排id，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')
    ​

11. defer

    res = models.UserInfo.objects.defer('id')
    ​

12. or

    Q=or
    from django.db.models import Q
    res = models.UserInfo.objects.filter(Q(Q(id__get=3) | Q(name='zekai')) & Q(age=23))
    ​

13. F

    from django.db.models import F
    models.UserInfo.objects.update(name=F('name')+1)#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)
    ​
    print(res.query) #查看上述代码生成的sql语句
    ​