Double类型列NULL值处理逻辑未按预期生效求助

我编写了format_item函数用于处理字符串和数值类型列的NULL值，预期逻辑为：浮点类型列的NULL值替换为0.00，字符串类型列的NULL值替换为空字符串。但实际运行时，数据库中Double类型列的NULL值被处理成了空字符串（日志中第三个列表的倒数第二个元素）。

相关代码 #

处理NULL值的format_item函数 #

def format_item(item):
    if item is None:
        if isinstance(item, (Decimal, float)):
            return 0.00
        else:
            return ""
    else:
        if isinstance(item, (Decimal, float)):
            return float(item)
        else:
            return str(item)

异常数据日志 #

['681738eb', 'Agi', '6817-abc', '5280-oou', 'xyz', 'ert', 'yuo', 'test1', 'garbage', 13456.76, 16148.12, 2691.36, '2023-11-30 16:16:38']
['681738eb', 'Agi', '6817-abc', '5280-oou', 'xyz', 'ert', 'yuo', 'test1', 'garbage', 13456.76, 16148.12, 13.92, '2023-12-01 08:48:33']
['681738eb', 'Agi', '6817-abc', '5280-oou', 'xyz', 'ert', 'yuo', 'test1', 'garbage', 13456.76, 16148.12,  '', '2023-11-30 16:17:14']

调用format_item的stored_procedure_call函数片段 #

def stored_procedure_call(SP_name, id, entity):
    logging.info(f"Fetching DB connection details.")
    try:
        # Load env file
        load_dotenv()
        # Create the connection object
        conn = mysql.connector.connect(
            user=os.getenv('USER_NAME'),
            password=get_db_password(os.getenv('RDS_HOST')),
            host=os.getenv('RDS_HOST'),
            database=os.getenv('DB_NAME'),
            port=os.getenv('PORT'))

        # Create a cursor
        cursor = conn.cursor()
    except Exception as error:
        logging.error("An unexpected error occurred: {}".format(error))

    try:
        # Call the stored procedure with the provided ID
        cursor.callproc(SP_name, [id, entity])
        conn.commit()

        result_list = []
        for result in cursor.stored_results():
            rows = result.fetchall()
            for row in rows:
                result_list.append(list(row))
                logging.info(row)

        if not result_list:
            return {
                'statusCode': 200,
                'body': json.dumps([])
            }

        result_list_serializable = [list(format_item(item) for item in tup) for tup in result_list]

        return {
            'statusCode': 200,
            'headers': {
                'Content-Type': 'application/json'
            },
            'body': json.dumps(result_list_serializable)
        }

核心问题出在format_item函数的判断逻辑：

1. 当item为None时，isinstance(item, (Decimal, float))永远返回False，因为None的类型是NoneType，不属于任何数值类型。这导致所有NULL值都会进入else分支，返回空字符串，完全忽略了列的原始类型。
2. 数据库返回的Double类型NULL值在Python中直接是None，丢失了原始列的类型元数据，无法通过isinstance判断它原本的数值类型属性。

方案一：基于列元数据判断类型 #

从数据库结果集中获取列的原始类型信息，处理NULL值时根据列类型决定替换值。修改stored_procedure_call函数如下：

def stored_procedure_call(SP_name, id, entity):
    logging.info(f"Fetching DB connection details.")
    try:
        load_dotenv()
        conn = mysql.connector.connect(
            user=os.getenv('USER_NAME'),
            password=get_db_password(os.getenv('RDS_HOST')),
            host=os.getenv('RDS_HOST'),
            database=os.getenv('DB_NAME'),
            port=os.getenv('PORT'))
        cursor = conn.cursor()
    except Exception as error:
        logging.error("An unexpected error occurred: {}".format(error))

    try:
        cursor.callproc(SP_name, [id, entity])
        conn.commit()

        result_list = []
        column_types = []
        for result in cursor.stored_results():
            # 获取每一列的类型标识
            column_types = [desc[1] for desc in result.description]
            rows = result.fetchall()
            for row in rows:
                result_list.append(list(row))
                logging.info(row)

        if not result_list:
            return {
                'statusCode': 200,
                'body': json.dumps([])
            }

        # 定义数值类型判断逻辑
        from mysql.connector import FieldType
        def is_numeric_col(col_type):
            return col_type in (FieldType.FLOAT, FieldType.DOUBLE, FieldType.DECIMAL)

        # 结合列类型处理每一行数据
        result_list_serializable = []
        for row in result_list:
            processed_row = []
            for idx, item in enumerate(row):
                if item is None:
                    processed_row.append(0.00 if is_numeric_col(column_types[idx]) else "")
                else:
                    if isinstance(item, (Decimal, float)):
                        processed_row.append(float(item))
                    else:
                        processed_row.append(str(item))
            result_list_serializable.append(processed_row)

        return {
            'statusCode': 200,
            'headers': {
                'Content-Type': 'application/json'
            },
            'body': json.dumps(result_list_serializable)
        }

方案二：SQL层提前处理NULL值 #

修改存储过程，在查询阶段直接替换NULL值，无需修改Python代码：

-- 数值类型列用0.00替换NULL
SELECT IFNULL(double_column, 0.00) AS double_column,
-- 字符串类型列用空字符串替换NULL
IFNULL(varchar_column, '') AS varchar_column
FROM your_target_table;

该方案从数据源层面解决问题，更高效且避免了Python端的类型判断误差。

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