psycopg2调用copy_from插入pandas数据时float被识别为字符串报错如何解决

报错的根本原因是写入CSV的过程中，原本应为整数的数值被输出为带.0后缀的浮点格式字符串，PostgreSQL的integer类型无法自动识别这类字符串为合法整数值，具体触发逻辑如下：

• pandas原生整数类型不支持存储空值（NaN），如果你的整数列曾经存在NaN，pandas会自动将该列的数据类型转换为float64以兼容空值
• 你执行的NaN替换为-1的操作仅修改了字段值，没有变更列的数据类型，因此目标列依然是float64类型
• 调用to_csv方法时，float类型的整数值会被默认序列化为带.0后缀的字符串，比如0会变成"0.0"，传入copy_from后就会触发PostgreSQL的类型校验错误

方案1（推荐）：替换空值后强制转换目标列为整数类型 #

明确PostgreSQL表中对应为integer类型的列，在替换NaN后手动转为int类型即可，该方案不会影响其他真实浮点类型列的精度，示例代码如下：

def copy(conn, table, df):
    sio = StringIO()
    
    # 处理NaN
    df = set_empty_defaults_df(df)
    
    # 新增：转换PG中对应整数类型的列为int，下列表替换为你实际的整数字段名
    int_cols = ['hour', 'minute', 'day', 'month', 'year']
    df[int_cols] = df[int_cols].astype(int)
        
    sio.write(df.to_csv(index=None, header=None, sep="|"))
    sio.seek(0)
    
    print("Table %s" % (table))
        
    with conn.cursor() as c:    
        c.copy_from(
            file=sio,
            table=table,
            sep="|"
        )
        conn.commit()

如果你使用pandas 1.0及以上版本，也可以在导入数据时就将列声明为支持空值的Int64类型，就不会因为存在NaN自动转为float64。

方案2：修改to_csv的浮点输出格式 #

如果不想修改DataFrame的列类型，且所有浮点列的数值本质都是整数，可以在调用to_csv时指定float_format参数，将浮点数值输出为无小数位的格式：

sio.write(df.to_csv(index=None, header=None, sep="|", float_format='%.f'))

注意：如果存在需要保留小数的真实浮点列，不要使用该方案，否则会丢失精度。

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