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?

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)

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)