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?
Greg :
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:\n\nimport sys\nimport psycopg2, psycopg2.extras\ncur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)\ndbname = 'db_to_check_for_existance'\ncon = None\n\ntry:\n con = psycopg2.connect(database=\"postgres\", user=\"postgres\")\n cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)\n cur.execute(\"select * from pg_database where datname = %(dname)s\", {'dname': dbname })\n answer = cur.fetchall()\n if len(answer) > 0:\n print \"Database {} exists\".format(dbname)\n else:\n print \"Database {} does NOT exist\".format(dbname)\nexcept Exception, e:\n print \"Error %s\" %e\n sys.exit(1)\nfinally:\n if con:\n con.close()\n\n\nWhat 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. ",
2014-09-22T01:03:01