python之excel与mysql之间的交互

pip install openpyxl
pip install sqlalchemy
pip install pandas

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

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)

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

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