python - jquery datatables with mongodb(mongoengine)

T

techno206

I am new to Python, MongoDB(mongoengine(ODM)) and the pyramid framework as a whole. I am currently working on a project using the above mentioned technologies and I want to use jQuery datatables(also new to this) I found a link on their site on how to use datatables with MongoDB, but it is in php and my translation skills aren't that good.

my question is:
Is it possible to use datatables using the above mentioned technologies, ifyes, then how?

I have tried converting someone else's code that used SQLAlchemy with datatables, but I am stuck as I do not know how to change the function.

@view_config(
route_name='candidate.list.json',
renderer='json',
permission="admin"
)

def candidate_list_json(context, request):
def format_output(vals):
vals = list(vals)
vals[-1] = """<div class="btn-group"><a href='%s' class=btn>View</a><a href='%s' class=btn>Edit</a></div>""" % (
request.route_url('candidates', id_number=vals[-1], traverse=()),
request.route_url('candidates', id_number=vals[-1], traverse="edit")
)
vals[0] = "<a href='%s'>%s</a>" % (request.route_url('candidates', id_number=vals[0], traverse=()), vals[0])
return vals
if has_permission('admin', context, request):
basefilt = None # I changed Up to here
return handle_datatable(
request,
Agents.id,
[Agents.db_agent_id, Agents.db_name, Agents.id_number, Agents.mobile_number, OrgUnits.name, Agents.db_agent_id],
lambda term: or_(Agents.db_agent_name.like('%'+term+'%'), OrgUnits.name.like('%'+term+'%'), Agents.mobile_number.like('%'+term+'%'), Agents.id_number.like('%'+term+'%'), ),
join=[Agents.ou],
formatfunc=format_output,
base_filt=basefilt
)

handle_datatable is a method:

def handle_datatable(request, idcol, cols, filtfunc, options=None, cache='short_term', formatfunc=None, displaylength=90, join=None, base_filt=None, outerjoin=None, groupby=None, no_paginate=False, ordercols=None, orderby=None, printquery=False, nocount=False):
s = sqlahelper.get_session()
if groupby is not None and type(groupby) != list and type(groupby) != tuple:
groupby = [groupby]

def attachfilt(q, filt, nogroup=False):
if filt:
q = q.filter(and_(*filt))
if join:
q = q.join(*join)
if outerjoin:
q = q.outerjoin(*outerjoin)
if options:
q = q.options(*options)
if groupby and not nogroup:
for g in groupby:
q = q.group_by(g)
return q

@cache_region('short_term')
def perform_count(filt, idcol, term):
if not nocount:
return attachfilt(s.query(idcol), filt, nogroup=False).count()
else:
return 0

#@cache_region('short_term', 'handle_search')
def perform_search(filt, cols, iStart, iLength, order_cols):
q = attachfilt(s.query(*cols), filt)
if order_cols:
q = q.order_by(*order_cols)

if printquery:
print q
if no_paginate:
rows = q.all()
else:
rows = q[iStart:iStart+iLength]
if callable(formatfunc):
data = [formatfunc(row[:]) for row in rows]
else:
data = [row[:] for row in rows]
return data

if not callable(filtfunc):
raise Exception("Filter Function is not callable")
if not cols:
raise Exception("Please provide columns to search")

if not no_paginate:
iStart = int(str(request.params.get("iDisplayStart", 0)))
iLength = int(str(request.params.get("iDisplayLength", displaylength)))
else:
iStart = 0
iLength = 0

if not ordercols:
ordercols = cols

if orderby:
order_cols = orderby
else:
order_cols = []
if request.params.get("iSortCol_0", None):
iSortingCols = int(str(request.params.get('iSortingCols', 0)))
for k in range(0, iSortingCols):
iSortCol = int(str(request.params.get('iSortCol_%s' % k, 0)))
sSortDir = str(request.params.get('sSortDir_%s' % k, 0))
if str(request.params.get('bSortable_%s' % iSortCol, 'false') == 'true'):
col = ordercols[iSortCol]
if sSortDir == "asc":
order_cols.append(col.asc())
else:
order_cols.append(col.desc())
search = request.params.get("sSearch", None)
filt = []
if search:
filt = filtfunc(search)
if filt is not None and type(filt) != list:
filt = [filt]
if type(cols) != list:
cols = [cols]


itotal = perform_count([base_filt], idcol, search)
if no_paginate:
iLength = itotal
if base_filt is not None:
filt.append(base_filt)
cnt = perform_count(filt, idcol, search)
data = perform_search(filt, cols, iStart, iLength, order_cols)

return dict(
sEcho=request.params.get("sEcho", 0),
iTotalRecords=itotal,
iTotalDisplayRecords=cnt,
aaData=data
)

As I said before I am new to these Technologies, but I am willing to learn,if you can just point me in the right direction.
 
R

renier de bruyn

UPDATE:

So now I got this code:

import pymongo
from pyramid_mongo import get_db

# translation for sorting between datatables api and mongodb
order_dict = {'asc': 1, 'desc': -1}


class DataTables_Handler(object):
def __init__(self, request, columns, index, collection):
self.columns = columns
self.index = index
self.db = get_db(request)
self.collection = collection

# values specified by the datatable for filtering, sorting, paging
self.request_values = request.params

# results from the db
self.result_data = None

# total in the table after filtering
self.cardinality_filtered = 0

# total in the table unfiltered
self.cadinality = 0

self.run_queries()

def output_result(self):
output = {}
output['sEcho'] = str(int(self.request_values['sEcho']))
output['iTotalRecords'] = str(self.cardinality)
output['iTotalDisplayRecords'] = str(self.cardinality_filtered)
aaData_rows = []

for row in self.result_data:
aaData_row = []
for i in range(len(self.columns)):

aaData_row.append(row[self.columns].replace('"', '\\"'))

# add additional rows here that are not represented in the database
# aaData_row.append(('''<input id='%s' type='checkbox'></input>''' % (str(row[ self.index ]))).replace('\\', ''))

aaData_rows.append(aaData_row)

output['aaData'] = aaData_rows

return output

def run_queries(self):

# pages has 'start' and 'length' attributes
pages = self.paging()

# the term you entered into the datatable search
filters = self.filtering()

# the document field you chose to sort
sorting = self.sorting()

# get result from db
self.result_data = self.db.self.collection.find(spec=filters,
skip=pages.start,
limit=pages.length,
sort=sorting)

total_count = len(list(self.db.self.collection.find(spec=filters)))

self.result_data = list(self.result_data)

self.cardinality_filtered = total_count

self.cardinality = len(list(self.db.self.collection.find()))

def filtering(self):

# build your filter spec
filters = {}
if (self.request_values.has_key('sSearch')) and (self.request_values['sSearch'] != ""):

# the term put into search is logically concatenated with 'or' between all columns
or_filter_on_all_columns = []

for i in range(len(self.columns)):
column_filter = {}
column_filter[self.columns] = {'$regex': self.request_values['sSearch'], '$options': 'i'}
or_filter_on_all_columns.append(column_filter)
filters['$or'] = or_filter_on_all_columns
return filters

def sorting(self):
order = []
# mongo translation for sorting order

if (self.request_values['iSortCol_0'] != "") and (self.request_values['iSortingCols'] > 0):
order = []
for i in range(int(self.request_values['iSortingCols'])):
order.append((self.columns[int(self.request_values['iSortCol_' + str(i)])], order_dict[self.request_values['sSortDir_' + str(i)]]))
return order

def paging(self):
pages = namedtuple('pages', ['start', 'length'])
if (self.request_values['iDisplayStart'] != "") and (self.request_values['iDisplayLength'] != -1):
pages.start = int(self.request_values['iDisplayStart'])
pages.length = int(self.request_values['iDisplayLength'])
return pages

with this code in the View:

@view_config(
route_name='candidates.list.json',
renderer='json',
permission='admin'
)
def candidate_list_json(context, request):
columns = [ 'id_number', 'first_name', 'email', 'mobile_number']
index_column = "id_number"
collection = "candidates"

results = DataTables_Handler(request, columns, index_column, collection).output_result()

# return the results as a string for the datatable
return {"results": results}

and this in the template:

<a id="btn-addteam" class="btn btn-success" href="{{'add_candidate'|route_url}}"><i class="icon-plus"></i> Add Candidate</a>

ID Number Candidate Name Candidate email Mobile Number Health -->

<script src="{{'kivu:static/datatables/jquery.dataTables.js'|static_url}}"></script>
<script src="{{'kivu:static/datatables/dataTables.scroller.js'|static_url}}"></script>
<script>
$(document).ready(function() {
url = "{{'candidates.list.json'|route_url}}";
var oTable = $('#candidate_search').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sPaginationType": "full_numbers",
"bjQueryUI": true,
"sAjaxSource": url
});
} );</script>

But its giving me a js error:

TypeError: aData is undefined
for ( var i=0, iLen=aData.length ; i<iLen ; i++ )

my GET response returns:

{"results": {"aaData": [], "iTotalRecords": "0", "sEcho": "1", "iTotalDisplayRecords": "0"}}

there is data in the database. What am I missing?
 
P

Peter Otten

renier said:
UPDATE:

So now I got this code:

import pymongo
from pyramid_mongo import get_db

# translation for sorting between datatables api and mongodb
order_dict = {'asc': 1, 'desc': -1}


class DataTables_Handler(object):
def __init__(self, request, columns, index, collection):
self.columns = columns
self.index = index
self.db = get_db(request)
self.collection = collection

# values specified by the datatable for filtering, sorting, paging
self.request_values = request.params

# results from the db
self.result_data = None

# total in the table after filtering
self.cardinality_filtered = 0

# total in the table unfiltered
self.cadinality = 0

self.run_queries()

def output_result(self):
output = {}
output['sEcho'] = str(int(self.request_values['sEcho']))
output['iTotalRecords'] = str(self.cardinality)
output['iTotalDisplayRecords'] = str(self.cardinality_filtered)
aaData_rows = []

for row in self.result_data:
aaData_row = []
for i in range(len(self.columns)):

aaData_row.append(row[self.columns].replace('"',
'\\"'))

# add additional rows here that are not represented in the
# database aaData_row.append(('''<input id='%s'
# type='checkbox'></input>''' % (str(row[ self.index
# ]))).replace('\\', ''))

aaData_rows.append(aaData_row)

output['aaData'] = aaData_rows

return output

def run_queries(self):

# pages has 'start' and 'length' attributes
pages = self.paging()

# the term you entered into the datatable search
filters = self.filtering()

# the document field you chose to sort
sorting = self.sorting()

# get result from db
self.result_data = self.db.self.collection.find(spec=filters,
skip=pages.start,
limit=pages.length,
sort=sorting)

total_count =
len(list(self.db.self.collection.find(spec=filters)))

self.result_data = list(self.result_data)

self.cardinality_filtered = total_count

self.cardinality = len(list(self.db.self.collection.find()))

def filtering(self):

# build your filter spec
filters = {}
if (self.request_values.has_key('sSearch')) and
(self.request_values['sSearch'] != ""):

# the term put into search is logically concatenated with 'or'
# between all columns
or_filter_on_all_columns = []

for i in range(len(self.columns)):
column_filter = {}
column_filter[self.columns] = {'$regex':
self.request_values['sSearch'], '$options': 'i'}
or_filter_on_all_columns.append(column_filter)
filters['$or'] = or_filter_on_all_columns
return filters

def sorting(self):
order = []
# mongo translation for sorting order

if (self.request_values['iSortCol_0'] != "") and
(self.request_values['iSortingCols'] > 0):
order = []
for i in range(int(self.request_values['iSortingCols'])):
order.append((self.columns[int(self.request_values['iSortCol_'
+ str(i)])], order_dict[self.request_values['sSortDir_' +
str(i)]]))
return order

def paging(self):
pages = namedtuple('pages', ['start', 'length'])
if (self.request_values['iDisplayStart'] != "") and
(self.request_values['iDisplayLength'] != -1):
pages.start = int(self.request_values['iDisplayStart'])
pages.length = int(self.request_values['iDisplayLength'])
return pages

with this code in the View:

@view_config(
route_name='candidates.list.json',
renderer='json',
permission='admin'
)
def candidate_list_json(context, request):
columns = [ 'id_number', 'first_name', 'email', 'mobile_number']
index_column = "id_number"
collection = "candidates"

results = DataTables_Handler(request, columns, index_column,
collection).output_result()

# return the results as a string for the datatable
return {"results": results}

and this in the template:

<a id="btn-addteam" class="btn btn-success"
href="{{'add_candidate'|route_url}}"><i class="icon-plus"></i> Add
Candidate</a>

ID Number Candidate Name Candidate email Mobile Number Health -->

<script
src="{{'kivu:static/datatables/jquery.dataTables.js'|
Click to expand...

static_url}}"> said:
<script
src="{{'kivu:static/datatables/dataTables.scroller.js'|
Click to expand...
static_url}}"> said:
<script>
$(document).ready(function() {
url = "{{'candidates.list.json'|route_url}}";
var oTable = $('#candidate_search').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sPaginationType": "full_numbers",
"bjQueryUI": true,
"sAjaxSource": url
});
} );</script>

But its giving me a js error:

TypeError: aData is undefined
for ( var i=0, iLen=aData.length ; i<iLen ; i++ )

my GET response returns:

{"results": {"aaData": [], "iTotalRecords": "0", "sEcho": "1",
{"iTotalDisplayRecords": "0"}}

there is data in the database. What am I missing?
Click to expand...

Could it have to do with the mismatch of 'aData' versus 'aaData'?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Python code problem 2
Need help with this script 4
Python point location of intersect between two lines 0
How many times does unittest run each test? 9
useless python - term rewriter 0
Yet another "simple" headscratcher 4
ChatBot 4
generate and send mail with python: tutorial 8

Members online

No members online now.
Total: 41 (members: 0, guests: 41)
Robots: 213

Forum statistics

Threads
473,763
Messages
2,569,563
Members
45,039
Latest member
CasimiraVa

Latest Threads

Top