Django（六）、ORM操作进阶

你的名字 2022-06-01 13:52 233阅读 0赞

**前言**  
上一篇中，介绍了django的单表操作，本篇描述一下django的多表操作，包括条件过滤、联表操作等.

**一、双下划綫条件过滤：**

# 获取个数
            #
            # models.Tb1.objects.filter(name='seven').count()
    
            # 大于，小于
            #
            # models.Tb1.objects.filter(id__gt=1)              # 获取id大于1的值
            # models.Tb1.objects.filter(id__gte=1)              # 获取id大于等于1的值
            # models.Tb1.objects.filter(id__lt=10)             # 获取id小于10的值
            # models.Tb1.objects.filter(id__lte=10)             # 获取id小于10的值
            # models.Tb1.objects.filter(id__lt=10, id__gt=1)   # 获取id大于1 且 小于10的值
    
            # in
            #
            # models.Tb1.objects.filter(id__in=[11, 22, 33])   # 获取id等于11、22、33的数据
            # models.Tb1.objects.exclude(id__in=[11, 22, 33])  # not in
    
            # isnull
            # Entry.objects.filter(pub_date__isnull=True)
    
            # contains
            #
            # models.Tb1.objects.filter(name__contains="ven")
            # models.Tb1.objects.filter(name__icontains="ven") # icontains大小写不敏感
            # models.Tb1.objects.exclude(name__icontains="ven")
    
            # range
            #
            # models.Tb1.objects.filter(id__range=[1, 2])   # 范围bettwen and
    
            # 其他类似
            #
            # startswith，istartswith, endswith, iendswith,
    
            # order by
            #
            # models.Tb1.objects.filter(name='seven').order_by('id')    # asc
            # models.Tb1.objects.filter(name='seven').order_by('-id')   # desc
    
            # group by
            #
            # from django.db.models import Count, Min, Max, Sum
            # models.Tb1.objects.filter(c1=1).values('id').annotate(c=Count('num'))
            # SELECT "app01_tb1"."id", COUNT("app01_tb1"."num") AS "c" FROM "app01_tb1" WHERE "app01_tb1"."c1" = 1 GROUP BY "app01_tb1"."id"
    
            # limit 、offset
            #
            # models.Tb1.objects.all()[10:20]
    
            # regex正则匹配，iregex 不区分大小写
            #
            # Entry.objects.get(title__regex=r'^(An?|The) +')
            # Entry.objects.get(title__iregex=r'^(an?|the) +')
    
            # date
            #
            # Entry.objects.filter(pub_date__date=datetime.date(2005, 1, 1))
            # Entry.objects.filter(pub_date__date__gt=datetime.date(2005, 1, 1))
    
            # year
            #
            # Entry.objects.filter(pub_date__year=2005)
            # Entry.objects.filter(pub_date__year__gte=2005)
    
            # month
            #
            # Entry.objects.filter(pub_date__month=12)
            # Entry.objects.filter(pub_date__month__gte=6)
    
            # day
            #
            # Entry.objects.filter(pub_date__day=3)
            # Entry.objects.filter(pub_date__day__gte=3)
    
            # week_day
            #
            # Entry.objects.filter(pub_date__week_day=2)
            # Entry.objects.filter(pub_date__week_day__gte=2)
    
            # hour
            #
            # Event.objects.filter(timestamp__hour=23)
            # Event.objects.filter(time__hour=5)
            # Event.objects.filter(timestamp__hour__gte=12)
    
            # minute
            #
            # Event.objects.filter(timestamp__minute=29)
            # Event.objects.filter(time__minute=46)
            # Event.objects.filter(timestamp__minute__gte=29)
    
            # second
            #
            # Event.objects.filter(timestamp__second=31)
            # Event.objects.filter(time__second=2)
            # Event.objects.filter(timestamp__second__gte=31)

**二、其他操作**

# 执行原生SQL
        #
        # from django.db import connection, connections
        # cursor = connection.cursor()  # cursor = connections['default'].cursor()
        # cursor.execute("""SELECT * from auth_user where id = %s""", [1])
        # row = cursor.fetchone()

**三、联表操作**  
**1.一对多**  
表结构如下：

class Book(models.Model):
        name = models.CharField(max_length=32)
    
    
    class Author(models.Model):
        name = models.CharField(max_length=32)
        books = models.ForeignKey(to="Book",to_field="id")

查询:

#方式一
        a1=models.Author.objects.filter(id=1).first()
        a1.books.name  #以属性的方式关联，Author类的对象通过books属性关联至Book对象，再通过.name属性获取Book对象的name字段
    
        #方式二
        res=models.Author.objects.filter(id=1).values("books_id","books__name")
        print(res)
        #在本地表通过ForeignKey关联，会在本地表内默认生成一个以指定属性名+'_'+'to_field字段名'的字段，例如这里会生成一个books_id字段，代指book.id字段，因此books_id不需跨表查询，books__id需跨表查询。通过双下划线，可以跨表关联字段，例如book__name字段代指book.name

改:

models.Author.objects.filter(id=1).update("books":[b1,b2..])

**总结：**  
一对多操作,单对象，通过”.”属性方法方式跨表操作，queryset通过”\_\_”跨表取字段

**2.多对多**  
**方式一：**  
自定义第三张表：

class Book(models.Model): name = models.CharField(max_length=32) class Author(models.Model): name = models.CharField(max_length=32) class Relation(models.Model): b = models.ForeignKey('Book') a= models.ForeignKey('Author')

通过普通表进行反向间接查找

a1=models.Author.objects.filter(id=1).first()
    obj = a1.relation_set.all()
    #obj取出的是所有包含a1记录的relation类中的对象，queryset格式,使用类名+"_set"的格式时，类名字母需小写
    obj1 = a1.relation_set.select_related('b').all()
    #obj1取出的是所有包含a1记录通过relation类关联的Book类的对象，queryset格式
    
    for i in obj:
        print(i.b.name)  
    for i in obj:
        print(i.name)

2 通过关系表relation进行查找

obj = models.Relation.objects.filter(a__=1).all()
    for i in obj:
        print(i.b.name)

通过中间关系表，相较于上面的反向查找更好理解。

**方式二**  
只定义两张表，关系表由django自动创建维护

#models.py
    class Book(models.Model):
        name = models.CharField(max_length=32)
    
    
    class Author(models.Model):
        name = models.CharField(max_length=32)
        m = models.ManyToManyField('Book')
    
    
    # views.py
    
        # ManyToManyField
    
        obj = models.Author.objects.filter(id=1).first()
    
    
        # 增
        # obj.m.add(3)
        # obj.m.add(3,4)
        # obj.m.add(*[1,2])
    
        # 删
        # obj.m.remove(3)
        # obj.m.remove(3,4)
        # obj.m.remove(*[1,2])
    
        # 改
        # obj.m.set([1,2,])
    
        #查
        obj.m.all()  #查出所有的关联Book对象
        obj.m.all().values("id","name")  #查出所有的关联Book对象的name字段
    
        # for row obj.m.all():
        # print(row.id,row.name)
    
        #清除
        # obj.m.clear()
    
    
        # 反向查 在没有定义ManyToManyField 字段的表中查
        b= models.Book.objects.filter(id=1).first()
        b_authors = obj.author_set.all()  #获取所有的对应的Author类的对象
    
        # 反向的改
        obj.author_set.set([1,2,3])
    
        for row in girl_list:
            print(row.id,row.name)

总结：在没有ForeignKey或者ManyToManyField的表内反向查找时，需要使用使用类名+”\_set”的格式来获取对象

## one to one/maney to maney/foreign key各种混合关系查询 ##

class HostInfo(models.Model):
        host = models.OneToOneField(to=Host, verbose_name="主机", primary_key=True)
        hostname = models.CharField(verbose_name="主机名", max_length=100, blank=True, null=True)
    
    class Host(models.Model):
        name = models.CharField(verbose_name="资产名", max_length=100, unique=True)
        ip = models.ManyToManyField(to=IP, verbose_name="IP", blank=True)
        category = models.ForeignKey(to=Category, verbose_name="分类")
    
    class Category(models.Model):
        name = models.CharField(max_length=20, verbose_name="分类")
    
    class IP(models.Model):
        address = models.CharField(verbose_name="IP地址", max_length=20)

# 根据ip地址查host，hostinfo
    ips = ("192.168.1.2","192.168.1.3",)
    host = Host.objects.filter(ip__address__in=ips)
    hostinfo = HostInfo.objects.filter(host__ip__address__in=ips)
    
    # 根据类型查host,hostinfo
    host = Host.objects.filter(category__name="physical")
    hostinfo = HostInfo.objects.filter(host__category__name="physical")
    
    # one to one单层关系正查反查：
    hostinfo = HostInfo.objects.get(hostname="test")
    host = hostinfo.host
    
    host = Host.objects.get(name="company-test1")
    hostinfo = host.hostinfo  #onetoone反查使用反查对象类名首字母小写可获取到对象