Overview
- A PostgreSQL table can be removed from the database using the SQL statement DROP TABLE.
- If the intend is to delete only the rows of a PostgreSQL table the SQL statement DELETE FROM can be used.
Deleting a PostgreSQL table using Python
- The psycopg2 module provides the database access API for Python to connect to a PostgreSQL server.
- psycopg2 module provides the connect() method to establish a database session with the PostgresSQL server.
- Once a database connection is established, a database cursor is opened to execute SQL statements over the database connection.
- In this example, the DROP TABLE is executed using the cursor to remove a PostgreSQL table.
Example
# ----- Example Python Program to remove a PostgreSQL database table
import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
# Start a PostgreSQL database session
psqlCon = psycopg2.connect("dbname=test user=test password=");
psqlCon.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT);
# Open a database cursor
psqlCursor = psqlCon.cursor();
# Name of the table to be deleted
tableName = "news_stories_archive";
# Form the SQL statement - DROP TABLE
dropTableStmt = "DROP TABLE %s;"%tableName;
# Execute the drop table command
psqlCursor.execute(dropTableStmt);
# Free the resources
psqlCursor.close();
psqlCon.close();Output
To get the list of tables from the psql- interactive console type \dt.
Before removing a table from a PostgreSQL database
test | components | table | test
test | delivered_parts | table | test
test | devices | table | test
test | news_stories | table | test
test | news_stories_archive | table | test
test | orders | table | testAfter removing a table from a PostgreSQL database
test | components | table | test
test | delivered_parts | table | test
test | devices | table | test
test | news_stories | table | test
test | orders | table | test