Django：Paginator +原始SQL查询 [英] Django: Paginator + raw SQL query

问题描述

我在我的网站上使用Django Paginator，甚至写了一个特殊的模板标签，使其更加方便。但是现在我已经到了一个状态，我需要做一个复杂的自定义原始SQL查询，没有 LIMIT 将返回约100K条记录。

如何使用Django Pagintor与自定义查询？

我的问题的简化示例：

我的模型：

  class PersonManager（models.Manager）：
 
 def complex_list（self）：
 
 from django.db import connection 
 
 #Real查询复杂得多
 cursor.execute（ SELECT * FROM`myapp_person`）; 
 
 result_list = [] 
 
在cursor.fetchall（）中的行：
 result_list.append（row [0]）; 
 
 return result_list 
 
 
 class Person（models.Model）：
 name = models.CharField（max_length = 255）; 
 surname = models.CharField（max_length = 255）; 
 age = models.IntegerField（）; 
 
 objects = PersonManager（）; 
  

我与Django ORM使用pagintation的方式：

  all_objects = Person.objects.all（）; 
 
 paginator = Paginator（all_objects，10）; 
 
 try：
 page = int（request.GET.get（'page'，'1'））
除了ValueError：
 page = 1 
 
 try：
 persons = paginator.page（page）
 except（EmptyPage，InvalidPage）：
 persons = paginator.page（paginator.num_pages）
  

这样，Django变得非常聪明，并向查询添加了 LIMIT 执行时。但是当我使用自定义管理器时：

  all_objects = Person.objects.complicated_list（）; 
  

所有数据都被选中，只有python列表被切片，这是非常慢的。

解决方案

看看Paginator的源代码， page（）函数认为这只是在您身边实施切片的唯一问题，并将其翻译成到SQL查询中相关的LIMIT子句。您可能还需要添加一些缓存，但是这样就开始看起来像QuerySet，所以也许你可以做别的事情：

• 你可以创建数据库VIEW使用CREATE VIEW myview AS [您的查询];


• 为该VIEW添加Django模型，使用 Meta：managed = False


• 像任何其他模型一样使用该模型，包括切片其查询器 - 这意味着它非常适合与Paginator一起使用

（为了您的信息 - 我一直在使用这种方法很长一段时间，即使复杂的多对多关系与VIEW伪造m2m中间表。）

I'm using Django Paginator everywhere on my website and even wrote a special template tag, to make it more convenient. But now I got to a state, where I need to make a complex custom raw SQL query, that without a LIMIT will return about 100K records.

How can I use Django Pagintor with custom query?

Simplified example of my problem:

My model:

class PersonManager(models.Manager):

    def complicated_list(self):

        from django.db import connection

        #Real query is much more complex        
        cursor.execute("""SELECT * FROM `myapp_person`""");  

        result_list = []

        for row in cursor.fetchall():
            result_list.append(row[0]); 

        return result_list


class Person(models.Model):
    name      = models.CharField(max_length=255);
    surname   = models.CharField(max_length=255);     
    age       = models.IntegerField(); 

    objects   = PersonManager();

The way I use pagintation with Django ORM:

all_objects = Person.objects.all();

paginator = Paginator(all_objects, 10);

try:
    page = int(request.GET.get('page', '1'))
except ValueError:
    page = 1

try:
    persons = paginator.page(page)
except (EmptyPage, InvalidPage):
    persons = paginator.page(paginator.num_pages)

This way, Django get very smart, and adds LIMIT to a query when executing it. But when I use custom manager:

all_objects = Person.objects.complicated_list();

all data is selected, and only then python list is sliced, which is VERY slow. How can I make my custom manager behave similar like built in one?

解决方案

Looking at Paginator's source code, page() function in particular, I think that it's only matter of implementing slicing on your side, and translating that to relevant LIMIT clause in SQL query. You might also need to add some caching, but this starts to look like QuerySet, so maybe you can do something else:

• you can create database VIEW using CREATE VIEW myview AS [your query];
• add Django model for that VIEW, with Meta: managed=False
• use that model like any other model, including slicing its querysets - this means it's perfectly suitable for using with Paginator

(For your information - I've been using this approach for a long time now, even with complex many-to-many relationships with VIEWs faking m2m intermediate tables.)

这篇关于Django：Paginator +原始SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！