1

With pyqgis I can load a vector layer from a PostgreSQL/PostGIS database :

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "myBase", "myUser", "myPassword")
uri.setDataSource("mySchema", "myTable", "geom")
vlayer = iface.addVectorLayer(uri.uri(False), "myLayer", "postgres")

I want to apply a SQL query to load just a part of my features. With the documentation i've read i can add a simple WHERE clause :

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "myBase", "myUser", "myPassword")
uri.setDataSource("mySchema", "myTable", "geom", "name like 'Example'")
vlayer = iface.addVectorLayer(uri.uri(False), "myLayer", "postgres")

But I'm not able to build a more complex syntax like st_intersects. For example :

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "myBase", "myUser", "myPassword")
uri.setWkbType(QgsWkbTypes.MultiPolygon)
uri.setKeyColumn(u"idA")
myQuery = """SELECT a.name FROM public.tableA a INNER JOIN public.tableB b ON st_intersects(a.geom, b.geom) WHERE a.name LIKE 'Example' AND b.name LIKE 'Something';"""
uri.setDataSource("mySchema", myQuery, "geom")
vlayer = iface.addVectorLayer(uri.uri(False), "myLayer", "postgres")

How can i use a full SQL query with a QgsDataSourceUri class ? Maybe with setSql method ?

2
  • 1
    Add the idA column. The Python2 u is no more necessary, Python3 is full UTF-8. If you haven't a unique column id in integer, bigint, etc., the layer is invalid. Jan 28, 2020 at 14:29
  • Two essentials points that I didn't know : query must have an id column (in integer of course) and the geom. SQL query have to be surrounded by brackets and escapes quotes must be strictly observed. I post a full answer for other users. Thanks for the tips.
    – GeoGyro
    Jan 28, 2020 at 15:10

1 Answer 1

5

A meticulous syntax must be respected, especially escaped quotes, and an id (integer) and a geom must appear in the query. Here is a full example with st_intersects :

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "myBase", "myUser", "myPassword")
sql = 'SELECT a.idA, a.geom, a.name FROM public.tableA a INNER JOIN public.tableB b ON st_intersects(a.geom, b.geom) WHERE a.name LIKE \'Example\' AND b.name LIKE \'Something\''
uri.setDataSource('', f'({sql})', 'geom', '', 'idA')
vlayer = iface.addVectorLayer(uri.uri(), 'myLayer','postgres')
# Optional    
print (sql)
2
  • 2
    Since Python3.6, you can use f-string for pretty string formating : uri.setDataSource('', f'({sql})', 'geom', '', 'idA'). Jan 28, 2020 at 15:04
  • Thanks, more readable syntax.
    – GeoGyro
    Jan 28, 2020 at 15:08

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