Django的orm

一、基本查询

查询

res = models.Student.objects.all()

返回的是QuerySet  列表中套对象

res = models.Student.objects.values("name").all()
 # 返回的是QuerySet  列表中套字典

res = models.Student.objects.values_list("name").all()

返回的是QuerySet  列表中套元组
res = models.Student.objects.first()
列表中的第一个

　　

二、高级查询

## 1. in
# res = models.UserInfo.objects.filter(id__in=[1,2,3])
# print(res)

## 2. not in
# res = models.UserInfo.objects.exclude(id__in=[1,2,3])
# print(res)

## 3. like
## where name like 'ze%' ## 以ze开头的所有的数据
### startswith： 以 某单词开头
### istartswith : ignore (忽略)  以 某单词开头 忽略大小写
# res = models.UserInfo.objects.filter(name__startswith="ze")
# res = models.UserInfo.objects.filter(name__istartswith="ze")
# print(res)

## where  name  like '%ze'
## endswith : 以 某个单词结尾
## iendswith:  ignore (忽略)  以 某单词结尾 忽略大小写
# res = models.UserInfo.objects.filter(name__endswith='ze')

## where name like "%ze%"
### contains : 包含某一个单词
### icontains: 包含某一个单词 不区分大小写
# models.UserInfo.objects.filter(name__contains='ze')

### 4. between.. and..
### models.UserInfo.objects.filter(id__range=[1,2])

### 5. limit 10, 20
# models.UserInfo.objects.all()[开始位置:结束位置]
# models.UserInfo.objects.all()[0:10]
# models.UserInfo.objects.all()[10:20]

### 6. order by age asc, name desc
# res = models.UserInfo.objects.all().order_by('id') ## 默认升序
### 前面加一个 ‘-’ 代表 降序
# res = models.UserInfo.objects.all().order_by('-id','name')
# print(res.query)

### 7. group by
# from django.db.models import Count, Max, Min, Sum
# res = models.UserInfo.objects.values('name').annotate(xxx=Count('id'))
# print(res.query)

# SELECT "app01_userinfo"."id", COUNT("app01_userinfo"."name") AS "xxx"
# FROM "app01_userinfo"
# GROUP BY "app01_userinfo"."id";


#### 8. only: 只取某一个列的值
## SELECT "app01_userinfo"."id", "app01_userinfo"."name", 
"app01_userinfo"."age" FROM "app01_userinfo"
# res = models.UserInfo.objects.only('name',  'age').all()
# print(res) ## <QuerySet [<UserInfo: UserInfo object>, 
<UserInfo: UserInfo object>, <UserInfo: UserInfo object>,
 <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, 
<UserInfo: UserInfo object>]>

### 9. defer: 除了这几列之外的所有列的值
### SELECT "app01_userinfo"."id", "app01_userinfo"."ut_id" 
FROM "app01_userinfo"
# res = models.UserInfo.objects.defer('id','name', 'age').all()
# print(res.query)

### 10.using: 想要使用哪个数据库， 就将这个数据库的配置名称写到using中
# models.UserInfo.objects.all().using("xxxx")


### 11. 表中总共多少条数据
# res = models.UserInfo.objects.count()
# print(res)

### 12. 第一条数据
# res = models.UserInfo.objects.first()
# print(res)

## 13.最后一条数据
# res = models.UserInfo.objects.last()


## 14.gt lt
# res = models.UserInfo.objects.filter(id__gt=3)
# res = models.UserInfo.objects.filter(id__gte=3)
# res = models.UserInfo.objects.filter(id__lt=3)
# res = models.UserInfo.objects.filter(id__lte=3)

### 15. and操作
# res =  models.UserInfo.objects.filter(id=1, name='zekai')
# print(res.query)

### 16.or操作
# from django.db.models import Q
# res = models.UserInfo.objects.filter( Q(Q(id=1) | Q(name='zekai'))
 & Q(name='xxxx')  )
# print(res.query)

### 17. 在原来的基础上更新值
# from django.db.models import F
# models.UserInfo.objects.update(age = F('age') + 1)

## 18。原生sql
# from django.db import  connection
# cursor = connection.cursor()
# cursor.execute("select * from app01_userinfo where id=%s", [1,])
# # res = cursor.fetchall()
# # res = cursor.fetchone()
# # print(res)

## 19. 原生sql
# models.UserInfo.objects.raw('select * from app01_userinfo')

　　

三、多对多关系表

第一种方式：自定义
models.py:
class Boy(models.Model):
	name = models.CharField(max_length=32, null=True)

class Girl(models.Model):
	nick = models.CharField(max_length=32, null=True)

class Love(models.Model):
	b = models.ForeignKey("Boy", null=True)
	g = models.ForeignKey("Girl", null=True)
	
	class Meta:
		unique_together = [
			('b',  'g')
		]

views.py:

### 1. 查询和 xx 约会的 姑娘
# res = models.Boy.objects.filter(name='xx').first()
# # print(res) ### Boy object
# ### 反向查询 love中的相亲记录
# love_list = res.love_set.all() ## <QuerySet [<Love: Love object>,
 <Love: Love object>]>
# for obj in love_list:
#     ### 正向查询 girl表中的nick
#     print(obj.g.nick)

#### 2.查询和 xx 约会的 姑娘
# res = models.Love.objects.filter(b__name='xx').all()
# print(res) ## <QuerySet [<Love: Love object>, <Love: Love object>]>
# for obj in res:
#     print(obj.g.nick)

### 3.查询和 xx 约会的 姑娘
res = models.Love.objects.filter(b__name='xx').values("g__nick")
print(res)

　　

第二种方式：django定义的
通过 ManyToManyField() 来生成第三张表

models.py:

class Boy(models.Model):
	name = models.CharField(max_length=32, null=True)
	g = models.ManyToManyField('Girl', null=True)

class Girl(models.Model):
	nick = models.CharField(max_length=32, null=True)

views.py:

### django
### 添加
obj = models.Boy.objects.filter(name='xx').first()
# print(obj)### Boy object
# obj.g.add(3)
# obj.g.add(*[1,2])

### 重置
# obj.g.set([4])

### 查询
# obj = models.Boy.objects.filter(name='xx').first()
# res = obj.g.all()
# print(res) ## <QuerySet [<Girl: Girl object>, <Girl: Girl object>,
 <Girl: Girl object>]>
# for obj  in res:
#     print(obj.nick)

## 删除
obj = models.Boy.objects.filter(name='xx').first()
obj.g.clear()

　　

四、增删改

增

### 插入一条数据
	models.xxx.objects.create(name='xxx')
	
### 插入多条数据 
obj = [
models.UserInfo(name='zekai', age=12,  ut_id=2),
models.UserInfo(name='xxxxx', age=13,  ut_id=2),
models.UserInfo(name='dsadsa', age=14,  ut_id=1),
models.UserInfo(name='gfdgfdg', age=24,  ut_id=2),
models.UserInfo(name='tretre', age=45,  ut_id=3),
models.UserInfo(name='gfdgfd', age=42,  ut_id=2),
	]
models.UserInfo.objects.bulk_create(obj)

删
models.xxxx.objects.all().delete()
models.xxxx.objects.filter(name='kkk').delete()
	
ps:
ut = models.ForeignKey("UserType", null=True, on_delete=models.CASCADE)
CASCADE: 设置级联删除
SET_NULL : 取消级联删除


改
	
models.userinfo.objects.filter(xxxx).update()
	

　　

 

转载于:https://www.cnblogs.com/wangwei5979/p/11197799.html