postgis – Getting Schema Names from PostGIS Database in PyQGIS

postgispostgresqlpyqgis

I would like to list the names of all schemas of PostGIS in a given DB called DB-1 in PyQGIS.
My attempt was inspired by : Getting table names from PostGIS database in PyQGIS

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.schemas(QSql.Schemas)
print names

it results :

Traceback (most recent call last):
  File "C:\OSGeo4W\apps\Python39\lib\code.py", line 90, in runcode
    exec(code, self.locals)
  File "<input>", line 1, in <module>
  File "<string>", line 8, in <module>
AttributeError: 'QSqlDatabase' object has no attribute 'schemas'

I think the issues is there : names=db.schemas( QSql.Schemas). Is there any other attribute that could do the job?

Best Answer

You can use the following recipe as it does not involve any third party library like psycopg2 as there is a native support for what you want in PyQGIS.

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.schemas())

# Approach 2 using existing connection (declared in "Data Source manager | PostgreSQL")
conn2 = md.createConnection('DB-1')
print(conn2.schemas())

Edit due to question in question :)

You may try to check if database exists going through the postgres database (that always exists in a PostgreSQL instance)

md = QgsProviderRegistry.instance().providerMetadata('postgres')
uri = QgsDataSourceUri()
# Important to keep postgres as 3rd argument or will not work
uri.setConnection("localhost", "5432", "postgres", "your_user", "your_password")
conn_postgres_db = md.createConnection(uri.uri(), {})

db_name_to_check = 'db_name_to_test'
sql_test_db_exists = f"""
select exists(
 SELECT datname FROM pg_catalog.pg_database WHERE lower(datname) = lower('{db_name_to_check}')
);
"""

result = conn_postgres_db.executeSql(sql_test_db_exists)
if result[0][0]:
    print('Present')
else:
    print('Absent')
Related Question