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(’点个赞吧’)