Pyhton SQL:错误十进制（'1367'）不可JSON序列化

Question

我有一个python中的SQL语句，我试图从数据库中获取最大错误

def alarms(session, machine_id, ts, ts_start, machine_serial):
    stmt_raw = '''
        WITH count_data AS (
        SELECT
            identifier.name,
            PDA_BitCount.ts,
            PDA_BitCount.high,
            alarm_info.ala_text
        FROM
            machine
        INNER JOIN
            identifier
            ON
                identifier.machine_id = :machineid_arg
        INNER JOIN
            PDA_BitCount
            ON
                PDA_BitCount.identifier_id = identifier.id
        LEFT JOIN
            alarm_info
            ON
                alarm_info.ala_group = CAST(SUBSTR(identifier.name, 8, 2) AS INTEGER) AND
                alarm_info.ala_number = CAST(SUBSTR(identifier.name, 11, 2) AS INTEGER) AND
                alarm_info.machine_id = :machineid_arg
        WHERE
            machine.serial = :machine_serial_arg AND
            identifier.name LIKE 'Alarm_G%' AND
            PDA_BitCount.ts > :ts_start_arg AND 
            PDA_BitCount.ts < :ts_arg AND
            alarm_info.ala_language = 'de'
        ), commulated_data AS (
        SELECT
            count_data.name AS alias,
            COUNT(*) AS count,
            REPLACE(ala_text, '{TextSnippet_6}', '') AS ala_text
        FROM
            count_data
        WHERE
            count_data.high = TRUE
        GROUP BY
            count_data.name
        ORDER BY
            count DESC
        ), CalcSum AS (
        SELECT SUM(commulated_data.count) AS sum
        FROM commulated_data
        ) SELECT *
        FROM commulated_data
        JOIN CalcSum
        WHERE commulated_data.count != 0
        LIMIT 3
    '''
    stmt_args = {
        'machineid_arg': machine_id,
        'ts_arg': ts,
        'ts_start_arg': ts_start,
        'machine_serial_arg': machine_serial,
        'includes_arg': [''],
        'excludes_arg': [
            'G01N01', 'G01N02', 'G01N03', 'G01N04', 'G01N05', 'G01N06', 'G01N07', 'G01N08', 'G01N09',
            'G01N10', 'G01N11', 'G01N12', 'G01N13', 'G01N14', 'G01N15', 'G01N16', 'G01N17', 'G01N18',
            'G01N19', 'G01N20', 'G01N21', 'G01N22', 'G01N23', 'G01N24', 'G01N25', 'G01N26', 'G01N27',
            'G01N28', 'G01N29', 'G01N30', 'G01N31', 'G01N32', 'G01N33', 'G01N34', 'G01N35', 'G01N36',
            'G01N37', 'G01N38', 'G01N39', 'G01N40', 'G01N41', 'G01N42', 'G01N43', 'G01N44', 'G01N45',
            'G01N46', 'G01N47', 'G01N48', 'G01N49', 'G01N50', 'G01N51', 'G01N52', 'G01N53', 'G01N54',
            'G01N55', 'G01N56', 'G01N57', 'G01N58', 'G01N59', 'G01N60', 'G01N61', 'G01N62', 'G01N63',
            'G01N64', 'G24N01', 'G24N02', 'G24N03', 'G24N04', 'G24N05', 'G24N06', 'G24N07'
        ],
    }

    stmt = text(stmt_raw).columns(
        # ts_insert = ISODateTime
    )

    result = session.execute(stmt, stmt_args)

    alarms = []

    for row in result:
        alarms.append({
            'alias': row[0],
            'count': row[1],
            'ala_text': row[2],
            'sum': row[3],
        })

    return alarms

问题是我的上一个JoinCalcSum出现了这个错误，我不知道如何处理它。找到了足够多关于此的主题，但不在此上下文中。我该怎么办？没有这个连接，我的结果很好，但是我需要这个来自CalcSum的sum

Answer 1

问题是psycopg2更喜欢返回比python标准float更稳定的Decimal类型。这对于防止浮点错误很好，但它让用户有责任使其JSON可序列化

如果我正确理解您的查询，那么count和sum实际上都是整数。如果这是真的，只需将它们转换为int。否则，将它们强制转换为float

alarms = []

for row in result:
    alarms.append({
        'alias': row[0],
        'count': int(row[1]),
        'ala_text': row[2],
        'sum': int(row[3]),
    })

---

如果我能就您的代码留下一些建议：

更好的做法是使用列表分解为row[0]提供一个更直观的名称：

alarms = []

for alias, count, ala_text, sum in result:
    alarms.append({
        'alias': alias,
        'count': int(count),
        'ala_text': ala_text,
        'sum': int(sum),
    })

您可以使用列表理解一次性构建列表：

alarms = [{
    'alias': alias,
    'count': int(count),
    'ala_text': ala_text,
    'sum': int(sum),
} for alias, count, ala_text, sum in result]