convert a MySQL decimal into an acceptable format to be able to serialize it

Question

Possible Duplicate:
Python JSON serialize a Decimal object

I have the following SQL:

SELECT 
    concat(UNIX_TIMESTAMP(date), '000') as datetime, 
    SUM(royalty_price) as sales 
FROM 
    sales_raw 
GROUP BY 
    datetime

The results look something like:

datetime        sales
1337151600000   1045.71 (decimal)
1337238000000   478.04
1337324400000   300.96
1337410800000   289.02

From this I get the following error:

Exception Type: TypeError at /ajax/graph/
Exception Value: Decimal('1045.71') is not JSON serializable

How would I convert the decimal into an acceptable format in mysql to be able to serialize this data?

I would prefer to do it within mysql if possible. – David542 May 31 '12 at 19:43 — David542, May 31 '12 at 19:43

score 4 · Accepted Answer · answered May 31 '12 at 20:02

4

If you must do it in MySQL, just CAST to a string:

SELECT 
    CONCAT(UNIX_TIMESTAMP(date), '000') AS datetime, 
    CAST(SUM(royalty_price) AS CHAR)    AS sales
FROM 
    sales_raw 
GROUP BY 
    datetime

answered May 31 '12 at 20:02

eggyal

122,705
18
212
237

This would serialize the field into a `JSON` string rather than number (`"1045.71"` rather than `1045.71`). Not sure that what the author wants. – Quassnoi May 31 '12 at 20:10
@Quassnoi: Good point; if the OP wants a floating point number, he can cast to `DOUBLE` instead - although I suppose one should caution about the approximate nature of floating point... since JSON doesn't support fixed-point types, perhaps it should be serialised as two integers (those to the left and right of the decimal point)? Or else go with a string and perform type conversion within one's application. – eggyal May 31 '12 at 20:13

convert a MySQL decimal into an acceptable format to be able to serialize it

1 Answers1