Error while testing postgresql database with python

Question

I wanted to start into using databases in python. I chose postgresql for the database "language". I already created several databases, but now I want simply to check if the database exists with python. For this I already read this answer: Checking if a postgresql table exists under python (and probably Psycopg2) and tried to use their solution:

import sys
import psycopg2

con = None

try:
    con = psycopg2.connect(database="testdb", user="test", password="abcd")
    cur = con.cursor()
    cur.execute("SELECT exists(SELECT * from information_schema.testdb)")
    ver = cur.fetchone()[0]
    print ver
except psycopg2.DatabaseError, e:
    print "Error %s" %e
    sys.exit(1)
finally:
    if con:
        con.close()

But unfortunately, I only get the output

Error relation "information_schema.testdb" does not exist
LINE 1: SELECT exists(SELECT * from information_schema.testdb)

Am I doing something wrong, or did I miss something?

Answer 1

Your question confuses me a little, because you say you want to look to see if a database exists, but you look in the information_schema.tables view. That view would tell you if a table existed in the currently open database. If you want to check if a database exists, assuming you have access to the 'postgres' database, you could:

import sys
import psycopg2, psycopg2.extras
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
dbname = 'db_to_check_for_existance'
con = None

try:
    con = psycopg2.connect(database="postgres", user="postgres")
    cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
    cur.execute("select * from pg_database where datname = %(dname)s", {'dname': dbname })
    answer = cur.fetchall()
    if len(answer) > 0:
        print "Database {} exists".format(dbname)
    else:
        print "Database {} does NOT exist".format(dbname)
except Exception, e:
    print "Error %s" %e
    sys.exit(1)
finally:
    if con:
        con.close()

What is happening here is you are looking in the database tables called pg_database. The column 'datname' contains each of the database names. Your code would supply db_to_check_for_existance as the name of the database you want to check for existence. For example, you could replace that value with 'postgres' and you would get the 'exists' answer. If you replace the value with aardvark you would probably get the does NOT exist report.

Answer 2

If you're trying to see if a database exists:

curs.execute("SELECT exists(SELECT 1 from pg_catalog.pg_database where datname = %s)", ('mydb',))

It sounds like you may be confused by the difference between a database and a table.