Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #62295 > unrolled thread

python - jquery datatables with mongodb(mongoengine)

Back to article view | Back to comp.lang.python

Contents

  python - jquery datatables with mongodb(mongoengine) techno206@gmail.com - 2013-12-18 04:20 -0800
    Re: python - jquery datatables with mongodb(mongoengine) techno206@gmail.com - 2013-12-18 04:22 -0800
    Re: python - jquery datatables with mongodb(mongoengine) renier de bruyn <techno206@gmail.com> - 2013-12-18 23:50 -0800
      Re: python - jquery datatables with mongodb(mongoengine) Peter Otten <__peter__@web.de> - 2013-12-19 09:18 +0100

#62295 — python - jquery datatables with mongodb(mongoengine)

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, if yes, 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.

[toc] | [next] | [standalone]

#62296

please reply here: http://stackoverflow.com/questions/20656134/python-jquery-datatables-with-mongodbmongoengine

[toc] | [prev] | [next] | [standalone]

#62383

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[i]].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[i]] = {'$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?

[toc] | [prev] | [next] | [standalone]

#62387

renier de bruyn wrote:

> 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[i]].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[i]] = {'$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?

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web