本文共 7316 字，大约阅读时间需要 24 分钟。
一对多的表查询
| 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这个对象，才能给他添加到运维组。