Loading valid PostgreSQL as QgsVectorLayer with PyQGIS

Question

I have a QGIS script I am writing in PyCharm, which I am trying to load a vector layer stored in a PostgreSQL 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', True)
qgs = QgsApplication([], False)
# I have also tried the following:
# qgs = QgsApplication([], True)
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')
# I have also tried the following
# geo_uri.setDataSource(db_schema, tablename, geometrycol)
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")
# A working version using an exported geopackage
# geo_layer = QgsVectorLayer('/home/<path to geopackage>/geo_layer.gpkg|layername=geo_layer', "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 checked my credentials with DBeaver, and I can connect.

I have made sure I have the correct QgsApplication prefixPath by opening my QGIS Desktop, opening the Python Console and executing the following:

QgsApplication.prefixPath() 
'/usr'

Also, in QGIS Desktop, the specific vector layer I am trying to validate, I can export that layer into a GeoPackage and connect to it and validate the Vector Layer. So I think I have the correct QgsApplication configuration.

geo_layer = QgsVectorLayer('/home/<path to geopackage>/geo_layer.gpkg|layername=geo_layer', "Test", "ogr")

This is the workaround I have in place, but I would like to connect directly to my PostgreSQL database.

This is NOT a closed question. The following links do NOT provide an answer to the solution: Add PostGIS layer to QGIS via Python Console

The Vector layer remains invalid with the recommended changes being made.

PyQGIS QgsVectorLayer() Loading Invalid Layer in Standalone Python Script?

I can also confirm my QgsApplication prefixPath is correct

• ThomasG77: Used your code with my own db parameters and your sample works. To be sure, tablename = 'Geo_Layer' is not tablename = 'geo_layer' ? Did you run your sample in PyQGIS console commenting the standalone code part to validate your layer and its parameters?
• user908759: @ThomasG77 The table value of Geo_Layer is correct. commenting out the standalone code results in the layer being invalid. Is there a way to load the Layer in my QGIS Desktop Project in the Python Console and verify it's configuration? Since I know that vector layer is valid. Thank you for your help!
• ThomasG77: Yes, it's possible. Go to see how open your file and run it in PyQGIS Desktop console courses.spatialthoughts.com/…
• ThomasG77: Sorry, what a mistake when sharing previous link courses.spatialthoughts.com/pyqgis-in-a-day.html#hello-world
• user908759: @ThomasG77 I am able to get the layer with the following QgsProject.instance().mapLayer('<layer_id>').source() which gives me the connection info. it seems there are some other parameters like sslmode, srid, type, and checkPrimaryKeyUnicity I will try to add these