Creating a postgresql DB using psycopg2

Question

I'm trying to create a postgres DB using a python script. Some research showed that using the psycopg2 module might be a way to do it. I installed it and made the required changes in the pg_hba.conf file. I used the following code to create the DB:

#!/usr/bin/python # -*- coding: utf-8 -*-  from psycopg2 import connect import sys from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT  con = None con = connect(user='****', host = 'localhost', password='****')  dbname = "voylla_production1710"  con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) cur = con.cursor() cur.execute('CREATE DATABASE ' + dbname) cur.close() con.close() 

I tried replacing con = connect(user='nishant', host = 'localhost', password='everything') with con = connect(user='nishant', password='everything')

But I'm getting the following Error:

con = connect(user='nishant', host = 'localhost', password='everything')   File "/usr/lib/python2.7/dist-packages/psycopg2/__init__.py", line 179, in connect connection_factory=connection_factory, async=async)  psycopg2.OperationalError: FATAL:  database "nishant" does not exist 

Could someone please tell me the right way of doing it. Thanks

Answer 1

PostgreSQL's client connects to a database named after the user by default. This is why you get the error FATAL: database "nishant" does not exist.

You can connect to the default system database postgres and then issue your query to create the new database.

con = connect(dbname='postgres', user='nishant', host='localhost', password='everything') 

Make sure your nishant user has permission to create databases.

Edit: By the way, check out the ~/.pgpass file to store password securely and not in the source code (http://www.postgresql.org/docs/9.2/static/libpq-pgpass.html). libpq, the postgresql client librairy, check for this file to get proper login information. It's very very handy.