Format Query statement for use in Virtual Layer query from PyQGIS

Question

Based on QGIS: SQL query within python console to sum a column based on values of other two columns

I am trying to write a script that will get the field names to use from PyQGIS but am having issues with the format of the query statement.

I have

layer = qgis.utils.iface.activeLayer()
for field in layer.fields():
    current_field=field.name()
    print(current_field) 
    query = "Select", current_field, "count(*) from", layer,"group by", current_field,"order by 2 Desc"""
    print(query)
    vlayer = QgsVectorLayer( "?query={}".format(query), current_field, "virtual" )
    QgsProject.instance().addMapLayer(vlayer)

But this is not formatting the query correctly so it comes out as

('Select', 'Type', 'count(*) from', <qgis._core.QgsVectorLayer object at 0x00000244813FD558>, 'group by', 'Type', 'order by 2 Desc')

Which gives the following error

Query execution error on CREATE TEMP VIEW _tview AS ('Select', 'Type', 'count(*) from', <qgis._core.QgsVectorLayer object at 0x00000244813FD558>, 'group by', 'Type', 'order by 2 Desc'): 1 - near "(": syntax error

How do I format the query= correctly? I have also tried it with layer.name()

It should be formatted as

Select Type, count(*) from SW_Test group by Type order by 2 Desc

Which results in

Answer 1

In your query= line you are creating a tuple, not a string as required. Try replacing the , with + and use layer.name() as allready suggested:

query = "Select " + current_field + ", count(*) from " + layer.name() + " group by " + current_field + " order by 2 Desc"

or use string formatting like

query = "Select {0}, count(*) from {1} group by {0} order by 2 Desc".format(current_field, layer.name())

wich is better readable in my opinion.