Delete rows of a PostgreSQL database table using Python

Deletion of rows of a PostgreSQL database table from a Python program is done through executing the SQL DELETE statement through a cursor object of psycopg2.

PostgreSQL Updated 8 Jul 2019 2 min read

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

python
# 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

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