SQL strings and SQLTABLE issue in last web2py release. Is there a solution for it?

[Paolo Caruccio]

The relevant code that generates the issue is:

    rischi_della_mansione = "GROUP_CONCAT(rischio, ', ')"


    l = [db.RISCHI_MANSIONE.on(
            (db.RISCHI_MANSIONE.mansione_ID==db.MANSIONE.id)),
         db.RISCHI.on(
            (db.RISCHI.id==db.RISCHI_MANSIONE.rischio_ID))
        ]


    mansioni = db((db.MANSIONE.id>0)).select(db.MANSIONE.mansione,
                                             rischi_della_mansione,
                                             left=l,
                                             groupby=db.MANSIONE.id
                                            )


    fattori_rischio = SQLTABLE(mansioni,
                               truncate=None,
                               _id="rischi",
                               headers={'MANSIONE.mansione':'MANSIONE',
                                        rischi_della_mansione:'FATTORI DI RISCHIO'
                                        },
                                        columns=['MANSIONE.mansione',
                                                 rischi_della_mansione
                                        ]
                                )

The traceback screenshot is:

Please note that the code is working well on web2py Version 2.14.6-stable+timestamp.2016.05.10.00.21.47

Has anybody found the same issue and solved it?

Thank you.

[Dave S]

On Wednesday, March 14, 2018 at 3:32:46 PM UTC-7, Paolo Caruccio wrote:

The relevant code that generates the issue is:

[...]

The traceback screenshot is:

 

Please note that the code is working well on web2py Version 2.14.6-stable+timestamp.2016.05.10.00.21.47

Has anybody found the same issue and solved it?

Thank you.

 
I see you're running on Windows but with a stand-alone Python.  Are you using the source download of web2py?  What version of pydal do you have?

/dps

[Paolo Caruccio]

Hi Dave.

Yes, I am running on Windows 8 with stand-alone Python. I am using the source downloaded from github this evening. The pydal version is 17.11

The same issue is on a production server Ubuntu 16.04 with nginx and uwsgi and latest stable version of web2py

The database is SQLite 3 in both configurations.

[Anthony]

Looks like the problem was introduced here (and updated here). Feel free to open an issue on Github.

Anthony

[Leonel Câmara]

I'm going to say it outright I don't think this should be supported.

I don't think this should be considered a backwards compatibility problem, because you're not using the DAL API you're just sending SQL in a string. 
 
If you put that "GROUP_CONCAT" in a pyDAL Expression and that doesn't work then I would agree it's a problem. Try this:

from pydal.objects import Expression
dialect = db._adapter.dialect

def group_concat(first, second, query_env={}):
    return "GROUP_CONCAT(%s, '%s')" % (dialect.expand(first, query_env), second)

rischi_della_mansione = Expression(db, group_concat, db.RISCHI.id, ', ', 'string')


We need to define that the proper way to put custom SQL mixed with DAL code is using Expression and document how to use it. SQL in strings isn't acceptable, for that you use executesql.

[Anthony]

On Thursday, March 15, 2018 at 7:59:42 AM UTC-4, Leonel Câmara wrote:

I'm going to say it outright I don't think this should be supported.

I don't think this should be considered a backwards compatibility problem, because you're not using the DAL API you're just sending SQL in a string.

Agreed, it's not strictly a backward compatibility issue. Therefore, we don't necessarily need to restore the old functionality exactly (i.e., allow SQL strings to be passed to .select() and expect SQLTABLE to display the results), but it would be good to provide some means of achieving similar results.

 

from pydal.objects import Expression
dialect = db._adapter.dialect

def group_concat(first, second, query_env={}):
    return "GROUP_CONCAT(%s, '%s')" % (dialect.expand(first, query_env), second)

rischi_della_mansione = Expression(db, group_concat, db.RISCHI.id, ', ', 'string')


We need to define that the proper way to put custom SQL mixed with DAL code is using Expression and document how to use it. SQL in strings isn't acceptable, for that you use executesql.

The above is not part of the DAL public API either, and it is a rather cumbersome way to add custom SQL to a select. So, rather than simply documenting the above, it would be better if we could come up with a simplified API for adding custom SQL expressions to both queries  (i.e., "WHERE" clauses) and selects. In the short term, I suppose we could document the above as a workaround, but I'm not sure we want to commit to that as a public API that must be supported indefinitely (we then lose the freedom to change any of those exposed internal implementation details).

Anthony

[Paolo Caruccio]

Leonel's suggestion worked but I had to set the key within headers dictionary and column name within column lists of SQLTABLE to 

'''GROUP_CONCAT("RISCHI"."rischio", \', \')'''

However, it seems to me that pyDal accepts SQL strings but it is SQLTABLE that doesn't handle them as before. So I have two questions.

1) If pyDal regularly returns the rows of a select containing a SQL string, why doesn't SQLTABLE - which should just serialize these in a view - accept them?

2) since the problem is generated by the following line

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

In the SQLTABLE class is it doable  to apply the Leonel suggested code or to make a fallback to the old working code when f._table is None ?

Thank you for your attention.