20

I have a class in Python for retrieving all the columns in a table and return a JSON with this data.

The problem is at least one of those columns is a datetime and I can't seem to understand how to serialize the columns so I can generate a valid JSON.

My class is as follows:

class GetTodos(Resource):
    def get(self):
        con = cx_Oracle.connect('brunojs/bdpf5@127.0.0.1/orcl')
        cur = con.cursor()
        cur.execute("select * from organite_repository")
        r = [dict((cur.description[i][0], value) \
                for i, value in enumerate(row)) for row in cur.fetchall()]
        cur.connection.close()
        return (r[0] if r else None) if None else r

Any hints on this?

Bruno Fernandes
  • 427
  • 2
  • 6
  • 14

2 Answers2

51

JSON doesn't have a default datetime type, so this is why Python can't handle it automatically. So you need to make the datetime into a string one way or another. I think the best way is to write a custom handler to help the json module.

import datetime
import json

def datetime_handler(x):
    if isinstance(x, datetime.datetime):
        return x.isoformat()
    raise TypeError("Unknown type")

json.dumps(data, default=datetime_handler)
Michael Mulqueen
  • 1,033
  • 10
  • 11
  • I updated this with `raise x` for my case. – Bruno Fernandes Mar 09 '16 at 15:23
  • 1
    Use `json.JSONEncoder.default = datetime_handler` to parse datetime automatically without using `default=datetime_handler` every time. – ChaimG Feb 10 '17 at 15:11
  • I'm getting a TypeError with this answer, that says datetime_handler accepts 1 argument but JSON is trying to hand it 2. – adamcircle Jun 22 '17 at 18:03
  • Have just tried my original answer with Python 2.6.9 and 3.4.1 and it works with both. If you are following the answer from @ChaimG you may need to specify a self parameter as well because you've overriding this: https://docs.python.org/3/library/json.html#json.JSONEncoder.default – Michael Mulqueen Jun 23 '17 at 08:37
  • maybe would be wise to update json module and allow datetime conversion ? – comte Jun 28 '17 at 06:47
  • @comte The problem is that there is no standard representation for dates/datetimes in JSON. The obvious choice is to use ISO 8601 format, but others might store it in other ways (for example epoch time). So I don't think you'll ever see this changed in the stdlib, unless more data types are standardised in JSON at a later date. – Michael Mulqueen Jun 28 '17 at 09:25
  • @MichaelMulqueen, agree ISO8601 should be the one. Maybe python folks should handover java folks in defining JSON standard (after all Stanford annouced they'll stop teaching java in 101 courses!). More seriously, I guess it should be implemented by default with optional argument to stick to JSON official guidelines (something such as pure_json=False in relevant methods). It would 1) set a de-facto standard where it's lacking 2) avoid so many devs to reinvent the wheel for such a daily use issue... – comte Jun 29 '17 at 10:46
  • I had a list of datetime elements and I used a for loop to iterate over each one and used isoformat() to convert them... – aqteifan Nov 21 '19 at 16:20
  • I am using the "convert to string option" mentioned by @Michael Mulqueen in my flask app and it works like charm. Thanks you. – Shravya Mutyapu Jul 09 '21 at 04:50
4

A simple way to do it is to cast your data to string. That way, you will be able to dump with json.

>>> datetime.now()
datetime.datetime(2016, 3, 8, 11, 37, 24, 123639)

>>> str(datetime.now())
'2016-03-08 11:37:27.511053'

But, you could also implement a serializer to transform the data as you want.

feliperuhland
  • 59
  • 2
  • it should be like str(datetime.datetime.now()), because: [ERROR] AttributeError: module 'datetime' has no attribute 'now' – omalave Jun 17 '21 at 18:11
  • @omalave datetime.now() is possible provided you import datetime in this way --> >>>from datetime import datetime >>>datetime.now() >>> datetime.datetime(2022, 6, 20, 15, 31, 27, 900332) – SrTan Jun 20 '22 at 10:02