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