0

When getting results from a mysql database using dict cursor class, the types are converted to python's types and are "included" in the dictionary's values (I'm sorry I don't know how to better word this).

I've looked for examples and I can't seem to see others getting their results in the "format" I do.

This is the result i get after cursor.fetchall():

{
'description': 'Outside',
'date': datetime.datetime(2020, 5, 4, 15, 0),
'temperature': Decimal('14.9'),
'humidity': Decimal('48.0')
}

This is giving me issues when trying to convert the whole dictionary to json at once with json.dumps(): TypeError: the JSON object must be str, bytes or bytearray, not Decimal

Is there a way for the cursor to return just {'temperature' : 14.9} as opposed to {'temperature': Decimal('14.9')}?

Same with the date.

My apologies if I am not including all the necessary information, I'm not sure what other info I could share to help you help me. Please let me know and I'll gladly add it.

To connect and create the cursor I just followed the example on github from pymysql

When opening the connection I'm not passing the charset option, my db is "utf8_general_ci". I tried passing "utf8" and "utf8mb4" and it didn't make a difference.

Thanks in advance.

Josar
  • 1
  • I think the simplest method is to use ```simplejson``` package as described in https://stackoverflow.com/users/2482605/fearlessfuture answer in the SO question: https://stackoverflow.com/questions/16957275/python-to-json-serialization-fails-on-decimal – pink spikyhairman May 04 '20 at 13:57
  • This is a neat approach to json serialisation https://hynek.me/articles/serialization/ – snakecharmerb May 04 '20 at 13:58
  • Generally you want to handle this when serialising to JSON, rather than when fetching from the database, because converting from Decimal to other numeric types could lead to inaccuracies in calculations. – snakecharmerb May 04 '20 at 14:18
  • Thanks everyone for your assistance. @snakecharmerb thanks for your advice on treating after the database query and during json serialisation. I've followed up the link you provided and ended up creating an additional json encoder function to add to the 'default=' option. – Josar May 05 '20 at 12:33

0 Answers0