python导入excel文件和连接本地mysql数据库

绪论

在日常的数据分析工作中，数据留存是非常重要的一个环节。同时众所周知数据库留存数据才是真正高质量的数据留存方式。所以每月定时导入我们想要留存的数据到数据库，就成为了一个非常重要的环节，下面我将展示如何实现这一步骤。

# pip install PyMySQL
# pip install sqlalchemy
import pymysql #写sql代码
import os
import pandas as pd
import ntpath
from datetime import datetime


#更换读取路径
os.chdir("C:\\Users\\ALSC\\Desktop")

#连接数据库
conn = pymysql.connect(host = '127.0.0.1' # 连接名称，默认127.0.0.1
                    ,user = 'root' # 用户名
                    ,passwd='root' # 密码
                    ,port= 3306 # 端口，默认为3306
                    ,db='mydatabase' # 数据库名称
                    ,charset='utf8' # 字符编码
                    )
###############
####写如数据库###
###############
cur = conn.cursor() # 生成游标对象

#创建
sql = "CREATE TABLE IF NOT EXISTS `场地明细`(" \
   "`日期` VARCHAR(100) NULL," \
   "`所属大区` VARCHAR(100) NULL," \
   "`园区` VARCHAR(100) NULL," \
   "`城市` VARCHAR(100) NULL," \
   "`城市等级` VARCHAR(100) NULL," \
   "`场地类别` VARCHAR(100) NULL," \
   "`办公室类型` VARCHAR(100) NULL," \
   "`租赁面积` DECIMAL(10, 0) NULL" \
")ENGINE=InnoDB DEFAULT CHARSET=utf8;" # SQL语句
cur.execute(sql) # 执行SQL语句


##第二部分
#################
#####导入场地数据###
##################
os.getcwd()
print(os.path.abspath('.'))
os.chdir("C:\\Users\\ALSC\\Desktop\\分析报表\\场地明细")#修改工作路径


## 定义路径，注意文件夹之间用“\\”最后要有个“\\”
path = os.path.abspath('.')


## 获取所有文件的完整路径名
all_files_path=[]
for root, dirs, files in os.walk(path, topdown=False):
    if len(files) > 0:
        each_foder_files=[os.path.join(root, x) for x in files]
        all_files_path.extend(each_foder_files)


##############################
#####批量读取数据、并添加时间#####
##############################
df = pd.DataFrame()
for f in all_files_path:
    each_df = pd.read_excel(f, sheet_name="Sheet1", engine="openpyxl")
    name = ntpath.basename(f)
    name = name.split(".")[0]
    name = name.replace("年", "-").replace("月", "-").replace("日", "")
    name = datetime.strptime(name, "%Y-%m-%d")
    each_df.insert(loc=0, column="日期", value=name)
    df = df.append(each_df)
df = df.iloc[:,0:8]
df["租赁面积"] = df["租赁面积"].fillna(0)
df = df.fillna("Na")

#第三部分
############
###输入数据##
############
for row in df.itertuples():
    cur.execute('INSERT INTO 场地明细 (日期, 所属大区, 园区, 城市, 城市等级, 场地类别, '
                   '办公室类型, 租赁面积) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)',
                   (row.日期, row.所属大区, row.园区, row.城市, row.城市等级,
                    row.场地类别, row.办公室类型, row.租赁面积))
conn.commit()



##############
##导入人员数据##
##############
#创建
sql = "CREATE TABLE IF NOT EXISTS `人员明细`(" \
   "`日期` VARCHAR(100) NULL," \
   "`所属大区` VARCHAR(100) NULL," \
   "`省份` VARCHAR(100) NULL," \
   "`城市` VARCHAR(100) NULL," \
   "`城市等级` VARCHAR(100) NULL," \
   "`园区` VARCHAR(100) NULL," \
   "`场地类别` VARCHAR(100) NULL," \
   "`办公室类型` VARCHAR(100) NULL," \
   "`CORP` VARCHAR(100) NULL," \
   "`BG` VARCHAR(100) NULL," \
   "`BU` VARCHAR(100) NULL," \
   "`一级子部门` VARCHAR(100) NULL," \
   "`二级子部门` VARCHAR(100) NULL," \
   "`类型` VARCHAR(100) NULL," \
   "`工号` VARCHAR(100) NULL," \
   "`姓名` VARCHAR(100) NULL," \
   "`花名` VARCHAR(100) NULL" \
")ENGINE=InnoDB DEFAULT CHARSET=utf8;" # SQL语句
cur.execute(sql) # 执行SQL语句

os.chdir("C:\\Users\\ALSC\\Desktop\\分析报表\\人员明细")#修改工作路径
path = os.path.abspath('.')#指定路径
## 获取所有文件的完整路径名
all_files_path=[]
for root, dirs, files in os.walk(path, topdown=False):
    if len(files) > 0:
        each_foder_files=[os.path.join(root, x) for x in files]
        all_files_path.extend(each_foder_files)


##############################
#####批量读取数据、并添加时间#####
##############################
df1 = pd.DataFrame()
for f in all_files_path:
    each_df = pd.read_excel(f, sheet_name="sheet1", engine="openpyxl")
    name = ntpath.basename(f)
    name = name.split(".")[0]
    name = name.replace("年", "-").replace("月", "-").replace("日", "")
    name = datetime.strptime(name, "%Y-%m-%d")
    each_df.insert(loc=0, column="日期", value=name)
    df1 = df1.append(each_df)
df1 = df1.iloc[:,0:17]
df1 = df1.fillna("Na")

#第三部分
############
###输入数据##
############
for row in df1.itertuples():
    cur.execute('INSERT INTO 人员明细 (日期, 所属大区, 省份, 城市, 城市等级, 园区, '
                   '场地类别, 办公室类型, CORP, BG, BU, 一级子部门, 二级子部门, 类型, 工号,'
                '姓名, 花名) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
                   (row.日期, row.所属大区, row.省份, row.城市, row.城市等级,
                    row.园区, row.场地类别, row.办公室类型, row.CORP, row.BG, row.BU, row.一级子部门, row.二级子部门,
                    row.类型, row.工号, row.姓名, row.花名))
conn.commit()
cur.close() # 关闭游标
conn.close() # 关闭连接