如何通过平面文件向Oracle数据库加载数据？Python大文件加载遇连接中断

原代码的问题在于一次性把所有记录加载到内存，不仅占用大量内存，而且逐条插入的方式会产生大量数据库交互，长时间运行很容易触发连接中断。以下是几种可行的优化方案：

方案1：批量插入+分批次处理 #

把数据分成小批次，每批次用executemany批量插入，既减少内存占用，又提升插入效率，降低连接中断概率。

修改后的代码示例：

from tqdm import tqdm

insert_sty = "insert into MRSTY (CUI,TUI,STN,STY,ATUI,CVF) values (:0,:1,:2,:3,:4,:5)"
batch_size = 1000  # 可根据内存和数据库性能调整
file_path = "../umls_files/umls-2023AA-metathesaurus-full/2023AA/META/MRSTY.RRF"

# 先获取总行数用于进度条
num_lines = sum(1 for line in open(file_path))

with open(file_path, 'r') as f:
    batch = []
    for line in tqdm(f, total=num_lines, desc="Processing and inserting records"):
        line = line.strip()
        if not line:
            continue
        parts = line.split("|")
        if parts:
            parts.pop()  # 移除最后一个空元素
        batch.append(parts)
        
        # 达到批次大小就执行插入
        if len(batch) >= batch_size:
            try:
                cur.executemany(insert_sty, batch)
                conn.commit()  # 每批次提交一次，避免事务过大
                batch = []  # 清空批次
            except Exception as e:
                print(f"Batch insert error: {str(e)}")
                print(f"Failed batch sample: {batch[0]}")
                conn.rollback()
                batch = []
    
    # 处理剩余的不足一个批次的记录
    if batch:
        try:
            cur.executemany(insert_sty, batch)
            conn.commit()
        except Exception as e:
            print(f"Final batch insert error: {str(e)}")
            conn.rollback()

方案2：调整Oracle连接超时参数 #

在Python连接Oracle时，可以设置更长的超时时间，比如使用cx_Oracle的话，在连接字符串里加入超时相关参数：

import cx_Oracle

# 示例连接配置
dsn = cx_Oracle.makedsn("hostname", "port", service_name="service_name")
conn = cx_Oracle.connect(user="username", password="password", dsn=dsn, 
                         connect_timeout=600,  # 连接超时设为10分钟
                         expire_time=10)  # 每10分钟发送心跳包保持连接
cur = conn.cursor()

也可以修改Oracle服务器端的sqlnet.ora文件，增加以下参数延长超时时间：

SQLNET.INBOUND_CONNECT_TIMEOUT = 600
SQLNET.SEND_TIMEOUT = 600
SQLNET.RECV_TIMEOUT = 600

方案3：使用Oracle官方工具（推荐） #

对于超大数据量，最高效的方式是用Oracle原生工具：

• SQL*Loader：编写控制文件（.ctl）直接命令行加载，性能远高于Python代码。控制文件示例：

LOAD DATA
INFILE '../umls_files/umls-2023AA-metathesaurus-full/2023AA/META/MRSTY.RRF'
INTO TABLE MRSTY
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
    CUI,
    TUI,
    STN,
    STY,
    ATUI,
    CVF
)

执行命令：

sqlldr username/password@database control=mrsty.ctl log=mrsty.log

• 外部表：把平面文件映射成Oracle的外部表，直接通过SQL查询或插入，适合需要先做数据清洗的场景。

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