这里记录下layui数据表格多个查询条件数据提交查询,数据库返回对应数据，数据库使用第三方模块flask_sqlalchemy, 需要对flask及flask_sqlalchemy有一定的了解
一.html前端增加条件输入框以及增加对提交按钮的监听操作1.使用table.reload()方法对表格重载
| 语法 | 说明 | 适用场景 |
| table.reload(ID, options, deep) | 参数 ID 即为基础参数id对应的值，见：设定容器唯一ID 参数 options 即为各项基础参数 参数 deep：是否采用深度重载（即参数深度克隆，也就是重载时始终携带初始时及上一次重载时的参数），默认 false 注意：deep 参数为 layui 2.6.0 开始新增。 | 所有渲染方式 |
| tableIns.reload(options, deep) | 参数同上 tableIns 可通过 var tableIns = table.render() 得到 | 仅限方法级渲染 |
// 监听搜索操作
form.on('submit(data-search-btn)', function (data) {
console.log(data.field);
var result = JSON.stringify(data.field);
//执行搜索重载
table.reload('currentTableId', {
page: {
curr: 1
}
, where: {
Params:result
}
}, 'data');
return false;
});
2.html完整代码
layui
1.flask完整代码
# encoding:utf-8
"""
@file = app
@author = zouju
@create_time = 2022-06-14- 8:59
"""
import json
from flask import Flask, render_template, jsonify, request
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import and_
class BaseConfig:
# mysql 配置
MYSQL_USERNAME = "root"
MYSQL_PASSWORD = "root"
MYSQL_HOST = "127.0.0.1"
MYSQL_PORT = 3306
MYSQL_DATABASE = "flask_layui"
# mysql 数据库的配置信息
SQLALCHEMY_DATABASE_URI = f"mysql+pymysql://{MYSQL_USERNAME}:{MYSQL_PASSWORD}@{MYSQL_HOST}:{MYSQL_PORT}/{MYSQL_DATABASE}?charset=utf8mb4"
SQLALCHEMY_POOL_SIZE = 30
SQLALCHEMY_TRACK_MODIFICATIONS = True
SQLALCHEMY_NATIVE_UNICODE = 'utf8'
SQLALCHEMY_ECHO = False
db = SQLAlchemy()
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='ID')
id_number = db.Column(db.String(50), comment='登录ID')
id_password = db.Column(db.String(50), comment='sha256密码')
name = db.Column(db.String(50), comment='姓名')
department = db.Column(db.String(50), comment='部门')
position = db.Column(db.String(50), comment='职位')
role = db.Column(db.Integer, default=0, comment='角色类型')
role_cn = db.Column(db.String(50), comment='角色类型中文')
user_status = db.Column(db.Integer, comment='用户状态')
email = db.Column(db.String(50), comment='Email')
class ModelFilter:
"""
orm多参数构造器
"""
filter_field = {}
filter_list = []
type_exact = "exact"
type_neq = "neq"
type_greater = "greater"
type_less = "less"
type_vague = "vague"
type_contains = "contains"
type_between = "between"
def __init__(self):
self.filter_field = {}
self.filter_list = []
def exact(self, field_name, value):
"""
准确查询字段
:param field_name: 模型字段名称
:param value: 值
"""
if value and value != '':
self.filter_field[field_name] = {"data": value, "type": self.type_exact}
def neq(self, field_name, value):
"""
不等于查询字段
:param field_name: 模型字段名称
:param value: 值
"""
if value and value != '':
self.filter_field[field_name] = {"data": value, "type": self.type_neq}
def greater(self, field_name, value):
"""
大于查询字段
:param field_name: 模型字段名称
:param value: 值
"""
if value and value != '':
self.filter_field[field_name] = {"data": value, "type": self.type_greater}
def less(self, field_name, value):
"""
大于查询字段
:param field_name: 模型字段名称
:param value: 值
"""
if value and value != '':
self.filter_field[field_name] = {"data": value, "type": self.type_less}
def vague(self, field_name, value: str):
"""
模糊查询字段
:param field_name: 模型字段名称
:param value: 值
"""
if value and value != '':
self.filter_field[field_name] = {"data": ('%' + value + '%'), "type": self.type_vague}
def left_vague(self, field_name, value: str):
"""
左模糊查询字段
:param field_name: 模型字段名称
:param value: 值
"""
if value and value != '':
self.filter_field[field_name] = {"data": ('%' + value), "type": self.type_vague}
def right_vague(self, field_name, value: str):
"""
左模糊查询字段
:param field_name: 模型字段名称
:param value: 值
"""
if value and value != '':
self.filter_field[field_name] = {"data": (value + '%'), "type": self.type_vague}
def contains(self, field_name, value: str):
"""
包含查询字段
:param field_name: 模型字段名称
:param value: 值
"""
if value and value != '':
self.filter_field[field_name] = {"data": value, "type": self.type_contains}
def between(self, field_name, value1, value2):
"""
范围查询字段
:param field_name: 模型字段名称
:param value1: 值1
:param value2: 值2
"""
if value1 and value2 and value1 != '' and value2 != '':
self.filter_field[field_name] = {"data": [value1, value2], "type": self.type_between}
def get_filter(self, model: db.Model):
"""
获取过滤条件
:param model: 模型字段名称
"""
for k, v in self.filter_field.items():
if v.get("type") == self.type_vague:
self.filter_list.append(getattr(model, k).like(v.get("data")))
if v.get("type") == self.type_contains:
self.filter_list.append(getattr(model, k).contains(v.get("data")))
if v.get("type") == self.type_exact:
self.filter_list.append(getattr(model, k) == v.get("data"))
if v.get("type") == self.type_neq:
self.filter_list.append(getattr(model, k) != v.get("data"))
if v.get("type") == self.type_greater:
self.filter_list.append(getattr(model, k) > v.get("data"))
if v.get("type") == self.type_less:
self.filter_list.append(getattr(model, k) < v.get("data"))
if v.get("type") == self.type_between:
self.filter_list.append(getattr(model, k).between(v.get("data")[0], v.get("data")[1]))
return and_(*self.filter_list)
app = Flask(__name__)
app.config.from_object(BaseConfig)
db = SQLAlchemy(app)
@app.get('/')
def index():
return render_template('layui_table.html')
@app.post('/user-query')
def user_query():
data_list = []
post_data = request.form
print('table.render提交数据:', post_data)
page_number = int(request.form.get('page', ''))
page_limit = int(request.form.get('limit', ''))
search_param = request.form.get('Params', None)
if search_param is None:
return jsonify({"code": 0, "msg": "", "count": 0, "data": data_list})
else:
print('查询参数:', search_param)
search_param_dict = json.loads(search_param)
id_number = search_param_dict['userid']
name = search_param_dict['username']
department = search_param_dict['department']
position = search_param_dict['position']
user_status = search_param_dict['status']
role = search_param_dict['role']
# 查询参数构造
mf = ModelFilter()
# filed_dict为model或数据库对应字段
filed_dict = {0: 'id_number', 1: 'name', 2: 'department', 3: 'position', 4: 'user_status', 5: 'role_cn'}
param_list = [id_number, name, department, position, user_status, role]
for i in range(len(param_list)):
if param_list[i] not in (None, ''): # 查询条件不为空,则添加查询条件
db_field = filed_dict[i]
mf.vague(field_name=db_field, value=param_list[i])
filters = mf.get_filter(User)
print('查询参数构造:', filters)
# orm查询 使用分页获取data需要.items
user_pagination = User.query.filter(filters).order_by(User.id.desc()).paginate(page=page_number, per_page=page_limit)
# 序号
count = (page_number - 1) * page_limit
# 对应到前端html的data
for item in user_pagination.items:
count += 1
item_data = {
"id": count,
"userid": item.id_number,
"name": item.name,
"department": item.department,
"position": item.position,
"email": item.email,
"status": item.user_status,
"role": item.role_cn,
}
data_list.append(item_data)
return jsonify({"code": 0, "msg": "", "count": user_pagination.total, "data": data_list})
if __name__ == '__main__':
app.run(host='0.0.0.0', port=5000, debug=True)
2.这里构建了一个查询参数对象ModelFilter，当查询条件不为空时，则增加到参数构建filters变量中，可以快速完成多种条件的查询，减少SQL代码
3.支持分页查询
三.页面截图
