JSON: TypeError: Decimal ('34 .3 ') is not JSON serializable
I am running a SQL query that returns a list of Decimals. When I try to convert this to JSON, I get a type error.
Request:
res = db.execute("""
SELECT CAST ((SUM(r.SalesVolume)/1000.0) AS decimal(6,1))
FROM RawData r
INNER JOIN Product p
ON r.ProductId = p.ProductId
INNER JOIN Calendar c
ON r.DayId = c.DayId
WHERE c.WeekCodeInYear BETWEEN 1 AND 12
AND
c.YearId = 2014
GROUP BY c.WeekCodeInYear """)
Results list:
[Decimal('34.3'), Decimal('50.9'), Decimal('31.5'), Decimal('23.3'), Decimal('19
.7'), Decimal('56.9'), Decimal('43.8'), Decimal('35.2'), Decimal('29.2'), Decima
l('43.7'), Decimal('42.6'), Decimal('23.4')]
code:
for row in res:
testlist.append (row[0])
print testlist
list = json.dumps(testlist)
And I get Unable to serialize error
Tried searching the web, doesn't help much. Please note that the final list will be displayed as input.
source to share
As the error says, the type Decimal
cannot be serialized directly to JSON. Given the cast Decimal
to float
, if you want to store this as a number, however, you can get round-off errors. i.e.
for row in res:
testlist.append(float(row[0]))
Or, alternatively, create a list using a list comprehension, this time I typed str
.
testlist = [str(row[0]) for row in res]
The latter is an appropriate representation because the type Decimal
can be unambiguously represented str
. You can take the original value like this
from decimal import Decimal
jlist = json.dumps(testlist) # don't use list as it predefined type
new_list = json.loads(jlist)
new_dlist = [Decimal(s) for s in new_list]
new_dlist
must be identical to the original templist
.
source to share
Use an override default
:
import json
from decimal import Decimal
def default(obj):
if isinstance(obj, Decimal):
return str(obj)
raise TypeError("Object of type '%s' is not JSON serializable" % type(obj).__name__)
json.dumps(testlist, default=default)
Or just str
to Decimal
an object:
for row in res:
testlist.append (str(row[0]))
json.dumps(testlist)
source to share