1

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

enter image description here

1 Answer 1

2

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.

1
  • 2
    Or since Python 3.6 with new f-strings : query = f"Select {current_field}, count(*) from {layer.name()} group by {current_field} order by 2 Desc" Aug 5, 2019 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.