用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加引号了,然后就好了。

六、优化方向

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

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

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

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180918G1WJWY00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券