Python PostgreSQL - Drop Table


Advertisements

You can drop a table from PostgreSQL database using the DROP TABLE statement.

Syntax

Following is the syntax of the DROP TABLE statement in PostgreSQL −

DROP TABLE table_name;

Example

Assume we have created two tables with name CRICKETERS and EMPLOYEES using the following queries −

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, 
   Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);
CREATE TABLE
postgres=#
postgres=# CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), 
   AGE INT, SEX CHAR(1), INCOME FLOAT
);
CREATE TABLE
postgres=#

Now if you verify the list of tables using the “\dt” command, you can see the above created tables as −

postgres=# \dt;
            List of relations
 Schema | Name       | Type  | Owner
--------+------------+-------+----------
 public | cricketers | table | postgres
 public | employee   | table | postgres
(2 rows)
postgres=#

Following statement deletes the table named Employee from the database −

postgres=# DROP table employee;
DROP TABLE

Since you have deleted the Employee table, if you retrieve the list of tables again, you can observe only one table in it.

postgres=# \dt;
            List of relations
Schema  | Name       | Type  | Owner
--------+------------+-------+----------
public  | cricketers | table | postgres
(1 row)


postgres=#

If you try to delete the Employee table again, since you have already deleted it, you will get an error saying “table does not exist” as shown below −

postgres=# DROP table employee;
ERROR: table "employee" does not exist
postgres=#

To resolve this, you can use the IF EXISTS clause along with the DELTE statement. This removes the table if it exists else skips the DLETE operation.

postgres=# DROP table IF EXISTS employee;
NOTICE: table "employee" does not exist, skipping
DROP TABLE
postgres=#

Removing an Entire Table Using Python

You can drop a table whenever you need to, using the DROP statement. But you need to be very careful while deleting any existing table because the data lost will not be recovered after deleting a table.

import psycopg2
#establishing the connection
conn = psycopg2.connect(database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432')

#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists
cursor.execute("DROP TABLE emp")
print("Table dropped... ")

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Output

#Table dropped...

Useful Video Courses


Video

Python Online Training

Most Popular

187 Lectures 17.5 hours

Malhar Lathkar

Video

Python Essentials Online Training

55 Lectures 8 hours

Arnab Chakraborty

Video

Learn Python Programming in 100 Easy Steps

136 Lectures 11 hours

In28Minutes Official

Video

Python with Data Science

75 Lectures 13 hours

Eduonix Learning Solutions

Video

Python 3 from scratch to become a developer in demand

70 Lectures 8.5 hours

Lets Kode It

Video

Python Data Science basics with Numpy, Pandas and Matplotlib

63 Lectures 6 hours

Abhilash Nelson

Advertisements