当前位置： 首页 > article >正文

实战Flask+BootstrapTable最实用服务端分页查询动态表头及数据(ajax方式)

article 2024/11/15 21:05:19

看到这篇文章的朋友们是幸运的,我用了很久才实战出如下结果,且行且珍惜,祝好!

话不多说,有图有源码

1.看图,实现服务端动态表头数据,分页,查询,排序

1.数据准备

CREATE TABLE `goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '商品名',
  `no` varchar(255) DEFAULT NULL COMMENT '编号',
  `price` double DEFAULT NULL COMMENT '价格',
  `num` int(11) DEFAULT NULL COMMENT '数量',
  `inputtime` datetime DEFAULT NULL COMMENT '日期',
  `content` varchar(255) DEFAULT NULL COMMENT '说明',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO goods VALUES ('6', '商品6', '6', '60', '600', '2018-05-26 11:18:39', '商品详情6');
INSERT INTO goods VALUES ('7', '商品7', '7', '70', '700', '2018-05-26 11:18:39', '商品详情7');
INSERT INTO goods VALUES ('8', '商品8', '8', '80', '800', '2018-05-26 11:18:39', '商品详情8');
INSERT INTO goods VALUES ('9', '商品9', '9', '90', '900', '2018-05-26 11:18:39', '商品详情9');
INSERT INTO goods VALUES ('12', '商品12', '12', '120', '1200', '2018-05-26 11:18:40', '商品详情12');
INSERT INTO goods VALUES ('13', '商品13', '13', '130', '1300', '2018-05-26 11:18:40', '商品详情13');
INSERT INTO goods VALUES ('14', '商品14', '14', '140', '1400', '2018-05-26 11:18:40', '商品详情14');
INSERT INTO goods VALUES ('15', '商品15', '15', '150', '1500', '2018-05-26 11:18:40', '商品详情15');
INSERT INTO goods VALUES ('16', '商品16', '16', '160', '1600', '2018-05-26 11:18:40', '商品详情16');
INSERT INTO goods VALUES ('17', '商品17', '17', '170', '1700', '2018-05-26 11:18:40', '商品详情17');
INSERT INTO goods VALUES ('18', '商品18', '18', '180', '1800', '2018-05-26 11:18:40', '商品详情18');
INSERT INTO goods VALUES ('19', '商品19', '19', '190', '1900', '2018-05-26 11:18:40', '商品详情19');
INSERT INTO goods VALUES ('20', '商品20', '20', '200', '2000', '2018-05-26 11:18:40', '商品详情20');
INSERT INTO goods VALUES ('21', '商品21', '21', '210', '2100', '2018-05-26 11:18:40', '商品详情21');
INSERT INTO goods VALUES ('22', '商品22', '22', '220', '2200', '2018-05-26 11:18:40', '商品详情22');
INSERT INTO goods VALUES ('23', '商品23', '23', '230', '2300', '2018-05-26 11:18:40', '商品详情23');
INSERT INTO goods VALUES ('24', '商品24', '24', '240', '2400', '2018-05-26 11:18:40', '商品详情24');
INSERT INTO goods VALUES ('25', '商品25', '25', '250', '2500', '2018-05-26 11:18:40', '商品详情25');
INSERT INTO goods VALUES ('26', '商品26', '26', '260', '2600', '2018-05-26 11:18:40', '商品详情26');
INSERT INTO goods VALUES ('27', '商品27', '27', '270', '2700', '2018-05-26 11:18:40', '商品详情27');
INSERT INTO goods VALUES ('28', '商品28', '28', '280', '2800', '2018-05-26 11:18:40', '商品详情28');
INSERT INTO goods VALUES ('29', '商品29', '29', '290', '2900', '2018-05-26 11:18:40', '商品详情29');
INSERT INTO goods VALUES ('30', '商品30', '30', '300', '3000', '2018-05-26 11:18:40', '商品详情30');
INSERT INTO goods VALUES ('31', '商品31', '31', '310', '3100', '2018-05-26 11:18:40', '商品详情31');
INSERT INTO goods VALUES ('32', '商品32', '32', '320', '3200', '2018-05-26 11:18:40', '商品详情32');
INSERT INTO goods VALUES ('33', '商品33', '33', '330', '3300', '2018-05-26 11:18:40', '商品详情33');
INSERT INTO goods VALUES ('34', '商品34', '34', '340', '3400', '2018-05-26 11:18:40', '商品详情34');
INSERT INTO goods VALUES ('35', '商品35', '35', '350', '3500', '2018-05-26 11:18:40', '商品详情35');
INSERT INTO goods VALUES ('36', '商品36', '36', '360', '3600', '2018-05-26 11:18:40', '商品详情36');
INSERT INTO goods VALUES ('37', '商品37', '37', '370', '3700', '2018-05-26 11:18:40', '商品详情37');
INSERT INTO goods VALUES ('38', '商品38', '38', '380', '3800', '2018-05-26 11:18:40', '商品详情38');
INSERT INTO goods VALUES ('39', '商品39', '39', '390', '3900', '2018-05-26 11:18:40', '商品详情39');
INSERT INTO goods VALUES ('40', '商品40', '40', '400', '4000', '2018-05-26 11:18:40', '商品详情40');
INSERT INTO goods VALUES ('41', '商品41', '41', '410', '4100', '2018-05-26 11:18:40', '商品详情41');
INSERT INTO goods VALUES ('42', '商品42', '42', '420', '4200', '2018-05-26 11:18:40', '商品详情42');
INSERT INTO goods VALUES ('43', '商品43', '43', '430', '4300', '2018-05-26 11:18:40', '商品详情43');
INSERT INTO goods VALUES ('44', '商品44', '44', '440', '4400', '2018-05-26 11:18:40', '商品详情44');
INSERT INTO goods VALUES ('45', '商品45', '45', '450', '4500', '2018-05-26 11:18:40', '商品详情45');
INSERT INTO goods VALUES ('46', '商品46', '46', '460', '4600', '2018-05-26 11:18:40', '商品详情46');
INSERT INTO goods VALUES ('47', '商品47', '47', '470', '4700', '2018-05-26 11:18:40', '商品详情47');
INSERT INTO goods VALUES ('48', '商品48', '48', '480', '4800', '2018-05-26 11:18:40', '商品详情48');
INSERT INTO goods VALUES ('49', '商品49', '49', '490', '4900', '2018-05-26 11:18:40', '商品详情49');
INSERT INTO goods VALUES ('50', '商品50', '50', '500', '5000', '2018-05-26 11:18:40', '商品详情50');
INSERT INTO goods VALUES ('51', '商品51', '51', '510', '5100', '2018-05-26 11:18:40', '商品详情51');
INSERT INTO goods VALUES ('52', '商品52', '52', '520', '5200', '2018-05-26 11:18:41', '商品详情52');
INSERT INTO goods VALUES ('53', '商品53', '53', '530', '5300', '2018-05-26 11:18:41', '商品详情53');
INSERT INTO goods VALUES ('54', '商品54', '54', '540', '5400', '2018-05-26 11:18:41', '商品详情54');
INSERT INTO goods VALUES ('55', '商品55', '55', '550', '5500', '2018-05-26 11:18:41', '商品详情55');
INSERT INTO goods VALUES ('56', '商品56', '56', '560', '5600', '2018-05-26 11:18:41', '商品详情56');
INSERT INTO goods VALUES ('57', '商品57', '57', '570', '5700', '2018-05-26 11:18:41', '商品详情57');
INSERT INTO goods VALUES ('58', '商品58', '58', '580', '5800', '2018-05-26 11:18:41', '商品详情58');
INSERT INTO goods VALUES ('60', '商品60', '60', '600', '6000', '2018-05-26 11:18:41', '商品详情60');
INSERT INTO goods VALUES ('61', '商品61', '61', '610', '6100', '2018-05-26 11:18:41', '商品详情61');
INSERT INTO goods VALUES ('62', '商品62', '62', '620', '6200', '2018-05-26 11:18:41', '商品详情62');
INSERT INTO goods VALUES ('63', '商品63', '63', '630', '6300', '2018-05-26 11:18:41', '商品详情63');
INSERT INTO goods VALUES ('64', '商品64', '64', '640', '6400', '2018-05-26 11:18:41', '商品详情64');
INSERT INTO goods VALUES ('65', '商品65', '65', '650', '6500', '2018-05-26 11:18:41', '商品详情65');
INSERT INTO goods VALUES ('66', '商品66', '66', '660', '6600', '2018-05-26 11:18:41', '商品详情66');
INSERT INTO goods VALUES ('67', '商品67', '67', '670', '6700', '2018-05-26 11:18:41', '商品详情67');
INSERT INTO goods VALUES ('68', '商品68', '68', '680', '6800', '2018-05-26 11:18:41', '商品详情68');
INSERT INTO goods VALUES ('69', '商品69', '69', '690', '6900', '2018-05-26 11:18:41', '商品详情69');
INSERT INTO goods VALUES ('70', '商品70', '70', '700', '7000', '2018-05-26 11:18:41', '商品详情70');
INSERT INTO goods VALUES ('71', '商品71', '71', '710', '7100', '2018-05-26 11:18:41', '商品详情71');
INSERT INTO goods VALUES ('72', '商品72', '72', '720', '7200', '2018-05-26 11:18:41', '商品详情72');
INSERT INTO goods VALUES ('73', '商品73', '73', '730', '7300', '2018-05-26 11:18:41', '商品详情73');
INSERT INTO goods VALUES ('274', '商品74', '74', '740', '7400', '2018-05-26 11:18:41', '商品详情74');
INSERT INTO goods VALUES ('275', '商品75', '75', '750', '7500', '2018-05-26 11:18:41', '商品详情75');
INSERT INTO goods VALUES ('276', '商品76', '76', '760', '7600', '2018-05-26 11:18:41', '商品详情76');
INSERT INTO goods VALUES ('277', '商品77', '77', '770', '7700', '2018-05-26 11:18:41', '商品详情77');
INSERT INTO goods VALUES ('278', '商品78', '78', '780', '7800', '2018-05-26 11:18:41', '商品详情78');
INSERT INTO goods VALUES ('279', '商品79', '79', '790', '7900', '2018-05-26 11:18:41', '商品详情79');
INSERT INTO goods VALUES ('280', '商品80', '80', '800', '8000', '2018-05-26 11:18:42', '商品详情80');
INSERT INTO goods VALUES ('281', '商品81', '81', '810', '8100', '2018-05-26 11:18:42', '商品详情81');
INSERT INTO goods VALUES ('282', '商品82', '82', '820', '8200', '2018-05-26 11:18:42', '商品详情82');
INSERT INTO goods VALUES ('283', '商品83', '83', '830', '8300', '2018-05-26 11:18:42', '商品详情83');
INSERT INTO goods VALUES ('284', '商品84', '84', '840', '8400', '2018-05-26 11:18:42', '商品详情84');
INSERT INTO goods VALUES ('285', '商品85', '85', '850', '8500', '2018-05-26 11:18:42', '商品详情85');
INSERT INTO goods VALUES ('286', '商品86', '86', '860', '8600', '2018-05-26 11:18:42', '商品详情86');
INSERT INTO goods VALUES ('287', '商品87', '87', '870', '8700', '2018-05-26 11:18:42', '商品详情87');
INSERT INTO goods VALUES ('288', '商品88', '88', '880', '8800', '2018-05-26 11:18:42', '商品详情88');
INSERT INTO goods VALUES ('289', '商品89', '89', '890', '8900', '2018-05-26 11:18:42', '商品详情89');
INSERT INTO goods VALUES ('290', '商品90', '90', '900', '9000', '2018-05-26 11:18:42', '商品详情90');
INSERT INTO goods VALUES ('291', '商品91', '91', '910', '9100', '2018-05-26 11:18:42', '商品详情91');
INSERT INTO goods VALUES ('292', '商品92', '92', '920', '9200', '2018-05-26 11:18:42', '商品详情92');
INSERT INTO goods VALUES ('293', '商品93', '93', '930', '9300', '2018-05-26 11:18:42', '商品详情93');
INSERT INTO goods VALUES ('294', '商品94', '94', '940', '9400', '2018-05-26 11:18:42', '商品详情94');
INSERT INTO goods VALUES ('295', '商品95', '95', '950', '9500', '2018-05-26 11:18:42', '商品详情95');
INSERT INTO goods VALUES ('296', '商品96', '96', '960', '9600', '2018-05-26 11:18:42', '商品详情96');
INSERT INTO goods VALUES ('297', '商品97', '97', '970', '9700', '2018-05-26 11:18:42', '商品详情97');
INSERT INTO goods VALUES ('298', '商品98', '98', '980', '9800', '2018-05-26 11:18:42', '商品详情98');
INSERT INTO goods VALUES ('299', '商品99', '99', '990', '9900', '2018-05-26 11:18:42', '商品详情99');
INSERT INTO goods VALUES ('300', '商品100', '100', '1000', '10000', '2018-05-26 11:18:42', '商品详情100');

2.前端页面

<!DOCTYPE html>
{% from "common/_macro.html" import static %}
<html>
<meta charset="utf-8">
<head>
<!-- 引入bootstrap样式 -->
<link rel="stylesheet" href="/static/bootstrap/css/bootstrap.min.css" />
<!-- 引入bootstrap-table样式 -->
<link rel="stylesheet" href="/static/bootstrap-table-develop/bootstrap-table.min.css" />
<!-- layer -->
<link rel="stylesheet" href="/static/layer/2.4/skin/layer.css" />
<!-- jquery -->
<script type="text/javascript" src="/static/js/jquery-2.2.0.min.js" ></script>
<!-- bootstrap -->
<script type="text/javascript" src="/static/bootstrap/js/bootstrap.min.js" ></script>
<!-- bootstrap-table -->
<script type="text/javascript" src="/static/bootstrap-table-develop/bootstrap-table.min.js" ></script>
<!-- 引入中文语言包 -->
<script type="text/javascript" src="/static/bootstrap-table-develop/locale/bootstrap-table-zh-CN.min.js" ></script>
<!-- layer -->
<script type="text/javascript" src="/static/layer/2.4/layer.js" ></script>

</head>
<body class="gray-bg">
    <h1 align="center">Bootstrap-Table 服务端动态表头及数据,分页,查询,排序</h1>
	<div class="wrapper wrapper-content ">
		<div class="col-md-12">
            <form id="toolbar" class="form-inline" role="form">
                <div class="form-group">
                    <input type="text" id="search_name" class="form-control"  placeholder="商品名称">
                </div>
                <button type="button" id="btn_search" class="btn btn-default btn-info">搜索</button>
            </form>
            <table id="table" class="table-striped " style="table-layout:auto;;">

            </table>
        </div>
    </div>

</body>
</html>
<script>
    $(function () {
        var $table = $('#table');
        var tableColumns = [];
        var loadData =[];
        //-----gtj 通过ajax 初始化表头及数据
        ajaxTable('/hellotableserverpagesearch/list');

        $table.bootstrapTable({
            dataType:"json",    //服务器返回的数据类型
            method:"get",
            toolbar: "#toolbar",
            locale: "zh-CN", //en-US
            clickEdit: true,
            showRefresh: true,
            showColumns: true,
            search: true,
            showPaginationSwitch: true,     //显示切换分页按钮
            clickToSelect: true,  //点击row选中radio或CheckBox
            showExport: true,
            contentEditable: true,
            stickyHeader: true,
            theadClasses: "thead-blue",//设置thead-blue为表头样式
            columns: tableColumns,

            //----gtj 增加查询参数处理
            sortable:true,      //是否启用排序
            sortOrder:"asc",    //排序方式
            //-----gtj 分页方式
            pagination: true,       //显示分页条
            paginationVAlign: "bottom",
            paginationShowPageGo: true,
            showJumpto: true,
            pageNumber: 1, //初始化加载第一页，默认第一页
            queryParamsType:"undefined",    //设置参数格式
            //得到查询的参数
            queryParams : function (params) {
                var temp = {
                    page:params.pageNumber, //首页页码
                    rows:params.pageSize,    //每页的记录行数
                    sortName: params.sortName,      //排序列名
                    sortOrder: params.sortOrder, //排位命令（desc，asc）
                    //------gtj 搜索内容变量值------------------
                    sname: $('#toolbar #search_name').val()
                };
                return temp;
            },
            sidePagination: 'server',//指定服务器端分页
            pageSize: 10,//单页默认显示记录数
            pageList: [10, 20, 30, 40],//分页步进值
            search: false //是否显示表格搜索，
        });


        function ajaxTable(url){
            $.ajax({
                url: url,
                type: "GET",
                dataType: "json",
                success: function(rs){
                    console.log(rs.data.rows)
                    loadData = rs.data.rows;
                    tableColumns = rs.data.columns;
                    $table.bootstrapTable('refreshOptions',
                    {
                        url: url,
                        columns: tableColumns,
                        responseHandler: function (rs) {
                            return {
                                "rows": rs.data.rows,
                                "total": rs.data.total
                            };
                        }

                    });
                },
                error: function(rs){
                    console.log(rs)
                }
            });
        }
        // 搜索查询按钮触发事件
        $("#btn_search").click(function () {
            //alert($('#search_sptype').val());
            $('#table').bootstrapTable('refresh'); // 很重要的一步，刷新url！
            //$('#search_sptype').val();
        })
    })
</script>

3.后端内容

from sqlalchemy import create_engine, text
import pandas as pd
#=============================================================
#---------------------阿桂天山 Ewangda--------------------------
"""
Bootstrap-table 服务端分页,查询,排序
"""
@app.route('/hellotableserverpagesearch', methods=['GET','POST'])
def hellotableserverpagesearch():
    return render_template('/hello_tableserverpagesearch.html')

@app.route('/hellotableserverpagesearch/list', methods=['GET','POST'])
def hellotableserverpagesearch_list():
    engine = create_engine("mysql+pymysql://root:123123@127.0.0.1:3306/testdb?charset=utf8", echo=True)

    # --------sqlalchemy获取字段名--------------
    conn = engine.connect(close_with_result=True)  # --连接使用后自动关闭
    cursor = conn.execute(text("SELECT * FROM goods")).cursor  # --返回cursor
    fields_list = cursor.description  
    colnames = [col[0] for col in fields_list]
    columns = [{'checkbox': True}]
    for f in colnames:
        col = {'field': f, 'title': f, 'sortable': True}
        columns.append(col)

    info = request.args #.get_json()  # .form
    rows = info.get('rows', 10)  # 每页显示的条数
    # print("rows",rows)
    page = info.get('page', 1)  # 分片数，(页码-1)*limit，它表示一段数据的起点
    # print("page:",page)
    sname = r"%{0}%".format(info.get('sname', ''))
    # print(sname)
    sortname = info.get('sortName', '')
    sortorder = info.get('sortOrder', '')
    totalRecouds_sql = r"select count(*) from goods where name like '{0}'".format(sname)
    # print(totalRecouds_sql)
    result = engine.execute(text(totalRecouds_sql))
    # 从元组中取出记录条数((数目,),)
    totalNum = int(result.fetchall()[0][0])
    # print("totalnum:", totalNum)
    offset = int(rows) * int(int(page) - 1)
    if sortname == '':
        sql = "select * from goods where name like '{0}' limit {1},{2}".format(sname, offset, int(rows))
    else:
        sql = "select * from goods where name like '{0}' order by {1} {2} limit {3},{4} ".format(sname, sortname,
                                                                                                      sortorder, offset,
                                                                                                      int(rows))
    print(sql)
    sql = text(sql)
    df = pd.read_sql(sql, engine)
    rows = df.to_dict(orient='records')
    # print(rows)
    data = {
        'columns': columns,
        'rows': rows,
        'total': totalNum
    }

    return restful.success(data=data)

就此收功.


http://www.kler.cn/a/147890.html

相关文章：

  • 网络延迟对Python爬虫速度的影响分析
  • IP数据云 识别和分析tor、proxy等各类型代理
  • docker更改数据目录
  • 【常见问题解答】远程桌面无法复制粘贴的解决方法
  • 2023年MathorCup数学建模B题城市轨道交通列车时刻表优化问题解题全过程文档加程序
  • C 语言 【模拟实现内存库函数】
  • 群晖NAS配置之自有服务器ngrok实现内网穿透
  • bluez inquiry 流程梳理--从代码层面理解bluez架构
  • opencv-医学图像预处理
  • LeetCode算法题解（动态规划）|LeetCode198. 打家劫舍、LeetCode213. 打家劫舍 II、LeetCode337. 打家劫舍 III
  • 小程序中的大道理--综述
  • Android12：内置第三方应用，权限控制器已停止运行，应用app已停止运行
  • PC行内编辑
  • 一篇文章搞懂 JavaScript 箭头函数
  • 力扣2.两数相加
  • IDEA：Command line is too long
  • 嵌入式常见协议---IIC协议
  • 《使用Python将Excel数据批量写入MongoDB数据库》
  • C++(20)：通过remove_cvref_t退化类型
  • 自动化部署 扩容openGauss —— Ansible for openGauss
  • 文件上传绕过
  • 设计模式篇---外观模式
  • 搜索的剪枝
  • 论文阅读：“Appearance Capture and Modeling of Human Teeth”
  • 【复杂网络建模】——ER网络度分布、无标度网络度分布
  • centos 7.7 安装Python-3.7.4