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

The set_isolation_level was a necessary step for me to get it to work. — wordsforthewise
– wordsforthewise, Commented Jan 4, 2018 at 17:08

tomp · Accepted Answer · 2015-11-28 18:28:50Z

61

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.

edited Nov 28, 2015 at 18:28

tomp

6536 silver badges24 bronze badges

answered Oct 17, 2013 at 12:28

Nicolas

8187 silver badges7 bronze badges

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

4 Comments

nish Over a year ago

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

Heinz Over a year ago

@Nicolas 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?

Nicolas Over a year ago

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

Liubov Over a year ago

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

Collectives™ on Stack Overflow

Creating a postgresql DB using psycopg2

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related