Layui 数据表格多条件查询与后端交互

z60015260

已于 2022-06-16 11:25:32 修改

阅读量5k

点赞数 5

分类专栏： Layui flask python 文章标签： layui javascript 前端

于 2022-06-15 15:05:07 首次发布

本文链接：https://blog.csdn.net/qq_27454363/article/details/125296824

版权

Layui 同时被 3 个专栏收录

10 篇文章 4 订阅

订阅专栏

python

10 篇文章 2 订阅

订阅专栏

flask

8 篇文章 0 订阅

订阅专栏

这里记录下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完整代码

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>layui</title>
    <meta name="renderer" content="webkit">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <link rel="stylesheet" href="../static/layui-v2.6.8/layui/css/layui.css">
</head>
<body>
<div class="layuimini-container">
	<fieldset class="table-search-fieldset">
	    <legend>查询条件</legend>
	    <div style="margin: 10px 10px 10px 10px">
	        <form class="layui-form layui-form-pane" action="">
	            <div class="layui-form-item">
	                <div class="layui-inline">
	                    <label class="layui-form-label">工号</label>
	                    <div class="layui-input-inline">
	                        <input type="text" name="userid" autocomplete="off" class="layui-input">
	                    </div>
	                </div>
	                <div class="layui-inline">
	                    <label class="layui-form-label">姓名</label>
	                    <div class="layui-input-inline">
	                        <input type="text" name="username" autocomplete="off" class="layui-input">
	                    </div>
	                </div>
	                <div class="layui-inline">
	                    <label class="layui-form-label">部门</label>
	                    <div class="layui-input-inline">
	                        <input type="text" name="department" autocomplete="off" class="layui-input">
	                    </div>
	                </div>
					<div class="layui-inline">
					    <label class="layui-form-label">职位</label>
					    <div class="layui-input-inline">
					        <input type="text" name="position" autocomplete="off" class="layui-input">
					    </div>
					</div>
					<div class="layui-inline">
					    <label class="layui-form-label">状态</label>
					    <div class="layui-input-inline">
							<select name="status" >
								<option value=""></option>
							    <option value="1">在线</option>
							    <option value="0">离线</option>
							</select>
					    </div>
					</div>
	                <div class="layui-inline">
	                    <label class="layui-form-label">角色</label>
	                    <div class="layui-input-inline">
							<select name="role" id="role" class="layui-input">
								<option value=""></option>
								<option value="普通用户">普通用户</option>
								<option value="管理员">管理员</option>
								<option value="超级管理员">超级管理员</option>
							</select>
	                    </div>
	                </div>
	                <div class="layui-inline">
						<button type="submit" class="layui-btn layui-btn-normal"  lay-submit lay-filter="data-search-btn"><i class="layui-icon"></i> 查 询</button>
						<button type="reset" class="layui-btn layui-btn-primary"><i class="layui-icon layui-icon-refresh"></i> 重 置</button>
	                </div>
	            </div>
	        </form>
	    </div>
	</fieldset>

    <table class="layui-hide" id="currentTableId" lay-filter="currentTableFilter"></table>
	
	<script type="text/html" id="user-enable">
	    {{ '{{#if (d.status == 1) { }}
	    <span style="color: green">在线</span>
	    {{# }else if(d.status == 0){ }}
	    <span style="color: gray">离线</span>
	    {{# } }}'|safe }}
	</script>

</div>

<script src="../static/layui-v2.6.8/layui/layui.js" charset="utf-8"></script>

<script>
    layui.use(['form', 'table'], function () {
        var $ = layui.jquery,
            form = layui.form,
            table = layui.table;
        table.render({
            elem: '#currentTableId',
            url: '/user-query',
			method:'post',
            defaultToolbar: [{ layEvent: 'refresh', icon: 'layui-icon-refresh'}, 'filter', 'exports', 'print'],
            cols: [[
                {type: "checkbox", width: 50},
                {field: 'id', width: 80, title: '序号', align:'center', sort: true},
				{field: 'userid', width: 100, title: '工号', align:'center', sort: true},
                {field: 'name', width: 180, title: '姓名', align:'center', sort: true},
                {field: 'department', width: 180, title: '部门',align:'center', sort: true},
				{field: 'position', width: 180, title: '职位',align:'center', sort: true},
                {field: 'email', title: '邮箱', width: 240, minWidth: 150, align:'center', sort: true},
				{field: 'status', width: 180, title: '状态', align:'center', sort: true, templet: '#user-enable',},
                {field: 'role', width: 180, title: '角色',align:'center', align:'center', sort: true, templet: '#roleCN'},
                {title: '操作', minWidth: 240, toolbar: '#currentTableBar', align: "center"}	
            ]],
            limits: [10, 20, 50, 100],
            limit: 10,
            page: true,
            skin: 'row',
			even: true
        });
		
		// 监听搜索操作
		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;
		});
    });
</script>

</body>
</html>

二.后端使用flask,数据库使用第三方模块flask_sqlalchemy

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.支持分页查询

三.页面截图

z60015260

关注

5
点赞
踩
18

收藏

觉得还不错? 一键收藏
打赏
1
评论
Layui 数据表格多条件查询与后端交互

这里记录下layui数据表格多个查询条件数据提交查询,数据库返回对应数据，数据库使用第三方模块flask_sqlalchemy1.使用table.reload()方法对表格重载2.html完整代码1.flask完整代码2.这里构建了一个查询参数对象ModelFilter，当查询条件不为空时，则增加到参数构建filters变量中，可以快速完成多种条件的查询，减少SQL代码3.支持分页查询......
复制链接

扫一扫