Excel数据导入Mysql常见问题汇总：如何解决导入数据时遇到的大批量插入问题？

import xlrd
import pymysql

# 连接数据库
conn = pymysql.connect(host='localhost', user='root', passWord='password', database='database')
cursor = conn.cursor()

# 读取Excel表格数据
data = xlrd.open_workbook('data.xlsx')
table = data.sheet_by_name('Sheet1')
rows = table.nrows

# 构建values子句
values = []
for i in range(1, rows):
    values.append(tuple(table.row_values(i)))

# 批量插入
sql = "INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)"
cursor.executemany(sql, values)
conn.commit()

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

LOAD DATA INFILE 'data.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTioNALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 LINES;

import pandas as pd
from sqlalchemy import create_engine

# 连接数据库
engine = create_engine('mysql+pymysql://root:password@localhost/database')

# 读取Excel表格数据
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# 批量插入
df.to_sql('table_name', engine, if_exists='append', index=False)

# 关闭连接
engine.dispose()