使用df.to_dict()执行sqlalchemy.update时触发数值范围错误

我正在实现用pandas DataFrame更新SQL Server数据库表的功能，逻辑是先找出数据库表和DataFrame的重叠ID，再用sqlalchemy.update和bindparam更新这些ID对应的记录。

实现代码 #

def update_table(engine, user_table, dataframe: pd.DataFrame):

    dataframe = dataframe.add_prefix("b_")

    stmt = (
        update(user_table)
        .where(
            user_table.c.ID == bindparam("b_ID")
        )
        .values(
            other_column_a=bindparam("b_other_column_a"),
            other_column_b=bindparam("b_other_column_b"),
            other_column_c=bindparam("b_other_column_c")
        )
    )

    with engine.connect() as conn:
        result = conn.execute(stmt, dataframe.to_dict(orient="records"))
        conn.commit()
        return result


def main():

# 其他代码（engine已通过sqlalchemy.create_engine()初始化完成）

    # 查找需要更新的ID
    ids_to_update = old_ids.intersection(new_ids)
    if isinstance(ids_to_update, set) & (len(ids_to_update) != 0):
        with engine.connect() as conn:

            meta_data = MetaData(schema="uesm")
            meta_data.reflect(bind=conn)
            user_table = meta_data.tables[f"uesm.{table_name}"]

            df_to_update = new_dataset[
                new_dataset[id_names[table_name]].isin(ids_to_update)
            ]

            result = update_table(engine, user_table, df_to_update)

            print(f"Updated {result.rowcount} records in table {table_name}.")

生成的SQL语句 #

UPDATE schema_name.table_name SET other_column_a=?, other_column_b=?, other_column_c=? WHERE schema_name.table_name.[ID] = ?

错误信息 #

sqlalchemy.exc.DataError: (pyodbc.DataError) ('22003', '[22003] [Microsoft][ODBC Driver 18 for SQL Server]Numeric value out of range (0) (SQLExecDirectW)')

注：传入的数值都不超过3位，目标列类型是decimal(8,3)。

表DDL #

CREATE TABLE [schema_name].[table_name](
    [ID] [nvarchar](200) NOT NULL,
    [valid_from] [date] NOT NULL,
    [valid_to] [date] NOT NULL,
    [column_A] [nvarchar](200) NOT NULL,
    [column_B] [nvarchar](200) NOT NULL,
    [column_C] [decimal](8, 3) NOT NULL,
    [column_D_flag] [char](1) NOT NULL,
    [column_E] [nvarchar](200) NULL,
 CONSTRAINT [PK_table_name] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UNIQUE_UEBERGABESTELLE] UNIQUE NONCLUSTERED 
(
    [valid_from] ASC,
    [valid_to] ASC,
    [column_A] ASC,
    [column_B] ASC,
    [column_C] ASC,
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

传入的字典数据示例 #

dict = [
    {
        "b_ID": "AMAG_Ranshofen 110_110_1.0",
        "b_valid_from": Timestamp("2023-01-01 00:00:00"),
        "b_valid_to": Timestamp("2050-01-01 00:00:00"),
        "b_column_A": "AMAG",
        "b_column_B": "Ranshofen 110",
        "b_column_C": 110,
        "b_column_D_flag": 0,
        "b_column_E": nan,
    },
    {
        "b_ID": "EKW_Großraming 110_110_1.0",
        "b_valid_from": Timestamp("2023-01-01 00:00:00"),
        "b_valid_to": Timestamp("2050-01-01 00:00:00"),
        "b_column_A": "EKW",
        "b_column_B": "Großraming 110",
        "b_column_C": 110,
        "b_column_D_flag": 0,
        "b_column_E": nan,
    }
]

疑问 #

是不是nan导致的问题？但nan只出现在非数值列里。

排查方向与解决方法 #

• 修正column_D_flag的类型：数据库中该列是char(1)，但你传入的是整数0。SQLAlchemy不会自动转换类型，把0改为字符串"0"即可，比如在DataFrame里执行df_to_update['column_D_flag'] = df_to_update['column_D_flag'].astype(str)。
• 处理NaN值：pandas的nan无法直接映射到SQL的NULL，把DataFrame中的nan替换为None，执行df_to_update = df_to_update.where(pd.notnull(df_to_update), None)。
• 显式指定绑定参数类型：对于数值列（如column_C），显式声明参数类型匹配数据库的decimal(8,3)：

  from sqlalchemy import Numeric
  stmt = (
      update(user_table)
      .where(user_table.c.ID == bindparam("b_ID"))
      .values(
          column_C=bindparam("b_column_C", type_=Numeric(8,3)),
          # 其他列按需添加
      )
  )
  

• 检查DataFrame列类型：确认column_C是数值类型，没有被误转为字符串；所有字符类型列都保持字符串格式。

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