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

话不多说,有图有源码

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.前端页面

{% from "common/_macro.html" import static %}

Bootstrap-Table 服务端动态表头及数据,分页,查询,排序

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)

就此收功.

相关阅读

评论可见,请评论后查看内容,谢谢!!!
 您阅读本篇文章共花了: