Remove a PostgreSQL Database Using Psycopg and Python

Overview:

  • A database in PostgreSQL is a container for schemas that contain several namespaces and tables underneath.
  • PostgreSQL databases map to the catalogs as defined by the SQL standards. 
  • Database creation and removal operations are not frequent operations when compared to insert, update and deletion of data. 
  • The removal operation of a database involves removal of the underlying schemas, namespaces and tables. Hence care must be taken to ensure necessary backups have been made prior to removal of a database.

 Example:

# ----- Example Python program that removes a PostgreSQL database -----
import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

# Connect to PostgreSQL DBMS
postgresConnection = psycopg2.connect("user=test password=test");
postgresConnection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT);

# Obtain a database Cursor
cur_ob     = postgresConnection.cursor();
name_db   = "MyTestDatabase";
 
# Create database statement
sqlCreateDB = "create database "+name_db+";"

# Create a database in PostgreSQL database
cur_ob.execute(sqlCreateDB);

# SQL statement for querying the list of databases
sqlQuery = "SELECT datname FROM pg_database WHERE datistemplate = false;";

# Execute the query statement
cur_ob.execute(sqlQuery);

# Retrieve all the rows from the cursor
rows = cur_ob.fetchall();

print("---List of databases before removing one:---");
for row in rows:
    print('%s' %(row[0]));

# Remove database statement
sqlRemDb = "DROP database "+name_db+";"

cur_ob.execute(sqlRemDb);

# Remove the PostgreSQL database
cur_ob.execute(sqlQuery);

# Retrieve all the rows from the cursor
rows = cur_ob.fetchall();

print("---List of databases after removing one:---");

for row in rows:
    print('%s' %(row[0]));

Output:

---List of databases before removing one:---

postgres

selvamvinod

test

socialmedia

mytestdatabase

---List of databases after removing one:---

postgres

selvamvinod

test

socialmedia

 


Copyright 2024 © pythontic.com