python入门学习（excel导入mysql）

背景说明：

基于Jira设计了一套研发的KPI考核的机制，主要采用任务+工时的形式，分为工作量+质量+规范的考核体系。

数据都从Jira导出，经过规则计算后，生成对应的每个人的考核数据和分值。

现在是前期探索，所以可能中间算法或者数据来源都有变更，目前还不适合编码固定算法，所以现在先使用excel的透视表功能。

将基础数据放在A.xlsx，透视表在B.xlsx中，数据源指定到A。这样以后A的基础数据更新后，B直接刷新透视表即可变为最新数据。

现在问题是员工希望了解自己的基础数据，在excel中不太方便查询，所以想要自动导入数据库中，通过BI系统可以让员工自行查看分析基础数据。

前置条件：

Jira和BI都是现有的系统，没有研发人员，基础数据A是由研发助理手动处理的。我设想最简单的方法就是让助理通过web直接上传至服务器，服务器上写一个脚本解析excel并且插入对应的数据表中，BI就可以直接连接数据库提供查询界面了。

shell脚本解析和操作数据库比较困难，所以就把目光放在了python身上。

硬件环境：

• 操作系统：CentOS 6.5

• 数据库：Mysql 5.6.41

• 编程语言：Python 2.7.3 Python 开发者年度调查报告,Python3 采用率超 84% 推荐使用Python3

• 编码工具：Vim (只是一段很小的脚本，就直接在服务器上用vim编写了，但是linux默认的颜色渲染还是很好的，如图)

d38488c7-cca3-489e-a053-70d6193b42cd.png

编码过程要点

1. 头部引入

import MySQLdb as mdb # mysql

import os

import sys

import re

import xlrd # 读取 excel

引用要产生效果，需要安装对应的包

python提供了很多的外部包调用来解决问题，我们本次的重点是excel 和myslq 所以需要引用 MySQLdb 和 xlrd，在系统命令行执行以下命令即可：

pip install MySQL-python

pip install xlrd

这样就安装了mysql和excel的支持

2. excel读取

wb = xlrd.open_workbook('%s%s' %(filepath,excel.split(',')[0]))

s01 = wb.sheet_by_name('S01-本月workpoint原始数据'.decode('utf-8'))

s02 = wb.sheet_by_name('S02-上月跨本月工时原始数据'.decode('utf-8'))

s03 = wb.sheet_by_name('S03-本月跨下月工时原始数据'.decode('utf-8'))

s04 = wb.sheet_by_name('S04-在线问题工时原始数据'.decode('utf-8'))

一行代码 xlrd.open_workbook 就直接打开了excel ，wb.sheet_by_name就直接将行列数据读取为数组，完全没有废话。

3. mysql写入

mysql的操作比较常规，主要分为两大块，连接mdb.connect()，游标connect.cursor()

connect负责建立连接通道，cursor负责具体操作执行

# 建立连接

conn = mdb.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='dbname', charset='utf8')

# 使用cursor()方法获取操作游标

cursor = conn.cursor()

# 执行插入语句

sSql = 'insert into z_kpi_s01 (k_month,col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10,col_11,col_12) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'

cursor.executemany(sSql,values)

4. 整体代码

整体代码中还使用到了：

• for循环

• if判断

• 正则判断

• 打印带参数文字

#!/usr/bin/env python

# -*- coding: utf-8 -*-

import MySQLdb as mdb

import os

import sys

import re

import xlrd

conn = mdb.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='dbname', charset='utf8')

# 使用cursor()方法获取操作游标

cursor = conn.cursor()

# 因该模块底层其实是调用CAPI的，所以，需要先得到当前指向数据库的指针。

try:

    # 读取Excel文件，并且确认是否需要导入

    needImportXlsx = []

    filepath = '/data/'

    pathDir = os.listdir(filepath)

    print 'list size is : %s' % len(pathDir)

    for allDir in pathDir:

        fnCount = cursor.execute('select filename from fileattachment where id=%s' % allDir)

        filename = cursor.fetchone()[0]

        canMatch = re.match(r'\d{6}', filename)

        if not (canMatch):

            continue

        tmpCount = cursor.execute('select * from z_kpi_s01 where k_month=%s' % filename)

        print '%s tmp count is : %s ' % (filename, tmpCount)

        if tmpCount == 0:

            needImportXlsx.append(allDir + ',' + filename)

    if len(needImportXlsx) == 0:

        print '!!!no file need import!!!'

        sys.exit()

    print 'all need import xlsx are : %s ' % needImportXlsx

    for excel in needImportXlsx:

        wb = xlrd.open_workbook('%s%s' % (filepath, excel.split(',')[0]))

        s01 = wb.sheet_by_name('S01-本月workpoint原始数据'.decode('utf-8'))

        s02 = wb.sheet_by_name('S02-上月跨本月工时原始数据'.decode('utf-8'))

        print 's01 has col %d row %d \n s02 has col %d row %d ' % (

            s01.ncols, s01.nrows, s02.ncols, s02.nrows)

        # s01 insert mysql

        values = []

        for i in range(s01.nrows):

            if i == 0:

                continue

            value = s01.row_values(i)[:12]

            value.insert(0, excel.split(',')[1])

            values.append(value)

        sSql = 'insert into z_kpi_s01 (k_month,col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10,col_11,col_12) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'

        cursor.executemany(sSql, values)

        # s02 insert mysql

        values = []

        for i in range(s02.nrows):

            if i == 0:

                continue

            value = s02.row_values(i)[:31]

            value.insert(0, excel.split(',')[1])

            values.append(value)

        sSql = 'insert into z_kpi_s02 (k_month,col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10,col_11,col_12,col_13,col_14,col_15,col_16,col_17,col_18,col_19,col_20,col_21,col_22,col_23,col_24,col_25,col_26,col_27,col_28,col_29,col_30,col_31) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'

        cursor.executemany(sSql, values)

    # 如果没有设置自动提交事务，则这里需要手动提交一次

    conn.commit()

except:

    import traceback

    traceback.print_exc()

    # 发生错误时会滚

    conn.rollback()

finally:

    # 关闭游标连接

    cursor.close()

    # 关闭数据库连接

    conn.close()

总结

在这个之前还从来没有写过Python，写整体代码大概花了2个小时左右，其他时间花在服务器上面的安装和一些权限上。整体感觉Python的优势很明显，作为一种粘合代码，执行一些Shell脚本比较难实现的功能，是非常方便的。语法也很简单，但是对于我这样的Java开发者有时候习惯性的 {} 和 ; 反而是错误。

参考

主要参考了这两篇文章：

Python连接MySQL数据库

python脚本实现excel和mysql数据库表的导入导出