I want to add a PostGIS SELECT query as a new layer to a QGIS 3 project using the Python console.
It is a simple process to do this using the SQL Window in the DB Manager of QGIS. Here you can create a layer from any SQL query of a PostGIS enabled PostgreSQL database.
The following works for adding an entire PostGIS table and I’m aware a filter can be added by providing a fourth argument to uri.setDataSource(). However, this method, to my knowledge, does not allow you to do multi table queries and joins.
from qgis.core import * from qgis.core import QgsVectorLayer, QgsDataSourceUri from qgis.core import QgsProject uri = QgsDataSourceUri() uri.setConnection("localhost", "5432", "my_database", "postgres", "password") uri.setDataSource("public", "my_table", "geom") vlayer = QgsVectorLayer(uri.uri(), "my_new_layer", "postgres") QgsProject.instance().addMapLayer(vlayer, True)
Ideally I would like to replace "my_table"
with an SQL query like SELECT * FROM my_table
. This would allow you to extend functionality to more complicated queries like:
SELECT * FROM my_table_1 JOIN my_table_2 ON my_table_1.foreign_key = my_table_2.primary_key;
The eventual goal is to then implement this code in a QGIS plugin.
Any help would be much appreciated!
Advertisement
Answer
I found the solution here:
https://gis.stackexchange.com/a/197800
Simply, you must leave the schema blank and include it in the SQL query instead.