This tutorial picks up from where theQuerying Data from Tables Tutorial left off.

Steps for deleting data from a 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 theconnect() function of thepsycopg2 module.

conn = psycopg2.connect(config)

Theconnect() function returns a newconnection object.

Next, create a newCursor object by calling thecursor() method of theConnection object:

cur= conn.cursor()

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

TheDELETE statement with a placeholder for the value of theid field is as follows:

DELETE FROM table_1 WHEREid = %s;

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

cur.execute(delete_sql, (value1,))

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

conn.commit()

Finally, close the communication with the PostgreSQL database server by calling theclose() method of thecursor andconnection objects.

cur.close()conn.close()

If you use context managers, you don’t need to explicitly close the cursor or connection.

Example of deleting data in PostgreSQL table in Python

We will use theparts table in thesuppliers database for the demonstration purposes.

The followingdelete_part() function deletes a row in theparts table specified by thepart_id.

import psycopg2from configimport load_configdef delete_part(part_id):    """ Delete part by part id """    rows_deleted= 0    sql= 'DELETE FROM parts WHERE part_id =%s'    config= load_config()    try:        with  psycopg2.connect(**config) as conn:            with  conn.cursor() as cur:                # execute the UPDATE statement                cur.execute(sql, (part_id,))                rows_deleted= cur.rowcount            # commit the changes to the database            conn.commit()    except (Exception, psycopg2.DatabaseError)as error:        print(error)    finally:        return rows_deletedif __name__ == '__main__':    deleted_rows= delete_part(2)    print('The number of deleted rows: ', deleted_rows)

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

python delete.py

Output:

The number of deleted rows:  1

Retrieve data from theparts table again to confirm the deletion:

SELECT * FROM parts;

Output:

part_id |  part_name---------+-------------       1 | SIM Tray       3 | Vibrator       4 | Antenna       5 | Home Button       6 | LTE Modem       8 | OLED(6 rows)

The output indicates that part id 2 has been deleted successfully.

Download the project source code

Summary

• Use theexecute() method of acursor object to delete a row from a table.

Last updated onMay 19, 2024

On this page

• Steps for deleting data from a PostgreSQL table in Python
• Example of deleting data in PostgreSQL table in Python
• Summary