CODEWITHSUNDEEP
djangojson
geekchic
geekchic

Reputation: 2431

Converting result of MySQL query in Django to JSON

So I query my database using a mySQL query like so: 

    cursor = connection.cursor()
    cursor.execute("select winner,count(winner) as count from DB")
    data = cursor.fetchall()

Now I want to send the table in data to my app (as a GET request) in JSON. Doing this is not sending a properly formatted JSON response and I am unable to parse it on the client side. 

    return HttpResponse(json.dumps(data), content_type='application/json;charset=utf8')

The json.dumps(data) returns this: 

    [["John Doe", 45]]

Any help in this regard would be appreciated.

Upvotes: 0

Views: 8856

Answers (3)

milosz
milosz

Reputation: 658

from django.http import JsonResponse
from django.db import connections
from django.http import HttpResponse
import json

def testRawQuery(request):
    cursor = connections['default'].cursor()
    cursor.execute("select winner,count(winner) as count from DB")
    objs = cursor.fetchall() 
    json_data = []
    for obj in objs:
        json_data.append({"winner" : obj[0], "count" : obj[1]})
    return JsonResponse(json_data, safe=False)

Upvotes: 0

daveoncode
daveoncode

Reputation: 19578

The JSON is properly formatted, but you are dumping a list, you should dump a dictionary instead... something like:

myData = {'people': data}

json.dumps(myData)

The point is this: a valid json response must start and end with curly braces, so in order to serve a valid json you have to dump a Python dictionary object as a "root object"... in other words you need at least an object with a key.

From http://json.org

JSON is built on two structures:

A collection of name/value pairs. In various languages, this is realized as an object, record, struct, dictionary, hash table, keyed

list, or associative array. An ordered list of values. In most languages, this is realized as an array, vector, list, or sequence.

Upvotes: 2

Henrik Andersson
Henrik Andersson

Reputation: 47172

from django.core import serializers

json_data = serializers.serialize('json', data)
return HttpResponse(json_data, mimetype='application/json')

However not everything can be serialized like this into JSON, some things need a custom encoder

You should use a model and the ORM instead of writing your own SQL. You could easily convert your statement to this simple model and succinctly ORM call.

class Winner(models.Model):
   name = models.CharField()

and your database call would now be Winner.objects.all() which would give all winners

and with the count

Winner.objects.annotate(wins=Count('name'))

Upvotes: 0

Related Questions