Get PostGIS table list with PyQGIS 3.10 and newer

Question

I have to load some PostGIS layers with PyQGIS to QGIS projects. Until Version 3.8 of QGIS I had a working solution. The Connection is made with this code:

from qgis.core import QgsDataSourceUri, QgsVectorLayer, QgsDataSourceUri
import re
import time
import db_manager.db_plugins.postgis.connector as con

...

class PgConnection:
    def __init__(self, host, dbname, port):
        self.uri = QgsDataSourceUri()
        self.host = host
        self.dbname = dbname
        self.port = port
        self.authcfg = None
        self.user = ''
        self.passwd = ''
        settings = QgsSettings()
        settings.beginGroup('PostgreSQL/connections')
        for connectionName in settings.childGroups():
            if settings.value(f'{connectionName}/host') == host and \
                settings.value(f'{connectionName}/database') == dbname:
                    self.authcfg = settings.value(f'{connectionName}/authcfg')
                    break
        if self.authcfg is None:
            self.uri.setConnection(self.host, port, self.dbname, None, None)
            connInfo = self.uri.connectionInfo()
            (success, user, passwd) = QgsCredentials.instance().get(connInfo, None, None)
            if success:
                self.uri.setPassword(passwd)
                self.uri.setUsername(user)
        else:
            self.uri.setConnection(self.host, self.port, self.dbname, None, None, authConfigId=self.authcfg)

Now I need to get all tables from a specific schema. Until QGIS version 3.8 I used following code for this (in the same class):

def getPgTableNames(self, schema):
    tablenames = con.PostGisDBConnector(self.uri)
    return tablenames.getTables(schema)

Since version 3.10 this is not longer working as con.PostGisDBConnector(self.uri) throws an error. What is the correct way to get the tablenames?

Hordor Hordor · Accepted Answer · 2020-07-20T09:18:45

It took a while to find my error. The above code does not establish a connection to the database. I did this later when adding layers to the project. However i had to add following line:

self.conn = QgsProviderRegistry.instance().providerMetadata('postgres').createConnection(self.uri.uri(),{})

the function to return the tablelist is now:

def getPgTableNames(self):
        tablenames = self.conn.tables(self.schema)
        return tablenames

And the complete code:

    from qgis.core import QgsDataSourceUri, QgsVectorLayer, QgsProviderRegistry, QgsProviderMetadata
    import re
    import time
    
    #Klassendefinitionen
    class PgConnection:
        def __init__(self, host, dbname, port, schema):
            self.uri = QgsDataSourceUri()
            self.host = host
            self.dbname = dbname
            self.port = port
            self.schema = schema
            self.authcfg = None
            self.user = ''
            self.passwd = ''
            pgProvider = QgsProviderRegistry.instance().providerMetadata('postgres')
            settings = QgsSettings()
            settings.beginGroup('PostgreSQL/connections')
            for connectionName in settings.childGroups():
                if settings.value(f'{connectionName}/host') == host and \
                    settings.value(f'{connectionName}/database') == dbname:
                        self.authcfg = settings.value(f'{connectionName}/authcfg')
                        break
            if self.authcfg is None:
                self.uri.setConnection(self.host, port, self.dbname, None, None)
                connInfo = self.uri.connectionInfo()
                (success, user, passwd) = QgsCredentials.instance().get(connInfo, None, None)
                if success:
                    self.uri.setPassword(passwd)
                    self.uri.setUsername(user)
            else:
                self.uri.setConnection(self.host, self.port, self.dbname, None, None, authConfigId=self.authcfg)
            
            self.conn = QgsProviderRegistry.instance().providerMetadata('postgres').createConnection(self.uri.uri(),{})
    
        def getPgTableNames(self):
            tablenames = self.conn.tables(self.schema)
            return tablenames
            
    #Verbindungsdaten angeben       
    host = 'some_host'
    dbname = 'some_db'
    schema = 'some_schema'
    port = '1234'
    
    # Verbindung zu Postgis aufbauen
    uri = PgConnection(host,dbname, port, schema)
    tableList = uri.getPgTableNames()
for t in tableList:
    print (t.defaultName())

Get PostGIS table list with PyQGIS 3.10 and newer

2 Answers