Pandas模块之Python批量处理Excel导入MySQL

       最近由于比赛需求，需要大量的数据支持系统的功能测试。然后比较坑的是，所有的源数据都是来自Excel文件格式。虽然我们可以用Navicat Premium 12类似的工具导入，但是这类的工具似乎只有一个一个导入excel文件的功能，并不能满足我们有3600多张Excel表的需求呀。

       于是就想到了我们伟大的python，可以说python这种语言用来处理批量数据实在是再好不过了。于是开始了我的踩坑之路。废话不多说，先看看我们的数据。 

       这里图片隐去了部分敏感信息。可以看出数据有7列，而且其中的经纬度的一列需要人为拆分成两部分。接着数据还有一点需要关注的是，在每张表的后面都有一些错误数据，需要人为去除。

        接着就是重头戏了：开始Python的批处理。首先我们需要安装python，在这里本人使用的是python 3.5.4版本，防止其中显示中文乱码的情况，不过python 2.7.X的同学也可以尝试尝试。

       Python的安装这里就不再展示。直接开始Excel处理文件所涉及的一些模块吧。这里先介绍一下Pandas模块，这个模块就是python用来处理Excel的主要模块。如何安装：

      找到自己安装的python路径下的Scripts模块，在这里启动cmd命令行模式并且输入“pip install pandas”：

        然后就回进行安装，网速可能会有点慢，但是无妨。等待安装成功之后，可以通过python状态下的“import pandas”来测试是否安装成功。接着再安装pymysql模块和xlrd模块：

       安装成功后就可以通过如下命令进行测试：

       接着就可以开始Excel批量处理了。直接上代码了，先批量创建数据库表，用来插入Excel中的数据。

import  pymysql
import os
import numpy as np
import pandas as pd
#开启数据库连接
conn = pymysql.connect(
    user = 'root',
    host = 'localhost',
    password= '111',
    db = 'ai_taxi_1',
    port = 3306,
)
#获取文件的上级目录，对应下图的路径
dir=r'F:\原始数据\323_324\二分'
# 此处获取目录中所有的文件名称
file_name=os.listdir(dir)   
#遍历所有文件创建数据库表
for i in range(len(file_name)):
    #此处通过获取源文件的名称并截取其中我们所需求的创建数据库表名称
    file_str=file_name[i][:16]
    sql="""create table """+file_str+"""(id_car char(20) not null,tel char(20),time char(40),speed char(20),dir char(20),status char(20),lon char(20),lat char(20))"""
    cur = conn.cursor()
    cur.execute(sql)
conn.commit()
cur.close()
conn.close()

运行之后创建与目录中对应数量的表。接着对数据库中创建好的表进行插入数据操作，代码如下：

import os
import numpy as np
import pandas as pd
import  pymysql
#开启数据库连接
conn = pymysql.connect(
    user = 'root',
    host = 'localhost',
    password= '111',
    db = 'ai_taxi_1',
    port = 3306,
)
#获取游标
cur = conn.cursor()
#此函数用来插入数据
def add_data(filename,taxi_id,taxi_tel,taxi_time,taxi_speed,taxi_dir,taxi_status,taxi_lon,taxi_lat):
    file_name_handle=filename[:16]
    if(taxi_time=="1899-12-30 00:00:00"):
        return
    sql="""insert into """ + file_name_handle +"""(id_car,tel,time,speed,dir,status,lon,lat) values("""+ "\"" +taxi_id +"\""+","+"\"" +taxi_tel +"\""+","+"\"" +taxi_time +"\""+","+"\"" +taxi_speed +"\""+","+"\"" +taxi_dir +"\""+","+"\"" +taxi_status +"\""+","+"\"" +taxi_lon +"\""+","+"\"" +taxi_lat +"\""+""")"""
    cur.execute(sql)

#此处获取文件所在的目录的上一级
dir=r'F:\原始数据\323_324\二分'
# 此处获取目录中所有的文件名称
file_name=os.listdir(dir)   
#此处获取所有的文件路径
file_dir=[os.path.join(dir,x) for x in file_name]  
#遍历所有文件开始处理Excel
for j in range(len(file_dir)):
    excel=pd.read_excel(file_dir[j]);
    #删除文件的第0到第1行并且保存下来，这里如果不写inplace=True会导致文件修改不成功
    excel.drop([0,1],inplace=True)
    #将Excel的列名转换为我们需要的
    excel.columns=['id','tel','time','latlon','speed','dir','status']
    #获取每列
    id_col=excel.iloc[:,[0]]
    tel_col=excel.iloc[:,[1]]
    time_col=excel.iloc[:,[2]]
    latlon_col=excel.iloc[:,[3]]
    speed_col=excel.iloc[:,[4]]
    dir_col=excel.iloc[:,[5]]
    status_col=excel.iloc[:,[6]]
    #对每一列数据进行处理，从DataFrame类型转换为list类型
    id_list=id_col.values.tolist()
    tel_list=tel_col.values.tolist()
    time_list=time_col.values.tolist()
    latlon_list=latlon_col.values.tolist()
    speed_list=speed_col.values.tolist()
    dir_list=dir_col.values.tolist()
    status_list=status_col.values.tolist()
    #创建对应的字符串用来存储最终数据
    id_str=''
    tel_str=''
    time_str=''
    latlon_str=''
    speed_str=''
    dir_str=''
    status_str=''
    lon_str=''
    lat_str=''
    tel_str_1=''
    speed_str_1=''
    #对每一列的每一行的数据进行转换，转换为str类型
    for i in range(len(id_list)):
        id_list_index = id_list[i]
        tel_list_index = tel_list[i]
        time_list_index = time_list[i]
        latlon_list_index = latlon_list[i]
        speed_list_index = speed_list[i]
        dir_list_index = dir_list[i]
        status_list_index = status_list[i]
        #下面对于time、电话、速度三个单独处理，是由于取出的字段还是list类型，需要再次转换
        #time处理
        #此处time_list_index[0]由于数据问题所以是datatime类型，所以通过str()函数进行转换
        time_str = str(time_list_index[0])
        #电话处理  ！！！电话和速度此处由于字段需要重新创建字符串进行存储，但是我们最终的目的就是讲所有的转换为str类型
        tel_str_1 = tel_str.join(str(tel_list_index[0]))
        #速度处理
        speed_str_1 = speed_str.join(str(speed_list_index[0]))

        id_str = id_str.join(id_list_index)
        latlon_str = latlon_str.join(latlon_list_index)
        dir_str = dir_str.join(dir_list_index)
        status_str = status_str.join(status_list_index)
        #下面对于经纬度列单独处理，拆分开
        lat_list = latlon_str.split(',')
        lon_str = lat_list[0]
        lat_str = lat_list[1]
        #执行数据库插入操作
        add_data(file_name[j],id_str,tel_str_1,time_str,speed_str_1,dir_str,status_str,lon_str,lat_str)
    conn.commit()
cur.close()
conn.close()

最终大功告成。

感谢您的观看。若有其他疑问欢迎加QQ1242229486,进行咨询。