Python and PostgreSQL - Deleting Data

In order to delete data in a PostgreSQL database, the SQL ‘Delete’ statement needs to be used. The following example deletes a record with a specific ‘id’ from the ‘person’ table, which was used in the examples for selecting, inserting and updating data.

Deleting data works in a similar way to the other examples, where a parameterised query is used. Firstly, a connection to the database is established, then the query parameter for the ‘id’ of the record to be deleted is defined as a variable, which is bound in to the following SQL statement when it is executed and a confirmation message is displayed. The execution of the SQL statement is wrapped in a ‘try-except-finally’ block to catch any errors that may arise and close the database connection at the end.

import psycopg2

# Database connection variable.
connect = None

try:

    # Connect to database.
    connect = psycopg2.connect(host='localhost', database='Demo',
                               user='testUN', password='testPW')

except psycopg2.DatabaseError as e:

    # Confirm unsuccessful connection and stop program execution.
    print("Database connection unsuccessful.")
    quit()

# Cursor to execute query.
cursor = connect.cursor()

# Query parameter.
id = 2

# SQL to delete data in the person table.
sqlDelete = \
    "DELETE FROM person \
     WHERE id = %s"

try:

    # Execute query and commit changes.
    cursor.execute(sqlDelete, (id,))
    connect.commit()

    # Confirm successful deletion of person information.
    print("Person information deleted successfully.")

except psycopg2.DatabaseError as e:

    # Confirm error deleting person information and stop program execution.
    print("Error deleting person information.")
    quit()

finally:

    # Close database connection.
    connect.close()

The contents of the ‘person’ table now looks as follows.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Bloggs Mrs 1985-05-19