1

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?

Community
  • 1
  • 1
arc_lupus
  • 3,942
  • 5
  • 45
  • 81
  • 2
    the answer you link to uses `information_schema.tables`. You can't just replace `tables` with your database name. – Laurence Sep 21 '14 at 20:30
  • But if I use `information_schema.tables`, I get `false`, which is wrong... – arc_lupus Sep 21 '14 at 20:33
  • if you haven't created any tables yet, there will be no rows in `information_schema.tables`. I suspect that the mere fact the `connect` call works is proof that the database exists. If would be very easy to test that. – Laurence Sep 21 '14 at 20:36
  • @Laurence: You mean simply test if connect works? And yes, the database is empty at the moment. Is that the reason that the function fails? – arc_lupus Sep 21 '14 at 20:40

2 Answers2

2

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.

Greg
  • 6,571
  • 2
  • 27
  • 39
1

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Yep, that was my problem (after I am still a beginner in SQL, I switched database and table). Your solution solved it. – arc_lupus Sep 22 '14 at 09:01
  • 1
    @arc_lupus If it helps: a PostgreSQL instance contains databases, which contain schema, which contain tables, views, and other *relations*, as well as various non-relation objects like types. See http://stackoverflow.com/q/12232640/398670 – Craig Ringer Sep 22 '14 at 11:54