python 将xlsx数据导入mysql

 

import pandas as pd  # 先装个pandas ,pip install pandas
import pymysql

# 读入数据库
filename = 'student.xlsx'  # 本地需要导入数据库的文件
data = pd.read_excel(filename)
# 建立数据库连接
db = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="root", db="test", charset="utf8")
cursor = db.cursor()
# 判断数据表是否存在
try:
    cursor.execute(
        'create table student(id int auto_increment primary key,name varchar(100),sex varchar(20), teacher_id varchar(20))'
    )
except Exception as e:
    raise e

query = 'insert into student(name,sex,teacher_id) values (%s,%s,%s)'
for i in range(0, len(data)):
#id为自增字段，不需要添加
    name = data.iloc[i, 1]
    sex = data.iloc[i, 2]
    teacher_id = data.iloc[i, 3]
    values = (str(name), str(sex), str(teacher_id))
    cursor.execute(query, values)
cursor.close()
db.commit()
print("数据导入成功")
db.close()