后端数据库分页是根据limit实现的
第一种写法： limit 每页的个数 offset 起始位置
select id,name,sex,tel,addr from user limit 1 offset 1;
select * from student limit pageSize offset (pageNumber-1)*pageSize
第二种写法： limit 起始位置，每页的个数
select id,name from user limit 1,2;
select * from studnet limit (pageNumber-1)*pageSize,pageSize
当数据量比较大的时候分页采用子查询的方式
select id,name from user where id >= (select id from user order by id limit 1,1) limit 2;
select id,name from user where id >= (select id from user order by id limit 起始位置,1) limit 每页的个数;
Python代码中实现后台数据分页
views.py文件
Page()函数是mypage.py里边类的方法
user_count() 是获取mysql 数据总数的方法
def index(request):
page_num = request.GET.get("page")
login_user = request.session.get('user')
count = user_count()
per_page = 2
page_obj = Page(page_num, count,url_prefix="/users/",per_page=2, max_page=4,)
page_num = page_obj.num
users = mysql_page(per_page,page_num)
page_html = page_obj.page_html()
return render(request,'static/index.html',{"users": users,"login_user": login_user,"page_html": page_html})mypage.py
class Page():
def __init__(self, page_num, total_count, url_prefix, per_page, max_page=4):
"""
:param page_num: 当前页码数
:param total_count: 数据总数
:param url_prefix: a标签href的前缀
:param per_page: 每页显示多少条数据
:param max_page: 页面上最多显示几个页码
"""
self.url_prefix = url_prefix
self.max_page = max_page
# 每一页显示多少条数据
# 总共需要多少页码来展示
total_page, m = divmod(total_count, per_page)
if m:
total_page += 1
self.total_page = total_page
try:
page_num = int(page_num)
# 如果输入的页码数超过了最大的页码数，默认返回最后一页
if page_num > total_page:
page_num = total_page
except Exception as e:
# 当输入的页码不是正经数字的时候 默认返回第一页的数据
page_num = 1
self.page_num = page_num
# 页面上总共展示多少页码
if total_page < self.max_page:
self.max_page = total_page
@property
def num(self):
return self.page_num
def page_html(self):
# 自己拼接分页的HTML代码
html_str_list = []
# 加上第一页
html_str_list.append('<li><a href="{}?page=1">首页</a></li>'.format( self.url_prefix))
# 判断一下 如果是第一页，就没有上一页
if self.page_num <= 1:
html_str_list.append('<li class="disabled"><a href="#"><span aria-hidden="true">«</span></a></li>'.format(self.page_num-1))
else:
# 加一个上一页的标签
html_str_list.append('<li><a href="{}?page={}"><span aria-hidden="true">«</span></a></li>'.format( self.url_prefix, self.page_num-1))
for i in range(1,self.total_page+1):
# 如果是当前页就加一个active样式类
if i == self.page_num:
tmp = '<li class="active"><a href="{0}?page={1}">{1}</a></li>'.format(self.url_prefix, i)
else:
tmp = '<li><a href="{0}?page={1}">{1}</a></li>'.format( self.url_prefix, i)
html_str_list.append(tmp)
# 加一个下一页的按钮
# 判断，如果是最后一页，就没有下一页
if self.page_num >= self.total_page:
html_str_list.append('<li class="disabled"><a href="#"><span aria-hidden="true">»</span></a></li>')
else:
html_str_list.append('<li><a href="{}?page={}"><span aria-hidden="true">»</span></a></li>'.format( self.url_prefix, self.page_num+1))
# 加最后一页
html_str_list.append('<li><a href="{}?page={}">尾页</a></li>'.format( self.url_prefix, self.total_page))
page_html = "".join(html_str_list)
return page_htmlmodels,py
def mysql_page(per_page,page):
start = (page - 1 ) * per_page
sql = 'select id,name,sex,age,tel,addr,role from user where id >= \
( select id from user order by id limit %s,1)limit %s'
args = (start,per_page)
conn = MySQLdb.connect(host=MYSQL_HOST, port=MYSQL_PORT, user=MYSQL_USER, passwd=MYSQL_PASSWORD, db=MYSQL_DB,
charset='utf8mb4')
cursor = conn.cursor()
cursor.execute(sql, args)
lines = cursor.fetchall()
users = []
for line in lines:
users.append({
'id': line[0],
'name': line[1],
'sex': '男' if line[2] else '女',
'age': line[3],
'tel': line[4],
'addr': line[5],
'role': line[6]
}
)
cursor.close()
conn.close()
return users
