How to Delete Data From PostgreSQL Tables Using Python

PostgreSQL data can be manipulated through interaction with Python. For this purpose, a popular library named psycopg2 is used. psycopg2 serves as an efficient PostgreSQL adapter that allows us to establish a connection between Python and PostgreSQL. Once the connection between Postgres and Python is established, you can perform any operations, such as creating, searching, updating, and deleting data. In this guide, we will show you how to delete data from a Postgres table using Python.

How to Delete Data From PostgreSQL Tables Using Python?

Before deleting the data from Postgres tables using Python, you need to make sure that “psycopg2” is installed on your system. It can be installed by executing the following “pip” command:

pip install psycopg2

Output

img

To delete the table’s data from the PostgreSQL database using Python programming, simply follow the below-provided steps.

Step 1: Import psycopg2

Open any code editor, make a new file with the “.py” extension, and import the “psycopg2” at the top of it

import psycopg2

Step 2: Connect Python With Postgres

Make a Postgres Python connection by providing the essential connection details, such as database name, password, hostname, etc:

conn_details = psycopg2.connect(
host="localhost",
database="postgres",
user="postgres",
password="*****",
port= '5432'
)

In the above snippet, the connect() function is utilized to connect to the “postgres” database as a “postgres” user.

Step 3: Check the Table’s Data

Navigate to the SQL Shell and execute the “SELECT” command to see the table’s data:

SELECT * FROM emp_info;
img

Step 4: Delete the Table’s Data

Suppose we want to remove those employees whose “joining_date” is greater than “2023-01-01”. To do this, execute the delete query with the help of execute() function:

cursor = conn_details.cursor()
delete_tbl_data = "DELETE FROM emp_info WHERE joining_date > '2023-01-01';"
cursor.execute(delete_tbl_data)

Here:

  • First, a cursor object is created.
  • After that, the DELETE FROM command is executed to remove data from the “emp_info” table.
  • Finally, the query is executed using the “execute()” function to delete the table’s data.

Step 5: Save Changes

In the below code snippet, the commit() and close() functions are utilized to save the changes and close the cursor and the connection:

conn.commit()
cursor.close()
conn.close()

Here is what you will get on successful execution:

img

The given code was executed successfully.

Step 6: Confirm Table’s Data Deletion

Navigate back to the SQL Shell and run the “SELECT” query for the “emp_info” table:

SELECT * FROM emp_info;

The below snippet demonstrates that the select records have been successfully removed from the “emp_info” table:

img

That’s all about deleting data from a Postgres table using Python.

Conclusion

To delete data from PostgreSQL tables using Python, open any code editor, make a new file with the “.py” extension, and import the “psycopg2” at the top of it. Connect Python with Postgres using psycopg2.connect(), and execute the delete query with the help of the “execute()” function to remove the selected records from a Postgres table. This post has illustrated a stepwise guide on deleting data from Postgres tables using Python.