5

I would like to list the name of all tables of PostGIS in a given DB called DB-1 in PyQGIS. I found this Q&A at Stack Oveflow: https://stackoverflow.com/questions/34418667/get-table-names-from-postgis-database-with-pyqgis

But it seems this solution doesn't work anymore when I try this code:

from PyQt5.QtSql import *
db = QSqlDatabase.addDatabase("BD-1");
db.setHostName("localhost");
db.setDatabaseName("BD-1");
db.setUserName("postgres");
db.setPassword("postgres");
db.open();
names=db.tables( QSql.Tables)
print names

And the result is empty array :

[]

enter image description here

enter image description here

4
  • 2
    How does it not work? Syntax error? Something not found? Only lists half the tables? Please edit the question and tell us what the problem is. What version of QGIS? Qt4 isn't supported in the latest, so maybe that's the problem and you need to use Qt5? How can we tell?
    – Spacedman
    Oct 29, 2021 at 8:05
  • 1
    it seems to be syntax error, and I've edited my post and tried in PyQT5 but problems remains.
    – zlikotp
    Oct 29, 2021 at 8:12
  • please tell us what the actual error is
    – Ian Turton
    Oct 29, 2021 at 10:05
  • The first error was a syntax error due to some spaces.. and now I've corrected it, it result an empty array. It seems something is wrong with my database name?
    – zlikotp
    Oct 29, 2021 at 10:18

2 Answers 2

2

You can find another approach, at least for recent QGIS 3 versions

md = QgsProviderRegistry.instance().providerMetadata('postgres')

# Approach 1 without using existing connection
uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "DB-1", "postgres", "postgres")
conn1 = md.createConnection(uri.uri(), {})
print(conn1.tables())
print([f'{i.schema()}.{i.tableName()}' for i in conn1.tables()])

# Approach 2 using existing connection
# (declared in "Data Source manager | PostgreSQL")
conn2 = md.createConnection('DB-1')
print(conn2.tables())
print([f'{i.schema()}.{i.tableName()}' for i in conn2.tables()])

# Get list of schema and table name excluding system tables
print([f'{i.schema()}.{i.tableName()}' for i in conn2.tables() if i.schema() not in ('information_schema', 'pg_catalog')])
# Get list of table name excluding system tables
print([f'{i.tableName()}' for i in conn2.tables() if i.schema() not in ('information_schema', 'pg_catalog')])

You can filter tables using first argument for schema name e.g conn1.tables('schema_name') and/or combined with the type of tables you want conn1.tables('public', QgsAbstractDatabaseProviderConnection.Aspatial)

Second argument can be amongst https://qgis.org/api/classQgsAbstractDatabaseProviderConnection.html#af21b880041c38786e158ce1f564211b0 Second argument can be combined at it's based on flags mechanism.

4
  • at the moment the result looks like : [<QgsAbstractDatabaseProviderConnection.TableProperty: 'qgis_projects'>] How can I get only the names ?
    – zlikotp
    Oct 30, 2021 at 11:31
  • And also, when I do : print([f'{i.schema()}.{i.tableName()}' for i in conn2.tables()]) I get a lot of useless data that doesn't match witch schema.. Is there a way to have only the list of the names of tables (schema.tables) that appears in the schemas on the PostGIS windows?
    – zlikotp
    Oct 30, 2021 at 11:38
  • 1
    Edited to filter to exclude system schemas
    – ThomasG77
    Oct 30, 2021 at 11:59
  • and about using this filter : print(conn1.tables('test2')) , I get [<QgsAbstractDatabaseProviderConnection.TableProperty: 'point_topo'>, <QgsAbstractDatabaseProviderConnection.TableProperty: 'geography_columns'>, <QgsAbstractDatabaseProviderConnection.TableProperty: 'geometry_columns'>, <QgsAbstractDatabaseProviderConnection.TableProperty: 'qgis_projects'>, <QgsAbstractDatabaseProviderConnection.TableProperty: 'spatial_ref_sys'>] How can I get only the name of tables?
    – zlikotp
    Oct 30, 2021 at 12:10
2

had to write QSqlDatabase.addDatabase("QPSQL") My error was that I thought I had to write my DB name.

 from PyQt5.QtSql import *
        db = QSqlDatabase.addDatabase("QPSQL");
        db.setHostName("localhost");
        db.setDatabaseName("DB-1");
        db.setUserName("postgres");
        db.setPassword("postgres");
        db.open();
        names=db.tables( QSql.Tables)
        print names

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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