用python读取Excel数据，并插入到MySQL数据库

一、业务场景

最近在工作中遇到这样一个问题：为了系统功能权限设置，收集了若干Excel文件，表中数据样式如下：

其中标三角号的表示需要权限，无权限则无任何数据。

目前初级需求是将姓名，员工号，开通权限的报表编号整理出来，并插入MySQL数据库，插入效果如下图：

二、用到的包

1.xlrd

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

主要方法：

2.pymysql

官方网址：https://pypi.org/project/PyMySQL/

注意：pymysql为python3环境下使用，python2下请使用MySQLdb

主要方法：

三、Excel数据格式化思路

1.数据结构选择

最开始我是没想一步到位，直接整理好然后插数据库的。我是想能不能把Excel里的数据格式化下来，存入文件中。最理想的存储结构就是字典了，把每一行数据都看作是一个字典，行表头作为键，数据作为值。每个报表有权限记为1，无权限记为0。整理出来的结果大概是这样子：

具体代码实现如下：

2.数据存储

本例用的存储方案是使用json.dumps()的格式化存储，将字典转化为字符串存储，使用时再转换回来，实现代码如下：

3.多个文件的情况

如果提报上来的文档有多个，手工输入文件名也是一项繁琐的工作，其实在我的这项工作中就有20个文件左右。所以又使用os包做了一个文档遍历的函数，具体代码如下：

4.主程序

将上述几个函数结合一下，主程序就可以有了：

四、MySQL数据插入思路

1.格式化文本解析

首先再使用json.loads()方法，将字符串解析回来

2.反向查找值为1的报表名

这里用到了字典的反向查找，查找值为1的报表名，并将用户姓名和ID一块写入元组。由于一个用户不止一个报表权限，所以发现一个写一个。

这样我们就可以得到最终要插入数据库的结构了。

3.插入数据库

data_list已经是最终的结构化数据了，而且会有不止一条数据，所以我们用了executemany()这个方法，用于批量执行SQL语句。

4.大功告成

五、注意事项&踩坑

解析Excel出来后，有时数据会出现'/xa0'这个字符，百度以后发现是不间断空白符，解决方案：分割再组合

插入数据库时，有一个报错，提示信息：pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1.00748912842339'', ''0'', ''0'', ''16.114739990234'', ''0.00759455235674977'', ' at line 1")（原报错信息找不到了，搜的我的一个回帖，帖子链接），查了半天，发现是在变量代换的时候，如果本身是字符串，就不用再给%s加引号了，然后就好了。

六、优化方向

因为在最开始做的时候思路不连贯，所以做了一个格式化存储程序，一个读取再插库程序，其实可以在最开始判断单元格是否为空时，就将权限梳理出来，省去中间的转换过程。

另外在实际业务中，有三种权限收集模板，主要区别是报表名称的行编号不同，其他一致。这个也是小改动了，根据文件名做了模板判断，然后在解析时读取不同的行就好了。

咸鱼的杂七杂八，分享一条咸鱼的所思所想