向Oracle插入含浮点值的Pandas DataFrame时遇SQLAlchemy错误求助

插入整数、字符串类型数据到Oracle数据库正常，但插入浮点类型时，SQLAlchemy抛出如下错误：

sqlalchemy.exc.ArgumentError: Oracle FLOAT types use 'binary precision', which does not convert cleanly from decimal 'precision'.  Please specify this type with a separate Oracle variant, such as Float(precision=53).with_variant(oracle.FLOAT(binary_precision=176), 'oracle'), so that the Oracle specific 'binary_precision' may be specified accurately.

原代码 #

import pandas as pd
import os
import sqlalchemy as sa
from sqlalchemy import text
from sqlalchemy.dialects import oracle


oracle_db = sa.create_engine('oracle://username:password@instance/?service_name=MBCDFE')
conn = oracle_db.connect()
df = pd.DataFrame({
    #"column1": [1, 1, 1], #- inserting into DB works with just numbers
    "column1": [1.2, 1.2, 1.2],
    "column2": ["a", "bb", "c"],
    "column3": ["K", "L", "M"]
})

df.to_sql("python", conn, if_exists="replace", index=True)
conn.commit()
conn.close()

解决方案 #

在df.to_sql()中通过dtype参数为浮点列指定Oracle专属的FLOAT类型，明确设置二进制精度。修改后的代码如下：

import pandas as pd
import sqlalchemy as sa
from sqlalchemy.dialects import oracle


oracle_db = sa.create_engine('oracle://username:password@instance/?service_name=MBCDFE')
conn = oracle_db.connect()
df = pd.DataFrame({
    "column1": [1.2, 1.2, 1.2],
    "column2": ["a", "bb", "c"],
    "column3": ["K", "L", "M"]
})

# 为浮点列指定Oracle兼容的类型
dtype = {
    "column1": sa.Float(precision=53).with_variant(oracle.FLOAT(binary_precision=176), 'oracle')
}

df.to_sql("python", conn, if_exists="replace", index=True, dtype=dtype)
conn.commit()
conn.close()

说明 #

• Oracle的FLOAT类型使用二进制精度，而SQLAlchemy默认的Float类型基于十进制精度，两者无法直接转换，因此需要通过with_variant为Oracle数据库指定专门的类型变体。
• binary_precision=176对应Oracle中最大的FLOAT精度，你可以根据实际存储需求调整这个值（比如53对应双精度浮点数）。

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