Python PostgreSQL – Drop Table

Difficulty Level : Medium
Last Updated : 23 Aug, 2021

In this article, we are going to see how to drop tables in PostgreSQL using pyscopg2 module Python. In PostgreSQL DROP TABLE is used to remove the existing table from the database. It removes table definition and all associated data, indexes, rules, triggers, and constraints for that table. If the particular table doesn’t exist then it shows an error.

Syntax: DROP TABLE table_name;
Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.
To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course

Table Used:

Here, we are using the accounts table for demonstration.

Now let’s drops this table, for we will use will psycopg2 module to connect the PostgreSQL and execute the SQL query in cursor.execute(query) object.

Syntax: cursor.execute(sql_query);

Example 1: Drop table using psycopg2

Here we are going to drop the table using the DELETE clause.

Syntax: DROP TABLE table_name;

Code:

Python3

# importing psycopg2
import psycopg2
  
conn=psycopg2.connect(
    database="test",
    user="postgres",
    password="password",
    host="localhost",
    port="5432"
)
  
  
# Creating a cursor object using the cursor() 
# method
cursor = conn.cursor()
  
# drop table accounts
sql = '''DROP TABLE accounts '''
  
# Executing the query
cursor.execute(sql)
print("Table dropped !")
  
# Commit your changes in the database
conn.commit()
  
# Closing the connection
conn.close()

Output:

Table dropped !

Example 2: Drop table before checking it is exist or not

If you try to delete the same table again, since you have already deleted it, you will get an error saying “table does not exist”, so we can resolve using the IF EXIST clause.

Syntax: DROP TABLE table_name IF EXITS table_name;

Code:

Python3

# importing psycopg2
import psycopg2
  
conn=psycopg2.connect(
    database="geeks",
    user="postgres",
    password="root",
    host="localhost",
    port="5432"
)
  
  
# Creating a cursor object using the cursor()
# method
cursor = conn.cursor()
  
# drop table accounts
sql = '''DROP table IF EXISTS accounts '''
  
# Executing the query
cursor.execute(sql)
  
print("Table dropped !")
# Commit your changes in the database
conn.commit()
  
# Closing the connection
conn.close()