2 
        uri = QgsDataSourceUri()
        uri.setConnection(aHost=self.db_conn.host, aPort=str(self.db_conn.port), aDatabase=self.db_conn.database, aUsername=self.db_conn.user, aPassword=self.db_conn.password)
        query = f"select target_geometry.target_geometry_id, st_setSRID(target_geometry.target_geometry_geometry, 4326) as target_geometry_geometry from flight_planning.target_geometry where target_geometry.target_geometry_id = {selection[0].text()};"
        uri.setDataSource('', f'({query})', 'target_geometry_geometry', '', 'target_geometry_id')
        #uri.setDataSource("", "target_geometry", "target_geometry_geometry", f'target_geometry.target_geometry_id = {selection[0].text()}', 'target_geometry_id')
        self.target_poly_layer = QgsVectorLayer(uri.uri(), "target_geometry", "postgres")
        QgsProject.instance().addMapLayer(self.target_poly_layer)

The above code gives me this in the layer tree: enter image description here

and in the properties, the source looks correct: enter image description here

I have also tried the commented out line above as an alternative and it also does not work. If I don't try to use a query or a WHERE clause and just bring in the whole column from the table then it works just fine, but I only need part of the data.

I am using the answer from pyqgis - Load a PostgreSQL/PostGIS layer from a SQL query as a reference.

Improve this question
6
  • 3
    Try without the semicolon ; at the end of the query.
    – J. Monticolo
    Jun 29 at 14:48
  • @J.Monticolo Wow that did it. Thanks!
    – tbob
    Jun 29 at 14:51
  • @J.Monticolo convert that to an answer :-)
    – til_b
    Jul 1 at 13:33
  • @J.Monticolo is it possible to pull my WHERE clause out of the formatted string and put it as an argument in the setDataSource function? I'm trying to do that but its not working.
    – tbob
    Jul 8 at 21:31
  • @tbob: f"select ... from ... {my_where_condition_as_str};" and my_where_condition_as_str = f"where target_geometry.target_geometry_id = {selection[0].text()};"
    – J. Monticolo
    Jul 9 at 10:33

1 Answer 1

Reset to default
0

Based on the comments from @J.Monticolo I removed the semicolon in the query and it works now.

Improve this answer
1
  • 1
    It's because QGIS doesn't execute straight "your query". Instead, it's encapsulate your query in "its" QGIS own query, similar to SELECT * FROM (your query) as t and other metadata QGIS needs to display it.
    – etrimaille
    Jul 2 at 9:27

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