高效处理含末尾双换行的大CSV文件PostgreSQL上传方案

问题背景 #

• 处理超百万行（>>1e6 rows）的.csv文件，约1%的文件解压后末尾存在多余换行符（应为1个，实际为2个）
• 使用PostgreSQL的COPY命令上传时，该多余换行会触发报错，尝试通过WHERE子句忽略空行未达到预期效果
• 需求：在上传流程中以最高效方式处理（无需重新读取整个CSV文件）

高效处理方案 #

方案1：解压后直接截断末尾多余换行 #

修改解压函数，仅在解压完成后处理文件末尾的换行符，避免额外读取整个文件：

import gzip
import os

def unzip_gz(file_path, new_file_path):
    with gzip.open(file_path, "rb") as f_in:
        with open(new_file_path, "wb") as f_out:
            f_out.write(f_in.read())
    
    # 处理末尾多余换行
    with open(new_file_path, "rb+") as f:
        f.seek(-2, os.SEEK_END)  # 跳到倒数第二个字节
        last_two = f.read(2)
        # 兼容Linux(\n\n)和Windows(\r\n\r\n)换行格式
        if last_two == b'\n\n' or last_two == b'\r\n\r\n':
            f.seek(-1, os.SEEK_END)
            f.truncate()  # 截断最后一个换行符

方案2：通过PostgreSQL COPY命令过滤空行 #

调整COPY语句的WHERE子句逻辑，利用非空字段过滤空行：

BEGIN WORK;
LOCK TABLE table_name;
COPY table_name
FROM file_path
WITH (FORMAT CSV, NULL '');
-- 假设表中第一个字段不允许为空，以此过滤空行
WHERE column1 IS NOT NULL;
COMMIT WORK;

注意：此方案依赖表结构（需存在非空字段），若表允许所有字段为NULL，则方案1更可靠。

方案3：流式解压实时处理（适合超大文件） #

针对内存压力大的超大文件，采用流式读取，实时检查并处理末尾换行：

import gzip

def unzip_gz_with_fix(file_path, new_file_path):
    with gzip.open(file_path, "rb") as f_in:
        with open(new_file_path, "wb") as f_out:
            buffer = b''
            chunk_size = 1024 * 1024  # 按1MB块流式读取
            while True:
                chunk = f_in.read(chunk_size)
                if not chunk:
                    break
                buffer += chunk
                # 写入大部分内容，保留最后4字节用于检查换行符
                if len(buffer) > 4:
                    f_out.write(buffer[:-4])
                    buffer = buffer[-4:]
            # 清理剩余buffer中的多余换行
            while buffer.endswith(b'\n'):
                buffer = buffer[:-1]
                if buffer.endswith(b'\r'):
                    buffer = buffer[:-1]
            # 补充一个正常换行（若需要）
            if not buffer.endswith(b'\n'):
                buffer += b'\n'
            f_out.write(buffer)

方案对比 #

• 方案1：实现简单，性能损耗极小，适合绝大多数场景
• 方案2：无需修改Python代码，直接通过SQL解决，但依赖表结构
• 方案3：适合超大文件，流式处理不占用过多内存，逻辑稍复杂

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