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.
1 | conn = psycopg2.connect(dsn) |
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:
1 | cur = conn.cursor() |
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:
1 | DELETE FROM table_1 WHERE id = %s; |
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:
1 | cur.execute(delete_sql, (value_1,)) |
After that, save the changes to the database permanently by calling the commit() method of the connection object.
1 | conn.commit() |
Finally, close the communication with the PostgreSQL database server by calling the close() method of the cursor and connection objects.
1 2 | cur.close() conn.close() |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | #!/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 |
Before testing the delete_part() function, we query data from the parts table as follows:
1 2 3 4 5 6 7 8 9 10 | 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) |
Now we run the Python program to delete the part with the part id 1.
1 2 3 | if __name__ == '__main__': deleted_rows = delete_part(2) print('The number of deleted rows: ', deleted_rows) |
We select data from the parts table again to confirm the deletion made by the Python program.
1 2 3 4 5 6 7 8 9 | suppliers=# SELECT * FROM parts; part_id | part_name ---------+------------- 2 | Speaker 3 | Vibrator 4 | Antenna 5 | Home Button 6 | LTE Modem (5 rows) |
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.