Python实现监控共享盘excel文件上传并插入数据至MySQL数据库（简易数据补录系统实现方案）

一、业务场景

对于数据分析平台来说，线下数据入仓（数据补录）是必不可少的组件，常用在用户自行上传指标类数据，或线下手工数据上传等场景中。本文主要介绍一种采用Python脚本监控Linux共享盘目录，采用配置表方式实现通用化文件上传监控以及数据入仓的解决方案。用户将excel文件直接复制到共享盘目录中，脚本即可自动监控文件创建动作，并运行后续excel导入程序，处理结果会通过钉钉UMS接口进行消息通知，具有灵活方便、使用场景广泛的特点。

二、用到的包

1.xlrd

python中用于Excel文件读取的包，在线文档地址：https://xlrd.readthedocs.io/en/latest/

主要方法：

    file = xlrd.open_workbook(file_path) #打开Excel文件
    sheet_1 = file.sheet_by_index(0) #根据sheet页的排序选取sheet
    row_content = sheet_1.row_values(3) #获取指定行的数据，返回列表，排序自0开始
    row_number = sheet_1.nrows #获取有数据的最大行数
    col_number = sheet_1.ncols #获取有数据的最大列数

2.pyinotify

pyinotify模块用来监测文件系统的变化，依赖于Linux内核的inotify功能，inotify是一个事件驱动的通知器，其通知接口从内核空间到用户空间通过三个系统调用。pyinotify结合这些系统调用，提供一个顶级的抽象和一个通用的方式来处理这些功能。

¹git网址：https://github.com/seb-m/pyinotify

pyinotify的主要监控动作如下表：

主要方法：

#!/usr/bin/env python
# _*_ coding:utf-8 _*_

import os

from pyinotify import WatchManager, Notifier, ProcessEvent, IN_DELETE, IN_CREATE, IN_MODIFY


class EventHandler(ProcessEvent):
    """事件处理"""

    def process_IN_CREATE(self, event):
        print("Create file: % s" % os.path.join(event.path, event.name)) # 创建事件触发内容

    def process_IN_DELETE(self, event):
        print("Deletefile: % s" % os.path.join(event.path, event.name)) # 删除事件触发内容

    def process_IN_MODIFY(self, event):
        print("Modifyfile: % s" % os.path.join(event.path, event.name)) # 修改事件触发内容


def FSMonitor(path):
    wm = WatchManager() #创建监控实例

    mask = IN_DELETE | IN_CREATE | IN_MODIFY # 监控的事件类型

    notifier = Notifier(wm, EventHandler()) # 绑定一个事件

    wm.add_watch(path, mask, auto_add=True, rec=True) # 添加监控的对象，如有文件夹自动添加监控

    print('now starting monitor % s' % (path))


    while True:

        try:
            notifier.process_events()

            if notifier.check_events():

                notifier.read_events()

        except KeyboardInterrupt:

            notifier.stop() # 持续监控

            break

if __name__ == "__main__":
    FSMonitor('/root') # 监控目录

三、上传脚本功能开发思路

1.主要考虑的功能

a.excel表头兼容

excel功能强大，用户可能会采用复杂表头样式，因此在确定好上传模板后需要配置该模板的表头行数，如遇复杂表头，可在数据处理过程中自动忽略表头行的数据。

b.上传结果通知

文件上传成功或失败需要用户感知，结合公司现状，采用了钉钉UMS消息接口的方式对上传结果进行钉钉通知，包括上传成功、文件格式错误、命名格式错误等消息提示，以及由于其他系统问题，对IT人员的提示，具体通知人员也在配置表中一并配置，并且可以在配置表中配置多个通知对象，以逗号隔开。

def ding_message(status, user_name, content):
    ding_url = r'http://ums.websit.com:8080/ums/message'
    headers = {
        "Content-Type": "application/json"
    }
    user_name_list = user_name.split(',') # 多个对象切分为列表
    for i in user_name_list: # 每个对象分别通知
        msg = {
            "msgTitle": status,
            "receiver": i,
            "msgBody": content
        }
        requests.post(url=ding_url, headers=headers, data=json.dumps(msg))
    return 0

c.插入/更新规则

用户上传数据可能有两种目的，插入新数据或者更新老数据。为了区分这两类场景，采用了规范文件名的方式处理。例如文件名格式规定为：文件头_文件尾.xlsx。用户插入数据时，文件头的部分存储在目标表中上传文件名字段。当用户上传新的excel表时，会首先根据文件头字段执行一次删除操作，然后进行数据的插入，以此实现插入/更新的效果。文件尾可用于给用户标记版本用。

d.通用性

系统的通用性是重点考虑的问题，要保证每次新增上传需求时，只用较少的配置实现。本方案采用一张MySQL配置表解决该问题，将上传目录与目标表名配置到表中，做一个绑定关系，新增需求只要配置表里维护一下即可。目前使用的配置表格式如下：

CREATE TABLE `apd_dir_table_mapping` (
  `dir_name` varchar(255) NOT NULL COMMENT '服务器目录地址',
  `table_name` varchar(100) NOT NULL COMMENT '插入表名',
  `head_row` tinyint(4) NOT NULL COMMENT '表头行数',
  `it_user_name` varchar(100) DEFAULT NULL COMMENT 'it通知人',
  `dept_user_name` varchar(100) DEFAULT NULL COMMENT '业务通知人',
  KEY `apd_dir_table_mapping_dir_name_IDX` (`dir_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

该配置主要涵盖了目录地址与插入表名关系，需要忽略的表头行数，以及it和业务通知人。

2.excel文件处理

excel文件依旧是采用经典的xlrd包进行读取，每行数据为一个元组，循环读取所有记录，插入至目标表中。

data = xlrd.open_workbook(pathname)
        sheet_1 = data.sheets()[0]
        nrows = sheet_1.nrows
        ncols = sheet_1.ncols
        print(time.strftime("%Y/%m/%d %H:%M:%S", time.localtime()),' open excel success!')
        if nrows < head_row:  # 判断有效行数是否小于表头行
            status = '[%s]上传失败' % file_name
            ding_message(status, dept_user_name, '未检测到有效数据，请检查上传文件内容或联系管理员')
            print(time.strftime("%Y/%m/%d %H:%M:%S", time.localtime()), ' 未检测到有效数据，请检查上传文件内容或联系管理员...')
        else:
            data_list = []
            for i in range(head_row, nrows):
                row_list = []
                for j in range(ncols):
                    # print(sheet_1.cell_value(i, j))
                    row_list.append(None if sheet_1.cell_value(i, j) == '' else str(sheet_1.cell_value(i, j)).strip()) # 去除字符串左右两边空格或tab
                row_list.append(file_name_format)
                row_list.append(int(time.time()))
                data_list.append(tuple(row_list))
            excel_data = tuple(data_list)

3.程序启动及日志记录

因为pyinotify包的应用环境为Linux环境，所以需要在Linux服务器上创建共享目录。然后将Python脚本上传至同一服务器上。启动脚本可以使用Linux的nohup命令，将脚本日志输出至指定文件中。

nohup python3 -u /opt/script/py_script/upload_monitor.py >> /opt/script/py_script/monitor.out &

4.主程序逻辑结构

以上功能组合，最终的主程序逻辑结构如下图：

四、用户约束

1.文件规范

a.规范文件名

文件名必须遵从：文件头_文件尾.xlsx的命名格式，以确保上传正确的文件格式，以及判断插入/更新。

b.规范文件内容

数据内容不可有合并单元格，表头由于配置了忽略，出于格式考虑可以做合并。

2.上传规范

由于Linux跟windows之间的差异性，用户需要将处理好的文件直接复制到共享盘，而不能在共享盘中进行操作，直接进行改名或者excel内容编辑是无法获取到对应变化的。

3.配置人员规范

a.建表规范

• 上传表字段顺序需要与excel表顺序一致。
• 为了实现插入/更新,以及记录数据插入时间，表末尾固定自行新增两列filename/insert_timestamp 用于记录上传文件名，以及插入时戳。
• 如业务有主键需求，可以在建表时设置唯一主键或组合键，进一步控制用户行为，保证数据准确性。

五、优化方向

• 可在配置表新增数据库IP等配置，以满足往不同数据库中插入数据的需求。
• 后续公司上线了dataworks工具，可以结合pyodps实现将数据上传至maxcompute端。由于MySQL与maxcompute原理不同，在maxcompute的插入/更新操作需要重新开发。

参考博客：
用pyinotify监控Linux文件系统
Python模块学习 - pyinotify

---

1. pymysql为python3版本下的MySQL数据库处理包，python2版本的包名为MySQLdb，具体用法请自行搜索。 ↩︎