Published in


DataTables.js for Django

Python Classes to Implement AJAX Data Table in Django

Photo by Mika Baumeister on Unsplash

Data Table Factory

import pandas as pd
from .factories.aws_dtfactory import AWSDataTableFactory

class DataTableFactory(object):
"""Data Table Factory class to generate a related sub data table factory class


def get_factory(query_engine):
"""Get a query_engine related data table factory object

query_engine (str): [aws] To get related factory class
an object that is created by using query_engine related data table factory class
if query_engine == "aws":
return AWSDataTableFactory(query_engine)
return pd.DataFrame()

AWS Data Table Factory

from sqlalchemy import create_engine
import pandas as pd

def get_redshift_con():
user = ""
password = ""
host = ""
port = ""
database = ""
return create_engine(

class AWSDataTableFactory(object):
def __init__(self, query_engine):
"""Create an AWSDataTableFactory object

AWS specific data table data generator
query_engine (str): "aws"
self.query_engine = query_engine
self.query = None
self.columns = []
self.displayed_columns = []

def set_query(self, query):
"""Set factory's query

query (str): The query will be executed

It doesn't return anything. It sets the factory's query.
self.query = query

def set_columns(self):
"""Set factory's columns

It doesn't return anything. It sets the factory's columns.
limited_query = self.query.split('limit')[0]
limited_query += " limit 1"
res = self.get_data(limited_query)
self.columns = [col for col in res.columns]
self.displayed_columns = self.columns[:4]

def set_displayed_columns(self, column_list):
"""Set factory's displayed columns

column_list (list): List of columns
self.displayed_columns = column_list

def get_displayed_columns_for_data_table(self):
"""Get data table columns for AJAX request

list: List of dictionaries
return [{'data': col} for col in self.displayed_columns]

def get_data(self, query=None, *args, **kwargs):
"""Get (extract) data from the source by executing the query with the engine

query (str): The query will be executed

table_data (pd.DataFrame): Returns the data that extracted from AWS
if self.query is None:
raise NotImplementedError(
'Firstly you have to set a query for the engine')
con = get_redshift_con()
if query is None:
table_data = pd.read_sql(con=con, sql=self.query, *args, **kwargs)
table_data = pd.read_sql(con=con, sql=query, *args, **kwargs)
return table_data

def filter_by_request_args(self, **kwargs):
"""Filter and sort the data by using request.GET parameters

**kwargs: request.GET

dict: to use in the frontend with ajax data table requests
draw = int(kwargs.get('draw', None)[0])
length = int(kwargs.get('length', None)[0])
start = int(kwargs.get('start', None)[0])
search_value = kwargs.get('search[value]', None)[0]
order_column = kwargs.get('order[0][column]', None)[0]
order = kwargs.get('order[0][dir]', None)[0] # asc or desc
factory_filters = [f for f in kwargs if f.startswith('factory_filter')]
order_column = self.columns[int(order_column)]
queryset = self.get_data()

############ factory_filters applying ############
if len(factory_filters) > 0:
for ff in factory_filters:
query_col = ff.split('factory_filter-')[1]
query_val = kwargs.get(ff)[0]
if query_val != 'All':
queryset = queryset[queryset[query_col] == query_val]

total = len(queryset)
if search_value:
queryset = queryset[queryset.apply(lambda row: row.astype(
str).str.contains(search_value).any(), axis=1)]

count = len(queryset)

by=order_column, ascending=False if order == 'desc' else True, inplace=True)

queryset = queryset[self.displayed_columns]

return {
'data': queryset,
'count': count,
'total': total,
'draw': draw,
'start': start,
'length': length

Data Table View

from django.shortcuts import render
from django.contrib.auth.decorators import login_required
from django.views import View
from .utilities.dtfactory import DataTableFactory
from django.utils.decorators import method_decorator
from django.core.exceptions import PermissionDenied
from django.http import JsonResponse

@method_decorator(login_required, name="dispatch")
class DataTableView(View):
"""Data table class based view
This view can get the parameters below in the as_view function to use in the view
page_name (str): To display in page
query_engine (str): [aws] To create a DataTableFactory for querying the data source
query (str): To execute the query by using the connection that is created with query_engine
query_parameters (dict): Additional parameters to pass into to exec_sql method of the factory
permission (str): The needed permission to access the url
page_name = ""
query_engine = ""
query = ""
query_parameters = {}
permission = ""
filters = []

def get(self, request, *args, **kwargs):
# todo: kolon seçimi eklenecek
data_table_factory = DataTableFactory.get_factory(self.query_engine)

if request.GET.get('ajax_factory_loader', None) is not None:
filter_args = data_table_factory.filter_by_request_args(
data = filter_args['data'].to_dict(orient='records')
data = data[filter_args['start'] :filter_args['start'] + filter_args['length']]
result = {'data': data, 'draw': filter_args['draw'], 'recordsTotal': filter_args['total'],
'recordsFiltered': filter_args['count']}
return JsonResponse(result)
return render(
context={"page_name": self.page_name,
'filters': self.filters, 'factory': data_table_factory},

def dispatch(self, request, *args, **kwargs):
if not request.user.has_perm(self.permission):
raise PermissionDenied(
"You do not have permission to view this page")
return super().dispatch(request, *args, **kwargs)

from django.urls import path
from . import views

app_name = "dtfactory"

urlpatterns = [
page_name="Data Table Demo",
query=f"select * from QUERY",
{'filter_column': 'custom_region', 'display_name': 'Region', 'type': 'static',
'filter_values': ['All', 'TR', 'EN']}

Template File

<!DOCTYPE html>
<html lang="en">
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Data Table Demo</title>

<table id="demoDT">
{% for col in factory.displayed_columns %}
<th>{{ col }}</th>
{% endfor %}


<script src=""></script>
$(document).ready(function () {
function customExportAction(e, dt, button, config) {
var self = this;
var oldStart = dt.settings()[0]._iDisplayStart;'preXhr', function (e, s, data) {
// Just this once, load all data from the server...
data.start = 0;
data.length = 2147483647;'preDraw', function (e, settings) {
// Call the original action function
if (button[0].className.indexOf('buttons-copy') >= 0) {
$, e, dt, button, config);
} else if (button[0].className.indexOf('buttons-excel') >= 0) {
$.fn.dataTable.ext.buttons.excelHtml5.available(dt, config) ?
$, e, dt, button, config) :
$, e, dt, button, config);
} else if (button[0].className.indexOf('buttons-csv') >= 0) {
$.fn.dataTable.ext.buttons.csvHtml5.available(dt, config) ?
$, e, dt, button, config) :
$, e, dt, button, config);
} else if (button[0].className.indexOf('buttons-pdf') >= 0) {
$.fn.dataTable.ext.buttons.pdfHtml5.available(dt, config) ?
$, e, dt, button, config) :
$, e, dt, button, config);
} else if (button[0].className.indexOf('buttons-print') >= 0) {
$.fn.dataTable.ext.buttons.print.action(e, dt, button, config);
}'preXhr', function (e, s, data) {
// DataTables thinks the first item displayed is index 0, but we're not drawing that.
// Set the property to what it was before exporting.
settings._iDisplayStart = oldStart;
data.start = oldStart;
// Reload the grid with the original page. Otherwise, API functions like table.cell(this) don't work properly.
setTimeout(dt.ajax.reload, 0);
// Prevent rendering of the full data to the DOM
return false;
// Requery the server with the new one-time export settings

processing: true,
serverSide: true,
lengthChange: true,
ajax: {
url: "{{ request.get_full_path }}", // send ajax request to itself for filtering and paginating the data
data: {ajax_factory_loader:true},
columns: {{ factory.get_displayed_columns_for_data_table | safe }},
"dom": 'Blfrtip',
"buttons": [
extend: 'excel',
text: 'Excel',
filename: "{{page_name}}",
header: true,
title: "{{page_name}}",
"action": customExportAction
extend: 'csv',
text: 'CSV',
filename: "{{page_name}}",
header: true,
title: "{{page_name}}",
"action": customExportAction
extend: 'pdf',
text: 'PDF',
filename: "{{page_name}}",
header: true,
title: "{{page_name}}",
"action": customExportAction




Everything connected with Tech & Code. Follow to join our 1M+ monthly readers

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store

Lifelong learner & Freelancer. I use technology that helps me. I’m currently working as a Business Intelligence & Backend Developer.