CODEWITHSUNDEEP
python-3.xpostgresql
macphail
macphail

Reputation: 55

access postgresql server via python3 fails if (unrequested) db does not exist

psql user was created as part of the following:

postgres=# create database testdb;
postgres=# create user testuser with encrypted password 'testpass';
postgres=# grant all privileges on database testdb to testuser;
postgres=# alter user testuser createdb;

The below python3 script functions if database "testdb" exists.
However the script fails:

 (NameError: name 'con' is not defined)

if "testdb" does not exist.

I fail to understand the requirement, as I havent asked to connect to the db yet. any assistance you can offer to help me understand is appreciated in advance. tnx.

# import modules
import psycopg2

# connect to db server
try:
    con = psycopg2.connect(
        host = "127.0.0.1",
        port = "5432",
        user = "testuser",
        password = "testpass")

except:
    print("Unable to connect to db server")

# create cursor
cur = con.cursor()

# display list of db on server
cur.execute("""SELECT datname from pg_database""")
rows = cur.fetchall()

# print list of db on server
print ("\nConnection successful.  Listing databases on server:\n")
for row in rows:
    print ("   ", row[0])

# close the cursor
print ("\nClosing server connection.\n")
cur.close()

# close the connection
con.close()

Upvotes: 0

Views: 246

Answers (2)

jjanes
jjanes

Reputation: 44305

In PostgreSQL's libpq (which psycopg2 is based on), if you specify a user name but not a dbname, it automatically tries to connect to the database which takes the same spelling as the user you specified.

You specify the user 'testuser', but don't specify a database, so it tries to connect to the database spelled 'testuser'. If that doesn't exist, of course the connection will fail.

You said the database which has to exist for this to work is "testdb", but I see that at one point you had specified the user name as "testdb" but then edited your question and changed it to "testuser". So I think the example you show is not the code you actual are running, as you did some kind of piecemeal editing of it after the fact.

In your connection code, you should specify a dbname which you know already exists.

Upvotes: 1

eatmeimadanish
eatmeimadanish

Reputation: 3907

You should really look at the gevent connection I wrote over here. It automates all of this for you and you don't have to reinvent the wheel.

Python Postgres psycopg2 ThreadedConnectionPool exhausted

Upvotes: 0

Related Questions