[FIXED] "Row is not JSON serializable" error when sending result set to a Flask view

Issue

I'm tryin to send the result of a pyodbc query to my view but i get a TypeError: Object of type Row is not JSON serializable.

Suppose I have a table like this in SQL Server:

col1	col2	col3
1	val1	val2
2	val3	val4

I'm querying it through pyodbc and passing data to the view like this:

return render_template(
    'index.html'
    ,data={
        'foor':'bar'
        ,'rows':db.execute(
            'SELECT * '
            'FROM [' + table.schema_name + '].[' + table.table_name + ']'
        ).fetchall()
    }
)

If I print {{ data.rows }} I want to have [[1,"val1","val2"],[2,"val3","val4"]] but instead I get [(Decimal('1'), 'val1', 'val2'), (Decimal('2'), 'val3', 'val4')].

If I print(type(data.rows)) in the controller I obtain <class 'list'>.

Trying to convert it to JSON in the view with {{ data.rows|tojson }} results in TypeError: Object of type Row is not JSON serializable. How to achieve my need?

Solution

fetchall() returns a list of pyodbc.Row objects. Row objects are similar to tuples but, as the error message states, they are not JSON serializable.

results = crsr.execute(query).fetchall()
print(f"{type(results)} of type {type(results[0])}")
# <class 'list'> of type <class 'pyodbc.Row'>

json_string = json.dumps(results)
# TypeError: Object of type Row is not JSON serializable

We can convert the Row objects to true tuples so they can be serialized:

results = [tuple(row) for row in results]
print(f"{type(results)} of type {type(results[0])}")
# <class 'list'> of type <class 'tuple'>

json_string = json.dumps(results)
# no error (unless the tuples themselves contain elements that are not serializable)

If the tuples do contain elements that are not serializable (like Decimal objects) then we can coerce them to string …

json_string = json.dumps(results, default=str)

… or we can modify the query to CAST the decimals to floats, e.g., use

query = "SELECT CAST(x AS FLOAT) AS x FROM tbl"

instead of

query = "SELECT x FROM tbl"

Answered By - Gord Thompson