Overview
- The DELETE statement in PostgreSQL deletes one or more rows of a database table, based on the WHERE clause specified.
- If a WHERE clause is not specified, then all the data from a PostgreSQL table will be deleted.
- The DELETE statement also deletes rows from the child tables unless an ONLY clause is specified.
- The DELETE statement does not delete a table from a PostgreSQL table. It only deletes the contents i.e., rows of a PostgreSQL table.
Deleting rows from a PostgreSQL table using Python
- psycopg2 is the Python client API for PostgreSQL database server.
- Using Psycopg2 in a Python program a developer can create a connection object, a cursor object and execute the DELETE statement on a PostgreSQL server.
- Once, one or more rows are deleted the results can be verified by querying the PostgreSQL server using the SELECT statement.
Example
# Example Python program to delete rows from a PostgreSQL database table
import psycopg2
# Obtain a database connection
postgresConnection = psycopg2.connect("dbname=test user=test password=");
# Obtain a database cursor
postgresCursor = postgresConnection.cursor();
# Determine what rows are going to be deleted
selectStatement = "SELECT * FROM test.devices";
postgresCursor.execute(selectStatement);
rows = postgresCursor.fetchall();
print("List of rows before delete:");
for row in rows:
print(row);
# Provide a valid SQL statement. In this case a DELETE statement
deleteStatement = "DELETE FROM devices where status = 'Obsolete'";
# Delete obsolete rows
postgresCursor.execute(deleteStatement);
# Verify the delete has worked
selectStatement = "SELECT * FROM devices";
postgresCursor.execute(selectStatement);
rows = postgresCursor.fetchall();
print("List of rows after delete:");
for row in rows:
print(row);
# Close the database cursor and connection
postgresCursor.close();
postgresConnection.close();Output
List of rows before delete:
(4, 'Clock', 'Active')
(5, 'Transistor', 'Active')
(0, 'Hourglass', 'Obsolete')
(1, 'Sundial', 'Obsolete')
(2, 'Vacuum Tube', 'Obsolete')
(3, 'Steam Locomotive', 'Obsolete')
List of rows after delete:
(4, 'Clock', 'Active')
(5, 'Transistor', 'Active')