### Python 使用 Pandas 或 Openpyxl 将 XLSX 文件批量写入 MySQL 数据库 要实现将 `.xlsx` 文件中的数据读取并通过 `Pandas` 或 `Openpyxl` 插入到 MySQL 数据库中，以下是完整的解决方案。 #### 安装依赖项 确保安装所需的 Python 库： ```bash pip install pymysql pandas sqlalchemy openpyxl ``` 这些工具的作用如下： - **PyMySQL**: 提供与 MySQL 的连接支持。 - **Pandas**: 处理 Excel 和 DataFrame 转换。 - **SQLAlchemy**: 构建 ORM 工具以及简化数据库交互过程。 - **Openpyxl**: 支持读取和写入 `.xlsx` 文件[^1]。 --- #### 方法一：使用 Pandas 和 SQLAlchemy 实现批量插入 此方法通过 `Pandas` 读取 `.xlsx` 文件并将数据存储为 DataFrame，随后利用 `to_sql()` 函数将其写入 MySQL 数据库。 ##### 代码示例 ```python import pandas as pd from sqlalchemy import create_engine # 配置 MySQL 连接字符串 db_config = { 'user': 'root', 'password': 'your_password', 'host': 'localhost', 'database': 'test_db' } connection_str = f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}/{db_config['database']}" engine = create_engine(connection_str) # 读取 .xlsx 文件 file_path = 'example.xlsx' # 替换为实际路径 data_frame = pd.read_excel(file_path) # 默认读取第一个 sheet # 写入 MySQL 数据库 table_name = 'my_table' # 替换为目标表名 data_frame.to_sql(name=table_name, con=engine, if_exists='append', index=False) ``` 上述代码实现了以下功能： 1. 创建了一个基于 SQLAlchemy 的引擎实例用于管理数据库连接。 2. 利用 `pd.read_excel()` 加载 `.xlsx` 文件的内容至 DataFrame 中[^4]。 3. 使用 `to_sql()` 方法将 DataFrame 的内容批量插入目标 MySQL 表中。 注意：如果目标表不存在，则需提前创建好对应的结构或者设置参数自动创建新表。 --- #### 方法二：使用 Openpyxl 结合原生 SQL 查询执行批处理 当需要更高的灵活性时，可以选择手动加载 `.xlsx` 文件并逐条或分批次提交给 MySQL 数据库。 ##### 代码示例 ```python from openpyxl import load_workbook import pymysql.cursors # 加载工作簿 workbook = load_workbook(filename='example.xlsx') sheet = workbook.active # 初始化 MySQL 连接 connection = pymysql.connect( host='localhost', user='root', password='your_password', database='test_db', cursorclass=pymysql.cursors.DictCursor ) try: with connection.cursor() as cursor: insert_query = """ INSERT INTO my_table (column1, column2, column3) VALUES (%s, %s, %s); """ rows_to_insert = [] for row in sheet.iter_rows(min_row=2, values_only=True): # 跳过标题行 rows_to_insert.append(row[:3]) # 假设前三列对应于目标表字段 if len(rows_to_insert) >= 100: # 设置每批大小 cursor.executemany(insert_query, rows_to_insert) connection.commit() rows_to_insert.clear() # 插入剩余未满的一批 if rows_to_insert: cursor.executemany(insert_query, rows_to_insert) connection.commit() finally: connection.close() ``` 这段代码展示了如何： 1. 使用 `openpyxl.load_workbook()` 来解析 `.xlsx` 文件。 2. 手动构建待插入的数据列表，并调用 `executemany()` 方法完成高效批处理操作[^2]。 3. 控制每次提交的数量以优化性能。 --- #### 性能对比分析 | 方案 | 易用性 | 可扩展性 | 效率 | |------|--------|----------|-------| | Pandas + SQLAlchemy | ★★★★☆ | ★★★☆☆ | 较高（适合中小规模数据集） | | Openpyxl + PyMySQL | ★★☆☆☆ | ★★★★☆ | 更优（适用于大规模数据集） | 对于超大数据量的情况推荐采用第二种方案，因为它允许更精细地控制内存占用及事务频率[^3]。 ---