Django-ORM连表操作

 

创建表（一对多）

class UserType(models.Model):
    caption = models.CharField(max_length=32)

class UserInfo(models.Model):
    user_type = models.ForeignKey(UserType)        # user_type对象中封装id,caption
    username = models.CharField(max_length=32)
    age = models.IntegerField()

创建表（多对多）

class Host(models.Model):
    hostname = models.CharField(max_length=32)
    port = models.IntegerField()

class HostAdmin(models.Model):
    username = models.CharField(max_length=32)
    email = models.CharField(max_length=32)
    host = models.ManyToManyField(Host)

 

增加数据

方法一
直接在关联外键的字段后添加_id

def user_info(request):
     dic = {'username': 'user1', 'age': 33, 'user_type_id': 1}
     models.UserInfo.objects.create(**dic)
     result = models.UserInfo.objects.all()
     for item in result:
         print(item.username, item.age, item.user_type.caption)

  方法二

    dic = {'username': 'user1', 'age': 33, 'user_type': models.UserType.objects.get(id=1)}
    models.UserInfo.objects.create(**dic)
    result = models.UserInfo.objects.all()
    for item in result:
        print(item.username, item.age, item.user_type.caption)

 

查询数据

一对多

　　正向查询

    models.UserInfo.objects.filter(username="user1")
    result = models.UserInfo.objects.filter(user_type__caption="CEO")
    for item in result:
        print(item.username,item.age,item.user_type.caption)

　　反向查询

    #搜索条件：id,caption,userinfo
    models.UserType.objects.get(idd=1)
    line = models.UserType.objects.get(id=1)
    print line.id
    print line.caption
    print line.userinfo_set.all()
    print line.userinfo_set.filter(username='user1')
    models.UserInfo.objects.filter(user_type=line)
    #获取某个人是什么用户类型，当前用户类型有多少人
    #user1的用户类型
    user_type_obj = models.UserType.objects.get(userinfo__username='user1')
    print user_type_obj.caption
    print user_type_obj.userinfo_set.all().count()

 

多对多

方法一

　　#正向查
　　admin_obj = models.HostAdmin.objects.get(id=1)
　　admin_obj.host.all()
　　#反向查
　　host_obj = models.Host.objects.get(id=1)
　　host_obj.hostadmin_set.all()

  方法二

    relation_list = models.HostRelation.objects.all()
    # relation_list = models.HostRelation.filter(c2__username='dali')
    for item in relation_list:
        print item.c1.hostname
        print item.c2.username

 

 

数据添加

方法一

　　正向添加

    #获取user1用户
    admin_obj = models.HostAdmin.objects.get(username='user1')
    #获取指定条件的主机列表
    host_list = models.Host.objects.filter(id__lt=3)
    #将主机和用户添加到第三张对应表
    admin_obj.host.add(*host_list)

　　反向添加

    host_obj = models.Host.objects.get(id=3)
    admin_list = models.HostAdmin.objects.filter(id__gt=1)
    host_obj.hostadmin_set.add(*admin_list)

 

方法二

　　自定义对应表(推荐)

    #1
    models.HostRelation.objects.create(
        c1=models.Host.objects.get(id=1),
        c2=models.HostAdmin.objects.get(id=2),
    )
    #2
    models.HostRelation.objects.create(
        c1_id=2,
        c2_id=1,
    )

 

 点赞实例

创建表

class MyUser(models.Model):
    username = models.CharField(max_length=32)
    password = models.CharField(max_length=64)

    def __unicode__(self):
        return self.username

class News(models.Model):
    title = models.CharField(max_length=32)
    content = models.CharField(max_length=32)
    def __unicode__(self):
        return self.title

class Favor(models.Model):
    user_obj = models.ForeignKey(MyUser)
    new_obj = models.ForeignKey(News)

    def __unicode__(self):
        return "%s -> %s" %(self.user_obj.username, self.new_obj.title)

 

查询

    new_list = models.News.objects.all()
    #查询user1点赞的所有文章
    dali_list = models.News.objects.filter(favor__user_obj__username='user1')
    for item in new_list:
        #item == 一条新闻
        print('=========分割线============')
        print item.title
        print item.content
        print item.favor_set.all().count()
        #本文章中dali用户所点的赞数，此数永远为1
        print item.favor_set.filter(user_obj__username='user1').count()