提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  APP

当前栏目

基于flask和bootstrap-table的通用数据查询

2023-02-18 16:26:49 时间

基于flask和bootstrap-table的通用数据查询，大体思路是构建两张字典表，一张表存表信息，一张表存字段信息，字段信息表包含了字段的字段名、字段类型、字段长度、是否主键、是否为空、显示顺序、查询标志等等。

代码其实很简单，包括各数据的查询，路由和前端展示。

第一步，记录集转字典 

def getsqlresultdict(db, sql,params={}):
    """根据db和sql语句，将结果集转换为json格式
    根据db和sql语句，将结果集转换为json格式
    第一步：根据cursor获取元数据，生成键值列表
    第二步：遍历结果集，将键值列表和结果集组装成字典，加入列表
    第三步：将列表通过DataEncoder进行转码
    入参:
        db: 数据库实例.
        sql: 待运行的SQL语句
    出参:
        字典格式:
        举例：        {'Serak': ('Rigel VII', 'Preparer'),
          'Zim': ('Irk', 'Invader'),
         'Lrrr': ('Omicron Persei 8', 'Emperor')}
    异常:
        无    """
    resultdict = []

    cursor = db.session.execute(sql,params=params).cursor
    resultproxy = db.session.execute(sql,params=params).fetchall()

    # 获取元数据
    colname = [i[0] for i in cursor.description]

    # 获取结果集，组成字典，加入列表
    for rowproxy in resultproxy:
        rowresult = dict(zip(colname, rowproxy))
        resultdict.append(rowresult)

    return resultdict

第二步，包括表注释信息获取，字段信息获取（参见前文），表数据信息获取 

def gettableinfo(tablename):
    sqltable="select tablename,tablecomment from metatable where tablename=:tablename"
    params = {"tablename": tablename}
    tableinfo = getsqlresultdict(db, sqltable, params)
    return tableinfo[0]["tablecomment"]

def gettablecolumninfo(tablename):
    sqlcolumn="select tablename,columnname,columnorder,displayorder,queryorder,queryformatter," \
              "columntypename,columntype,isnullable,isprimarykey," \
              "columnLength,numericprecision,numericscale,columncomment" \
              "  from metacolumn where tablename=:tablename"
    params = {"tablename": tablename}
    tablecolumninfo = getsqlresultdict(db,sqlcolumn, params)
    return tablecolumninfo

def gettabledata(tablename,displaycolumnname):
    sqldata="select {} from {} ".format(displaycolumnname,tablename)
    params = {}
    tabledata = getsqlresultdict(db,sqldata, params)
    return tabledata

第三步，定义路由和前端返回值 

@app.route('/dataoperatev1/<tablename>')
def dataoperatev1(tablename):

    tableinfo=gettableinfo(tablename)

    tablecolumninfo=gettablecolumninfo(tablename)
    displaycolumn= sorted(tablecolumninfo, key=lambda r: r['displayorder'] if r['displayorder'] is not None else 0)
    displaycolumn = [item for item in displaycolumn if item['displayorder'] is not None]
    # 输出到前端的字段列表
    displaycolumnlist = [{"field":item["columnname"],"title":item["columncomment"],"sortable":True} for item in displaycolumn]
    # 要拼成SQL语句的列表
    displaycolumnname = ','.join([item['columnname'] for item in displaycolumn])

    tabledata = gettabledata(tablename,displaycolumnname)

    return render_template('dataoperatev1.html',tableinfo=tableinfo,data=tabledata,columns=displaycolumnlist)

第四步，前端页面 

<!DOCTYPE html>
<html>
   <head>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      <meta charset="utf-8" />
      <meta http-equiv="X-UA-Compatible" content="IE=edge">
      <meta content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no" name="viewport">
      <title>管理系统</title>
      <link rel="stylesheet" href="{{ url_for('static',filename='AdminLTE-Iframe/plugin/bootstrap-4.6.1/css/bootstrap.min.css') }}">
      <link rel="stylesheet" href="{{ url_for('static',filename='AdminLTE-Iframe/plugin/bootstrap-4.6.1/font/bootstrap-icons.css') }}">
      <link rel="stylesheet" href="{{ url_for('static',filename='AdminLTE-Iframe/plugin/font-awesome/css/font-awesome.min.css') }}">
      <link rel="stylesheet" href="{{ url_for('static',filename='AdminLTE-Iframe/plugin/Ionicons/css/ionicons.min.css') }}">
      <link rel="stylesheet" href="{{ url_for('static',filename='AdminLTE-Iframe/plugin/bootstrap-table/bootstrap-table.min.css') }}">
      <link rel="stylesheet" href="{{ url_for('static',filename='AdminLTE-Iframe/adminlte/css/AdminLTE.min.css') }}">
      <link rel="stylesheet" href="{{ url_for('static',filename='AdminLTE-Iframe/adminlte/css/skins/all-skins.min.css') }}">
      <link rel="stylesheet" href="{{ url_for('static',filename='AdminLTE-Iframe/system/css/common.css') }}">
      <link rel="stylesheet" href="{{ url_for('static',filename='AdminLTE-Iframe/layui/css/layui.css') }}">
      
      <script type="text/javascript" src="{{ url_for('static', filename='AdminLTE-Iframe/plugin/jquery/jquery.min.js') }}"></script>
      <script type="text/javascript" src="{{ url_for('static', filename='AdminLTE-Iframe/plugin/bootstrap-4.6.1/js/popper.min.js') }}"></script>
      <script type="text/javascript" src="{{ url_for('static', filename='AdminLTE-Iframe/plugin/bootstrap-4.6.1/js/bootstrap.min.js') }}"></script>
      <script type="text/javascript" src="{{ url_for('static', filename='AdminLTE-Iframe/layui/layui.js') }}"></script>
      <script type="text/javascript" src="{{ url_for('static', filename='AdminLTE-Iframe/plugin/bootstrap-table/bootstrap-table.min.js') }}"></script>
      <script type="text/javascript" src="{{ url_for('static', filename='AdminLTE-Iframe/plugin/bootstrap-table/locale/bootstrap-table-zh-CN.min.js') }}"></script>
      <script type="text/javascript" src="{{ url_for('static', filename='AdminLTE-Iframe/plugin/fastclick/fastclick.js') }}"></script>
      <script type="text/javascript" src="{{ url_for('static', filename='AdminLTE-Iframe/adminlte/js/adminlte.min.js') }}">  </script>
      
      <style type='text/css'>
         .row-index { width: 50px; display: inline-block; }
      </style>
      
      <script type='text/javascript'>
         $(window).load(function() {
            var data = {{  data | tojson }};
            var columns = {{   columns | tojson   }};
            $(function() {
               $('#table').bootstrapTable({
                  data: data,
                  columns: columns,
               });
            });
         });
      </script>
   </head>
   <body class="hold-transition sidebar-mini fixed skin-blue">
      <div class="card-body" style="padding-bottom: 0px;" id="shouRu">
         <div class="card">
            <div class="card-header" style="font-size: 20px; text-align: center;">{{ tableinfo }}表查询</div>
         </div>
         <table id="table" 
            data-toggle="true" 
            data-toolbar="#toolbar" 
            data-search="true"
            data-show-columns="true" 
            data-pagination="true">
         </table>
      </div>
   </body>
</html>

相关文章