django[三] ORM操作进阶

 

ORM的操作

常用操作

__exact 精确等于 like 'aaa'  model.objects.filter(field__exact='aaa')
__iexact 精确等于 忽略大小写 ilike 'aaa'
__contains 包含 like '%aaa%'
__icontains 包含 忽略大小写 ilike '%aaa%',但是对于sqlite来说,contains的作用效果等同于icontains。
__gt 大于
__gte 大于等于
__lt 小于
__lte 小于等于
__in 存在于一个list范围内  # User.objects.filter(age__in=[10, 20, 30])
__startswith 以…开头
__istartswith 以…开头 忽略大小写
__endswith 以…结尾
__iendswith 以…结尾,忽略大小写
__range 在…范围内
__year 日期字段的年份
__month 日期字段的月份
__day 日期字段的日
__isnull=True/False #User.objects.filter(username__isnull=True) //查询用户名为空/不为空的用户
__isnull=True 与 __exact=None的区别

User.objects.filter().excute(age=10) // 查询年龄不为10的用户
User.objects.filter().excute(age__in=[10, 20]) // 查询年龄不为在 [10, 20] 的用户

  

http://www.cnblogs.com/wupeiqi/articles/6216618.html

 

进阶操作

# 获取个数
        #
        # 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)

 

raw sql操作

d=Publish.objects.raw("select * from app01_author;")
[ i.name for i in d ]
['张三', '李四', '小龙虾', '小白', 'niubi', 'taotao']

  通过raw sql操作,可以直接控制sql语句,

 

假设数据库有一个员工表,表中的年龄都自加“1”,这里就需要到orm的F功能,如下面的代码

from django.db.models import F#首先要导入这个F模models.Uinfo.objects.all().update(age=F("age")+1)#这里的F功能后面的age,它就会让数据表表中的age这列+1

  F 就是用来更新获取原来值的功能

 

Q

数据库的查询条件我们可以使用filter,在filter里面的可以是两个条件他们之间是and的关系,也可以是一个字典,例如下面的代码

    models.Uinfo.objects.all().filter(id=1,name='李伟')
    conditon={
        'id':'1',
        'name':'李伟'
    }
    models.Uinfo.objects.all().filter(**conditon)

  

除了上面的方法,我们还可以加Q的对象,例如

方式一:

from django.db.models import Q
models.Uinfo.objects.all().filter(Q(id=1))#条件是id为1的时候
models.Uinfo.objects.all().filter(Q(id=1)|Q(id__gt=3))#条件是或的关系,|
models.Uinfo.objects.all().filter(Q(id=1) & Q(id=4))# 条件是and的关系

  

方式二:

#q1 里面的条件都是or的关系
    q1=Q()
    q1.connector = 'OR'
    q1.children.append(('id',1))
    q1.children.append(('id',3))
    q1.children.append(('id',6))
#q2里面的条件都是or的关系
    q2=Q()
    q2.connector = 'OR'
    q2.children.append(('c',2))
    q2.children.append(('c',4))
    q2.children.append(('c',6))
#con 通过and的条件把q1和q2 联系到一块 
    con=Q()
    con.add(q1,'AND')
    con.add(q2,'AND')

models.Tb1.objects.filter(con)

  

extra  --  构造额外的查询条件或者映射

extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)

Entry.objects.extra(select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,))
Entry.objects.extra(where=['headline=%s'], params=['Lennon'])
Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"])
Entry.objects.extra(select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid'])

  

distinct -- 去重

>>> models.Coupon.objects.values('object_id').distinct()
<QuerySet [{'object_id': 1}, {'object_id': 2}]>

  

order_by -- 排序

# 根据 object_id 升序,越来越大,再根据id降序,越来越小
models.Coupon.objects.all().order_by('object_id','-id')

<QuerySet [<Coupon: yhq2>, <Coupon: yhq1>, <Coupon: nike_yhq>]>

object_id:  1 1 2
id:  3 1 2

  

reverse -- 倒序

# 对已排序结果,再做一次倒序
models.Coupon.objects.all().order_by('object_id').reverse()

  

defer -- 映射中排序某列数据

models.Coupon.objects.defer('brief')
# 排除 brief字段?? 好像不管用啊

  

in_bulk -- 根据主键批量查找

>>> models.Coupon.objects.in_bulk([1,2,3])
{1: <Coupon: yhq1>, 2: <Coupon: nike_yhq>, 3: <Coupon: yhq2>}

  

get_or_create -- 如果存在,则获取,否则创建

# username是主键,defaults 指定创建时,其他字段的值
obj, created = models.UserInfo.objects.get_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 2})

  

 

first -- 第一个

last -- 最后一个

delete -- 删除

update -- 更新

exists -- 是否有结果

 

annotate -- 用于实现聚合group by查询

class Coupon(models.Model):
    """
    id      food_id     cloth_id  ……
        null        null
          1         null
    """
    name = models.CharField("活动名称",max_length=64)
    brief = models.TextField(blank=True,null=True,verbose_name="优惠券介绍")
    brand = models.ForeignKey("brand",blank=True,null=True,on_delete=models.CASCADE)
    content_type = models.ForeignKey(ContentType,blank=True,null=True, on_delete=models.CASCADE) # 代表哪个app下的哪张表(默认存储关系数据的表)
    object_id = models.PositiveIntegerField("绑定商品",blank=True,null=True) # 代表哪张表中的对象id
    content_obj = GenericForeignKey("content_type","object_id") #不会生成额外的列
    def __str__(self):
        return self.name

  

 

 

from django.db.models import Count, Avg, Max, Min, Sum
# 根据object_id group by
models.Coupon.objects.values('object_id').annotate(object_id_cnt=Count("object_id"))

SELECT "modelsPractice_coupon"."object_id", COUNT("modelsPractice_coupon"."object_id") AS "object_id_cnt" FROM "modelsPractice_coupon" GROUP BY "modelsPractice_coupon"."object_id"  LIMIT 21; args=()

结果:

<QuerySet [{'object_id': 1, 'object_id_cnt': 2}, {'object_id': 2, 'object_id_cnt': 1}]>

  

# 根据结果集 having
models.Coupon.objects.values('object_id').annotate(object_id_cnt=Count("object_id")).filter(object_id_cnt__gt=1)


结果:
<QuerySet [{'object_id': 1, 'object_id_cnt': 2}]>


# group by后做distinct,最后having(看起来没啥意义)
models.Coupon.objects.values('object_id').annotate(object_id_cnt=Count("object_id",distinct=True)).filter(object_id_cnt__g
te=1)

结果:
<QuerySet [{'object_id': 1, 'object_id_cnt': 1}, {'object_id': 2, 'object_id_cnt': 1}]>

   

 

 

ORM查询优化

表结构

from django.db import models


class Province(models.Model):
    name = models.CharField(max_length=10)

    def __unicode__(self):
        return self.name

    def __str__(self):
        return self.name

class City(models.Model):
    name = models.CharField(max_length=5)
    province = models.ForeignKey(Province,null=True, blank=True, on_delete=models.CASCADE)

    def __unicode__(self):
        return self.name


class Order(models.Model):
    customer   = models.ForeignKey("Person", on_delete=models.CASCADE)
    orderinfo  = models.CharField(max_length=50)
    time       = models.DateTimeField(auto_now_add = True)
    def __unicode__(self):
        return self.orderinfo

class Person(models.Model):
    firstname = models.CharField(max_length=10)
    lastname = models.CharField(max_length=10)
    needs = models.ForeignKey(to=Order, related_name='require',null=True, blank=True,on_delete=models.DO_NOTHING)
    visitation = models.ManyToManyField(City, related_name="visitor", null=True, blank=True)
    hometown = models.ForeignKey(City, related_name="birth", on_delete=models.CASCADE)
    living = models.ForeignKey(City, related_name="citizen", on_delete=models.CASCADE)

    def __unicode__(self):
        return self.firstname + self.lastname

  

select_related的使用

常用

model.tb.objects.all().select_related('外键字段')
model.tb.objects.all().select_related('外键字段__外键字段')

  

概念

对于一对一字段(OneToOneField)和外键字段(ForeignKey),可以使用select_related 来对QuerySet进行优化

在对QuerySet使用select_related()函数后,Django会获取相应外键对应的对象,从而在之后需要的时候不必再查询数据库了

citys = City.objects.all()
for c in citys:
    print(c.province)
        
    这样会导致线性的SQL查询,如果对象数量n太多,每个对象中有k个外键字段的话,就会导致n*k+1次SQL查询。在本例中,因为有3个city对象就导致了4次SQL查询
        (0.000) SELECT "select_related_city"."id", "select_related_city"."name", "select_related_city"."province_id" FROM "select_related_city"; args=()
        (0.000) SELECT "select_related_province"."id", "select_related_province"."name" FROM "select_related_province" WHERE "select_related_province"."id" = 1; args=(1,)
        (0.000) SELECT "select_related_province"."id", "select_related_province"."name" FROM "select_related_province" WHERE "select_related_province"."id" = 1; args=(1,)
        (0.000) SELECT "select_related_province"."id", "select_related_province"."name" FROM "select_related_province" WHERE "select_related_province"."id" = 1; args=(1,)
        浙江
        浙江
        浙江

  

如果我们使用select_related()函数:

citys = City.objects.select_related().all()
for c in citys:
    print(c.province)
    就只有一次SQL查询,显然大大减少了SQL查询的次数
    (0.001) SELECT "select_related_city"."id", "select_related_city"."name", "select_related_city"."province_id", "select_related_province"."id", "select_related_province"."name" FROM "select_related_city" INNER JOIN "select_related_province" ON ("select_related_city"."province_id" = "select_related_province"."id"); args=()
    浙江
    浙江
    浙江  

看到他做了了inner join,把foreign key的表连接过来

 

这样查询 living做关联的时候就不用再查询一次了
可以看到,Django使用了2次 INNER JOIN
来完成请求,获得了city表和province表的内容并添加到结果表的相应列,这样在调用
zgt.living的时候也不必再次进行SQL查询。
SELECT "select_related_person"."id", "select_related_person"."firstname", "select_related_person"."lastname", "select_related_person"."hometown_id", "select_related_person"."living_id", "select_related_city"."id", "select_related_city"."name", "select_related_city"."province_id", "select_related_province"."id", "select_related_province"."name" FROM "select_related_person" INNER JOIN "selec>

 

select_related() 支持三种方法:

1 指定 *fields 参数

这个参数是需要获取的外键(父表内容)的字段名,用来关联外键的字段名,比如:

   

   - 有外键的外键。 比如这里的  

>>> zhu = Person.objects.select_related('living__province').get(firstname="zhu")

(0.000) SELECT "select_related_person"."id", "select_related_person"."firstname", "select_related_person"."lastname", "select_related_person"."sex_id", "select_related_pers

>>> zhu = Person.objects.select_related('living__province').get(firstname="zhu")
>>> zhu.living.province  // 没有产生其他sql
<Province: 浙江>

  

  然而,未指定的外键则不会被添加到结果中,就会产生一条查询sql

>>> zhu.hometown.province
(0.000) SELECT "select_related_province"."id", "select_related_province"."name" FROM "select_related_<Province: 浙江>

  

   - 有多个外键时需要指定到哪个外键做关联(默认全部都关联)

>>> zhu = Person.objects.select_related('living__province').select_related('hometown__province

(0.000) SELECT "select_related_person"."id", "select_related_person"."firstname", "select_related_person"."lastname", "select_related_person"."sex_id", "select_related_person"."hometown_id", "select_related_person"."living_id", "select_related_city"."id", "select_related_city"."name", "select_related_city"."province_id", "select_related_province"."id", "select_related_province"."name", T4."id", T4."name", T4."province_id", T5."id", T5."name" FROM "select_related_person" INNER JOIN "select_related_city" ON ("select_related_person"."hometown_id" = "select_related_city"."id") INNER JOIN "select_related_province" ON ("select_related_city"."province_id" = "se

>>> zhu.hometown.province
<Province: 浙江>

  

 

2 指定 depth 参数 (已废弃了)

select_related() 接受depth参数,depth参数可以确定select_related的深度。Django会递归遍历指定深度内的所有的OneToOneField和ForeignKey

 

3 不指定参数

select_related() 也可以不加参数,这样表示要求Django尽可能深的select_related

  1. Django本身内置一个上限,对于特别复杂的表关系,Django可能在你不知道的某处跳出递归,从而与你想的做法不一样。具体限制是怎么工作的我表示不清楚。
  2. Django并不知道你实际要用的字段有哪些,所以会把所有的字段都抓进来,从而会造成不必要的浪费而影响性能。

小结

1 select_related主要针一对一和多对一关系进行优化。
2 select_related使用SQL的JOIN语句进行优化,通过减少SQL查询的次数来进行优化、提高性能。
3 可以通过可变长参数指定需要select_related的字段名。也可以通过使用双下划线“__”连接字段名来实现指定的递归查询。没有指定的字段不会缓存,没有指定的深度不会缓存,如果要访问的话Django会再次进行SQL查询。
4 也接受无参数的调用,Django会尽可能深的递归查询所有的字段。但注意有Django递归的限制和性能的浪

 

prefetch_related的应用

对于多对多字段(ManyToManyField)和一对多字段,可以使用prefetch_related()来进行优化

作用和方法

prefetch_related()和select_related()的设计目的很相似,都是为了减少SQL查询的数量,但是实现的方式不一样。后者是通过JOIN语句,在SQL查询内解决问题。

但是对于多对多关系,使用SQL语句解决就显得有些不太明智,因为JOIN得到的表将会很长,会导致SQL语句运行时间的增加和内存占用的增加。若有n个对象,每个对象的多对多字段对应Mi条,就会生成Σ(n)Mi 行的结果表

 

zhu = Person.objects.prefetch_related().filter(firstname='zhu').first()


(0.000) SELECT "select_related_person"."id", "select_related_person"."firstname", "select_related_person"."lastname", "select_related_person"."sex_id", "select_related_person"."hometown_id", "select_related_person"."living_id" FROM "select_related_person" WHERE "select_related_person"."firstname" = 'zhu' ORDER BY "select_related_person"."id" ASC  LIMIT 1; args=('zhu',)


# 关联外键内容
[ i.name for i in zhu.visitation.all() ]

(0.000) SELECT "select_related_city"."id", "select_related_city"."name", "select_related_city"."province_id" FROM ['嘉兴', '温州']

 

获取所有用户
plist = Person.objects.prefetch_related("visitation")


filter出去过嘉兴的数据
[ p.visitation.filter(name=u"嘉兴") for p in plist ]
(0.000) SELECT "select_related_city"."id", "select_related_city"."name", "select_related_city"."province_id" FROM "select_related_city" INNER JOIN "select_related_person_visitation" ON ("select_related_city"."id" = "select_related_person_visitation"."city_id") WHERE ("select_related_person_visitation"."person_id" = 1 AND "select_related_city"."name" = '嘉兴') LIMIT 21; args=(1, '嘉兴')
(0.000) SELECT "select_related_city"."id", "select_related_city"."name", "select_related_city"."province_id" FROM "select_related_city" INNER JOIN "select_related_person_visitation" ON ("select_related_city"."id" = "select_related_person_visitation"."city_id") WHERE ("select_related_person_visitation"."person_id" = 2 AND "select_related_city"."name" = '嘉兴') LIMIT 21; args=, '嘉兴')
(0.000) SELECT "select_related_city"."id", "select_related_city"."name", "select_related_city"."province_id" FROM "sel 

 

注意 QuerySet是lazy的,要用的时候才会去访问数据库。运行到第二行Python代码时,for循环将plist看做iterator,这会触发数据库查询。最初的两次SQL查询就是prefetch_related导致的。

虽然已经查询结果中包含所有所需的city的信息,但因为在循环体中对Person.visitation进行了filter操作,这显然改变了数据库请求。因此这些操作会忽略掉之前缓存到的数据,重新进行SQL查询。

常用使用方法

# 获取所有用户表
# 获取用户类型表where id in (用户表中的查到的所有用户ID)
models.UserInfo.objects.prefetch_related('外键字段')

  

select_related和prefetch_related实际应用

获得所有家乡是浙江的人

方法1:

最无脑的做法是先获得浙江省,再获得浙江的所有城市,最后获得故乡是这个城市的人。就像这样:

zj = Province.objects.get(name="浙江")
people = []

for city in zj.city_set.all():
  people.append(city.birth.all())

  显然这不是一个明智的选择,因为这样做会导致1+(浙江省城市数)次SQL查询。反正是个反例,导致的查询和获得掉结果就不列出来了。

 

方法2:

利用prefetch_related先查到省和市

zj=Province.objects.prefetch_related('city_set__birth').filter(name='浙江').first()

产生两条sql
(0.000) SELECT "select_related_province"."id", "select_related_province"."name" FROM "select_related_province" WHERE "select_related_province"."name" = '浙江' ORDER BYselect_related_province"."id" ASC  LIMIT 1; args=('浙江',)
(0.000) SELECT "select_related_city"."id", "select_related_city"."name", "select_related_city"."province_id" FROM "select_related_city" WHERE "select_related_city"."province_id" IN (1); args=(1,)

people =[]
for city in zj.city_set.all()
   people.extend(city.birth.all())

这个过程不会产生sql,说明prefetch_related已经把数据缓存下来了

因为是一个深度为2的prefetch,所以会导致2次SQL查询,有么有更好的办法呢

 

方法3:

利用select_related 直接获取外键缓存

p = list(Person.objects.select_related("hometown__province").filter(hometown__province__name="浙江"))

(0.000) SELECT "select_related_person"."id", "select_related_person"."firstname", "select_related_person"."lastname", "select_related_person"."needs_id", "select_related_person"."hometown_id", "select_related_person"."living_id", "select_related_city"."id", "select_related_city"."name", "select_related_city"."province_id", "select_related_province"."id", "select_related_province"."name" FROM "select_related_person" INNER JOIN "select_related_city" ON ("select_related_person"."hometown_id" = "select_related_city"."id") INNER JOIN "select_related_province" ON ("select_related_city"."province_id" = "select_related_province"."id") WHERE "select_related_province"."name" = '浙江'; args=('浙江',)

只会产生一句sql

  

select_related()的效率要高于prefetch_related()。因此,最好在能用select_related()的地方尽量使用它,也就是说,对于ForeignKey字段,避免使用prefetch_related()。

 

两者的联用

在我们一直使用的例子上加一个model:Order (订单)

如果我们拿到了一个订单的id 我们要知道这个订单的客户去过的省份。因为有ManyToManyField显然必须要用prefetch_related()。

方法1: 如果只用prefetch_related()会怎样呢?

把客户 城市 省份关联起来,后面可以filter 订单id
olist=Order.objects.prefetch_related("customer__visitation__province").all().first()

可以得到一个order列表: <QuerySet [<Order: Order object (1)>, <Order: Order object (2)>, <Order: Order object (3)>]>


关系到了4个表:Order、Person、City、Province,根据prefetch_related()的特性就得有4次SQL查询


(0.000) SELECT "select_related_order"."id", "select_related_order"."customer_id", "select_related_order"."orderinfo", "select_related_order"."time" FROM "select_related_order"  LIMIT 21; args=()
(0.000) SELECT "select_related_person"."id", "select_related_person"."firstname", "select_related_person"."lastname", "select_related_person"."hometown_id", "select_related_person"."living_id" FROM "select_related_person" WHERE "select_related_person"."id" IN (1, 2); args=(1, 2)
(0.000) SELECT ("select_related_person_visitation"."person_id") AS "_prefetch_related_val_person_id", "select_related_city"."id", "select_related_city"."name", "select_related_city"."province_id" FROM "select_related_city" INNER JOIN "select_related_person_visitation" ON ("select_related_city"."id" = "select_related_person_visitation"."city_id") WHERE "select_related_person_visitation"."person_id" IN (1, 2); args=(1, 2)
(0.000) SELECT "select_related_province"."id", "select_related_province"."name" FROM "select_related_province" WHERE "select_related_province"."id" IN (1); args=(1,)

 

>>> [ i.province.name for i in o.customer.visitation.all() ]
['浙江', '浙江']

  

 

方法2:

更好的办法是先调用一次select_related()再调用prefetch_related(),最后再select_related()后面的表

olist = Order.objects.select_related("customer").prefetch_related("customer__visitation__province").first()

(0.000) SELECT "select_related_order"."id", "select_related_order"."customer_id", "select_related_order"."orderinfo", "select_related_order"."time", "select_related_person"."id", "select_related_person"."firstname", "select_related_person"."lastname", "select_related_person"."hometown_id", "select_related_person"."living_id" FROM "select_related_order" INNER JOIN "select_related_person" ON ("select_related_order"."customer_id" = "select_related_person"."id") ORDER BY "select_related_order"."id" ASC  LIMIT 1; args=()
(0.000) SELECT ("select_related_person_visitation"."person_id") AS "_prefetch_related_val_person_id", "select_related_city"."id", "select_related_city"."name", "select_related_city"."province_id" FROM "select_related_city" INNER JOIN "select_related_person_visitation" ON ("select_related_city"."id" = "select_related_person_visitation"."city_id") WHERE "select_related_person_visitation"."person_id" IN (1); args=(1,)


获取一个order列表
<Order: Order object (1)>

  

这样只会有2次SQL查询,Django会先做select_related,之后prefetch_related的时候会利用之前缓存的数据,从而避免了1次额外的SQL查询:

>>> [ c for c in olist.customer.visitation.all() ]
[<City: City object (1)>, <City: City object (2)>]
>>> [ c.province.name for c in olist.customer.visitation.all() ]
['浙江', '浙江']
>>> set( c.province.name for c in olist.customer.visitation.all() )
{'浙江'}

  

值得注意的是,可以在调用prefetch_related之前调用select_related,并且Django会按照你想的去做:先select_related,然后利用缓存到的数据prefetch_related。然而一旦prefetch_related已经调用,select_related将不起作用。

 

小结

  • 因为select_related()总是在单次SQL查询中解决问题,而prefetch_related()会对每个相关表进行SQL查询,因此select_related()的效率通常比后者高。
  • 鉴于第一条,尽可能的用select_related()解决问题。只有在select_related()不能解决问题的时候再去想prefetch_related()。
  • 你可以在一个QuerySet中同时使用select_related()和prefetch_related(),从而减少SQL查询的次数。

 

posted @ 2019-05-23 12:14  richardzgt  阅读(515)  评论(0编辑  收藏  举报