Remove a PostgreSQL Database Using Psycopg and Python

A database in PostgreSQL is a collection of schemas. The example Python program removes a PostgreSQL database using a connection obtained through the psycopg2 client.

PostgreSQL Updated 20 Mar 2021 1 min read

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

python
# ----- 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

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

postgres

selvamvinod

test

socialmedia

mytestdatabase

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

postgres

selvamvinod

test

socialmedia