4

Note:

This is NOT a closed question. The following links does NOT provide an answer to the solution:

Add PostGIS layer to QGIS via Python Console

The Vector layer still 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

I have a QGIS script I am writing in PyCharm, that 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 check my credentials with DBeaver and I am able to 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.

Vince
  • 20,017
  • 15
  • 45
  • 64
user908759
  • 195
  • 7
  • 1
    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? – ThomasG77 Feb 07 '21 at 15:04
  • @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! – user908759 Feb 07 '21 at 15:20
  • Yes, it's possible. Go to see how open your file and run it in PyQGIS Desktop console https://courses.spatialthoughts.com/pyqgis-in-a-day.html#where-can-you-use-python-in-qgis#hello-world – ThomasG77 Feb 07 '21 at 15:28
  • Sorry, what a mistake when sharing previous link https://courses.spatialthoughts.com/pyqgis-in-a-day.html#hello-world – ThomasG77 Feb 07 '21 at 16:03
  • @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 – user908759 Feb 07 '21 at 16:03
  • Not need, just run the code from https://gist.githubusercontent.com/ThomasG77/e2997114ac549a3b833737fb36f508b1/raw/3658b33790e244ac915b547ec7a9a55988cc3fb7/debug-gis-stackexchange-q-386583.py (same as your with standalone part) in the PyQGIS Desktop QGIS console. Change until valid. – ThomasG77 Feb 07 '21 at 16:08

1 Answers1

0

I'm betting your geometry column is not named geom. Try wkb_geometry.

David Galt
  • 628
  • 3
  • 17