Django---orm多表操作

0. 对于ForeignKey的使用

0.1 概念

有ForeignKey的表成为子表，对应没有ForeignKey的表称为主表

0.2 方法

1. 直接使用values或values_list得到数据

filter和values以及values_list中字段的使用按照以下规则

主表中使用自己的字段直接使用，如果使用子表字段按照子表小写表名__字段名称

子表中使用自己的字段直接使用，如果使用主表字段按照外键名__主表字段名称的格式使用主表字段。

1. 先获取对象再通过对象获取数据

filter中字段名的使用与上一条所述相同，区别在于使用first()或all()得到对象后通过对象得到数据的方法

主表对象查询子表

如果主表得到的对象为f，子表表名为Son，那么获取子表数据的方法有一下几种

• f.son_set.all()
• f.son_set.first()
• f.son_set.values()
• f.son_set.values_list()

子表对象查询主表

如果子表对象为s，子表中对应主表的外键为fk_father，那么可以使用s.fk_father得到主表对象，通过主表对象可以得到主表中的各个数据。

1. 一对一

一对一有两种实现方式分别是ForeignKey+unique=True以及OneToOneField

使用ForeignKey的方式与上述ForeignKey的使用方一样，使用OneToOneField有别于ForeignKey的地方在于，通过主表对象查询子表时不需要使用_set，可以直接使用子表名获取子表对象

举例说明w.blog_set.first().site和w.blog.site得到的结果是一样的，前者使用ForeignKey后者使用OneToOneField。

2. 一对多

通过外键建立一对多的关系

class UserGroup(models.Model):    title = models.CharField(max_length=32)class UserInfo(models.Model):    user = models.CharField(max_length=32)    password = models.CharField(max_length=32)    age = models.IntegerField(default=0)    fk_ug = models.ForeignKey('UserGroup', null=True)

得到的表如下所示：

# UserGroupid|title1 |r&d2 |sales3 |financial# UserInfoid|user|age|fk_ug_id|password|1 |Jack|18 |1       |1232 |Mike|28 |2       |1233 |Dick|34 |3       |1234 |Kate|23 |1       |1235 |Zake|25 |1       |123

2.1 子表查询主表

• 先获取子表对象再查询数据

filter中如果使用子表字段可以直接使用，如果使用主表字段获取子表对象，需要使用外键名__主表字段的格式来获取子表对象

查询子表数据通过子表对象调用子表中的外键然后查询主表数据，查询格式子表对象.外键字段名.主表字段名

v = models.UserInfo.objects.filter(id=1).first()    print(v.user, v.password, v.age, v.fk_ug.title)

• 直接使用values跨表查询数据

在values中直接使用外键名实现跨表查询数据，查询格式外键字段名__主表字段名

ret = models.UserInfo.objects.all().values('id', 'user', 'fk_ug__title')    for item in ret:        print(item['id'], item['user'], item['fk_ug__title'])"""1 Jack r&d2 Mike sales3 Dick financial4 Kate r&d5 Zake r&d"""

上面两种方法都可以实现跨表查询，但是第一种每查询一次都要操作一次数据库，二第二种只操作一次数据库，拿到所有数据。

2.2 主表查询子表

• 先获取主表对象再查询数据

filter中如果使用主表字段直接使用，如果使用子表字段获取主表对象需要使用子表表名__子表字段的格式

通过主表对象查询子表数据，由于主表中没有外键需要使用_set查询子表

obj = models.UserGroup.objects.filter(id=1).first()    print(obj.userinfo_set) # singleTable.UserInfo.None    print(obj.userinfo_set.all()) # <QuerySet [<UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>]>    for row in obj.userinfo_set.all():        print(row.user, row.age)    """    Jack 18    Kate 23    Zake 25    """

• 直接使用values跨表查询数据，由于主表中没有外键，使用子表表名跨表查询，查询格式为子表表名__子表字段名

3. 多对多

3.1 基于关系表的多对多

两张表通过第三章关系表建立多对多的关系

class Boy(models.Model):    name = models.CharField(max_length=32)class Girl(models.Model):    nick = models.CharField(max_length=32)class Love(models.Model):    b = models.ForeignKey('Boy')    g = models.ForeignKey('Girl')

查询与name='Jack'的男生有关系的女生，Boy中没有外键，所以使用_set反向查找

obj = models.Boy.objects.filter(name='Jack').first()love_girls = obj.love_set.all()for row in love_girls:    print(row.g.nick) # 打印女生的名字

还可以直接使用关系表查询，注意b__name这种通过外键加双下滑线的使用方法

love_grils = models.Love.objects.filter(b__name='Jack') # 得到Love对象，下面没循环一次跨表一次for row in love_girls:    print(row.g.nick)

love_grils = models.Love.objects.filter(b__name='Jack').values('g__nick') # 得到字典，一次查询得到所有数据for item in love_girls:    print(item['g__nick']) # 打印女生的名字

love_grils = models.Love.objects.filter(b__name='Jack').select_related('g') # 得到对象，一次查询得到所有数据for obj in love_girls:    print(obj.g.nick) # 打印女生的名字

3.1.1联合唯一索引

不能出现多条相同的数据，使用联合唯一索引。通过在Meta类中设置unique_together实现，实现方法如下所示。

class Boy(models.Model):    name = models.CharField(max_length=32)class Girl(models.Model):    nick = models.CharField(max_length=32)class Love(models.Model):    b = models.ForeignKey('Boy')    g = models.ForeignKey('Girl')    class Meta:        unique_together = [            ('b','g'),        ]

3.2 基于ManyToManyField的多对多

Django提供了一个叫做ManyToManyField的关键字，实现两张表之间的多对多的关联关系，而不用定义第三张关系表，Django会自动生成关系表。在任意一张表中设置这个关键字都可以，具体使用方法如下所示。

class Boy(models.Model):    name = models.CharField(max_length=32)    m = models.ManyToManyField('Girl')class Girl(models.Model):    nick = models.CharField(max_length=32)

由于models中并没有定义关系表，所以无法直接操作关系表添加数据，因而需要通过定义了ManyToManyField的类的对象操作关系表，操作方法如下所示 ：

obj = models.Boy.objects.filter(name='Jack').first()# 添加obj.m.add(2)obj.m.add(3, 4)obj.m.add(*[1,5])# 删除obj.m.remove(2)obj.m.remove(3, 4)obj.m.remove(*[1,5])# 重置obj.m.set(*[1,]) # 只会剩下一条数据

该示例中使用了三种方法为关系表添加、删除、重置数据，表明添加数据可以接收三种参数。

3.2.1 数据查询

查询与有ManyToManyField的类的对象关联的数据成为正向查找， 反之成为反向查找

正向查找

obj = models.Boy.objects.filter(name='Jack').first()girl_list = obj.m.all()

通过obj.m.clear()可以删除关系表中与name='Jack'相关的所有数据

反向查找

使用_set实现反向查找

obj = models.Boy.objects.filter(name='Alice').first()boy_list = obj.boy_set.all()

使用ManyToManyField只能生成三列，两张表的id和自己的id

3.3 自定义关系表与ManyToManyField联合使用

class Boy(models.Model):    name = models.CharField(max_length=32)    m = models.ManyToManyField('Girl',through="Love",through_fields=('b','g',))class Girl(models.Model):    nick = models.CharField(max_length=32)class Love(models.Model):    b = models.ForeignKey('Boy')    g = models.ForeignKey('Girl')    class Meta:        unique_together = [            ('b','g'),        ]

相比于单独使用自定义关系表，多了查询和清空数据的功能。

相比于单独使用ManyToManyField，增加了灵活性，可以在关系表中定义更多的字段。

3.4 自关联

4. orm其它操作

4.1 排序

order_by('-id', 'name')按照id排序，如果有相同的id则按照name排序，id前的符号表名逆序拍续集。

4.2分组

首先引入模块from django.db.models import Count, Sum, Max, Min

user_list = models.UserInfo.objects.values('fk_ug_id').annotate(cnt=Count('id'))    print(user_list.query)    # SELECT "singleTable_userinfo"."fk_ug_id",    # COUNT("singleTable_userinfo"."id") AS "cnt"    # FROM "singleTable_userinfo"    # GROUP BY "singleTable_userinfo"."fk_ug_id"    user_list = models.UserInfo.objects.values('fk_ug_id').annotate(cnt=Count('id')).filter(cnt__gt=2)    print(user_list.query)    # SELECT "singleTable_userinfo"."fk_ug_id",     # COUNT("singleTable_userinfo"."id") AS "cnt"    # FROM "singleTable_userinfo"    # GROUP BY "singleTable_userinfo"."fk_ug_id"    # HAVING COUNT("singleTable_userinfo"."id") > 2

4.3 过滤

models.UserInfo.objects.filter(id__gt=1) # 大于models.UserInfo.objects.filter(id__gte=1) # 大于等于models.UserInfo.objects.filter(id__lt=1) # 小于models.UserInfo.objects.filter(id__lte=1) # 小于等于models.UserInfo.objects.filter(id__in=[1, 3, 4]) # 是列表中的某一个models.UserInfo.objects.filter(id__range=[1, 3]) # 在一个范围内models.UserInfo.objects.filter(name__startswith='xxx') # 区分大小写， 以‘xxx’开头models.UserInfo.objects.filter(name__istartswith='xxx') # 不区分大小写， 以‘xxx’开头models.UserInfo.objects.filter(name__endswith='xxx') # 区分大小写， 以‘xxx’结尾models.UserInfo.objects.filter(name__iendswith='xxx') # 不区分大小写， 以‘xxx’结尾models.UserInfo.objects.filter(name__contains='xxx') # 区分大小写， 包含‘xxx’models.UserInfo.objects.filter(name__icontains='xxx') # 不区分大小写， 包含‘xxx’models.UserInfo.objects.exclude(name__contains='xxx') # 不包含‘xxx’

4.4 F, Q, extra

4.4.1 F

from django.db.models import Fmodels.UserInfo.objects.all().update(age=F("age") + 1)# 通过F取得F中参数在数据库中对应的字段所对应的值

4.4.2 Q

用于构造复杂的查询条件

from django.db.models import Qmodels.UserInfo.objects.filter(Q(nid=8) | Q(nid__gt=10))# 取 nid = 8 或者 大于等于10

q1 = Q()q1.connector = 'OR'q1.children.append('id', 1)q1.children.append('id', 10)q1.children.append('id', 9)q2 = Q()q2.connector = 'OR'q2.children.append('c1', 1)q2.children.append('c1', 10)q2.children.append('c1', 9)con = Q()con.add(q1, 'AND')con.add(q2, 'AND')# 相当于 （id = 1 or id = 10 or id = 9）and (c1 = 1 or c2 = 10 or c3 = 9)

Q使用实例

from django.db.models import Qcondition_dict = {    'k1':[1, 2, 3, 4],    'k2':[1,],}con = Q()for k, v in condition_dict.items():    q = Q()    q.connector = 'OR'    for i in v:        q.children.append('id', i)    con.add(q, 'AND')models.UserInfo.objects.filter(con)

4.4.3 extra

通过extra实现子查询
v = models.UserInfo.objects.all().extra(select={"n": "select count(1) from app01_usertype where id>%s"}, select_params=[1,])

通过select_params给%s传参数，如果有多个参数，在列表中依次添加，按顺序调用

models.UserInof.objects.extra(    where=["id=1", "name=Jack"],)

models.UserInfo.objects.extra(                    select={'newid':'select count(1) from app01_usertype where id>%s'},                    select_params=[1,],                    where = ['age>%s'],                    params=[18,],                    order_by=['-age'],                    tables=['app01_usertype']                )                """                select                     app01_userinfo.id,                    (select count(1) from app01_usertype where id>1) as newid                from app01_userinfo,app01_usertype                where                     app01_userinfo.age > 18                order by                     app01_userinfo.age desc                """

4.4.4 raw sql

from django.db import connection, connectionscursor = connection.cursor() # connection=default数据cursor = connections['db2'].cursor()cursor.execute("""SELECT * from auth_user where id = %s""", [1])row = cursor.fetchone()row = cursor.fetchall()

4.4.5 连表查询

使用select_related连表查询

q = models.UserInfo.objects.all().select_related('fk_ug')    print(q.query)    # SELECT "singleTable_userinfo"."id",    # "singleTable_userinfo"."user",    # "singleTable_userinfo"."password",    # "singleTable_userinfo"."age",    # "singleTable_userinfo"."fk_ug_id",    # "singleTable_usergroup"."id",     # "singleTable_usergroup"."title"    # FROM "singleTable_userinfo"    # LEFT OUTER JOIN "singleTable_usergroup"    # ON ("singleTable_userinfo"."fk_ug_id" = "singleTable_usergroup"."id")

4.4.6 多次查询

prefetch_related

q = models.UserInfo.objects.all().prefetch_related('fk_ug')for row in q:    print(row.id, row.fk_ug.title)    # 1 r & d    # 2 sales    # 3 financial    # 4 r & d    # 5 r & d    # 6 sales

可以看出上述语句实现了跨表，但并咩有连表查询，而是使用多次查询，

首先，select * from UserInfo并统计fk_ug的种类

然后通过select * from usertype where id in [2, 4]得到usertype，然后得到最终的数据。

具体实现更复杂，这里只是阐述基本的实现原理。

通过这种方式，在数据量比较大的情况下，相比于连表提高了查询效率。