每日定点将excel数据批量插入mysql数据库对应表

         最近在研究利用Metabase+MySQL实现excel数据的页面呈现，但excel数据每天需要手工上传真的很烦人，所以在网上参考各路大神，用Python实现了excel数据的每日自动上传至MySQL数据库。

思路分为三步：

先导入模块：

import pymysql
import pandas as pd
import os
import time
from datetime import datetime

第一步：定义一个可以获取指定文件夹中的所有excel路径名的函数：

file_name = []
dir_name = []
def get_file(file_dir):
    for parent, dirnames, filenames in os.walk(file_dir):
        # 第一个参数是文件夹路径，第二个参数是文件名，第三个参数是文件夹
        for filename in filenames:
            a = os.path.join(parent, filename)
            file_name.append(a)
        #这一部分是返回当前文件夹的子文件夹名，后续未用到：
        for dirname in dirnames:
            b = os.path.join(parent, dirname)
            dir_name.append(b)
            # 文件夹名
    return file_name

第二步：  定义一个指定excel文件所有sheet批量导入mysql的函数

def pro(path):
    #获取excel文件对象
    date_xls = pd.ExcelFile(path)
    # print(date_xls)
    #for循环遍历所读excel中的sheet
    for sheetname in date_xls.sheet_names:
        #将sheet数据读取为dataframe
        df=pd.DataFrame(pd.read_excel(open(path,'rb'),sheet_name=sheetname))
        df=df.fillna(0)
        #将dataframe转为元组列表数据，execute函数导入mysql用
        pro1=df.apply(lambda x:tuple(x),axis=1).values.tolist()
        # print(pro1)
        #读取字段名，同时将列表格式转为字符串格式
        cl=df.columns.tolist()
        #计算字段个数
        b=df.columns.size
        #计算行数
        c=df.shape[0]
        #计算列数
        l=df.shape[1]
        #设置空字符串
        zhanweifu = ""
        clname =""
        #将字符串拼接，实现需要的SQL语句，此处需注意"`"是键盘ESC下方的按键
        for i in range(0, b - 1):
            zhanweifu = zhanweifu + "%s,"
            clname=clname+"`"+str(cl[i])+"`"+","
        zhanweifu = zhanweifu + "%s"
        clname=clname+"`"+str(cl[b-1])+"`"
        sheetname="`"+sheetname+"`"
        # 游标方法
        
        conn = pymysql.connect(host="localhost",user="root",password="123456",db="damaomao",port=3306,charset='utf8')

        # 获取游标
        cs = conn.cursor()
        #注意点：excel中的sheet名必须与mysql中的表名一致，sheet字段名必须与mysql表中字段名一致
        sql = "insert into " +sheetname + "(" + clname + ")" + " values" + "(" + zhanweifu + ")"

        # print(sql)
        try:
            #批量插入
            cs.executemany(sql,pro1)
            conn.commit()
        except UnicodeEncodeError:
            print('插入failed')
            conn.rollback()
        finally:
            conn.close()

            print('退出')

 第三步：结合两个函数，实现指定文件夹所有excel表中数据的定时批量导入：

if __name__ == '__main__':
    # 调用函数
    #返回文件夹中所有excel文件名
    get_file(r'C:\Users\Desktop\TJ_Morningmeeting\自动导入\11.06')
    Now_time=datetime.now()
    Now_date=Now_time.day
    while True:
        if Now_time.minute+Now_time.hour*60>8*60+5 and Now_date<=datetime.now().day:
            #for循环将所有excel的所有sheet批量导入
            for i in file_name:
                pro(i)
            Now_date=Now_date+1
        print(Now_date)
        time.sleep(60)

话不多说，上完整代码：

import pymysql
import pandas as pd
import os
import time
from datetime import datetime

file_name = []
dir_name = []
def get_file(file_dir):
    for parent, dirnames, filenames in os.walk(file_dir):
        # 第一个参数是文件夹路径，第二个参数是文件名，第三个参数是文件夹
        for filename in filenames:
            a = os.path.join(parent, filename)
            file_name.append(a)

        for dirname in dirnames:
            b = os.path.join(parent, dirname)
            dir_name.append(b)
            # 文件夹名
    return file_name
def pro(path):
    #获取excel文件对象
    date_xls = pd.ExcelFile(path)
    # print(date_xls)
    #for循环遍历所读excel中的sheet
    for sheetname in date_xls.sheet_names:
        #将sheet数据读取为dataframe
        df=pd.DataFrame(pd.read_excel(open(path,'rb'),sheet_name=sheetname))
        df=df.fillna(0)
        #将dataframe转为元组列表数据，execute函数导入mysql用
        pro1=df.apply(lambda x:tuple(x),axis=1).values.tolist()
        # print(pro1)
        #读取字段名，同时将列表格式转为字符串格式
        cl=df.columns.tolist()
        #计算字段个数
        b=df.columns.size
        #计算行数
        c=df.shape[0]
        #计算列数
        l=df.shape[1]
        #设置空字符串
        zhanweifu = ""
        clname =""
        #将字符串拼接，实现需要的SQL语句，此处需注意"`"是键盘ESC下方的按键
        for i in range(0, b - 1):
            zhanweifu = zhanweifu + "%s,"
            clname=clname+"`"+str(cl[i])+"`"+","
        zhanweifu = zhanweifu + "%s"
        clname=clname+"`"+str(cl[b-1])+"`"
        sheetname="`"+sheetname+"`"
        # 游标方法
        
        conn = pymysql.connect(host="localhost",user="root",password="123456",db="damaomao",port=3306,charset='utf8')

        # 获取游标
        cs = conn.cursor()
        #注意点：excel中的sheet名必须与mysql中的表名一致，sheet字段名必须与mysql表名一致
        sql = "insert into " +sheetname + "(" + clname + ")" + " values" + "(" + zhanweifu + ")"

        # print(sql)
        try:
            #批量插入
            cs.executemany(sql,pro1)
            conn.commit()
        except UnicodeEncodeError:
            print('插入failed')
            conn.rollback()
        finally:
            conn.close()

            print('退出')

if __name__ == '__main__':
    # 调用函数
    #返回文件夹中所有excel文件名
    get_file(r'C:\Users\Desktop\TJ_Morningmeeting\自动导入\11.06')
    Now_time=datetime.now()
    Now_date=Now_time.day
    while True:
        if Now_time.minute+Now_time.hour*60>8*60+5 and Now_date<=datetime.now().day:
            #for循环将所有excel的所有sheet批量导入
            for i in file_name:
                pro(i)
            Now_date=Now_date+1
        print(Now_date)
        time.sleep(60)