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

Beatrice

The set_isolation_level was a necessary step for me to get it to work. – Nitriding

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.

Ajani

Thanks a lot, your edit and ALTER USER your_username CREATEDB; worked. – Beatrice

@Ajani I successfully created new DB by your method, and I want to add a line that can print out current DB I have in the server, how can I write the code? – Anomaly

Query Postgres metadata to list databases : select datname from pg_database. Refer to the documentation on postgresql.org for more information on PG metadata. – Ajani

Small fix: when keyword arguments are used in psycopg2.connect, database name should be given with 'database' keyword (instead of 'dbname'): docs – Beluga