Python SQL: Error Decimal('1367') is not JSON serializable

Question

I've got an SQL statement in Python where I'm trying to get the top errors from my database.

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

Problem is with my last Join CalcSum then I get this error and I don't know how to handle it. Found more than enough topics about this, but not in this context. How can I handle this? Without this Join my Result is just fine, but I need this sum from CalcSum.

Ruben Helsloot · Accepted Answer · 2020-08-18 09:47:28Z

The problem is that psycopg2 prefers to return the more stable Decimal type over the python-standard float. That's great to prevent floating point errors, but it leaves the responsibility to make it JSON serialisable with the user.

If I understand your query correctly, both count and sum are actually integers. If that is true, just cast them to int. Otherwise, cast them to float.

alarms = []

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

If I can leave some recommendations about your code:

It's nicer to use list destructuring to give row[0] a more intuitive name:

alarms = []

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

And you can construct the list in one go using list comprehension:

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

Collectives™ on Stack Overflow

Python SQL: Error Decimal('1367') is not JSON serializable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related