python之excel与mysql之间的交互

一.准备工作

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,
}

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)

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

 1.5 如果默认端口不是3306

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