Python PostgreSQL - Delete Data

You can delete the records in an existing table using the DELETE FROM statement of PostgreSQL database. To remove specific records, you need to use WHERE clause along with it.

Syntax

Following is the syntax of the DELETE query in PostgreSQL −

DELETE FROM table_name [WHERE Clause]

Example

Assume we have created a table with name CRICKETERS using the following query −

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

And if we have inserted 5 records in to it using INSERT statements as −

postgres=# insert into CRICKETERS values ('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Virat', 'Kohli', 30, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1

Following statement deletes the record of the cricketer whose last name is 'Sangakkara'.

postgres=# DELETE FROM CRICKETERS WHERE LAST_NAME = 'Sangakkara';
DELETE 1

If you retrieve the contents of the table using the SELECT statement, you can see only 4 records since we have deleted one.

postgres=# SELECT * FROM CRICKETERS;
 first_name | last_name | age | place_of_birth | country
------------+-----------+-----+----------------+-------------
Jonathan    |     Trott |  39 | CapeTown       | SouthAfrica
Virat       |     Kohli |  31 | Delhi          | India
Rohit       |    Sharma |  33 | Nagpur         | India
Shikhar     |    Dhawan |  46 | Delhi          | India

(4 rows)

If you execute the DELETE FROM statement without the WHERE clause all the records from the specified table will be deleted.

postgres=# DELETE FROM CRICKETERS;
DELETE 4

Since you have deleted all the records, if you try to retrieve the contents of the CRICKETERS table, using SELECT statement you will get an empty result set as shown below −

postgres=# SELECT * FROM CRICKETERS;
 first_name | last_name | age | place_of_birth | country
------------+-----------+-----+----------------+---------
(0 rows)

Deleting Data Using Python

The cursor class of psycopg2 provides a method with name execute() method. This method accepts the query as a parameter and executes it.

Therefore, to insert data into a table in PostgreSQL using python −

  • Import psycopg2 package.

  • Create a connection object using the connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.

  • Turn off the auto-commit mode by setting false as value to the attribute autocommit.

  • The cursor() method of the Connection class of the psycopg2 library returns a cursor object. Create a cursor object using this method.

  • Then, execute the DELETE statement by passing it as a parameter to the execute() method.

Example

Following Python code deletes records of the EMPLOYEE table with age values greater than 25 −

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()

#Retrieving contents of the table
print("Contents of the table: ")
cursor.execute('''SELECT * from EMPLOYEE''')
print(cursor.fetchall())

#Deleting records
cursor.execute('''DELETE FROM EMPLOYEE WHERE AGE > 25''')

#Retrieving data after delete
print("Contents of the table after delete operation ")
cursor.execute("SELECT * from EMPLOYEE")
print(cursor.fetchall())

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

#Closing the connection
conn.close()

Output

Contents of the table:
[
   ('Ramya', 'Rama priya', 27, 'F', 9000.0), 
   ('Sarmista', 'Sharma', 26, 'F', 10000.0), 
   ('Tripthi', 'Mishra', 24, 'F', 6000.0), 
   ('Vinay', 'Battacharya', 21, 'M', 6000.0), 
   ('Sharukh', 'Sheik', 26, 'M', 8300.0)
]
Contents of the table after delete operation:
[  
   ('Tripthi', 'Mishra', 24, 'F', 6000.0), 
   ('Vinay', 'Battacharya', 21, 'M', 6000.0)
]
Advertisements