读取excel导入mysql数据库

import pandas as pd
import mysql.connector

# 读取Excel文件
excel_file_path = 'path_to_your_excel_file.xlsx'
df = pd.read_excel(excel_file_path)

# 连接MySQL数据库
db_config = {
    'host': 'your_host',
    'user': 'your_user',
    'password': 'your_password',
    'database': 'your_database'
}

connection = mysql.connector.connect(**db_config)
cursor = connection.cursor()

# 假设Excel文件的列名与数据库表的列名一致
table_name = 'your_table_name'

# 构建插入语句
placeholders = ', '.join(['%s'] * len(df.columns))
columns = ', '.join(df.columns)
insert_statement = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"

# 批量插入数据
data_to_insert = [tuple(x) for x in df.values]
cursor.executemany(insert_statement, data_to_insert)

# 提交事务
connection.commit()

# 关闭连接
cursor.close()
connection.close()

df['column_name'] = df['column_name'].astype('desired_type')

connection = mysql.connector.connect(charset='utf8', **db_config)

batch_size = 1000
for i in range(0, len(df), batch_size):
    batch_data = df[i:i+batch_size]
    cursor.executemany(insert_statement, [tuple(x) for x in batch_data.values])
    connection.commit()