I'm trying to create a Django view that returns JSON data from Postgres (i.e. the data from Postgres is already of the JSON type).
I use the Django dev server and Postgres in my development environment, and I use Apache and Postgres (a different server) in my production environment.
This view works fine in my development environment:
def my_custom_sql(request):
cursor = connection.cursor()
cursor.execute("... the query ...")
return HttpResponse(cursor.fetchone(), mimetype="application/json")
But the output is different in my production environment.
To make it work in production, I changed the fourth line from:
return HttpResponse(cursor.fetchone(), mimetype="application/json")
to:
return HttpResponse(json.dumps(cursor.fetchone()[0]), mimetype="application/json")
These are the results (I added line returns):
Development Environment:
Using cursor.fetchone()
- Correct
[{"date":"2014-06-12","count":3,"sum":3},
{"date":"2014-06-13","count":null,"sum":3},
{"date":"2014-06-14","count":null,"sum":3},
{"date":"2014-06-15","count":null,"sum":3},
{"date":"2014-06-16","count":null,"sum":3},
{"date":"2014-06-17","count":null,"sum":3}]
Using json.dumps(cursor.fetchone()[0])
- Incorrect: not real JSON.
"[{\"date\":\"2014-06-12\",\"count\":3,\"sum\":3}, \n
{\"date\":\"2014-06-13\",\"count\":null,\"sum\":3}, \n
{\"date\":\"2014-06-14\",\"count\":null,\"sum\":3}, \n
{\"date\":\"2014-06-15\",\"count\":null,\"sum\":3}, \n
{\"date\":\"2014-06-16\",\"count\":null,\"sum\":3}, \n
{\"date\":\"2014-06-17\",\"count\":null,\"sum\":3}]"
Production Environment: (different data, ignore that)
Using cursor.fetchone()
- Incorrect: note the python "u" strings.
[{u'date': u'2014-06-11', u'count': 4, u'sum': 4},
{u'date': u'2014-06-12', u'count': 8, u'sum': 12},
{u'date': u'2014-06-13', u'count': 7, u'sum': 19},
{u'date': u'2014-06-14', u'count': 6, u'sum': 25},
{u'date': u'2014-06-15', u'count': 1, u'sum': 26},
{u'date': u'2014-06-16', u'count': 9, u'sum': 35},
{u'date': u'2014-06-17', u'count': 4, u'sum': 39}]
Using json.dumps(cursor.fetchone()[0])
- Correct
[{"date": "2014-06-11", "count": 4, "sum": 4},
{"date": "2014-06-12", "count": 8, "sum": 12},
{"date": "2014-06-13", "count": 7, "sum": 19},
{"date": "2014-06-14", "count": 6, "sum": 25},
{"date": "2014-06-15", "count": 1, "sum": 26},
{"date": "2014-06-16", "count": 9, "sum": 35},
{"date": "2014-06-17", "count": 4, "sum": 39}]
How can I get the same result in both my production and development environments?
cursor.fetchone()[0]
is returningtext
(str
) which is why it is not serializing properly to JSON. While in your production environmentcursor.fetchone()[0]
is returningjson
(dict
). – cpburnz Jun 30 at 14:17