Web3Py equivalent of SQLFORM.grid

[John Bannister]

Hi All,

I have a number of web2py applications that use the grid/smartgrid functionality pretty heavily. The majority of the applications are working with large data sets (some tables have > 20M records) and everything runs fine.

With the coming of Web3Py (which looks great) my question is what would be a viable equivalent to web2py's grid/smartgrid?

I looked at Datatables some time back which worked great on smaller datasets but anything over 50K (on my testbed) starts to become unusable. I also implemented some server side processing for Datatables but never quite managed to get a usable solution or at least nothing that could handle big data sets as efficiently as web2py.

So basically looking for something that can handle large datasets that will plug into web3py if anyone has any recommendations they would be much appreciated or if anyone has managed to get Datatables working well with large data sets some guidance would be great.

Thanks in advance   

[José Luis Redrejo]

Hey John, 

I have used datatables with web2py in some projects and it was faster than smartgrid when used in server mode.

Whenever I had performance problems was because the code used .count() to show the total number of records. In my case the tables have several millions of records and that was way too slow. 

That's a common problem in many databases engines, not a datatables problem.

When using an aprox. number instead of executing the sql count() command, the datatable works fine showing hundred of rows from a table.

You have to make your own code for the server side, to support filter or sort if you need them, but it's quite straightforward.

José L.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/8e109e58-f4ac-4272-a835-d86e5101c7fe%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[John Bannister]

Hey Jose,

Thanks for the quick response. Much appreciated. Do you happen to have a small working example of Datatables setup in server mode that I could take a look at to hopefully save some time? I last looked at it a few years ago as mentioned and any pointers would be great.

Thanks in advance

John

To unsubscribe from this group and stop receiving emails from it, send an email to web...@googlegroups.com.

[José Luis Redrejo]

I can't show you all the code because the property belongs to the person who asked me to do it, 

This is the part that takes the dataables json requests, fetch the records and format them to be understood by datatables. I am skipping the sorting/filtering part , that builds the  query_filter and orderby variables

I have also changed the table name by MY_TABLE and remove one field name. 

Also, beware in this case the total number of records uses count(), you will have to replace it if your table has many records.

Hope it helps you.

José L.

@service.json
def logs_table():
    query_filter = None

    if request.vars.start is not None:
        iDisplayStart = int(request.vars.start)
    else:
        iDisplayStart = 0

    if request.vars.length is not None:
        iDisplayLength = int(request.vars.length)
    else:
        iDisplayLength = 10

    if request.vars.draw is not None:
        sEcho = int(request.vars.draw)
    else:
        sEcho = 1

        ....

        ....

    query = db(query_filter).select(db.MY_TABLE.ALL,
        limitby=(iDisplayStart, iDisplayStart + iDisplayLength),
        orderby=orderby)

    iTotalRecords = db(query_filter).count()

    # iTotalRecords = 1000000
    aaData = []

    for row in query:
        datarow = {}
        for col in row:
            if row[col] is not None:
                if col == 'MY_TABLE' or '_record' in col:
                    # internal dal col names
                    continue
                elif col == 'name of field that is a datetime':
                    datarow[col] = row[col].strftime('%Y-%m-%d %H:%M:%S')
                else:
                    datarow[col] = row[col]
            else:
                datarow[col] = ''

        aaData.append(datarow)
    return dict(draw=sEcho, recordsTotal=iTotalRecords, recordsFiltered=iTotalRecords, data=aaData)        

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/49640192-d789-44e5-a4e9-db852e8d760f%40googlegroups.com.

[John Bannister]

Hi Jose,

Completely understandable on the code side of things and appreciate your sharing. I will for sure revisit the Datatables option this week with the information you have provided.

Once again thanks for your time and assistance

John

[Massimo Di Pierro]

Web3py will have a replacement for smartgrid based on vue. You can look at the mtable component already in there as a prototype. Anyway, nothing wrong with datatables.

[John Bannister]

Hi Massimo,

Had a quick look at the mtable prototype in the _dashboad/dbadmin and re-visiting datatables as well. Will need to spend a bit more time on both before I can come to any sort of solution but thanks very much for the input.

[Massimo Di Pierro]

Please consider that it is not finished. As soon as done I will write some documentation. But basically you include utils.js, vue.min.js, axios.min.js, components/mtable.js and the you embed it

<div id="vue">

    <mtable url="apiurl"></mtable>

</div>

as a vue component.