SQLTABLE problem with 2.15.4

[DaneW]

I have upgraded from 2.14.6 to 2.15.4 (in PythonAnywhere using Python 2.7 and MySQL) and I now get AttributeError: 'Expression' object has no attribute 'tablename' whenever I use COUNT or SUM in a query for SQLTABLE.

It looks as though the intermediate variable names for the SUM and COUNT fields are not being found. SQLTABLE works fine for standard database fields.

A workaround seems to be to use grid=rows.as_list() rather than grid=SQLTABLE(rows,headers=hdrs) and then build the table manually in the view rather than just using {{=grid}} but this is extra work and I use SQLTABLE quite a lot.

Traceback (most recent call last):

  File "/home/appgov/web2py/gluon/restricted.py", line 219, in restricted

    exec(ccode, environment)

  File "/home/appgov/web2py/applications/apg/controllers/cg.py", line 836, in <module>

  File "/home/appgov/web2py/gluon/globals.py", line 414, in <lambda>

    self._caller = lambda f: f()

  File "/home/appgov/web2py/applications/apg/controllers/cg.py", line 657, in cgsusearch

    grid = SQLTABLE(rows, headers=hdrs, truncate=50)

  File "/home/appgov/web2py/gluon/sqlhtml.py", line 3330, in __init__

    tablemap = dict(((f.tablename, f.table) for f in fieldmap.values()))

  File "/home/appgov/web2py/gluon/sqlhtml.py", line 3330, in <genexpr>

    tablemap = dict(((f.tablename, f.table) for f in fieldmap.values()))

AttributeError: 'Expression' object has no attribute 'tablename'

Code: 

    query = db.dpspend.supplier.like(xsearch, case_sensitive=False)

    isum = db.dpspend.amount.sum()

    icount = db.dpspend.amount.count()

    # group by family then supplier ....

    rows = db(query).select(db.dpspend.dpfamily, db.dpspend.supplier, isum, icount, orderby=db.dpspend.dpfamily, groupby=db.dpspend.dpfamily|db.dpspend.supplier)

    # Calculate the overall total values ....

    for row in rows:

        session.cgtotamt = session.cgtotamt + row._extra[isum]

        session.cgtotrecs = session.cgtotrecs +  row._extra[icount]

        session.cgtotsus += 1

    db.dpspend.amount.represent  = lambda value, row: DIV('{0:,}'.format(0.0 if value == None else value), _style='text-align: right;')

    hdrs = {'dpspend.dpfamily':T('Department'),

            'dpspend.supplier':T('Supplier name'),

            'SUM(dpspend.amount)':T('Total amount'),

            'COUNT(dpspend.amount)':T('Records')}

    grid = SQLTABLE(rows, headers=hdrs, truncate=50)

    return dict(grid=grid)

[Donatas Burba]

The ticket is opened on this bug already without any attention, https://github.com/web2py/web2py/issues/1735

[Leonel Câmara]

Can you test something for me:

in your web2py folder in file gluon/sqlhtml.py in SQLTABLE can you replace  

tablemap = dict(((f.tablename, f.table) for f in fieldmap.values()))

with:

tablemap = dict(((f.tablename, f.table) if isinstance(f, Field) else (f._table._tablename, f._table) for f in fieldmap.values()))

Restart web2py and check if this solved your problem.

Otherwise you need to give me a test app because I don't really use SQLTABLE and this failing means there are more problems besides this evident one. If it works, then great and I'll submit a pull request with the fix.

[DaneW]

Leonel and Donatus - thank you for your responses.

Yes Leonel your suggested change works!

The one thing that is not working is that the headers for the Sum and Count columns display as "count('dpspend'.'amount')" and "SUM('dpspend'.'amount')" rather than the values that I specified in the headers variable - "Records" & "Total amount"). The other headers work fine.

I can willingly supply a test app if that would be helpful.

[Leonel Câmara]

Yes please provide me a test app I'll make it work.

[Leonel Câmara]

DaneW I think the problem is being caused by the new DAL entity quoting so your problem is fixed if you set entity_quoting=False in your DAL call or change your headers dictionary to have quotes for instance instead of 'SUM(dpspend.amount)' you will use "SUM('dpspend'.'amount')"

[DaneW]

Leonel - that seems like a good suggestion but I'm afraid it didn't work.

Changing the  'SUM(dpspend.amount)' to "SUM('dpspend'.'amount')" had no effect and when I tried rows=db(query).select(.....,entity_quoting=False) I got an exception - TypeError: _select_wcols() got an unexpected keyword argument 'entity_quoting'

Perhaps that was not the right place to put it. Is there any documentation about the new DAL entity quoting?

It will take me a day or so to prepare the test app and database.

[Leonel Câmara]

The entity_quoting should be in your db.py where you have db = DAL(..., entity_quoting=False)

Anyway, I'll take a look at your test app since the "SUM('dpspend'.'amount')" should have worked.

[DaneW]

Thank you - I've now added entity_quoting=False to the right place in the db=DAL statement and magically all the SQLTABLEs are now working!

There is one strange behaviour - any references to the SUM statement must have the word 'SUM' in uppercase but references to 'COUNT' must all be in lower case as in 

        <td style="text-align:right">{{='{0:,}'.format(row['_extra']['SUM(dpspend.amount)'])}} </td>

        <td>{{=row['_extra']['count(dpspend.amount)']}} </td>

or     hdrs = {'dpspend.dpentity':T('Entity'),

            'SUM(dpspend.amount)':T('Overall amount'),

            'count(dpspend.amount)':T('Records')}

[Leonel Câmara]

I actually think that is also a bug because it is an inconsistency. For some reason in https://github.com/web2py/pydal/blob/master/pydal/dialects/base.py the DAL is using caps for everything but not for count. I'll submit a pull request for that too.

web2py pull request fixing the tablename problem:

https://github.com/web2py/web2py/pull/1802

pydal pull request fixing "count" in lowercase:

https://github.com/web2py/pydal/pull/499

[DaneW]

I have now upgraded to 2.16.1 and everything is working including COUNT which now needs to be in uppercase.

Many thanks for all your work on Web2py Leonel!