python高效处理大数据：将Excel10万数据分批插入MySQL数据库的实战代码

将一个包含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 = None
    try:
        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.active
    rows = ws.iter_rows(values_only=True)
    headers = next(rows)  # 假设第一行为标题行

    while True:
        chunk = list(islice(rows, chunk_size))
        if not chunk:
            break
        yield 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 的值，以达到最佳性能。
• 错误处理:上述代码提供了基本的错误处理，但在生产环境中，需要更详细的日志记录和错误处理机制。

---

@漏刻有时