psycopg2 execute returns datetime instead of a string

Question

cur.execute("SELECT \
                title, \
                body, \
                date \ # This pgsql type is date
             FROM \
                table \
             WHERE id = '%s';", id)

response = cur.fetchall()

print response

As an example this gives me: -

[('sample title', 'sample body', datetime.date(2012, 8, 5))]

Which can't be passed to things like json.dumps so I'm having to do this: -

processed = []

for row in response:
    processed.append({'title' : row[0], 
                      'body' : row[1], 
                      'date' : str(row[2])
                     })

Which feels like poor form, does anyone know of a better way of handling this?

Electromotor

Answer 1

First of all, what did you expect to be returned from a field with a "date" data type? Explicitly, date, and driver obviously performs as expected here.

So your task is actually to find out how to say json encoder to encode instances of datetime.date class. Simple answer, improve encoder by subclassing a built-in one:

from datetime import date
import json

class DateEncoder(json.JSONEncoder):

    def default(self, obj):
        if isinstance(obj, date):
            return str(obj)
        return json.JSONEncoder.default(self, obj)

Usage (you need to explicitly say you're using custom encoder):

json.dumps(_your_dict, cls=DateEncoder)

Groat

Answer 2

As the previous answer suggests, this is the expected result from a query on a date field.However, one can simplify a lot more in the query itself. If you go through the postgres docs you can find the to_char() function. This will lead to simple change in your query

cur.execute("SELECT \
            title, \
            body, \
            to_char(date, 'YYY-MM-DD') \ # This pgsql type is date
         FROM \
            table \
         WHERE id = '%s';", id)

Klutz

Answer 3

Instead of adding encoding, you can customise the psycopg2 mapping between SQL and Python data types, as explained here:

https://www.psycopg.org/docs/advanced.html#type-casting-of-sql-types-into-python-objects

The code template is

date_oid = 1082 # id of date type, see docs how to get it from db
def casting_fn(val,cur):
  # process as you like, e.g. string formatting
# register custom mapping
datetype_casted = psycopg2.extensions.new_type((date_oid,), "date", casting_fn)
psycopg2.extensions.register_type(datetype_casted)

Once this is done, instead [('sample title', 'sample body', datetime.date(2012, 8, 5))] you will receive [('sample title', 'sample body', '2012-08-05')]

Mutate