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:

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?

CodePudding user response：

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"