Is there a way to access named PostgreSQL connections via PyQGIS in order to access and load spatial layers?

Question

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?

Answer 1

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)

Answer 2

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")