将一个包含10万行数据的Excel文件批量插入到MySQL数据库中，可以按照以下步骤进行。我们将使用 pandas 读取Excel文件，并使用 mysql.connector 来连接和插入数据到MySQL数据库。同时，我们会采用分批插入的方式来处理大量数据，以优化性能和内存使用。
一、导入库
import pandas as pd
import mysql.connector
from mysql.connector import Error
from openpyxl import load_workbook
from itertools import islice # 导入 islice 用于高效地读取Excel文件的块
二、创建与MySQL数据库的连接
# 创建与MySQL数据库的连接
def create_connection(host, user, password, database):"""创建与MySQL数据库的连接:param host: 数据库主机地址:param user: 数据库用户名:param password: 数据库密码:param database: 数据库名:return: 数据库连接对象"""connection = Nonetry:connection = mysql.connector.connect(host=host,user=user,password=password,database=database)print("Connection to MySQL DB successful")except Error as e:print(f"The error '{e}' occurred")return connection三、 执行批量插入操作
# 执行批量插入操作
def execute_batch_insert(connection, query, data, batch_size=1000):"""执行批量插入操作:param connection: 数据库连接对象:param query: 插入数据库的SQL查询:param data: 要插入的数据，为列表的元组:param batch_size: 每次批量插入的数量，默认为1000"""cursor = connection.cursor()try:for i in range(0, len(data), batch_size):batch = data[i:i + batch_size]cursor.executemany(query, batch)connection.commit()print("Batch insert completed successfully")except Error as e:print(f"The error '{e}' occurred")connection.rollback() # 如果发生错误，则回滚事务finally:cursor.close()四、 逐块读取Excel文件
# 逐块读取Excel文件
def read_excel_in_chunks(file_path, chunk_size=5000):"""逐块读取Excel文件:param file_path: Excel文件路径:param chunk_size: 每块的行数，默认为5000:return: 生成器，产生包含Excel数据的DataFrame块"""wb = load_workbook(filename=file_path, read_only=True)ws = wb.activerows = ws.iter_rows(values_only=True)headers = next(rows) # 假设第一行为标题行while True:chunk = list(islice(rows, chunk_size))if not chunk:breakyield pd.DataFrame(chunk, columns=headers)五、数据库配置执行操作
# 数据库配置
db_config = {'host': 'localhost', # 数据库主机'user': 'bigdata', # 数据库用户名'password': 'ydWi3RJMw4XSjzfc', # 数据库密码'database': 'bigdata' # 数据库名
}# Excel文件路径
excel_file_path = 'template.xlsx'# 创建数据库连接
connection = create_connection(**db_config)if connection is not None:# SQL插入语句insert_poi_query = """INSERT INTO po_poi (poi_name, poi_address, poi_lng, poi_lat)VALUES (%s, %s, %s, %s)"""# 分批读取Excel数据for chunk_df in read_excel_in_chunks(excel_file_path, chunk_size=5000):# 将DataFrame转换为列表元组values = [tuple(x) for x in chunk_df.to_numpy()]# 执行批量插入execute_batch_insert(connection, insert_poi_query, values)# 关闭数据库连接connection.close()
else:print("Failed to connect to the database, cannot proceed with the operation.")
注意事项
- 表结构: 确保目标表 your_table 已经存在，并且包含 poi_name, poi_address, poi_lng,
poi_lat, insert_time 字段。 - 权限: 确保数据库用户具有足够的权限来执行插入操作。
- 性能:根据你的系统资源，可能需要调整 chunk_size 和 batch_size 的值，以达到最佳性能。
- 错误处理:上述代码提供了基本的错误处理，但在生产环境中，需要更详细的日志记录和错误处理机制。
@漏刻有时
