Convert data retrieved from MySQL database into JSON object using Python/Django

Question

I have a MySQL database called People which contains the following schema <id,name,foodchoice1,foodchoice2>. The database contains a list of people and the two choices of food they wish to have at a party (for example). I want to create some kind of Python web-service that will output a JSON object.

An example of output should be like:

{
"guestlist": [
{"id":1,"name":"Bob","choice1":"chicken","choice2":"pasta"},{"id":2,"name":"Alice","choice1":"pasta","choice2":"chicken"}
], 
"partyname": "My awesome party", "day": "1", "month": "June", "2010": "null"
}

Basically every guest is stored into a dictionary 'guestlist' along with their choices of food. At the end of the JSON object is just some additional information that only needs to be mentioned once.

Currently, I have a Django Model/View setup where the Model will query the server, retrieve the results and store them in variables. The View should call the Model, and be able to just create the JSON object, but I've been running into some problems. Do I need to use a standard Model/View structure of Django or is there a simple solution?

Answer 1

You can serialize any django model: http://docs.djangoproject.com/en/1.2/topics/serialization/#topics-serialization

The serializers support both xml and json, and they take in querysets. Take a look at: http://docs.djangoproject.com/en/1.2/topics/serialization/#id2

Another approach is to build a dictionary yourself using the orm and serialize it using simplejson.

Answer 2

If you ever need anything more fancy than just a dump of a specific queryset in JSON, consider using django-piston to help automate the creation of APIs.