Delete rows of a PostgreSQL database table using Python

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.

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 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();

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')