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?

• Germán Carrillo: Any interest in doing this using only PyQGIS?
• zlikotp: @GermánCarrillo yes I would prefer a solution only in PyQGIS to be honest.
• ThomasG77: Made a pure PyQGIS answer
• Germán Carrillo: Thank you Thomas, I was about to do the same.

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

• zlikotp: it works, but if the dabase BD-1 doesn't exist a form appears. Is there anyway to check if the database BD-1 exist before print(conn2.schemas()) ?
• ThomasG77: Did not find out an approach with PyQGIS directly so going through PostgreSQL way. Maybe there is a better approach but did not found it...

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)