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.

 

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


Copyright 2020 © pythontic.com