Getting schema names from PostGIS database in PyQGIS

Question

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?

Answer 1

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

Answer 2

You could use psycopg2 and do a simple query on information_schema.schemata:

import psycopg2
db_conn = psycopg2.connect(host='localhost', port=5432, dbname='DB-1', user='postgres', password='postgres')
db_cursor = db_conn.cursor()
query = "select schema_name from information_schema.schemata"
db_cursor.execute(query)
schemata = list(db_cursor.fetchall())
schemata = [''.join(i) for i in schemata] # tuples to string-list
print(schemata)