如何实现Python中Excel与MySQL数据库的交互操作？

本文共计356个文字，预计阅读时间需要2分钟。

一、准备工作

1.1 安装以下包：

- pip install openpyxl- pip install sqlalchemy- pip install pandas

1.2 配置数据库信息在`conf.py`文件下，设置`DATABASE_CONFIG`如下：pythonDATABASE_CONFIG={ HOST: 127.0.0.1, USERNAME: root, PASSWORD: , DATABASE: bike}

一.准备工作

1.1安装以下包

pip install openpyxl
pip install sqlalchemy
pip 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_engine
import pandas as pd

from 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)
# 导出数据到excel
db.to_excel(excel_file)

View Code

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

from sqlalchemy import create_engine
import pandas as pd

from 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')

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