pandas和mysql之间数据的传输
前言
今天我的大领导突然跑到我面前指指点点说:小刘啊,来把这个测试库的数据导出到Excel里发给我,看数据嘛很正常,我回了句好的,一会发你。吭哧吭哧搞完发给了他,没一会又来了句,来,帮我把这份数据导进去,顺便把库里的数据删了。我。。。。他么当时就想一巴掌把他踢出去,看我很闲是咋的。又一想算了算了,这个月工资还没发呢,忍一手先!那就且看我如何用几行代码实现pandas和mysql的数据交互。
相关库介绍
pandas:实现Excel读取和mysql数据的写入。
sqlalchemy:给pandas提供链接mysql的引擎。
pymysql:链接mysql查询数据。
数据准备
构造Excel数据(被领导看到会不会被穿小鞋0.0)
构造数据库表(从0开始):
1.登录、建库、建表(就不从安装mysql开始,自己玩去)
注意:表名和字段名不是用的引号,而是英文状态下tab键上面内个。
关键代码
一、先展示sqlalchemy将数据存储到mysql。
import os
import pandas as pd
from sqlalchemy import create_engine
# os拼接Excel路径
excel_path = os.path.join(os.path.dirname(__file__), "guer.xlsx")
# 这种写法可以很方便的将多个sheet页的数据读到内存中,装个x
with pd.io.excel.ExcelFile(excel_path, engine="xlrd") as reader:
for sheet_name in reader.sheet_names:
if sheet_name == "user":
user_data = pd.read_excel(reader, sheet_name=sheet_name, header=0)
if not user_data.empty:
# 创建数据库引擎
mysql_engine = create_engine('mysql+pymysql://root:root@localhost:3306/demo_database')
# 存储方法1
# pd.io.sql.to_sql(user_data, "worker", con=mysql_engine, index=False, if_exists="replace")
# 存储方法2
user_data.to_sql("worker", con=mysql_engine, index=False, if_exists="replace")
来看下结果,完美!
二、从mysql中把数据导入到Excel表。
import os
import pandas as pd
from pymysql import connect
# pymysql链接数据库
conn = connect(host="localhost", port=3306, user="root",password="root",db="demo_database", charset="utf8")
# 注意:表名字段名含特殊字符或者空格不加``会出错
sql = "select * from `worker`"
# 直接根据sql语句读取数据
user_data = pd.read_sql(sql=sql, con=conn)
# 用完记得关闭哦
conn.close()
# 将数据保存到Excel,这种写法可以定制输出的excel格式,就不深究了
excel_path = os.path.join(os.path.dirname(__file__), "mysql_to_excel.xlsx")
with pd.io.excel.ExcelWriter(excel_path, engine="openpyxl") as writer:
user_data.to_excel(writer, "user", index=False)
完美解决领导诉求!顺利下班,哒哒哒哒哒哒。。。