3

As seen here, it is possible to use PyQGIS to load layers from PostgreSQL using an instance of QgsDataSourceUri(). You then set the connection and load a layer.

Loading PostGIS layer from SQL query using PyQGIS

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "myBase", "myUser", "myPassword")

In QGIS our users will have standardized named connections in the QGIS data browser, something along the lines of 'GIS-DATA' to connect to the GIS database etc. Is there a way to leverage using something like the following to avoid having to parse out all of the parameters each time?

uri = QgsDataSourceUri()
uri.setConnection("GIS-DATA")

Using named connections seems to be possible in Processing Algorithms (ex- see the PostgreSQL Execute and Load SQL tool, it has a parameter for 'connection name'). https://docs.qgis.org/3.28/en/docs/user_manual/processing_algs/qgis/database.html#export-to-postgresql

Is there a way to access this connection to add and load layers?

2 Answers 2

7

Try this. I have a connection called localhost data so I search for the word data in all the PostgreSQL connections:

from PyQt5.QtCore import QSettings
qs = QSettings()
L = []
for k in sorted(qs.allKeys()): #For all stored settings
     if all([word in k.lower() for word in ("postgresql", "data")]): #If the setting have both postgresql and data in it
        print(k)
        L.append(k)
#prints out all setting keys with the word postgresql and data in them, like:
#PostgreSQL/connections/localhost data/database
#PostgreSQL/connections/localhost data/host
#...
#And appends them to L list

#We need to pass each key to qs.value. For example qs.value("PostgreSQL/connections/localhost data/username")
#  to get the username

conn = '/'.join(L[0].split("/")[:-1])+"/" #Create the first part of the key
#'PostgreSQL/connections/localhost data/'

#Fetch the values
keywords = ["host", "port", "database", "username", "password"] #Each keyword and the conn string needs to be combined and passed to qs.value
host, port, database, username, password = [qs.value(f"{conn}{key}") for key in keywords] #Fetch the values we need

#Add a table called land in the public schema using the parameters
uri = QgsDataSourceUri()
uri.setConnection(host, port, database, username, password)
uri.setDataSource("public", "land", "geom")
vlayer = QgsVectorLayer(uri.uri(False), "layer name you like", "postgres")
QgsProject.instance().addMapLayer(vlayer)
5

Ah I think I actually figured it out from this page: Getting schema names from PostGIS database in PyQGIS

For connections that exist, you can use:

md = QgsProviderRegistry.instance().providerMetadata('postgres')
conn2 = md.createConnection('GIS-DATA')

instead of the following to create new ones:

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "GIS-DATA", "postgres", "postgres")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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