使用 Python 读取 .xlsx 文件的最快方法

我正在尝试使用 Python 将 .xlsx 文件中的数据读入 MySQL 数据库。

这是我的代码：

wb = openpyxl.load_workbook(filename="file", read_only=True)
ws = wb['My Worksheet']

conn = MySQLdb.connect()
cursor = conn.cursor()

cursor.execute("SET autocommit = 0")

for row in ws.iter_rows(row_offset=1):
     sql_row = # data i need
     cursor.execute("INSERT sql_row")

conn.commit() 

不幸的是， openpyxl 的 ws.iter_rows() 速度非常慢。我已经使用 xlrd 和 pandas 模块尝试了类似的方法。还是很慢。有什么想法吗？

好的，以下是使用 Python 加快 .xlsx 文件读取速度的一些方法：

1. 使用 openpyxl 的只读模式和值预加载：

• 已经在使用 read_only=True ，这很棒。

• 此外，使用 data_values=True 预加载单元格值可以提高性能：

  python wb = openpyxl.load_workbook(filename="file", read_only=True, data_only=True)

2. 批量插入 MySQL：

• 与其一次插入一行，不如将多行数据收集到一个列表中，并使用 executemany() 执行批量插入。

  ```python sql = "INSERT INTO your_table (column1, column2, ...) VALUES (%s, %s, ...)" values = []

  for row in ws.iter_rows(min_row=2, values_only=True): sql_row = list(row) # 假设需要所有列 values.append(sql_row)

  cursor.executemany(sql, values) conn.commit() ```

3. 尝试其他库：

• XlsxWriter 读取： 虽然主要用于写入，但 XlsxWriter 也可以读取文件，并且在某些情况下速度更快。

  ```python import xlsxwriter

  workbook = xlsxwriter.Workbook('file.xlsx') worksheet = workbook.add_worksheet()

  for row_num, row_data in enumerate(worksheet.iter_rows()): # 处理 row_data ```

• csv 模块（如果适用）： 如果可以将 .xlsx 文件转换为 .csv ，则使用内置的 csv 模块通常是最快的选择。

4. 优化 MySQL 设置：

• 增加 innodb_buffer_pool_size ： 这将为 MySQL 的缓冲池分配更多内存，从而可以更快地进行插入操作。
• 禁用索引（临时）： 如果要加载大量数据，请考虑在加载过程中临时禁用索引，然后在加载完成后重新启用它们。

5. 考虑其他工具：

• 对于非常大的数据集，使用专门的 ETL（提取、转换、加载）工具（如 Apache Kafka、Apache Nifi 或 Python 的 petl 库）可能是更好的选择。

代码示例（结合最佳实践）：

import openpyxl
import MySQLdb

wb = openpyxl.load_workbook(filename="file.xlsx", read_only=True, data_only=True)
ws = wb['My Worksheet']

conn = MySQLdb.connect()
cursor = conn.cursor()

sql = "INSERT INTO your_table (column1, column2, ...) VALUES (%s, %s, ...)"
values = []

for row in ws.iter_rows(min_row=2, values_only=True):
    sql_row = list(row)
    values.append(sql_row)

cursor.executemany(sql, values)
conn.commit() 

请记住根据的特定需求和数据大小测试不同的方法，以确定哪种方法最有效。