Django模型——执行SQL分页器的实现

django框架自带分页器：Paginator，但是基于ORM实现的，我们肯定会有直接执行sql的需求，在直接执行sql的时候，进行分页。我模仿自带的分页类实现的，所有方法名都保持一致。

---

import math

from django.db import connection
from django.utils.functional import cached_property


class InvalidPage(Exception):
    pass


class PageNotAnInteger(InvalidPage):
    pass


class EmptyPage(InvalidPage):
    pass


class SqlPaginator(object):
    def __init__(self, sql, params, per_page, orphans=0, allow_empty_first_page=True):
        # 防止用户输入了分号
        self.sql = sql.split(';')[0]
        self.params = params
        self.per_page = int(per_page)
        # 下面这两个参数一般不使用，orphans——开始计算的页码；allow_empty_first_page——第一页是否允许为空
        self.orphans = int(orphans)
        self.allow_empty_first_page = allow_empty_first_page
        self.number = -1

    def validate_number(self, number):
        """Validate the given 1-based page number."""
        try:
            if isinstance(number, float) and not number.is_integer():
                raise ValueError
            number = int(number)
            self.number = number
        except (TypeError, ValueError):
            raise PageNotAnInteger('That page number is not an integer')
        if number < 1:
            raise EmptyPage('That page number is less than 1')
        if number > self.num_pages:
            if number == 1 and self.allow_empty_first_page:
                pass
            else:
                raise EmptyPage('That page contains no results')
        return number

    def get_page(self, number):
        """
        Return a valid page, even if the page argument isn't a number or isn't
        in range.
        """
        try:
            number = self.validate_number(number)
        except PageNotAnInteger:
            number = 1
        except EmptyPage:
            number = self.num_pages
        return self.page(number)

    def page(self, number):
        """Return a Page object for the given 1-based page number."""
        number = self.validate_number(number)
        # 偏移量
        offset = (number - 1) * self.per_page
        sql = self.sql + ' LIMIT %s OFFSET %s;'
        cursor = connection.cursor()
        sql_params = []
        for p in self.params:
            sql_params.append(p)
        # append和extend的区别，append追加一个列表元素，extend添加新的列表——拼接
        sql_params.extend([self.per_page, offset])
        cursor.execute(sql, sql_params)
        self.__execute_sql(sql, sql_params)
        desc = cursor.description
        # 指定列表长度
        # field_list = []
        field_list = [None] * len(desc)
        for i in range(len(desc)):
            field_list[i] = desc[i][0]
        rows = cursor.fetchall()
        dict_list = [None] * len(rows)
        for i in range(len(rows)):
            # print('数据元组：', rows[i])
            # 拼接列表为字典
            dict_list[i] = dict(zip(field_list, rows[i]))
        return dict_list

    @cached_property
    def count(self):
        count_sql = (
            'SELECT COUNT(1) FROM ( '
            '{}'
            ') AS count_record;'
        ).format(self.sql)
        # print(count_sql)
        try:
            cursor = connection.cursor()
            cursor.execute(count_sql, self.params)
            self.__execute_sql(count_sql, self.params)
            result = cursor.fetchone()
            result = result[0]
        except:
            result = -1
        return result

    @cached_property
    def num_pages(self):
        """Return the total number of pages."""
        if self.count == 0 and not self.allow_empty_first_page:
            return 0
        hits = max(1, self.count - self.orphans)
        return math.ceil(hits / self.per_page)

    @property
    def page_range(self):
        """
        Return a 1-based range of pages for iterating through within
        a template for loop.
        """
        return range(1, self.num_pages + 1)

    @cached_property
    def has_next(self):
        return self.number < self.num_pages

    @cached_property
    def has_previous(self):
        return self.number > 1

    @cached_property
    def has_other_pages(self):
        return self.has_previous or self.has_next

    @cached_property
    def next_page_number(self):
        return self.validate_number(self.number + 1)

    @cached_property
    def previous_page_number(self):
        return self.validate_number(self.number - 1)

    @cached_property
    def start_index(self):
        """
        Return the 1-based index of the first object on this page,
        relative to total objects in the paginator.
        """
        # Special case, return zero if no items.
        if self.count == 0:
            return 0
        return (self.per_page * (self.number - 1)) + 1

    @cached_property
    def end_index(self):
        """
        Return the 1-based index of the last object on this page,
        relative to total objects found (hits).
        """
        # Special case for the last page because there can be orphans.
        if self.number == self.num_pages:
            return self.count
        return self.number * self.per_page

    def __execute_sql(self, sql, params):
        result_sql = sql % tuple(params)
        print('执行的SQL：', result_sql)
        return result_sql

 

示例如下：

def sql_paginator(request):
    sql = 'SELECT * FROM `perf` where year=%s;'
    paginator = SqlPaginator(sql, [2018], 5)
    print('数据总量：', paginator.count)
    print('第二页的数据：', paginator.page(2))
    print('总页数：', paginator.num_pages)
    print('是否有下一页：', paginator.has_next)
    print('页码范围：', paginator.page_range)
    return HttpResponse('test my paginator')

 

执行结果如下：

执行的SQL： SELECT COUNT(1) FROM ( SELECT * FROM `perf` where year=2018) AS count_record;
数据总量： 33
执行的SQL： SELECT * FROM `perf` where year=2018 LIMIT 5 OFFSET 5;
第二页的数据： [{'id': 15, 'year': 2018, 'quarter': 3, 'score': 63, 'create_time': datetime.datetime(2019, 7, 7, 13, 14, 18, 596779), 'update_time': datetime.datetime(2019, 7, 7, 13, 14, 18, 596779), 'staff_id': 4}, {'id': 16, 'year': 2018, 'quarter': 4, 'score': 91, 'create_time': datetime.datetime(2019, 7, 7, 13, 14, 18, 665779), 'update_time': datetime.datetime(2019, 7, 7, 13, 14, 18, 665779), 'staff_id': 1}, {'id': 17, 'year': 2018, 'quarter': 1, 'score': 68, 'create_time': datetime.datetime(2019, 7, 7, 13, 14, 18, 752779), 'update_time': datetime.datetime(2019, 7, 7, 13, 14, 18, 752779), 'staff_id': 8}, {'id': 19, 'year': 2018, 'quarter': 1, 'score': 64, 'create_time': datetime.datetime(2019, 7, 7, 13, 14, 18, 899779), 'update_time': datetime.datetime(2019, 7, 7, 13, 14, 18, 899779), 'staff_id': 3}, {'id': 21, 'year': 2018, 'quarter': 4, 'score': 61, 'create_time': datetime.datetime(2019, 7, 7, 13, 14, 19, 32779), 'update_time': datetime.datetime(2019, 7, 7, 13, 14, 19, 32779), 'staff_id': 8}]
总页数： 7
是否有下一页： True
页码范围： range(1, 8)

 

 

 

 

print_r('点个赞吧')；
var_dump('点个赞吧')；
NSLog(@"点个赞吧！")
System.out.println("点个赞吧!");
console.log("点个赞吧!");
print("点个赞吧!");
printf("点个赞吧!\n");
cout << "点个赞吧!" << endl;
Console.WriteLine("点个赞吧!");
fmt.Println("点个赞吧!")
Response.Write("点个赞吧")；
alert（’点个赞吧’）