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)

So why don't you do it all in Postgresql in instead of using Python? — Clodoaldo Neto
– Clodoaldo Neto, Commented Jun 22, 2014 at 7:52
@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 — Mirage
– Mirage, Commented Jun 22, 2014 at 12:03

Clodoaldo Neto · Accepted Answer · 2014-06-22 12:37:43Z

2

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

answered Jun 22, 2014 at 12:37

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel · Accepted Answer · 2014-06-22 07:14:27Z

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]]

answered Jun 22, 2014 at 7:14

Daniel

42.9k4 gold badges57 silver badges82 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related