Django（六）、ORM操作进阶

发表于 | 阅读次数：
本文字数： 6.9k | 阅读时长 ≈ 6 分钟

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

一、双下划綫条件过滤：

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
# 获取个数
        #
        # 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)

二、其他操作

1
2
3
4
5
6
# 执行原生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.一对多
表结构如下：

1
2
3
4
5
6
7
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")

查询:

1
2
3
4
5
6
7
8
#方式一
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

改:

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

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

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

1
2
3
4
5
6
7
8
9
10
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自动创建维护

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
#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各种混合关系查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 根据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反查使用反查对象类名首字母小写可获取到对象
赏一瓶快乐回宅水吧～
-------------本文结束感谢您的阅读-------------