xxxxxxxxxx
-- Query to get all database in PostgreSQL and their oid (alternative to \l)
SELECT oid, datname FROM pg_database;
xxxxxxxxxx
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
sales | ubuntu | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
xxxxxxxxxx
import psycopg2
# Establish a connection to the PostgreSQL server
conn = psycopg2.connect(
host="your_host",
port="your_port",
user="your_username",
password="your_password",
database="your_database"
)
# Create a cursor object to execute SQL queries
cur = conn.cursor()
# Execute the SQL query to retrieve the list of databases
cur.execute("SELECT datname FROM pg_database")
# Fetch all the results
database_list = cur.fetchall()
# Print the list of databases
for db in database_list:
print(db[0])
# Close the cursor and connection
cur.close()
conn.close()