PostgreSQL Python: Delete Data from Tables

Summary: this tutorial shows you how to delete data from the PostgreSQL tables in Python using psycopg database adapter.

Steps for deleting data from the PostgreSQL table in Python

To delete data from the PostgreSQL table in Python, you use the following steps:

First, create a new database connection by calling the connect() function of the psycopg module.

conn = psycopg2.connect(dsn)
Code language: Python (python)

The connect() function returns a new connection object.

Next, to execute any statement, you need a cursor object. To create a new cursor object, you call the cursor() method of the connection object as follows:

cur = conn.cursor()
Code language: Python (python)

Then, execute the DELETE statement. If you want to pass values to the DELETE statement, you use the placeholders ( %s) in the DELETE statement and pass input values to the second parameter of the execute() method.

The DELETE statement with a placeholder for the value of the id field is as follows:

DELETE FROM table_1 WHERE id = %s;
Code language: SQL (Structured Query Language) (sql)

To bind value value_1 to the placeholder, you call the execute() method and pass the input value as a tuple to the second parameter like the following:

cur.execute(delete_sql, (value_1,))
Code language: SQL (Structured Query Language) (sql)

After that, save the changes to the database permanently by calling the commit() method of the connection object.

conn.commit()
Code language: SQL (Structured Query Language) (sql)

Finally, close the communication with the PostgreSQL database server by calling the close() method of the cursor and connection objects.

cur.close()
conn.close()
Code language: Python (python)

Example of deleting data in PostgreSQL table in Python

We will use the parts table in the suppliers database that we created in the creating table tutorial for the sake of demonstration.

The following delete_part() function deletes a row in the parts table specified by the part_id.

#!/usr/bin/python

import psycopg2
from config import config

def delete_part(part_id):
    """ delete part by part id """
    conn = None
    rows_deleted = 0
    try:
        # read database configuration
        params = config()
        # connect to the PostgreSQL database
        conn = psycopg2.connect(**params)
        # create a new cursor
        cur = conn.cursor()
        # execute the UPDATE  statement
        cur.execute("DELETE FROM parts WHERE part_id = %s", (part_id,))
        # get the number of updated rows
        rows_deleted = cur.rowcount
        # Commit the changes to the database
        conn.commit()
        # Close communication with the PostgreSQL database
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

    return rows_deleted
Code language: Python (python)

Before testing the delete_part() function, we query data from the parts table as follows:

suppliers=# SELECT * FROM parts;
 part_id |  part_name
---------+-------------
       1 | SIM Tray
       2 | Speaker
       3 | Vibrator
       4 | Antenna
       5 | Home Button
       6 | LTE Modem
(6 rows)
Code language: SQL (Structured Query Language) (sql)

Now we run the Python program to delete the part with the part id 1.

if __name__ == '__main__':
    deleted_rows = delete_part(2)
    print('The number of deleted rows: ', deleted_rows)
Code language: Python (python)

We select data from the parts table again to confirm the deletion made by the Python program.

suppliers=# SELECT * FROM parts;
 part_id |  part_name
---------+-------------
       2 | Speaker
       3 | Vibrator
       4 | Antenna
       5 | Home Button
       6 | LTE Modem
(5 rows)
Code language: SQL (Structured Query Language) (sql)

The row with the part id 1 has been deleted successfully as expected.

In this tutorial, you have learned how to delete data from the PostgreSQL table in Python.