How to load Postgres QgsVectorLayer

Question

I have a QGIS script that I am trying to load a vector layer that is stored in a Postgres database. When I print the layer's isValid() method I get False. Here is my code:

from qgis.core import *

db_client = 'postgres'
db_host = 'localhost'
db_port = '5432'
db_name = 'database'
db_user = 'user'
db_password = 'pass123'
db_schema = 'public'
tablename = 'Geo_Layer'
geometrycol = 'geom'
tract_number_index = 3

QgsApplication.setPrefixPath('/usr/bin/qgis', True)
qgs = QgsApplication([], False)
qgs.initQgis()

geo_uri = QgsDataSourceUri()
geo_uri.setConnection(db_host, db_port, db_name, db_user, db_password)
geo_uri.setDataSource(db_schema, tablename, geometrycol, '', 'id')
geo_layer = QgsVectorLayer(geo_uri.uri(False), "Test", "postgres")
# Other configurations I have tried
# geo_layer = QgsVectorLayer(geo_uri.uri(), "Test", "postgres")
# geo_layer = QgsVectorLayer(geo_uri.uri(), "Test", "ogr")
# geo_layer = QgsVectorLayer(geo_uri.uri(False), "Test", "ogr")
print(geo_layer.isValid())

qgs.exitQgis()

I have provided the other QgsVectorLayer configurations I have tried. All print that the layer is not valid.

QGIS Version: 3.16.3-Hannover Python Version: 3.8.5 Ubuntu Version: 20.04.02 LTS

I have check my credentials with DBeaver and I am able to connect.

See https://gis.stackexchange.com/questions/128354/does-anyone-have-any-tips-on-figuring-out-why-a-vector-layer-didnt-load-in-pyqg/129675#129675 and [Failed to create memory layers in QGIS application on Linux](https://gis.stackexchange.com/a/155852/4972) for tips. If you always get layer is not valid, you should first make sure of the script initialization variables (e.g., layer prefix). More details in the second link I'm sending you. — Germán Carrillo, Jun 21 '21 at 20:53

score 0 · Answer 1 · answered Feb 07 '21 at 16:00

I once faced this issue when my geometry column in postgis contains multiple geometry type. In this case you can first filter the column for geometry types, and then for each geometry type construct a layer for qgis:

for geom in geometry_types:
    uri.setDataSource(schema, table, column, "GeometryType(%s)= '%s'" % (column, geom))
    vlayer = QgsVectorLayer(uri.uri(), layer_name, "postgres")
    print(vlayer.isValid())

You can check for the geometry types in postgis using following query:

SELECT DISTINCT(GeometryType("%s"::geometry)) FROM "%s";""" % (column, table)

How to load Postgres QgsVectorLayer

1 Answers1