Django 多表联合查询

一对多的表查询

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表中的内容。

>>> 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的所有主机对象

>>> 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提供了一种使用双下划线__的查询语法：

例如：

Uhost.objects.filter(project__id='org-81')

查找uhost表中，所有project id为‘org-81’的的主机

>>> 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’的主机信息

>>> 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的所有主机对象

>>> 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属性

>>> 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字符串的所有主机

>>> 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>]

一对多表创建对象：

>>> host=Uhost(id='aaaaa')
>>> host.project=Project.objects.get(id='org-81')
>>> host=Uhost(name='SRV-TEST')
>>> host.save()

多对多的查询

示例：

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表开始查

>>> UserInfo.objects.get(name='zeng').group.all()
[<GroupInfo: 运维组>, <GroupInfo: 报警组>]
>>> UserInfo.objects.get(name='zeng').group.filter(name='运维组')
[<GroupInfo: 运维组>]

从groupinfo表开始查

>>> 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'}]

多对多表 创建对象：

>>> u = UserInfo(name='he',email='he@qq.com')
>>> u.save()
>>> u.group.add(GroupInfo.objects.get(name='运维组'))

注意：

要添加新对象时，首先必须保证该对象在做ManyToMany的两张表中存在才行，比如上面的例子，我想创建一个叫he的用户，组为运维组。但是he这个用户不存在，所以先必须创建he这个对象，才能给他添加到运维组。