How can i get the list of databases in Postgresql in python

Question

I want to get the list of all databases in Postgresql server in python list. Basically then i want to create those insode another database But i am not able to get it.

This is what i have tried

config_read = {
 'host':'psql-002',
 'database':'tesdb',
 'user':'pguser',
 'port':'5432',
 'password':'mypass'
 }
class ExportPSQL():
 def __init__(self):
 try:
 conn = psycopg2.connect(**config_read) 
 self.conn = conn
 except:
 print "Eror Connecting database" 
 def run(self):
 # Get a list of databases with :
 db_command=" psql -h localhost -U pguser tesdb -c '\l'"
 db_list = os.popen(db_command)

• mhawke: Take a look at dba.stackexchange.com/a/1304 .
• Clodoaldo Neto: Which is the other database? Postgresql?
• Mirage: @ClodoaldoNeto yes other databse is also postgres
• Clodoaldo Neto: So why don't you do it all in Postgresql in instead of using Python?
• Mirage: @ClodoaldoNeto how can i do it in postgres. i thought i had to manually export and import each database and i had 30 databases. so i thought i will automate it with python

Answer 1

Do it all with pg_dumpall and psql as superuser

pg_dumpall -h localhost -U postgres -f pg_dumpall.sql

If you want to copy only the schema then use the --schema-only parameter.

To restore it to another cluster

psql -f pg_dumpall.sql postgres

Answer 2

What's missing in your solution is the reading and parsing of the information you get from psql:

def get_database_info(host, user):
 records, _ = subprocess.Popen(['psql','-lA','-F\x02','-R\x01','-h',host,'-U',user ],stdout=subprocess.PIPE).communicate()
 records = records.split('\x01')
 header = records[1].split('\x02')
 return [dict(zip(header,line.split('\x02'))) for line in records[2:-1]]