How to send data generated from python to jquery datatable for rendering
Question: **A] Summary of the problem:**

Using jquery datatable (<http://www.datatables.net/>) on the html page, Want
to send data generated by query from python to javascript, so that it can be
printed in the table. If someone can provide a sample implementation for this
or a starter link, it would be awesome.

**B] Models structure:**

The hierarchical relationship between the models is as follows:

UserReportecCountry (one) to UserReportedCity(many)

UserReportedCity(one) to UserReportedStatus(many)

    
    
    class UserReportedCountry(db.Model):
      country_name = db.StringProperty( required=True,
                              choices=['Afghanistan','Aring land Islands']
                             )
    
    class UserReportedCity(db.Model):
      country = db.ReferenceProperty(UserReportedCountry, collection_name='cities')
      city_name = db.StringProperty(required=True)   
    
    class UserReportedStatus(db.Model):
      city = db.ReferenceProperty(UserReportedCity, collection_name='statuses')
      status = db.BooleanProperty(required=True)
      date_time = db.DateTimeProperty(auto_now_add=True)
    

**C] HTML code excerpt**

The HTML code includes jquery , datatable javascript libraries. The datatable
javascript library is configured to allow multicolumn sorting.

    
    
    <!--importing javascript and css files -->
    <style type="text/css">@import "/media/css/demo_table.css";</style>  
    <script type="text/javascript" language="javascript" src="/media/js/jquery.js"></script>
    <script type="text/javascript" src="/media/js/jquery.dataTables.js"></script>
    
    <!-- Configuring the datatable javascript library to allow multicolumn sorting -->
    <script type="text/javascript">
        /* Define two custom functions (asc and desc) for string sorting */
        jQuery.fn.dataTableExt.oSort['string-case-asc']  = function(x,y) {
            return ((x < y) ? -1 : ((x > y) ?  1 : 0));
        };
    
        jQuery.fn.dataTableExt.oSort['string-case-desc'] = function(x,y) {
            return ((x < y) ?  1 : ((x > y) ? -1 : 0));
        };
    
        $(document).ready(function() {
        /* Build the DataTable with third column using our custom sort functions */
        // #user_reported_data_table is the name of the table which is used to display the data reported by the users
        $('#user_reported_data_table').dataTable( {
            "aaSorting": [ [0,'asc'], [1,'asc'] ],
            "aoColumns": [
                null,
                null,
                { "sType": 'string-case' },
                null
            ]
        } );
    } );
    </script>
    
    <!-- Table containing the data to be printed--> 
    <div id="userReportedData">
    <table cellpadding="0" cellspacing="0" border="0" class="display" id="user_reported_data_table">
        <thead>
            <tr>
                <th>Country</th>
                <th>City</th>
                <th>Status</th>
                <th>Reported at</th>
            </tr>
        </thead>
    
        <tbody>
            <tr class="gradeA">
                <td>United Status</td>
                <td>Boston</td>
                <td>Up</td>
                <td>5 minutes back</td>
            </tr>
        </tbody>
    </table>    
    

**C] python code exceprt:**

The code excerpt does the query of the data, puts the data in a "template" and
send it to the HTML page ( this is off-course not working right now :( )

    
    
    __TEMPLATE_ALL_DATA_FROM_DATABASE = 'all_data_from_database'
    def get(self): 
      template_values = {
            self.__TEMPLATE_ALL_DATA_FROM_DATABASE: self.get_data_reported_by_users()
        }
    
        self.response.out.write(template.render(self.__MAIN_HTML_PAGE, template_values))
    
    def get_data_reported_by_users(self):
        return db.GqlQuery("SELECT * FROM UserReportedCountry ORDER BY country_name ASC")         
    

**D] Technologies being used:**

1] Jquery

2] Jquery datatable

3] Google app engine

4] Python

5] Django.

thank you for reading.

[EDIT#1]

Code based on the response given by @Mark

Tried the following

    
    
    <!-- script snippet to setup the properties of the datatable(table which will contain site status    reported by the users) -->
    <script type="text/javascript">
        /* Define two custom functions (asc and desc) for string sorting */
        jQuery.fn.dataTableExt.oSort['string-case-asc']  = function(x,y) {
            return ((x < y) ? -1 : ((x > y) ?  1 : 0));
        };
    
        jQuery.fn.dataTableExt.oSort['string-case-desc'] = function(x,y) {
            return ((x < y) ?  1 : ((x > y) ? -1 : 0));
        };
    
        $(document).ready(function() {
        /* Build the DataTable with third column using our custom sort functions */
        // #user_reported_data_table is the name of the table which is used to display the data reported by the users
        $('#user_reported_data_table').dataTable( {
            "aaSorting": [ [0,'asc'], [1,'asc'] ],
            "aoColumns": [
                null,
                null,
                { "sType": 'string-case' },
                null
            ],
            /* enabling serverside processing, specifying that the datasource for this will come from  
               file ajaxsource , function populate_world_wide_data
            */
            "bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": "/ajaxsource/populate_world_wide_data"
        } );
    } );
    </script>
    
    <div id="userReportedData">
    <table cellpadding="0" cellspacing="0" border="0" class="display" id="user_reported_data_table">
        <thead>
            <tr>
                <th>Country</th>
                <th>City</th>
                <th>Status</th>
                <th>Reported at</th>
            </tr>
        </thead>
        <tbody>
    
        </tbody>
    </table>    
    

Python code, name of the file is ajaxsource.py

from django.utils import simplejson from google.appengine.ext import db

    
    
    def populate_world_wide_data(self,request):
        my_data_object = db.GqlQuery("SELECT * FROM UserReportedCountry ORDER BY country_name ASC") 
        json_object = simplejson.dumps(my_data_object)        
        self.response.out.write( json_object, mimetype='application/javascript')
    

This however only showed "processing" on the table.

Couple of queries, How would the datatable know where to print the country,
where to print the city and status?

[EDIT#2] Code based on the response given by @Abdul Kader

    
    
    <script type="text/javascript" src="/media/js/jquery.dataTables.js"></script>
    
    <!-- script snippet to setup the properties of the datatable(table which will contain site status reported by the users) -->
    <script type="text/javascript">
        /* Define two custom functions (asc and desc) for string sorting */
        jQuery.fn.dataTableExt.oSort['string-case-asc']  = function(x,y) {
            return ((x < y) ? -1 : ((x > y) ?  1 : 0));
        };
    
        jQuery.fn.dataTableExt.oSort['string-case-desc'] = function(x,y) {
            return ((x < y) ?  1 : ((x > y) ? -1 : 0));
        };
    
        $(document).ready(function() {
        /* Build the DataTable with third column using our custom sort functions */
        // #user_reported_data_table is the name of the table which is used to display the data reported by the users
        $('#user_reported_data_table').dataTable( {
            "aaSorting": [ [0,'asc'], [1,'asc'] ],
            "aoColumns": [
                null,
                null,
                { "sType": 'string-case' },
                null
            ]
        } );
    } );
    </script>
    
    
    <!-- Table containing the data to be printed--> 
    <div id="userReportedData">
    <table cellpadding="0" cellspacing="0" border="0" class="display" id="user_reported_data_table">
        <thead>
            <tr>
                <th>Country</th>
                <th>City</th>
                <th>Status</th>
                <th>Reported at</th>
            </tr>
        </thead>
    
       <tbody>
        <tr class="gradeA">
             {% for country in all_data_from_database %}
             <td>{{country}}</td>
             {%endfor%}
        </tr>
        </tbody>
    </table>  
    

Python code --

    
    
    __TEMPLATE_ALL_DATA_FROM_DATABASE = 'all_data_from_database'
    
    def get(self): 
        template_values = {
            self.__TEMPLATE_ALL_DATA_FROM_DATABASE: self.get_data_reported_by_users()
        }
    
        #rendering the html page and passing the template_values
        self.response.out.write(template.render(self.__MAIN_HTML_PAGE, template_values))
    
    def get_data_reported_by_users(self):
        return db.GqlQuery("SELECT * FROM UserReportedCountry ORDER BY country_name ASC") 
    

Item printed in the html page:

![enter image description here](http://i.stack.imgur.com/CkufL.png)

[EDIT#3] **EDIT that has worked.**

I modified the solution given by @Abdul Kader a bit and the following has
worked

HTML code:

    
    
    <!-- Table containing the data to be printed--> 
    <div id="userReportedData">
    <table cellpadding="0" cellspacing="0" border="0" class="display" id="user_reported_data_table">
        <thead>
            <tr>
                <th>Country</th>
                <th>City</th>
                <th>Status</th>
                <th>Reported at</th>
            </tr>
        </thead>
    
       <tbody>
    
        {% for country in countries %}
            {%for city in country.cities %}
                {%for status in city.statuses %}
                    <tr class="gradeA">
                        <td>{{country.country_name}}</td>
                        <td>{{city.city_name}}</td>
                        <td>{{status.status}}</td>
                        <td>{{status.date_time }}</td>
                    </tr>
                {%endfor%}  
            {%endfor%}      
        {%endfor%}
    
        </tbody>
    </table>  
    

Python code:

def get(self):

    
    
       __TEMPLATE_ALL_DATA_FROM_DATABASE = 'countries'
    
        country_query = UserReportedCountry.all().order('country_name')
        country = country_query.fetch(10)
    
        template_values = {
            self.__TEMPLATE_ALL_DATA_FROM_DATABASE: country
        }
    
        self.response.out.write(template.render(self.__MAIN_HTML_PAGE, template_values))
    

Enhancement Request: I believe this is a very basic way to do this and there
might be a solution that can involve a bit of ajax or more elegance. If
someone has a example or a open source project that is using datatables based
on python, please let me know.

Code review request: Can someone please review the code that i have done and
let me know if i am doing a mistake or something that can be done better or in
a more efficient manner.


Answer: In the DataTables documentation, they show an example of returning data
"[server-
side](http://www.datatables.net/examples/data_sources/server_side.html)". In
their example they are using PHP on the server, but how it's returned is by
encoding with JSON. This is easily done with [Python as
well](http://docs.python.org/library/json.html).

**EDITS**

The key is how the data is returned from the server:

    
    
    $(document).ready(function() {
        $('#example').dataTable( {
            "bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": "url/to/json/returning/python"
        } );
    } );
    

In the javascript above, it would be calling the python Django view directly
and expecting a JSON response.

The Django view would be something akin to (I am not a Django user, so this
might be off):

    
    
    from django.utils import simplejson
    
    def ajax_example(request):
        ## do your processing
        ## your return data should be a python object
        return HttpResponse(simplejson.dumps(my_data_object), mimetype='application/javascript')
    

