python用sql的limit语句进行分页

2019独角兽企业重金招聘Python工程师标准>>>

#coding:utf8
from math import ceil
class InvalidPage(Exception):
    pass
class PageNotAnInteger(InvalidPage):
    pass

class EmptyPage(InvalidPage):
    pass

class Paginator(object):
    def __init__(self, total_count, per_page, page_num, orphans=0, allow_empty_first_page=True):
        self.total_count = total_count
        self.per_page = int(per_page)
        self.page_num = page_num
        self.orphans = int(orphans)
        self.allow_empty_first_page = allow_empty_first_page
        self._num_pages = self._count = None

    def validate_number(self, number):
        "Validates the given 1-based page number."
        try:
            number = int(number)
        except (TypeError, ValueError):
            raise PageNotAnInteger("That page number is not an integer")
        if number < 1:
            raise EmptyPage('That page 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_limit(self, number):
        "Returns a Page object for the 1-based page number."
        number = self.validate_number(number)
        bottom = (number-1) * self.per_page
        top = bottom + self.per_page
        if top + self.orphans >= self.total_count:
            top = self.total_count
        return (bottom, top)

    def _get_num_pages(self):
        "Returns the total number of pages."
        if self._num_pages is None:
            if self.total_count == 0 and not self.allow_empty_first_page:
                self._num_pages = 0
            else:
                hits = max(1, self.total_count - self.orphans)

                self._num_pages = int(ceil(hits / float(self.per_page)))
        return self._num_pages
    num_pages = property(_get_num_pages)

    def _get_page_range(self):
        """
        Returns a 1-based range of pages for iterating through with
        a template for loop.
        """
        num_pages = self.num_pages
        if num_pages <= 5:
            range_list = range(1, num_pages+1)
        else:
            if self.page_num < 3:
                range_list = range(1, 6)
            elif self.page_num > (num_pages - 2):
                range_list = range(num_pages-4, num_pages+1)
            else:
                range_list = range(self.page_num-2, self.page_num+3)
        return range_list
    page_range = property(_get_page_range)

class Page(object):
    def __init__(self, object_list, number, paginator):
        self.object_list = object_list
        self.number = number
        self.paginator = paginator

    def __repr__(self):
        return '<Page %s of %s>' % (self.number, self.paginator.num_pages)

    def __len__(self):
        return len(self.object_list)

    def __getitem__(self, index):
        return list(self.object_list)[index]

    def __iter__(self):
        i = 0
        try:
            while True:
                v = self[i]
                yield v
                i += 1
        except IndexError:
            return

    def __contains__(self, value):
        for v in self:
            if v == value:
                return True
        return False

    def index(self, value):
        for i, v in enumerate(self):
            if v == value:
                return i
        raise ValueError

    def count(self, value):
        return sum([1 for v in self if v == value])

    # End of compatibility methods.

    def has_next(self):
        return self.number < self.paginator.num_pages

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

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

    def next_page_number(self):
        return self.number + 1

    def previous_page_number(self):
        return self.number - 1

    def start_index(self):
        """
        Returns 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.paginator.total_count == 0:
            return 0
        return (self.paginator.per_page * (self.number - 1)) + 1

    def end_index(self):
        """
        Returns 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.paginator.num_pages:
            return self.paginator.total_count
        return self.number * self.paginator.per_page


def get_limit(paginator, page_num):
    try:
        bottom, top = paginator.get_limit(page_num)
    except PageNotAnInteger:
        bottom, top = paginator.get_limit(1)
    except EmptyPage:
        bottom, top = paginator.get_limit(paginator.num_pages)
    #bottom上页的最后一个，top-bottom这页的偏移量
    return '%s, %s' % (bottom, top-bottom)

def my_paginatior(request,cur,sql,page_size=10):
    # print sql
    total_count = cur.execute(sql)
    current_page=int(request.GET.get("page",1))
    # 分页所用
    paginator = Paginator(total_count, page_size, current_page)
    limit= get_limit(paginator, current_page)
    sql=sql+' limit %s'%limit
    cur.execute(sql)
    page_obj_list =cur.fetchall() #当前页的记录
    page_obj = Page(page_obj_list, current_page, paginator)
    page_range = paginator.page_range
    # print list(page_range)
    return total_count,page_obj_list,page_range,page_obj

###########前台#################只展示有用部分
   {% for i in page_obj_list%}
                     <tr>
                        <td>{{ i.data_length }} {{i.data_unit}}</td>
                        <td>{{ i.index_length}} {{i.index_unit}}</td>
                        <td>{{ i.sum_length }} {{i.sum_unit}}</td>
{#                                <td> {{i.proj_name}}</td>#}
                     </tr>
                     {% endfor %}
                  </table>



                       <div style="text-align: center" >
                       <ul class="pagination">
              {% if page_obj %}
              <li><a href="?proj_name={{ proj_name }}&page=1">首页</a> </li>
              {% if page_obj.has_previous %}
                  <li><a title="上一页" href="?proj_name={{ proj_name }}&page={{ page_obj.previous_page_number }}">&lt;&lt;</a> </li>
              {% endif %}
              {% for i in page_range %}
                  <li {% ifequal i page_obj.number %} class="active"{% endifequal %}>
                          <a href="?proj_name={{ proj_name }}&page={{ i }}" >{{ i }}</a>
                 </li>
              {% endfor %}
              {% if page_obj.has_next %}
              <li><a title="下一页" href="?proj_name={{ proj_name }}&page={{ page_obj.next_page_number }}">&gt;&gt;</a> </li>

              {% endif %}
              <li><a href="?proj_name={{ proj_name }}&page={{ page_obj.paginator.num_pages }}">尾页</a> </li>
              {% endif %}

        </ul>
        </div>

 

转载于:https://my.oschina.net/u/1458120/blog/551128