0

I have created in postgresql a table, using the code below :

create table spyResults (id serial not null primary key, info jsonb not null);

Now, in Python I want to insert the data into that table. I'm passing the data with the following code:

cur.execute("INSERT INTO %s(info) VALUES (%s)",[AsIs('spyResults'),json.dumps(pDoc)])

pDoc is the Python dictionary and now I'm converting this to json and passing to the query, but the problem is in the pDoc dictionary we have: 

 datetime.datetime(2018, 3, 8, 10, 29, 49, 178285) also

when i am trying insert we are getting below error:

datetime.datetime(2018, 3, 8, 10, 29, 49, 178285) is not JSON serializable

Could please suggest how to resolve this issue. Thanks.

EDIT:its not duplicate questions as because we are not getting datetime object not as parameter, its coming with in the dictionary, we are able to insert the pDoc asis into the mongodb, i am expecting same with postgreSQL as well.

Sanjay
  • 75
  • 10
  • Edited please check once again and confirm. – Sanjay Mar 08 '18 at 16:49
  • Recommend reopening this because the duplication link does not address psycopg2. It is true that the immediate issue for the poster is simply the way that json.dumps has been called. However, psycopg2 has some specific APIs that handle the full scenario better. In particular, creating a subclass of psycopg2.extras.Json and passing it to psycopg2.extensions.register_adapter. – Dane White Aug 11 '21 at 17:59
  • Since this is currently closed, here's some code that uses psycopg2 APIs to register a JSON handler that can serialize dates. `class JsonWithDates(psycopg2.extras.Json): @staticmethod def _convert_date(obj): if isinstance(obj, (datetime, date)): return obj.isoformat() raise TypeError('Type {} not serializable'.format(type(obj))) def dumps(self, obj): return json.dumps(obj, default=JsonWithDates._convert_date) psycopg2.extensions.register_adapter(dict, JsonWithDates)` – Dane White Aug 11 '21 at 18:13
  • Registering the JSON adapter, your call would be `cur.execute("INSERT INTO %s(info) VALUES (%s)",[AsIs('spyResults'), pDoc])` – Dane White Aug 12 '21 at 00:02

1 Answers1

1

json cannot serialize dates out of the box. The easiest solution is to pass either a datetime object or perhaps a string, which can be achieved with your_datetime_object.isoformat() or with the strftime method if you wish to use a different format.

turnip
  • 2,246
  • 5
  • 30
  • 58
  • but in the postgreSQL timestamp is storing as string but if we want to do query against on date, how can we do that, like greater then current date or less then some other date? – Sanjay Mar 08 '18 at 21:39
  • You can query your database from Python like this: `cur.execute("SELECT * FROM my_table WHERE my_date > (%s)", datetime.date(2018, 03, 09))`. Passing a string instead should also work, as long as the format matches the format your date column expects. – turnip Mar 09 '18 at 09:46