Inconsistent results when exporting JSON from Postgres using Django

Question

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?

In your development environment it looks like cursor.fetchone()[0] is returning text (str) which is why it is not serializing properly to JSON. While in your production environment cursor.fetchone()[0] is returning json (dict). — ohmu
– ohmu, Commented Jun 30, 2014 at 14:17

Jeremy · Accepted Answer · 2014-06-30 14:11:02Z

3

I had something similar happen as well. My sql was returning json. Django on my dev environment was turning the json into a dictionary containing a list of lists, but django in production was returning a dictionary containing a list of strings.

The issue I had was different versions of psycopg2. I was running 2.5 in development, but 2.4 in production.

Check all your packages to be sure your dev environment is the same as your production environment.

answered Jun 30, 2014 at 14:11

Jeremy

1,8651 gold badge17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inconsistent results when exporting JSON from Postgres using Django

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related