python之excel与mysql之间的交互（excel python交互）

python之excel与mysql之间的交互（excel python交互）

一.准备工作

1.1安装以下包

pip install openpyxlpip install sqlalchemypip install pandas

1.2配置数据库的东西在conf.py文件下

DATABASE_CONFIG = { "HOST": "127.0.0.1", "USERNAME": "root", "PASSWORD": "", "DATABASE": "bike_ods", "PORT": 3306,}

View Code

1.3 mysql --->  excel

from sqlalchemy import create_engineimport pandas as pdfrom conf import DATABASE_CONFIG# 创建数据库连接ip = DATABASE_CONFIG["HOST"]username = DATABASE_CONFIG["USERNAME"]password = DATABASE_CONFIG["PASSWORD"]ods = DATABASE_CONFIG["DATABASE"]# 文件输出excel_file = "out.xlsx"engine = create_engine(f'mysql+pymysql://{username}:{password}@{ip}/{ods}')# 读取mysql数据select_sql = f"select * from areafence"db = pd.read_sql(select_sql, con=engine)# 导出数据到exceldb.to_excel(excel_file)

View Code

1.4 excel ---------> mysql

from sqlalchemy import create_engineimport pandas as pdfrom conf import LOCALHOST_CONFIG as DATABASE_CONFIG# 创建数据库连接ip = DATABASE_CONFIG["HOST"]username = DATABASE_CONFIG["USERNAME"]password = DATABASE_CONFIG["PASSWORD"]ods = DATABASE_CONFIG["DATABASE"]# 文件输出excel_file = "out.xlsx"# 创建数据库连接engine = create_engine(f'mysql+pymysql://{username}:{password}@{ip}/{ods}')# 读取xlsx文件df = pd.read_excel(excel_file)# 导入到mysql数据库df.to_sql(name='test_data', con=engine, index=False, if_exists='replace')

View Code

1.5 如果默认端口不是3306

engine = create_engine('mysql://user:password@localhost:3306/test?charset=utf8mb4')

-----------------------------------------------------------------------------------------------------------------------------------------

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。