项目结构如下：
model.py如下，需要在数据库里创建模型
import json from sqlalchemy import Column,Integer,String,UniqueConstraint,Index from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine import config from sqlalchemy.orm import scoped_session, sessionmaker engine = create_engine(config.SQLALCHEMY_DATABASE_URI) Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(32)) age = Column(Integer) def __init__(self,name,age): self.name = name self.age = age def __str__(self): info = { "id": self.id, "name": self.name, "age": self.age } return json.dumps(info) # Base.metadata.create_all(engine) # Base.metadata.drop_all(engine) def query(): sm = sessionmaker(bind=engine) session = sm() all = session.query(User).all() return all
app.py文件内容如下：
import json from flask_paginate import Pagination,get_page_parameter,request from flask import Flask,render_template from flask_sqlalchemy import SQLAlchemy app = Flask(__name__) import config from model.UserModel import User,query @app.route('/') def hello_world(): return render_template("index.html") @app.route('/list') def ShowData(): pageNum = int(request.args.get("pageNum")) pageSize = int(request.args.get("pageSize")) data_list = [] all = query() for res in all: datas = { "id": res.id, "name": res.name, "age": res.age } data_list.append(datas) info = {"code": 0, "message": "OK", "total": len(data_list), "data": data_list[(pageNum - 1) * pageSize:pageNum * pageSize]} print(info) return info if __name__ == '__main__': app.run(debug=True,) # db.create_all()
数据库配置config.py如下：
host = 'xxx.xxx.xxx.xxx' port = 3306 username = 'root' password = 'root' DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=username,password=password, host=host,port=port, db='testdb') SQLALCHEMY_DATABASE_URI = DB_URI SQLALCHEMY_TRACK_MODIFICATIONS = False SQLALCHEMY_ECHO = True
index.html内容如下：
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="/static/layui.js"></script>
<link rel="stylesheet" href="/static/css/layui.css">
</head>
<body>
<div class="layui-layout layui-layout-admin">
<div class="layui-header">
<div class="layui-logo layui-hide-xs layui-bg-black">layout demo</div>
<!-- 头部区域（可配合layui 已有的水平导航） -->
<ul class="layui-nav layui-layout-left">
</ul>
<ul class="layui-nav layui-layout-right">
<li class="layui-nav-item layui-hide layui-show-md-inline-block">
<a href="javascript:;">
<img src="//tva1.sinaimg.cn/crop.0.0.118.118.180/5db11ff4gw1e77d3nqrv8j203b03cweg.jpg" class="layui-nav-img">
tester
</a>
<dl class="layui-nav-child">
<dd><a href="">Your Profile</a></dd>
<dd><a href="">Settings</a></dd>
<dd><a href="">Sign out</a></dd>
</dl>
</li>
<li class="layui-nav-item" lay-header-event="menuRight" lay-unselect>
<a href="javascript:;">
<i class="layui-icon layui-icon-more-vertical"></i>
</a>
</li>
</ul>
</div>
<div class="layui-side layui-bg-black">
<div class="layui-side-scroll">
<!-- 左侧导航区域（可配合layui已有的垂直导航） -->
<ul class="layui-nav layui-nav-tree" lay-filter="test">
<li class="layui-nav-item layui-nav-itemed">
<a class="" href="javascript:;">menu group 1</a>
<dl class="layui-nav-child">
<dd><a href="javascript:;">menu 1</a></dd>
<dd><a href="javascript:;">menu 2</a></dd>
<dd><a href="javascript:;">menu 3</a></dd>
<dd><a href="">the links</a></dd>
</dl>
</li>
<li class="layui-nav-item">
<a href="javascript:;">menu group 2</a>
<dl class="layui-nav-child">
<dd><a href="javascript:;">list 1</a></dd>
<dd><a href="javascript:;">list 2</a></dd>
<dd><a href="">超链接</a></dd>
</dl>
</li>
<li class="layui-nav-item"><a href="javascript:;">click menu item</a></li>
<li class="layui-nav-item"><a href="">the links</a></li>
</ul>
</div>
</div>
<div class="layui-body">
<table class="layui-hide" id="test"></table>
</div>
<div id="page"></div>
<div class="layui-footer">
<!-- 底部固定区域 -->
底部固定区域
</div>
</div>
<script>
//JS
layui.use(['element', 'layer', 'util','table','laypage'], function(){
var element = layui.element
,layer = layui.layer
,util = layui.util
,$ = layui.$
,laypage = layui.laypage
var table = layui.table;
table.render({
elem: '#test'
,url: '/list'
,limit: 4
,page: true //是否显示分页
, request: {
pageName: 'pageNum' //页码的参数名称，默认：page
,limitName: 'pageSize' //每页数据量的参数名，默认：limit
}
,cols: [[ //标题栏
{field: 'id', title: 'ID', 80, sort: true}
,{field: 'name', title: '姓名', 120}
,{field: 'age', title: '年纪', minWidth: 150}
]]
,skin: 'line' //表格风格
,toolbar: 'default'
,parseData: function(res){ //将原始数据解析成 table 组件所规定的数据
return {
"code": res.code, //解析接口状态
"msg": res.message, //解析提示文本
"count": res.total, //解析数据长度
"data": res.data //解析数据列表
};
}
});
//头部事件
util.event('lay-header-event', {
//左侧菜单事件
menuLeft: function(othis){
layer.msg('展开左侧菜单的操作', {icon: 0});
}
,menuRight: function(){
layer.open({
type: 1
,content: '<div style="padding: 15px;">处理右侧面板的操作</div>'
,area: ['260px', '100%']
,offset: 'rt'
,anim: 5
,shadeClose: true
});
}
});
});
</script>
</body>
</html>
最终效果图如下：
分页功能
需要注意的是如果想实现分页在index.html中需要加入请求的页码和开启分页功能如下：
在后台需要得到分页的页码和当前页，如下：
