一、简介
Python导入Excel到Mysql数据库
- 自动创建ID为自增。
- 根据Excel表格表头自动生成数据库字段。
二、使用方法
修改configure
配置即可。
import pandas as pd
import sqlalchemy
configure = {
"DATABASE": "how2java",
"USER": "root",
"PASSWORD": "1234",
"TABLE": "test",
"HOST": "localhost",
"PORT": "3306",
"CHARSET": "utf8",
"EXCEL_PATH": r"C:\Users\25281\Desktop\新建Microsoft Excel 工作表.xlsx"
}
connect_sql = "mysql+pymysql://{0}:{1}@{2}:{3}/{4}?charset={5}".format(configure['USER'], configure['PASSWORD'],
configure['HOST'], configure['PORT'],
configure['DATABASE'], configure['CHARSET'],
)
engine = sqlalchemy.create_engine(connect_sql)
df = pd.read_excel(configure['EXCEL_PATH'])
df.to_sql(name='test', con=engine, if_exists='replace', index=False)
with engine.connect() as con:
con.execute("""ALTER TABLE `{}`.`{}` \
ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST, \
ADD PRIMARY KEY (`id`);"""
.format(configure['DATABASE'], configure['TABLE']))