大作业 数据清洗（清洗结果的展示与导出）

点击结果查看并导出即可查看清洗后的结果，清洗后数据会先保存到数据库中，然后可以通过穿梭框将要导出的属性列导出为excel

 

 

文件导出利用是原先的原始表数据导出,与之前的原理一摸一样，保存到数据库也与最一开始的文件上传并导入到数据库原理一样

#清洗数据存入数据库
def data_clean_save(data_clean,table_name,database_name):
    flag=1
    conn,cursor=get_conn_mysql_name(database_name)
    sql="DROP TABLE if EXISTS "+table_name+" ; "
    cursor.execute(sql)
    #判断表是否存在,存在就删除
    sql = " CREATE TABLE " + table_name + " ("
    key_0=data_clean.keys()
    key=""
    for i in key_0:
        key=key+","+i
    key=key[1:]
    j = 0
    for i in key_0:
        sql = sql + i + " TEXT  comment 'null,null',"
        j = j + 1;
    creat_sql = sql[0:-1] + ") ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;"
    print(creat_sql)
    # 获取%s
    s = ','.join(['%s' for _ in range(len(data_clean.columns))])
    # 获取values
    values = []
    for i in data_clean.values.tolist():
        values.append(i)
        # 组装insert语句
    insert_sql = 'insert into {}({}) values({})'.format(table_name, key, s)
    print(insert_sql)
    try:
        cursor.execute(creat_sql)
    except:
        traceback.print_exc()
        flag = 0
        print("表创建失败")
    # # 插入数据
    try:
        for i in values:
            cursor.execute(insert_sql, i)
            print(insert_sql)
            print(i)
        conn.commit()
    except:
        traceback.print_exc()
        flag = 0
        print("写入错误")
    close_conn_mysql(cursor, conn)
    return flag
    pass

#查看最终结果,将结果保存到数据库的bigwork_update_data
@app.route('/get_data_clean_result')
def get_data_clean_result():
    table_name = request.values.get("table_name")
    table_name=table_name+"_clean"
    num_0 = data_clean.shape[0]
    num_1 = data_clean.shape[1]
    data = []
    flag=0
    #存入数据库
    code=dataclean.data_clean_save(data_clean,table_name,"bigwork_update_data")
    #将数据转换为json
    for i in range(num_0):
        json_list = {}
        for j in range(num_1):
            json_list[data_clean.keys()[j]] = data_clean.values[i][j]
        data.append(json_list)
        flag=flag+1
    return jsonify({"code": 0, "msg": code, "count": flag, "data": data})

 

 

<!-- 内容主体区域 -->
        <blockquote class="layui-elem-quote layui-text">
            <h2>数据清洗：{{ table_name }}</h2>
            <h4 style="color: chocolate">已导入到数据库，表名：{{ table_name }}_clean</h4>
        </blockquote>
        <div style="padding: 15px;">
            <table id="demo" lay-filter="test"></table>
        </div>
        <blockquote class="layui-elem-quote layui-text">
            <h2>文件导出</h2>
        </blockquote>
        <div id="export_select" class="demo-transfer"></div>
        <br>
        <form class="layui-form" action="">
            <button type="button" class="layui-btn" id="export" >开始导出</button>
        </form>
        <br>
        <br>
        <br>

<script>
//JS
    data_key=[]
    data_key_select=[]
    layui.use(['element', 'layer', 'util'], function(){
        var element = layui.element
        ,layer = layui.layer
        ,util = layui.util
        ,$ = layui.$;

        //头部事件
        util.event('lay-header-event', {
        //左侧菜单事件
            menuLeft: function(othis){
                layer.msg('展开左侧菜单的操作', {icon: 0});
            }
            ,menuRight: function(){
                layer.open({
                    type: 1
                    ,content: '<div style="padding: 15px;">处理右侧面板的操作</div>'
                    ,area: ['260px', '100%']
                    ,offset: 'rt' //右上角
                    ,anim: 5
                    ,shadeClose: true

                });
            }
        });

    });
    layui.use('table', function(){
        var table = layui.table
        //第一个ajax获取表的详细数据以及重复值
        $.ajax({
             type: "GET",
             url: "/get_clean_result_key?table_name={{ table_name }}&database_name={{ database_name }}",
             dataType: "json",
             success: function(data){
                 for (i=0;i<data.len;i++){
                     data_key[i]={field:data.data[i],title:data.data[i],width:120}
                 }
                 // 设置key值
                 table.render({
                     elem: '#demo'//以此来区分不同的表格
                     ,height: 430
                     ,url: '/get_data_clean_result?table_name={{ table_name }}&database_name={{ database_name }}' //数据接口
                     ,page: false //开启分页
                     ,cols: [data_key]
                 });
             }
         });
        //获取缺省值信息
    });
    layui.use(['transfer', 'layer', 'util'], function(){
        var $ = layui.$
        ,transfer = layui.transfer
        ,layer = layui.layer
        ,util = layui.util;
        //获取穿梭框的值
        $.ajax({
             type: "GET",
             url: "/get_clean_result_key?table_name={{ table_name }}&database_name={{ database_name }}",
             dataType: "json",
             success: function(data){
                 for (i=0;i<data.len;i++){
                     data_key_select[i]={value:data.data[i], title: data.data[i], disabled: "", checked: ""}
                 }
                 //定义标题及数据源
                 transfer.render({
                    elem: '#export_select'
                    ,title: ['属性列', '导出列']  //自定义标题
                    ,data: data_key_select
                    //,width: 150 //定义宽度
                    ,height: 450 //定义高度
                    ,id: 'export_select_data'
                 })
             }
         });
        $('#export').click(function(){
            //url="http://127.0.0.1:5000/export?table_name={{ table_name }}&database_name={{ database_name }}"
            //window.open(url)
            var getData = transfer.getData('export_select_data');
            //将数据进行拼接
            var str=""
            for(i=0;i<getData.length;i++){
                str=str+getData[i].value+","
            }
            $.ajax({
                type: "GET",
                url: "/export_select",
                data:{getData_str:str,table_name:"{{table_name}}_clean",database_name:"bigwork_update_data"},
                dataType: "json",
                success: function(data){
                    if(data.flag==1){
                        window.open("http://127.0.0.1:5000/export_select_download?table_name={{ table_name }}_clean")
                    }else if(data.flag==0){
                        alert("导出失败")
                    }
                }
            });
        });
    });
</script>