SQL query in Python console to sum column based on values of other two columns

Question

We have 100's of shapefiles for which we have to generate excel reports based on a few (combination of) parameters in the attribute table. I am using SQL query to achieve this and when I run the query in the "Create Virtual layer" process, it works fine and the output has been generated, which will be saved as an excel file. Instead of this process, I am trying to run this through the Python console. Though the code didn't show any error, the output is not being printed. Do I have to change the SQL query format in the code?

Image 1: For the shapefile final_bdry, Total_Area has to be calculated based on two other columns - MS_Area, Ground_Con (similar to Pivot_Table in excel, the sum of a column based on a unique count of the other two columns). No issues with the query and I am getting the desired output in the virtual_layer. .

Image 2: But, if I run the same query within the Python console, the result is "None"

Ref: Used this as ref SQL queries in shapefiles using python/gdal return none result

Answer 1

You could try directly inserting your SQL query when creating a Virtual layer:

query = """ SELECT "MS_Area", "Ground_Con", sum("Area_Ha") as Total /*:ini*/ from final_bdry group by "MS_Area", "Ground_Con" """

vlayer = QgsVectorLayer( "?query={}".format(query), "vlayer", "virtual" )
QgsMapLayerRegistry.instance().addMapLayer(vlayer)

---

Example:

---

UPDATE for QGIS 3.0

QgsMapLayerRegistry object was changed in QGIS 3.0 for QgsProject. So, last line in 3.0 should be:

QgsProject.instance().addMapLayer(vlayer)