Flask03-小需求实践

需求描述：

目前有一些报表类的excel文档数据，数据量非常多，不方便查看和搜索。希望将这些所有的历史数据存在数据库，并在前端显示，同时提供搜索和绘图功能，方便查看。

实际的表涉及隐私且数据量大就不放出来了，简化版如下：
下表中除‘合计’一列不用读取外，其它数据都需要读取。

image.png

功能梳理如下：

1. 设计数据库（sqlite）
2. 从现有的报表类的excel文档记录中读取数据（openpyxl），存到数据库
3. 使用flask实现从数据库拿所有数据，并显示在前端
4. 前端提供根据某几个字段搜索的功能，显示搜索后的条目
5. 前端展示搜索后条目的折线图（echarts）

---

实现步骤如下

一，设计数据库

根据excel表，设计数据库如下。projects存项目基本信息，entries存每个项目每月的数据。

create table projects (
    project_id integer primary key autoincrement,
    company string not null,
    sites string not null,
    phase string not null,
    capacity real not null,
    prod_date string not null,
    t_year integer not null
);


create table entries (
    entry_id integer primary key autoincrement,
    project_id integer not null,
    t_month string not null,
    r_num REAL not null
);

创建一个访问数据库的类，可提供数据库相关的操作，包括：

• 连接数据库：connect_db()
• 初始化数据库（新建表）：init_db()
• 保存项目相关信息到projects： save_project(data)
• 保存项目每月数据到entries：save_entry(data)
• 根据project的信息从数据获取entries的数据：getEntries(cur)

二，openpyxl读excel文档，存数据库

这里记录一下openpyxl常用的命令：

excel = openpyxl.load_workbook(xlpath, data_only=True)

# 读文档，xlpath为文档路径，data_only为True表示只读数据，比如有些单元格是公式算出来的，这里就会读取算出来的值

excel.worksheets # 获取文档所有的表

sheet.sheet_state # sheet表的状态，比如visible，hidden

excel.close() # 关闭文档

mergeCells = xl.merged_cells.ranges # 获取表xl中合并的单元格范围

mergeCells[0].bounds # 返回min_col, min_row, max_col, max_row；也可直接.min_col这样读取

从excel读数据并存到数据库的代码如下：

import openpyxl
import accessDB


def openExcel(xlpath):
    # 读取xlsx文档，返回excel对象
    excel = openpyxl.load_workbook(xlpath, data_only=True)
    return excel


# 从表xl中获取数据
def getData(xl):
    projects = []
    entries = []      # 按照数据库设计定义这两个变量
    db = accessDB.MyDB().connect_db()   # 连接数据库
    # 获取数据库中projects/entries表当前的条数，+1后得到将要使用的id
    project_id = db.cursor().execute('select count(*) from projects').fetchall()[0][0] + 1
    entry_id = db.cursor().execute('select count(*) from entries').fetchall()[0][0] + 1

    mergeCells = xl.merged_cells.ranges     # 获取表xl中合并的单元格范围
    # 判断第一个合并单元格的范围（第一个合并的范围一般是我们需要读的‘公司’，比如test表中的‘北京’）。需读取的最大行赋值给lines
    # 如果这个范围的min_col==max_col，则读取1到max_row这些行数的数据即可；不是，则读取1到min_row-1这些行数的数据。
    if mergeCells[0].min_col == mergeCells[0].max_col:
        lines = mergeCells[0].max_row
    else:
        lines = mergeCells[0].min_row - 1

    # 对合并的单元格处理。比如：合并单元格范围为A2:A4，值为aaa，直接读A3和A4时会拿到null；这里处理是将A2的值赋给A3和A4
    for i in mergeCells:
        # print(i.bounds)  # min_col, min_row, max_col, max_row
        for j in range(i.min_row, i.max_row):
            xl.cell(row=j + 1, column=i.min_col).value = xl.cell(row=i.min_row, column=i.min_col).value

    for row in list(xl.rows)[1: lines]:     # 从第1行道最大行读数据
        project = [project_id]
        for i in range(0, 19):      # 只读19列的数据
            entry = [entry_id]
            if i < 6:       # 前6列的数据存在project中
                if row[i].value is None:
                    row[i].value = 'null'
                project.append(row[i].value)
            elif i > 6 & i < 19:    # 从7到18列的数据存在entry中
                if row[i].value is None:
                    row[i].value = 0
                entry.append(project_id)
                entry.append(i-6)   # 存月份
                entry.append(row[i].value)  # 存月份对应的值
                entry_id = entry_id + 1
                print(entry)
                entries.append(entry)
        project_id = project_id + 1
        # print(project)
        projects.append(project)
    return projects, entries


excel = openExcel('test.xlsx')

myDB = accessDB.MyDB()
myDB.init_db()      # 初始化数据库。后续如果是单纯添加数据，不用这一步

for sheet in excel.worksheets:
    if sheet.sheet_state == 'visible':      # 只读visible的表
        projects, entries = getData(sheet)
        for project in projects:
            myDB.save_project(project)      # 保存到数据库中projects表
        for entry in entries:
            myDB.save_entry(entry)          # 保存到数据库中entries表

excel.close()

三，flask实现从数据库拿数据，显示在前端

flask的运用是直接在官网实例flaskr上修改的

# -*- coding: utf-8 -*-
# all the imports
from flask import Flask, request, session, g, redirect, url_for, abort, \
     render_template, flash,jsonify
import accessDB

# create application
app = Flask(__name__)
app.config.from_pyfile('FLASKR_SETTINGS.py')


@app.before_request
def before_request():
    g.myDB = accessDB.MyDB()
    g.db = g.myDB.connect_db()
    # 从数据库获取各搜索框需要显示的内容。从数据库获取时使用distinct去重
    g.allCompany = g.myDB.allCompany()
    g.allSites = g.myDB.allSites()
    g.allPhase = g.myDB.allPhase()
    g.allYear = g.myDB.allYear()


@app.route('/')
def show_entries():
    session['search_entry'] = False
    cur = g.db.execute('select * from projects order by project_id asc').fetchall()
    entries = g.myDB.getEntries(cur)
    return render_template('show_entries.html', entries=entries, allCompany=g.allCompany, allSites=g.allSites, \
                           allPhase=g.allPhase, allYear=g.allYear)

对应的在show_entries.html页面上修改展示代码

  <table border="1" class="table table-bordered">
    <tr><th>#</th><th>公司</th><th>分场</th><th>项目期</th><th>容量</th><th>日期</th><th>年</th>
        {% for m in range(1,13) %}
            <th>{{m}}月</th>
        {% endfor %}
    </tr>
  {% for entry in entries %}
    <tr>
      {% for i in entry %}
        <td>{{ i }}</td>
      {% endfor %}
    </tr>
  {% endfor %}
  </table>

为了使表格更好看，引入了bootstrap，在html的head里加入以下代码即可。也可下载bootstrap的包放在本地。
<link rel="stylesheet" href="//apps.bdimg.com/libs/bootstrap/3.3.0/css/bootstrap.min.css">

四，搜索功能

搜索功能，须在前端获取搜索的文本，然后进行post请求。

@app.route('/search', methods=['GET', 'POST'])
def search_entry(test=None):
    session['search_entry'] = True
    key_name = '%'+request.form['company']+'%'
    key_site = '%'+request.form['sites']+'%'
    key_phase = '%'+request.form['phase']+'%'
    key_year = '%'+request.form['year']+'%'
    cur = g.db.execute('select * from projects where company like ? and sites like ? and phase like ? \
                and t_year like ? order by project_id asc', [key_name, key_site, key_phase, key_year]).fetchall()
    entries = g.myDB.getEntries(cur)

    # 根据搜索出来的数据整理绘图数据
    name = []
    data = []
    for entry in entries:
        na = entry[1]+entry[2]+str(entry[6])+entry[3]
        name.append(na)

        da = []
        for i in range(7, 19):
            da.append(entry[i])
        data.append(da)
    chartData = {'name':name, 'data':data}

    return render_template('show_entries.html', chartData=chartData, entries=entries, allCompany=g.allCompany, allSites=g.allSites, \
                           allPhase=g.allPhase, allYear=g.allYear)

前端代码中，下拉选项框中的数据从allCompany这些数据获取就可以了。

（刚开始只简单的做了个文本框去搜索，会更简单，不需从数据库获取下拉选项框的内容，不过当数据量很大时，对用户来说就很麻烦了）

五，折线图

使用echarts实现，官方下载后包放在本地项目static文件夹即可。html中加入以下代码。

<script src="{{ url_for('static', filename='echarts.min.js') }}"></script>

echarts代码从官网实例中拷贝过来在改改就ok了。主要是折线图的数据传入。

{% autoescape false %}
{% if session.search_entry %}
    <!-- 为ECharts准备一个具备大小（宽高）的Dom -->
<div id="main" style="width: 1200px;height:400px;"></div>
    <script type="text/javascript">
        // 基于准备好的dom，初始化echarts实例
        var myChart = echarts.init(document.getElementById('main'));

        // 折线图的数据传入
        var result = {{chartData}};
        var series = [];
        for(var i=0;i<result['name'].length;i++){
            series.push({
            name: result['name'][i],
            type: 'line',
            data: result['data'][i]
            });
        }

        // 指定图表的配置项和数据
        var option = {
        tooltip: {
            trigger: 'axis'
        },
        legend: {
            data:result['name']
        },
        grid: {
            left: '3%',
            right: '4%',
            bottom: '3%',
            containLabel: true
        },
        xAxis: {
            type: 'category',
            boundaryGap: false,
            data: ['一月','二月','三月','四月','五月','六月','七月','八月','九月','十月','十一月','十二月']
        },
        yAxis: {
            type: 'value'
        },
        series: series
        };

        // 使用刚指定的配置项和数据显示图表。
        myChart.setOption(option);
    </script>
{% endif %}
{% endautoescape %}

因为flask中数据传到前端会有一个自动转义的过程，导致单引号双引号会被转义成"和'，json格式的数据就处理不了了。

这里将代码放在{% autoescape false %} {% endautoescape %}之间可取消自动转义，就可以处理json数据了。

六，最终效果

本地运行后，访问127.0.0.1:5000如下图，和excel中的数据一致。

image.png

选择下拉框，搜索：北京-分1-一期，点击Search按钮后显示如下

image.png

选择下拉框，搜索：北京-分2，点击Search按钮后显示如下

image.png

另外加了个reset按钮重置搜索，会重新显示所有数据。