Python向Oracle插入数据遇DPY-4004错误，如何修改DataFrame解决？

问题背景 #

我用Python脚本创建Oracle的MRCONSO表，代码如下：

cur.execute('''BEGIN EXECUTE IMMEDIATE 'DROP TABLE MRCONSO'; 
               EXCEPTION WHEN OTHERS THEN NULL; 
               END;
            ''')

try:
    cur.execute('''
                CREATE TABLE MRCONSO (
                CUI char(8) NOT NULL,
                LAT char(3) NOT NULL,
                TS char(1) NOT NULL,
                LUI varchar2(10) NOT NULL,
                STT varchar2(3) NOT NULL,
                SUI varchar2(10) NOT NULL,
                ISPREF char(1) NOT NULL,
                AUI varchar2(9) NOT NULL,
                SAUI integer,
                SCUI varchar2(100),
                SDUI varchar2(100),
                SAB varchar2(40) NOT NULL,
                TTY varchar2(40) NOT NULL,
                CODE varchar2(100) NOT NULL,
                STR varchar2(3000) NOT NULL,
                SRL integer NOT NULL,
                SUPPRESS char(1) NOT NULL,
                CVF integer)
                PCTFREE 10 PCTUSED 80
                '''
               )
    
    print("MRCONSO Table Created")
                
except Exception as e:
    print("Error: ",str(e))

插入CSV数据时触发错误：

Oracle error message: DPY-4004: invalid number

数据处理代码：

df_umls = pd.read_csv("../umls_files/umls-2023AA-metathesaurus-full/2023AA/META/MRCONSO.RRF", sep = '|', low_memory=False)
df_umls.columns=["CUI", "LAT", "TS", "LUI", "STT", "SUI", "ISPREF", "AUI","SAUI", "SCUI", "SDUI", "SAB", "TTY", "CODE", "STR", "SRL", "SUPPRESS", "CVF", "JUNK"]
df_umls = df_umls.drop(columns=['JUNK'])

df_umls['CVF'] = df_umls['CVF'].fillna(0) 
df_umls['CVF'] = df_umls['CVF'].astype("Int64") 

df_umls['SAUI'] = df_umls['SAUI'].fillna(0) 
df_umls['SAUI'] = df_umls['SAUI'].astype("Int64") 


try:
    if conn:
        print("Oracle version:", oracledb.version)
        print("Database version:", conn.version)
        #print("Client version:", oracledb.clientversion())
        print('Inserting data into table....')
        for i,row in tqdm(df_umls.iterrows(), total=df_umls.shape[0]):
            sql_conso = "insert into MRCONSO (CUI,LAT,TS,LUI,STT,SUI,ISPREF, AUI, SAUI, SCUI, SDUI, SAB, TTY, CODE, STR, SRL, SUPPRESS, CVF) values (:0,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17)"
            cur.execute(sql_conso, tuple(row))
        # the connection is not autocommitted by default, so we must commit to save our changes
        conn.commit()
        print("Record inserted succesfully")
except DatabaseError as e:
    err, = e.args
    print("Oracle-Error-Code:", err.code)
    print("Oracle-Error-Message:", err.message)
finally:
    cur.close()
    conn.close()

当前DataFrame的数据类型：

CUI object
LAT object
TS object
LUI object
STT object
SUI object
ISPREF object
AUI object
SAUI Int64
SCUI object
SDUI object
SAB object
TTY object
CODE object
STR object
SRL int64
SUPPRESS object
CVF Int64
dtype: object

解决方法 #

DPY-4004错误是因为Oracle无法将某些值转换为整数类型，重点检查SAUI、CVF、SRL这三个整数类型字段，按以下步骤修改DataFrame：

1. 彻底清理非数字值 #

原始数据中可能存在空字符串、空格或其他非数字字符，仅用fillna无法处理这些情况，需用pd.to_numeric强制转换并过滤无效值：

# 处理SAUI：将非数字值转为NaN，再填充0后转整数类型
df_umls['SAUI'] = pd.to_numeric(df_umls['SAUI'], errors='coerce').fillna(0).astype('Int64')
# 处理CVF：同理
df_umls['CVF'] = pd.to_numeric(df_umls['CVF'], errors='coerce').fillna(0).astype('Int64')
# 处理SRL：确保无无效值
df_umls['SRL'] = pd.to_numeric(df_umls['SRL'], errors='coerce').fillna(0).astype('int64')

2. 兼容Oracle整数类型（可选） #

Pandas的Int64是可空整数类型，部分场景下可能和Oracle的integer类型存在转换冲突，可尝试转为普通int类型：

df_umls['SAUI'] = df_umls['SAUI'].fillna(0).astype(int)
df_umls['CVF'] = df_umls['CVF'].fillna(0).astype(int)

3. 定位错误行（调试用） #

如果仍报错，可以在插入循环中加入异常捕获，定位具体出错的行，针对性处理：

for i,row in tqdm(df_umls.iterrows(), total=df_umls.shape[0]):
    try:
        cur.execute(sql_conso, tuple(row))
    except DatabaseError as e:
        print(f"错误行索引：{i}，数据内容：{row}")
        raise e

4. 批量插入优化（推荐） #

逐行插入效率极低，建议用to_sql批量插入，同时指定字段类型映射避免转换问题：

from sqlalchemy import create_engine
# 创建Oracle连接引擎，替换为你的连接信息
engine = create_engine('oracle+oracledb://用户名:密码@主机:端口/服务名')
# 定义字段与Oracle类型的映射
dtype = {
    'CUI': 'CHAR(8)',
    'LAT': 'CHAR(3)',
    'TS': 'CHAR(1)',
    'LUI': 'VARCHAR2(10)',
    'STT': 'VARCHAR2(3)',
    'SUI': 'VARCHAR2(10)',
    'ISPREF': 'CHAR(1)',
    'AUI': 'VARCHAR2(9)',
    'SAUI': 'INTEGER',
    'SCUI': 'VARCHAR2(100)',
    'SDUI': 'VARCHAR2(100)',
    'SAB': 'VARCHAR2(40)',
    'TTY': 'VARCHAR2(40)',
    'CODE': 'VARCHAR2(100)',
    'STR': 'VARCHAR2(3000)',
    'SRL': 'INTEGER',
    'SUPPRESS': 'CHAR(1)',
    'CVF': 'INTEGER'
}
# 批量插入，chunksize可根据内存调整
df_umls.to_sql('MRCONSO', engine, if_exists='append', index=False, dtype=dtype, chunksize=1000)

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