Python导入Excel到Mysql数据库

一、简介

Python导入Excel到Mysql数据库

1. 自动创建ID为自增。
2. 根据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']))