Create and access a permanent connection in PostgreSQL using PyQGIS

Question

I'm trying to create a automatic connection from a QGIS Plugin to a PostgreSQL database and do manipulations like importing and exporting from the schemas.

I've found that you can use QgsProviderRegistry and QgsAbstractDatabaseProviderConnection to get access to the Schemas (Getting table names from PostGIS database in PyQGIS):

## CHECK SCHEMAS
md = QgsProviderRegistry.instance().providerMetadata('postgres')
uri = QgsDataSourceUri()
# Important to keep postgres as 3rd argument or will not work
uri.setConnection(localhost, "5432", db_name, username, password)
conn = md.createConnection(uri.uri(), {})
#print(conn)

for sch in conn.schemas():
    print(sch)

But this created connection seems temporary:

I've searched the documentation to understand better this question. But didn't discovered much.

---

Looking over this and using a test Table (shape)

Didn't worked as expected.

Answer 1

QGIS provide a plugin to import|export data from a PostgresSQL database.

In QGIS 3.30 , calling this plugin result in this code :

from qgis.core import QgsDataSourceUri
from db_manager.dlg_import_vector import DlgImportVector
from db_manager.db_plugins.postgis.plugin import PostGisDBPlugin, PGDatabase
from configparser import ConfigParser

services = ConfigParser()
services.read(os.environ['PGSERVICEFILE'] if 'PGSERVICEFILE' in os.environ else os.path.join(os.path.expanduser('~'), '.pg_service.conf'))

uri = QgsDataSourceUri()
uri.setConnection(services, dbname, '', '')
dbplugin = PostGisDBPlugin(dbname)
db = PGDatabase(dbplugin, uri)
dlg = DlgImportVector(None, db, uri, None)

This give access to the plugin directly set on the given database, but you need a good pg_service.conf file in your user space to make it work properly

https://docs.postgresql.fr/10/libpq-pgservice.html

Another example that directly load a layer from the db to the QGIS project. With the good imports and layer_name being the name of the layer in QGIS, table_name the name of the table in the given schema of the db. We also do a small job to look if a 'work' group exist , create it if not and add the layer to it.

root = QgsProject.instance().layerTreeRoot()
if root.findGroup('work') :
      work_group = root.findGroup('work')
else :
      work_group = root.addGroup('work')

uri = QgsDataSourceUri()
uri.setConnection(services, dbname, '', '')
uri.setDataSource('work', table_name, str(column_geom))

layer = QgsVectorLayer(uri.uri(), layer_name, "postgres")

QgsProject.instance().addMapLayer(layer, False)
work_group.addLayer(layer)
QgsProject.instance().write(QgsProject.instance().fileName())

This has changed a bit for some QGIS version, but with a bit of documentation and theses examples it should be possible. If you produce correct code, you can answer and close this.

Answer 2

As presented by @Maximilien the code works, but I've made slight changes due to some mentioned QGIS modifications such as tr(layer_name) to just layer_name and the setConnection() method.

import os
from qgis.core import QgsDataSourceUri, QgsProject, QgsVectorLayer
from db_manager.dlg_import_vector import DlgImportVector
from db_manager.db_plugins.postgis.plugin import PostGisDBPlugin, PGDatabase
from configparser import ConfigParser
###
services = ConfigParser()
services.read(os.environ['PGSERVICEFILE'] if 'PGSERVICEFILE' in os.environ else os.path.join(os.path.expanduser('~'), '.pg_service.conf'))

dbname = 'projeto1_sl'  # Replace with the appropriate service name from the pg_service.conf file
table_name = 'test'  # Replace with the name of your table
layer_name = 'db_test'  # Replace with the desired layer name
schema_name = 'prj_test'  # Replace with the desired schema name

root = QgsProject.instance().layerTreeRoot()
if root.findGroup('db'):
    work_group = root.findGroup('db')
else:
    work_group = root.addGroup('db')

uri = QgsDataSourceUri()
uri.setConnection(
    services.get(dbname, 'host'),
    services.get(dbname, 'port'),
    services.get(dbname, 'dbname'),
    services.get(dbname, 'user'),
    services.get(dbname, 'password')
)
uri.setDataSource(schema_name, table_name, 'geom')


layer = QgsVectorLayer(uri.uri(), layer_name, "postgres")

QgsProject.instance().addMapLayer(layer, False)
work_group.addLayer(layer)
QgsProject.instance().write(QgsProject.instance().fileName())

print('end')

But one thing still troubles me, is the permanency of the connection, this method brings the desired query if I add a point (tested) it updates the table in the server.

The test was the exported layer to the dataset and db_testwas the result of the script, so as mentioned, I've added a new feature and the server table is updated:

But if I go to the Browser Pannel,at the PostgreSQL tab or in the Data Source Manager, the recent connection isn't there, where is it stored?