0

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()
macphail
  • 55
  • 1
  • 7
  • 1
    Works fine here unrelated of existence of `testdb`. The issue is somewhere else. – Abelisto Apr 09 '20 at 19:23
  • 1
    Why do you have a try block? Your program can't do anything useful if the connection fails. Just let the program fail. All the try block does is suppress valuable diagnostic information. – jjanes Apr 09 '20 at 19:36
  • Add `raise` after `print("Unable to connect to db server")` – Abelisto Apr 09 '20 at 19:37
  • @Abelisto tnx for the feedback, im glad it worked, ill recreate and troubleshoot. – macphail Apr 09 '20 at 19:55
  • @jjanes my ultimate goal is "create db if doesnt exist" but im fairly newb, im sure my syntax is shite. ill review. again, tnx everyone for checking my work. peace – macphail Apr 09 '20 at 19:56
  • @eatmeimadanish impressive. maybe version2? ;) – macphail Apr 09 '20 at 20:02

2 Answers2

1

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.

jjanes
  • 37,812
  • 5
  • 27
  • 34
0

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

eatmeimadanish
  • 3,809
  • 1
  • 14
  • 20