I tried to learn about postgresql using python. I want to create condition CREATE DATABASE IF NOT EXISTS, but I always get an error. The error is :
File "learn_postgres.py", line 27, in InitDatabase
cursor.execute("CREATE DATABASE IF NOT EXISTS python_db") psycopg2.ProgrammingError: syntax error at or near "NOT"
LINE 1: CREATE DATABASE IF NOT EXISTS python_db
Postgres does not support the condition IF NOT EXISTS in the CREATE DATABASE clause, however, IF EXISTS is supported on DROP DATABASE
There are two options:
Drop & Recreate (WARNING: This will result in losing any information stored in the database, do this only if you don't care about the stored data)
cursor.execute('DROP DATABASE IF EXISTS python_db')
cursor.execute('CREATE DATABASE python_db')
# rest of the script
Check the catalog first & branch the logic in python
cursor.execute("SELECT 1 FROM pg_catalog.pg_database WHERE datname = 'python_db'")
exists = cursor.fetchone()
if not exists:
cursor.execute('CREATE DATABASE python_db')
# rest of the script
You could query from pg_catalog.pg_database to check if the database is exist like this:
SELECT datname FROM pg_catalog.pg_database WHERE datname = 'python_db'
Then from here you can add the logic to create your db.
from psycopg2 import sql
from psycopg2.errors import DuplicateDatabase
...
conn.autocommit = True
cursor = conn.cursor()
try:
cursor.execute(sql.SQL('CREATE DATABASE {}').format(sql.Identifier(DB_NAME)))
except DuplicateDatabase:
pass
Answer29 shoot be: 2. check the catalog first & branch the logic in python
SQL = "SELECT 1 FROM pg_catalog.pg_database WHERE datname = TEST'"
cursor.execute(SQL)
exists = cursor.fetchone()
if exists == 'None':
SQL = 'create database TEST'
cursor.execute(SQL)
create databasestatement? I do not see anyif not existsthere...CREATE DATABASE python_dbinstead.