SQLFORM.grid - Create from list of objects

[Gliese 581 g]

I am working on a project where we have a different subsystem whose API returns a list of certain type of objects. I want it to be displayed on my form. Can I use SQLForm.grid for this? Please suggest.

[Massimo Di Pierro]

We need more details but SQLFORM.grid is for displaying and editing tabular data from the database.

[Gliese 581 g]

Hi Massimo,

I will try to be more specific.

I am developing a web2py application(APP1) which works on the data which it gets from another web2py application(APP2).

We do not have access to the database of APP2, we access their data through jsonrpc web services exposed by APP2.

Now, there is an API(get_objects) of APP2 which gives us a list of certain type of objects. APP2 internally fetch this data from its database and convert it to a list of specific type of objects.

I want it to be displayed in a table with sorting and paging capabilities. 

Can I use web2py's SQLFORM.grid to achieve this?

Please suggest me any solution.

Thank you.

[Massimo Di Pierro]

You cannot unless you first load the data in a database. You can use a temporary in memory database like this:

fields = [Field('one'), Field('two'), Field('three'), ...]

temp_db = cache.ram('tmp_db',lambda: DAL('sqlite:memory').define_table('mytable',*fields)._db, None)

temp_db.mytable.truncate()

temp_db.mytable.insert(....)

form = SQLFORM.grid(temp_db.mytable)

[Anthony]

Not great if you need pagination, though (you'd need to fetch all the data from the API and put it in the temporary in-memory DB).

Perhaps another option would be to create a custom DAL adapter and override the .select() and .count() methods to pull data and counts from the API. Could get complicated, though.

Anthony

[Gliese 581 g]

Hi Massimo,

Thanks for suggesting this solution. I am now able to display a gird on my page with pagination and sorting options available. But whenever I click on page number or try to sort a column by clicking on its header, nothing happens. I get a blank page. I tried to debug it and found that when I click on any page number, post back takes place but form doesnt get validated(validation fails) and I do not get to see any grid. I am posting my code here. Please correct me if I have done anything wrong in it.

 

def index():
    result=None
    x=None
    searchform=SQLFORM.factory(
        Field('id', 'unicode',requires=empty_to_none),
        Field('account','unicode',requires=empty_to_none),
        Field('fromdate','unicode',requires=empty_to_none),
        Field('todate','unicode',requires=empty_to_none),
        Field('name','unicode',requires=empty_to_none),
        Field('status','integer'),
        Field('method',requires=IS_EMPTY_OR(IS_IN_SET(['Method1','Method2','Method3'], sort=True, zero='Select'))),
        Field('type','unicode',requires=IS_EMPTY_OR(IS_IN_SET(['Type1','Type2',], sort=True, zero='Select'))),
        Field('altname','unicode',requires=empty_to_none),
        Field('group','unicode',requires=IS_EMPTY_OR(IS_IN_SET(['Group1','Group2',], sort=True, zero='Select'))),
        Field('assignee','unicode',requires=empty_to_none),
        submit_button = 'Search'
    )   

 

##THIS CONDITION FAILS DURING POSTBACK(WHEN I CLICK ON PAGE NUMBERS OR COLUMN HEADERS FOR SORTING)
    if searchform.accepts(request.vars,keepvalues=True):
        searchform.process()
        id=searchform.vars.id
        status=searchform.vars.status
        method=searchform.vars.method
        name=searchform.vars.name 
        account=searchform.vars.account
        fromdate=searchform.vars.fromdate
        todate=searchform.vars.todate 
        type=searchform.vars.type

        altname=searchform.vars.altname
        assignee=searchform.vars.assignee
        group=searchform.vars.group                

##THIS METHOD RETURNS A LIST OF OBJECTS
        result=__get_objects(id, status, method, name, 
                  account, fromdate, todate, type, altname,
                  assignee, group)
##THIS IS CODE TO CREATE IN MEMORY TEMPORARY DATABASE        
        fields = [Field('ID'), Field('Method'), Field('Date'),
                  Field('Status'), Field('Account'), Field('Name'),
                  Field('Amount1'), Field('Amount2'), Field('Type') ]       

        temp_db = cache.ram('tmp_db',lambda: DAL('sqlite:memory').define_table('mytable',*fields)._db, None)      
        temp_db.mytable.truncate()  

        for obj in result['output']:
            temp_db.mytable.insert(ID=obj['id'],
                            Method=obj['method'],
                            Date=obj['date'],
                            Status=obj['status'],
                            Account=obj['account'],
                            Name=obj['name'],
                            Amount1=obj['amount1'],
                            Amount2=obj['amount2'],
                            Type=obj['type'])

        x = SQLFORM.grid(temp_db.mytable,create=False, deletable=False,
                         editable=False, maxtextlength=64, paginate=2,searchable=False,details=False)       
    else:
        response.flash = 'please fill the form'    
    return dict(form=searchform,result=result,x=x)

 

 

When postback happens due to any sorting or paging action, searchform.accepts(request.vars,keepvalues=True) fails and no grid gets displayed. Please help me to fix this.

 

Thank you

[Anthony]

You are processing the form twice:

if searchform.accepts(request.vars,keepvalues=True):
    searchform.process()

searchform.process() ultimately calls .accepts() -- no need to call both. The preferred method is now:

if searchform.process(keepvalues=True).accepted:

Also, once you load the first page of the grid, you won't be able to load additional pages or do any sorting because in the controller, the grid is only created within the "if searchform.accepts(...)" condition, which only happens when the search form is submitted (not on other clicks within the grid).

Finally, even if the grid could be navigated, your code is re-filling the temp db with the same data on every request, which is inefficient.

Anthony

On Tuesday, September 10, 2013 12:28:47 AM UTC-4, Gliese 581 g wrote:

Hi Massimo,

        temp_db = cache.ram('tmp_db',lambda: DAL('sqlite:memory').define_table('mytable',*fields)._db, None)      
        temp_db.mytable.truncate()  

        for obj in result['output']:
            temp_db.mytable.insert(ID=obj['id'],
                            Method=obj['method'],
                            Date=obj['date'],
                            Status=obj['status'],
                            Account=obj['account'],
                            Name=obj['name'],
                            Amount1=obj['amount1'],
                            Amount2=obj['amount2'],
                            Type=obj['type'])

        x = SQLFORM.grid(temp_db.mytable,create=False, deletable=False,
                         editable=False, maxtextlength=64, paginate=2,searchable=False,details=False)       
    else:
        response.flash = 'please fill the form'    
    return dict(form=searchform,result=result,x=x)

 

 

When postback happens due to any sorting or paging action, searchform.accepts(request.vars,keepvalues=True) fails and no grid gets displayed. Please help me to fix this.

 

Thank you

On Sunday, 8 September 2013 19:17:03 UTC+5:30, Massimo Di Pierro wrote:

[Gliese 581 g]

Hi Anthony,

Thanks for this useful information.

I tried

searchform.process(keepvalues=True).accepted

 But it evaluates to False. 

While below given line evaluates to True.

if searchform.accepts(request.vars,keepvalues=True):

 

I dont know why does it happen. Have I done anything wrong over here?

Could you please suggest any solution to achieve sorting and paging in this scenario?

 

Thank you.

[Anthony]

See my answer on SO: http://stackoverflow.com/a/18740517/440323

Anthony

On Tuesday, September 10, 2013 8:29:05 AM UTC-4, Gliese 581 g wrote:

Hi Anthony,

Thanks for this useful information.

I tried

searchform.process(keepvalues=True).accepted

 But it evaluates to False. 

While below given line evaluates to True.

if searchform.accepts(request.vars,keepvalues=True):

 

I dont know why does it happen. Have I done anything wrong over here?

Could you please suggest any solution to achieve sorting and paging in this scenario?

 

Thank you.

 

On Tuesday, 10 September 2013 16:43:22 UTC+5:30, Anthony wrote: