Django 数据库高级操作-过滤，反向查询，性能

Django 数据库高级操作-过滤，反向查询，性能

单表的基本操作 http://beanxyz.blog.51cto.com/5570417/1945887

常见字段的使用 http://beanxyz.blog.51cto.com/5570417/1945909

最基本的查询方式 http://beanxyz.blog.51cto.com/5570417/1950806

一对多的基本操作和实例  http://beanxyz.blog.51cto.com/5570417/1946602

多对多的基本操作和实例 http://beanxyz.blog.51cto.com/5570417/1952243

条件的过滤

下面是常见的条件设置，除了可以基本的filter之外，我们还有大量的条件语句可以使用。

查询数据库获取的QuerySet类型，对于这个类型我们类似Jquery一样使用链式编程，可以无限制的通过.来添加新的条件来过滤，可以看见大部分条件都是通过双下划线__来实现的

# 获取个数
# models.Tb1.objects.filter(name='seven').count()
    <span class="token comment"># 大于，小于</span>
    <span class="token comment"># models.Tb1.objects.filter(id__gt=1)              # 获取id大于1的值</span>
    <span class="token comment"># models.Tb1.objects.filter(id__gte=1)              # 获取id大于等于1的值</span>
    <span class="token comment"># models.Tb1.objects.filter(id__lt=10)             # 获取id小于10的值</span>
    <span class="token comment"># models.Tb1.objects.filter(id__lte=10)             # 获取id小于10的值</span>
    <span class="token comment"># models.Tb1.objects.filter(id__lt=10, id__gt=1)   # 获取id大于1 且 小于10的值</span>

    <span class="token comment"># in</span>
    <span class="token comment"># models.Tb1.objects.filter(id__in=[11, 22, 33])   # 获取id等于11、22、33的数据</span>
    <span class="token comment"># models.Tb1.objects.exclude(id__in=[11, 22, 33])  # not in</span>

    <span class="token comment"># isnull</span>
    <span class="token comment"># Entry.objects.filter(pub_date__isnull=True)</span>

    <span class="token comment"># contains</span>
    <span class="token comment"># models.Tb1.objects.filter(name__contains="ven")</span>
    <span class="token comment"># models.Tb1.objects.filter(name__icontains="ven") # icontains大小写不敏感</span>
    <span class="token comment"># models.Tb1.objects.exclude(name__icontains="ven")</span>

    <span class="token comment"># range</span>
    <span class="token comment"># models.Tb1.objects.filter(id__range=[1, 2])   # 范围bettwen and</span>

    <span class="token comment"># 其他类似</span>
    <span class="token comment"># startswith，istartswith, endswith, iendswith,</span>

    <span class="token comment"># order by</span>
    <span class="token comment"># models.Tb1.objects.filter(name='seven').order_by('id')    # asc</span>
    <span class="token comment"># models.Tb1.objects.filter(name='seven').order_by('-id')   # desc</span>

    <span class="token comment"># group by</span>
    <span class="token comment"># from django.db.models import Count, Min, Max, Sum</span>
    <span class="token comment"># models.Tb1.objects.filter(c1=1).values('id').annotate(c=Count('num'))</span>
    <span class="token comment"># SELECT "app01_tb1"."id", COUNT("app01_tb1"."num") AS "c" FROM "app01_tb1" WHERE "app01_tb1"."c1" = 1 GROUP BY "app01_tb1"."id"</span>

    <span class="token comment"># limit 、offset</span>
    <span class="token comment"># models.Tb1.objects.all()[10:20]</span>

    <span class="token comment"># regex正则匹配，iregex 不区分大小写</span>
    <span class="token comment"># Entry.objects.get(title__regex=r'^(An?|The) +')</span>
    <span class="token comment"># Entry.objects.get(title__iregex=r'^(an?|the) +')</span>

    <span class="token comment"># date</span>
    <span class="token comment"># Entry.objects.filter(pub_date__date=datetime.date(2005, 1, 1))</span>
    <span class="token comment"># Entry.objects.filter(pub_date__date__gt=datetime.date(2005, 1, 1))</span>

    <span class="token comment"># year</span>
    <span class="token comment"># Entry.objects.filter(pub_date__year=2005)</span>
    <span class="token comment"># Entry.objects.filter(pub_date__year__gte=2005)</span>

    <span class="token comment"># month</span>
    <span class="token comment"># Entry.objects.filter(pub_date__month=12)</span>
    <span class="token comment"># Entry.objects.filter(pub_date__month__gte=6)</span>

    <span class="token comment"># day</span>
    <span class="token comment"># Entry.objects.filter(pub_date__day=3)</span>
    <span class="token comment"># Entry.objects.filter(pub_date__day__gte=3)</span>

    <span class="token comment"># week_day</span>
    <span class="token comment"># Entry.objects.filter(pub_date__week_day=2)</span>
    <span class="token comment"># Entry.objects.filter(pub_date__week_day__gte=2)</span>

    <span class="token comment"># hour</span>
    <span class="token comment"># Event.objects.filter(timestamp__hour=23)</span>
    <span class="token comment"># Event.objects.filter(time__hour=5)</span>
    <span class="token comment"># Event.objects.filter(timestamp__hour__gte=12)</span>

    <span class="token comment"># minute</span>
    <span class="token comment"># Event.objects.filter(timestamp__minute=29)</span>
    <span class="token comment"># Event.objects.filter(time__minute=46)</span>
    <span class="token comment"># Event.objects.filter(timestamp__minute__gte=29)</span>

    <span class="token comment"># second</span>
    <span class="token comment"># Event.objects.filter(timestamp__second=31)</span>
    <span class="token comment"># Event.objects.filter(time__second=2)</span>
    <span class="token comment"># Event.objects.filter(timestamp__second__gte=31)</span></code></pre><div class="toolbar"></div></div>

上面这些方法可以实现大部分常见的简单查询过滤。有的时候，我们需要实现一些更复杂的查询语句，上面的语句就不够用了，这个时候可以通过extra来扩展。例如，主要看看select和where的自定义

# 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'])

反向查询

之前的博文里面，我们都是通过正向查找外键或者中间表来获取另外一个表的信息；如果希望倒过来，也是通过双下划线，比如 表名__字段 这种形式来实现

实例：

3张表，分别是单表，1对多和多对多的关系

#业务线
class Business(models.Model):
    # id
    caption = models.CharField(max_length=32)
#主机
class Host(models.Model):
    nid = models.AutoField(primary_key=True)
    hostname = models.CharField(max_length=32,db_index=True)
    ip = models.GenericIPAddressField(protocol="ipv4",db_index=True)
    port = models.IntegerField()
    b = models.ForeignKey(to="Business", to_field='id')
#程序
class Application(models.Model):
    name = models.CharField(max_length=32,unique=True)
    r = models.ManyToManyField("Host")

视图函数

def tt(request):
    #1对多正向查找
    print('1对多正向查找'.center(40, '-'))
    obj=models.Host.objects.filter(nid__gt=1)
    print(obj[0].nid,obj[0].hostname,obj[0].b.caption)
    #一些过滤条件
    print('过滤条件'.center(40,'-'))
    obj=models.Business.objects.filter(caption__contains='SALE').first()
    print(obj.id,obj.caption)
    obj=models.Business.objects.all().values('id','caption')
    print(obj, obj.order_by('id').reverse())
    obj=models.Application.objects.filter(name__exact='SQL Server').values('name','r__hostname','r__nid')
    print(obj)
    #1对多反向查找
    print('1对多反向查找'.center(40,'-'))
    obj=models.Business.objects.all().values('id','caption','host__ip','host__hostname')
    print(obj[0])
    #多对多正向查找
    print('多对多正向查找'.center(40, '-'))
    obj=models.Application.objects.all().first()
    print(obj.name, obj.r.all()[0].hostname)
    #多对多反向查询
    print('多对多反向查找'.center(40, '-'))
    obj=models.Host.objects.all().filter(nid__gt=1).values('nid','application__name').first()
    print(obj)
    return HttpResponse('ok')

执行结果

----------------1对多正向查找-----------------
183 SYDMGM01 SALE
------------------过滤条件------------------
5 SALE
<QuerySet [{'id': 5, 'caption': 'SALE'}, {'id': 19, 'caption': 'IT'}, {'id': 20, 'caption': 'HR'}]> <QuerySet [{'id': 20, 'caption': 'HR'}, {'id': 19, 'caption': 'IT'}, {'id': 5, 'caption': 'SALE'}]>
<QuerySet [{'name': 'SQL Server', 'r__hostname': 'SYDMGM01', 'r__nid': 183}, {'name': 'SQL Server', 'r__hostname': 'SYDAV01', 'r__nid': 190}, {'name': 'SQL Server', 'r__hostname': 'SYDMGM02', 'r__nid': 191}]>
----------------1对多反向查找-----------------
{'id': 5, 'caption': 'SALE', 'host__ip': '10.2.1.1', 'host__hostname': 'SYDMGM01'}
----------------多对多正向查找-----------------
AA SYDMGM01
----------------多对多反向查找-----------------
{'nid': 183, 'application__name': 'AA'}

性能

假设我们有一个Use表通过外键ut绑定了一个UserType表

默认情况下，如果我们直接使用下面代码，如果uses获取了10行数据，那么数据库实际上查询11次，对user查询一次，然后在for循环里面对usertype查询10次；这样效率很低

users = models.User.objects.all()
for row in users:
    print(row.user,row.pwd,row.ut_id)
    print(row.ut.name)

我们可以通过select_related进行优化，这样第一次查询的时候就进行一个跨表查询，获取指定外键的所有数据

users = models.User.objects.all().select_related('ut')
for row in users:
    print(row.user,row.pwd,row.ut_id)
    print(row.ut.name)

如果数据比较多，外键也多，那么速度可能还会比较慢，比较跨表查询的效率比较低，那么进一步的我们可以通过prefetch_related优化。他的基本原理是进行多次单表查询；比如第一次查询User表，然后第二次查询外键关联的表，然后把所有数据都放在内存里面，这样访问的速度就会快很多了。

users = models.User.objects.filter(id__gt=30).prefetch_related('ut','tu')
# select * from users where id > 30
# 获取上一步骤中所有的ut_id=[1,2]
# select * from user_type where id in [1,2]
# select * from user_type where id in [1,2]
for row in users:
    print(row.user,row.pwd,row.ut_id)
    print(row.ut.name)

参考资料：http://www.cnblogs.com/wupeiqi/articles/5246483.html

转载来源：https://blog.51cto.com/beanxyz/1967344