绪论
在日常的数据分析工作中,数据留存是非常重要的一个环节。同时众所周知数据库留存数据才是真正高质量的数据留存方式。所以每月定时导入我们想要留存的数据到数据库,就成为了一个非常重要的环节,下面我将展示如何实现这一步骤。
# 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() # 关闭连接