如何修改Python代码实现用Pandas从TXT批量插入多记录到Oracle表

阿华AIGC实验室

2026-5-6

解决多记录批量插入Oracle的问题

Hey there! Let's get your code fixed to handle multiple 3-line records and batch insert them into your Oracle table efficiently.

原代码的问题分析

Your current code collects all matching lines into a single list, converts it to a DataFrame, and tries to insert the entire column as a single record—this is why it only handles one entry. We need to group the lines into sets of 3 (each set being one database record) and then insert all those records at once.

修改后的代码方案

Here's the adjusted code that handles batch insertion correctly:

import pandas as pd
import cx_Oracle  # 确保已安装Oracle连接库

# 步骤1：读取并过滤文本文件中的行
record_lines = []
with open("text.txt", 'r') as myfile:
    for line in myfile:
        stripped_line = line.strip()
        # 只保留以INS或REF开头的行（跳过空行）
        if stripped_line.startswith(("INS", "REF")):
            record_lines.append(stripped_line)

# 步骤2：将行按每3条分组（每组对应一条数据库记录）
# 生成元组列表，每个元组包含a、b、c三个字段的值
batch_records = []
for i in range(0, len(record_lines), 3):
    # 获取当前记录的3行数据
    ins_line, ref1_line, ref2_line = record_lines[i:i+3]
    
    # 如果你需要从每行提取特定部分（而非整行），可以取消下方注释并调整
    # 例如：从INS行提取第2个*分割的值，从REF行提取第3个*分割的值
    # ins_parts = ins_line.split('*')
    # ref1_parts = ref1_line.split('*')
    # ref2_parts = ref2_line.split('*')
    # record_tuple = (ins_parts[1], ref1_parts[2], ref2_parts[2])
    
    # 如果直接将整行作为字段值，使用下面这行
    record_tuple = (ins_line, ref1_line, ref2_line)
    
    batch_records.append(record_tuple)

# 步骤3：批量插入所有记录到Oracle数据库
# 假设你已经完成了数据库连接和游标初始化
# conn = cx_Oracle.connect(你的连接参数)
# cursor = conn.cursor()

try:
    # 使用executemany实现高效批量插入（比循环execute快得多）
    cursor.executemany("""
        INSERT INTO your_table_name (a, b, c) 
        VALUES (:1, :2, :3)
    """, batch_records)
    conn.commit()
    print(f"成功插入 {len(batch_records)} 条记录！")
except cx_Oracle.Error as e:
    conn.rollback()
    print(f"插入失败：{e}")
finally:
    # 清理资源
    cursor.close()
    conn.close()