一对多的表查询
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
class
Project(models.Model):
name
=
models.CharField(u
'项目名称'
,max_length
=
32
,blank
=
True
)
id
=
models.CharField(u
'项目ID'
,max_length
=
32
,unique
=
True
,primary_key
=
True
,blank
=
True
)
create_date
=
models.DateTimeField(u
'创建时间'
, auto_now_add
=
True
)
update_date
=
models.DateTimeField(u
'更新时间'
, auto_now
=
True
)
def
__unicode__(
self
):
return
self
.name
class
Uhost(models.Model):
name
=
models.CharField(u
'计算机名'
,max_length
=
32
,blank
=
False
)
id
=
models.CharField(u
'实例ID'
,max_length
=
32
,blank
=
False
,primary_key
=
True
)
ip
=
models.GenericIPAddressField(u
'IP地址'
,blank
=
True
,null
=
True
)
cpu
=
models.CharField(u
'CPU/核'
,max_length
=
32
,blank
=
True
,null
=
True
)
memory
=
models.CharField(U
'内存/G'
,max_length
=
32
,blank
=
True
)
state
=
models.CharField(u
'实例状态'
,max_length
=
32
,blank
=
True
)
expiretime
=
models.DateTimeField(u
'到期时间'
, max_length
=
50
, null
=
True
, blank
=
True
)
isexpire
=
models.CharField(u
'是否过期'
, max_length
=
20
, blank
=
True
)
autorenew
=
models.CharField(u
'自动续费'
, max_length
=
20
, blank
=
True
)
tag
=
models.CharField(u
'业务组'
,max_length
=
32
,blank
=
True
)
networkstate
=
models.CharField(u
'网络状态'
,max_length
=
32
,blank
=
True
)
type
=
models.CharField(u
'实例类型'
,max_length
=
32
,blank
=
True
)
osfamily
=
models.CharField(u
'系统类型'
,max_length
=
32
,blank
=
True
)
ostype
=
models.CharField(u
'操作系统'
, max_length
=
50
, blank
=
True
)
chargetype
=
models.CharField(u
'付费类型'
, max_length
=
50
, blank
=
True
)
datadisk
=
models.IntegerField(u
'数据盘/G'
, blank
=
True
)
price
=
models.DecimalField(u
'价格'
,max_digits
=
8
,decimal_places
=
2
,null
=
True
,blank
=
True
)
zone
=
models.ForeignKey(Zone,verbose_name
=
u
'可用区'
,db_constraint
=
False
,on_delete
=
models.DO_NOTHING,blank
=
True
)
project
=
models.ForeignKey(Project,verbose_name
=
u
'所属项目'
,db_constraint
=
False
,on_delete
=
models.DO_NOTHING,blank
=
True
)
create_date
=
models.DateTimeField(u
'创建时间'
, auto_now_add
=
True
)
update_date
=
models.DateTimeField(u
'更新时间'
, auto_now
=
True
)
def
__unicode__(
self
):
return
self
.name
|
我建了两张表，project和uhost。
其中uhost表的project字段是设置了ForeignKey。
先看下project表中的内容。
|
1
2
3
4
5
6
7
8
9
10
11
|
>>> Project.objects.
all
()
[<Project: 上海别样红信息技术有限公司>, <Project: 备案专用>, <Project: gitlab>,
<Project: PublicTest>, <Project: SPMS>, <Project: 安全测试>, <Project: OTA>,
<Project:
99
数据同步中转，本项目与
99
内网打通，不允许添加任何机器>, <Project: Ops>,
<Project: iPms>]
>>> Project.objects.
all
().values(
'id'
)
[{
'id'
: u
'org-81'
}, {
'id'
: u
'org-aws3dj'
}, {
'id'
: u
'org-et55qg'
},
{
'id'
: u
'org-ghan2t'
}, {
'id'
: u
'org-ja1wvv'
}, {
'id'
: u
'org-kbxrx4'
},
{
'id'
: u
'org-pni2a2'
}, {
'id'
: u
'org-qf4d2n'
}, {
'id'
: u
'org-vzfixt'
},
{
'id'
: u
'org-wrg10n'
}]
|
表查询：
查询uhost表中name中包含OPS10的所有主机对象
|
1
2
3
4
5
6
7
8
|
>>> Uhost.objects.
filter
(name__contains
=
'OPS10'
)
[<Uhost: SRV
-
OPS10
-
CS05>, <Uhost: SRV
-
OPS10
-
SPPX01>, <Uhost: SRV
-
OPS10
-
MAIL01>,
<Uhost: SRV
-
OPS10
-
PROXY02>, <Uhost: SRV
-
OPS10
-
PROXY01>, <Uhost: SRV
-
OPS10
-
HAP02>,
<Uhost: SRV
-
OPS10
-
HAP01>, <Uhost: SRV
-
OPS10
-
ANSIBLE02>, <Uhost: SRV
-
OPS10
-
NGX01>,
<Uhost: SRV
-
OPS10
-
NGX02>, <Uhost: SRV
-
OPS10
-
PROXY05>, <Uhost: SRV
-
OPS10
-
ANSIBLE06>,
<Uhost: SRV
-
OPS10
-
DEPLOY01>, <Uhost: SRV
-
OPS10
-
NGINX01>, <Uhost: SRV
-
OPS10
-
ES02>,
<Uhost: SRV
-
OPS10
-
ES03>, <Uhost: SRV
-
OPS10
-
ES01>, <Uhost: SRV
-
OPS10
-
LOGSTASH01>,
<Uhost: SRV
-
OPS10
-
PROXY04>, <Uhost: SRV
-
OPS10
-
PROXY03>,
'...(remaining elements truncated)...'
]
|
正向查询：
若关系模型A包含与模型B关联的关联字段, 模型A的实例可以通过关联字段访问与其关联的模型B的实例:
Django提供了一种使用双下划线__的查询语法：
例如：
|
1
|
Uhost.objects.
filter
(project__id
=
'org-81'
)
|
查找uhost表中，所有project id为‘org-81’的的主机
|
1
2
3
4
5
6
7
|
>>> Uhost.objects.
filter
(project__id
=
'org-81'
)
[<Uhost: dbbackupsyncer2>, <Uhost: SRV
-
CPMS10
-
WEB16>, <Uhost: SRV
-
CPMS10
-
WEB15>,
<Uhost: publicconsole>, <Uhost: SRV
-
CPMS10
-
WEB14>, <Uhost: dbbackupsyncer>,
<Uhost: 官网>, <Uhost:
99exchangedb
>, <Uhost: dc1>, <Uhost: dc2>, <Uhost: publicweb>,
<Uhost: SRV
-
CPMS10
-
WEB13>, <Uhost: SRV
-
OTA10
-
WS04>, <Uhost: SRV
-
OTA10
-
WS05>,
<Uhost: SRV
-
OPS10
-
CS05>, <Uhost: SRV
-
OTA10
-
WS03>, <Uhost: SRV
-
OTA10
-
WEB04>,
<Uhost: SRV
-
OTA10
-
WEB03>, <Uhost:
99datasyncer
>, <Uhost: SRV
-
CPMS10
-
WEB31>,
'...(remaining elements truncated)...'
]
|
查询uhost表中project id包含‘ghan’的主机信息
|
1
2
3
4
5
|
>>> Uhost.objects.
filter
(project__id__contains
=
'ghan'
)
[<Uhost: SRV
-
OPS10
-
ANSIBLE06>, <Uhost: SRV
-
OPS10
-
DEPLOY01>, \
<Uhost: SRV
-
OPS01
-
DEPLOY01>, <Uhost: SRV
-
OPS10
-
NGINX01>, \
<Uhost: SRV
-
OPS10
-
ES02>, <Uhost: SRV
-
OPS10
-
ES03>, <Uhost: SRV
-
OPS10
-
ES01>,\
<Uhost: SRV
-
OPS10
-
LOGSTASH01>]
|
反向查询：
被索引的关系模型可以访问所有参照它的模型的实例，如Entry.blog作为Blog的外键，默认情况下Blog.entry_set是包含所有参照Blog的Entry示例的查询集,可以使用查询集API取出相应的实例。
查询project name为Ops的所有主机对象
|
1
2
3
4
5
|
>>> Project.objects.get(name
=
'Ops'
).uhost_set.
all
()
[<Uhost: SRV
-
OPS10
-
ANSIBLE06>, <Uhost: SRV
-
OPS10
-
DEPLOY01>,
<Uhost: SRV
-
OPS01
-
DEPLOY01>, <Uhost: SRV
-
OPS10
-
NGINX01>,
<Uhost: SRV
-
OPS10
-
ES02>, <Uhost: SRV
-
OPS10
-
ES03>, <Uhost: SRV
-
OPS10
-
ES01>,
<Uhost: SRV
-
OPS10
-
LOGSTASH01>]
|
查询project name为Ops的所有主机对象的name属性
|
1
2
3
4
5
|
>>> Project.objects.get(name
=
'Ops'
).uhost_set.values(
'name'
)
[{
'name'
: u
'SRV-OPS10-ANSIBLE06'
}, {
'name'
: u
'SRV-OPS10-DEPLOY01'
}, \
{
'name'
: u
'SRV-OPS01-DEPLOY01'
}, {
'name'
: u
'SRV-OPS10-NGINX01'
}, \
{
'name'
: u
'SRV-OPS10-ES02'
}, {
'name'
: u
'SRV-OPS10-ES03'
}, \
{
'name'
: u
'SRV-OPS10-ES01'
}, {
'name'
: u
'SRV-OPS10-LOGSTASH01'
}]
|
查询project name为Ops的，并且name包含OPS字符串的所有主机
|
1
2
3
4
5
|
>>> Project.objects.get(name
=
'Ops'
).uhost_set.
filter
(name__contains
=
'OPS'
)
[<Uhost: SRV
-
OPS10
-
ANSIBLE06>, <Uhost: SRV
-
OPS10
-
DEPLOY01>,
<Uhost: SRV
-
OPS01
-
DEPLOY01>, <Uhost: SRV
-
OPS10
-
NGINX01>,
<Uhost: SRV
-
OPS10
-
ES02>, <Uhost: SRV
-
OPS10
-
ES03>, <Uhost: SRV
-
OPS10
-
ES01>,
<Uhost: SRV
-
OPS10
-
LOGSTASH01>]
|
一对多表创建对象：
|
1
2
3
4
|
>>> host
=
Uhost(
id
=
'aaaaa'
)
>>> host.project
=
Project.objects.get(
id
=
'org-81'
)
>>> host
=
Uhost(name
=
'SRV-TEST'
)
>>> host.save()
|
多对多的查询
示例：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
class
GroupInfo(models.Model):
name
=
models.CharField(U
'组名'
,max_length
=
32
,blank
=
True
)
def
__unicode__(
self
):
return
self
.name
class
UserInfo(models.Model):
name
=
models.CharField(u
'姓名'
,max_length
=
32
,blank
=
True
)
email
=
models.EmailField(u
'邮箱'
)
group
=
models.ManyToManyField(GroupInfo)
def
__unicode__(
self
):
return
self
.name
|
查询：
从userinfo表开始查
|
1
2
3
4
|
>>> UserInfo.objects.get(name
=
'zeng'
).group.
all
()
[<GroupInfo: 运维组>, <GroupInfo: 报警组>]
>>> UserInfo.objects.get(name
=
'zeng'
).group.
filter
(name
=
'运维组'
)
[<GroupInfo: 运维组>]
|
从groupinfo表开始查
|
1
2
3
4
|
>>> GroupInfo.objects.get(name
=
'CTO'
).userinfo_set.
all
()
[<UserInfo: zhang>]
>>> GroupInfo.objects.get(name
=
'CTO'
).userinfo_set.values(
'name'
,
'email'
)
[{
'name'
: u
'zhang'
,
'email'
: u
'zhang@qq.com'
}]
|
多对多表 创建对象：
|
1
2
3
|
>>> u
=
UserInfo(name
=
'he'
,email
=
'he@qq.com'
)
>>> u.save()
>>> u.group.add(GroupInfo.objects.get(name
=
'运维组'
))
|
注意：
要添加新对象时，首先必须保证该对象在做ManyToMany的两张表中存在才行，比如上面的例子，我想创建一个叫he的用户，组为运维组。但是he这个用户不存在，所以先必须创建he这个对象，才能给他添加到运维组。
本文转自 曾哥最爱 51CTO博客，原文链接：http://blog.51cto.com/zengestudy/1906221，如需转载请自行联系原作者
