From SQL Query to QGis Layer with Python

Question

I am struggling to convert my sql query to a valid Postgres layer in QGIS with the help of Python. Here is what I tried:

SELECT * FROM test_geodb  as k, vg250 as o WHERE ST_Within(ST_PointOnSurface(k.geom),o.geom) AND o.id = 71

When running the above code in pgAdmin4 I get 2 rows as a result as I except it. When I try to call the same statement inside my Qgis Plugin, I get an empty layer.

sql = "select k.pot_field, k.geom, k.id from test_geodb  as k, vg250 as o WHERE ST_Within(ST_PointOnSurface(k.geom),o.geom) AND o.id = 71"    
self.uri1.setDataSource("", "test_geodb", "geom",  sql)  
layer = QgsVectorLayer(self.uri1.uri(False), "testlayer", "postgres")
QgsMapLayerRegistry.instance().addMapLayer(vlayer)   

While doing this:

self.uri1.setDataSource("public", "test_geodb", "geom", "w_speed = 5.75")        
vlayer = QgsVectorLayer(self.uri1.uri(False) ,"layername2","postgres")
QgsMapLayerRegistry.instance().addMapLayer(vlayer)

results in a correct selection and this:

self.db.setDatabaseName(self.uri1.database())
self.db.setUserName(self.uri1.username())
self.db.setPassword(self.uri1.password())
self.db.open()
self.query = QSqlQuery(self.db)
sql = "select k.pot_field, k.geom, k.id from test_geodb  as k, vg250 as o WHERE ST_Within(ST_PointOnSurface(k.geom),o.geom) AND o.id = 71"
while self.query.next():
    print self.query.record().value("geom")
    #geom_query = query.record().value("geom")

gives me the geometries of my polygons as postgis geometries of the two rows. By reading here, here and here, I tried to solve my problem. I assume it has to do with the setting of the setDataSource but I don't know how to fix it.

I am using Postgres 9.6, postgis 2.3 on windows 10 with QGIS 2.14

Answer 1

I finaly figured it out but it feels like a huge detour so I would appreciate any better suggestions:

What I did was opening my DB with python, cretaing a query which creates a table and then load this newly created table as a layer into qgis

 def sql_querry(self):
        self.db = QSqlDatabase.addDatabase("QPSQL")
        self.db.setHostName(self.uri1.host())
        self.db.setPort(int(self.uri1.port()))
        self.db.setDatabaseName(self.uri1.database())
        self.db.setUserName(self.uri1.username())
        self.db.setPassword(self.uri1.password())
        self.db.open()
        self.query = QSqlQuery(self.db)
        if self.count_query == 0:
            print "Query execute:"
            sql = "DROP TABLE  IF EXISTS test_db;CREATE TABLE test_db AS select k.pot_field, k.geom, k.id from test_geodb  as k, vg250 as o WHERE ST_Within(ST_PointOnSurface(k.geom),o.geom) AND o.id = 71;ALTER TABLE test_db   ADD COLUMN geoid SERIAL PRIMARY KEY;"
            self.count_query += 1
        else:
            sql = "DROP TABLE  IF EXISTS test_db;CREATE TABLE test_db AS select k.pot_field, k.geom, k.id from test_geodb  as k, vg250 as o WHERE ST_Within(ST_PointOnSurface(k.geom),o.geom) AND o.id = 68;ALTER TABLE test_db   ADD COLUMN geoid SERIAL PRIMARY KEY;"

        print self.query.exec_(sql)

The if-else statement was just for checking if could load different layers from the "same" table which also worked. Here is the code which I called to the create the layer:

    self.sql_querry()    
    self.uri1.setDataSource("public", "test_db", "geom")  
    layer = QgsVectorLayer(self.uri1.uri(False), "testlayer", "postgres")                
    if layer.isValid():
        QgsMapLayerRegistry.instance().addMapLayer(layer)  

As I said feel free to point me to a better solution!