Pandas筛选指定日期数据并插入MariaDB的优化方案咨询

一、优化日期筛选性能（解决append警告与大文件性能问题） #

你当前用iterrows逐行循环+append的方式存在两个核心问题：

• iterrows是逐行遍历，完全没用到Pandas底层优化的向量操作，大文件下速度极慢；
• append方法已被弃用，且每次调用都会创建新的DataFrame，内存开销极大。

最优方案1：全量加载后用布尔索引筛选（适合中小文件） #

直接利用Pandas的布尔索引完成筛选，这是底层优化的向量操作，速度比循环快几个数量级，且无警告：

import datetime as dt
import pandas as pd

# 读取CSV，直接得到DataFrame（无需额外转成pd.DataFrame(data)）
df = pd.read_csv('./files/salesreport.csv',
                   names=['col_1', 'col_2', 'col_3', 'col_4', 'col_5', 'col_6',
                          'col_7', 'col_8', 'col_9', 'col_10'],
                   sep=',',
                   header=None,
                   keep_default_na=False,
                   na_values=""
)

today_date = '07-17-2022'  # 测试用日期
# 一行完成筛选，性能拉满
tmp_df = df[df['col_1'] == today_date]

最优方案2：分块读取筛选（适合GB级超大文件） #

如果文件过大，内存无法一次性加载，用chunksize分块读取，筛选后再合并结果：

import datetime as dt
import pandas as pd

today_date = '07-17-2022'
chunk_size = 10000  # 根据你的内存大小调整块大小
filtered_chunks = []

# 分块读取CSV
for chunk in pd.read_csv('./files/salesreport.csv',
                         names=['col_1', 'col_2', 'col_3', 'col_4', 'col_5', 'col_6',
                                'col_7', 'col_8', 'col_9', 'col_10'],
                         sep=',',
                         header=None,
                         keep_default_na=False,
                         na_values="",
                         chunksize=chunk_size):
    # 筛选当前块中符合条件的数据
    filtered_chunk = chunk[chunk['col_1'] == today_date]
    filtered_chunks.append(filtered_chunk)

# 合并所有筛选后的块（官方推荐替代append的方式）
tmp_df = pd.concat(filtered_chunks, ignore_index=True)

二、MariaDB插入数据的最佳实践 #

1. 依赖准备 #

先安装必要的库：

pip install sqlalchemy pymysql

2. 基础插入方式（Pandas to_sql） #

用SQLAlchemy创建连接引擎，配合to_sql批量插入，适合中小数据量：

from sqlalchemy import create_engine

# 创建MariaDB连接引擎，替换成你的数据库信息
engine = create_engine('mysql+pymysql://your_username:your_password@localhost/your_db?charset=utf8mb4')

# 插入数据到指定表
tmp_df.to_sql(
    name='sales_table',  # 数据库目标表名
    con=engine,
    if_exists='append',  # 存在则追加，可选replace（清空重插）/fail（报错）
    index=False,  # 不要把DataFrame的索引插入到数据库
    chunksize=1000  # 每次插入1000行，避免单次插入数据过多超时
)

3. 超大数据量插入（LOAD DATA INFILE） #

如果数据量极大，LOAD DATA INFILE是MariaDB最快的批量插入方式，比to_sql快10倍以上：

from sqlalchemy import create_engine

# 先把筛选后的数据保存为临时CSV
tmp_df.to_csv('./tmp_filtered.csv', index=False, header=False)

# 创建连接时开启local_infile支持
engine = create_engine('mysql+pymysql://your_username:your_password@localhost/your_db?charset=utf8mb4&local_infile=1')

# 执行LOAD DATA命令
with engine.connect() as conn:
    conn.execute("""
        LOAD DATA LOCAL INFILE './tmp_filtered.csv'
        INTO TABLE sales_table
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n'
        (col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10);
    """)

额外注意事项 #

• 字段类型匹配：提前把DataFrame的日期列转成datetime类型，避免插入时类型错误：

  df['col_1'] = pd.to_datetime(df['col_1'], format='%m-%d-%Y')
  today_date = pd.to_datetime('07-17-2022', format='%m-%d-%Y')
  tmp_df = df[df['col_1'] == today_date]
  

• 数据库权限：使用LOAD DATA需要确保MariaDB服务器开启了local_infile参数，且数据库用户有FILE权限。

内容的提问来源于stack exchange，提问作者XLD_a