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!