The DELETE statement in SQL allows you to remove records from a table. In Python, you can use this functionality with PostgreSQL using the psycopg2 library. This tutorial guides you step-by-step through safely deleting rows using Python code.

Table of Contents

1. What You’ll Learn

2. Prerequisites

3. Setting Up Your Environment

4. Basic DELETE Statement

5. Using DELETE with a Condition

6. Deleting All Records

7. Full Example with Confirmation

8. Tips and Common Pitfalls

1. What You’ll Learn

• How to delete records from a PostgreSQL table using Python

• How to use WHERE safely to delete specific rows

• How to avoid accidental data loss

• How to verify deletion and commit changes

✅ 2. Prerequisites

• Python installed

• PostgreSQL installed and running

• A table to work with (we’ll use students)

• Install psycopg2:

pip install psycopg2-binary

3. Setting Up Your Environment

Assume you have a table named students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10)
);

Insert some sample data:

INSERT INTO students (name, age, grade) VALUES
('Alice', 14, '8th'),
('Bob', 13, '7th'),
('Carol', 15, '9th');

4. Basic DELETE Statement

Delete a student by name

import psycopg2

conn = psycopg2.connect(
    dbname="school",
    user="postgres",
    password="your_password",
    host="localhost",
    port="5432"
)

cur = conn.cursor()

cur.execute("DELETE FROM students WHERE name = %s", ('Bob',))
conn.commit()

print("Record deleted successfully.")

cur.close()
conn.close()

✅ Always use parameterized queries (%s) to prevent SQL injection.

5. Using DELETE with a Condition

You can delete records with any condition:

# Delete students younger than 14
cur.execute("DELETE FROM students WHERE age < %s", (14,))
conn.commit()

⚠️ 6. Deleting All Records

Use this carefully. It removes all rows from the table.

cur.execute("DELETE FROM students")
conn.commit()

⚠️ This does not reset the SERIAL primary key counter. Use TRUNCATE TABLE for a full reset.

7. Full Example with Confirmation

import psycopg2

try:
    conn = psycopg2.connect(
        dbname="school",
        user="postgres",
        password="your_password",
        host="localhost",
        port="5432"
    )
    cur = conn.cursor()

    # Delete a student named 'Carol'
    cur.execute("DELETE FROM students WHERE name = %s", ('Carol',))
    conn.commit()

    # Check remaining students
    cur.execute("SELECT * FROM students")
    rows = cur.fetchall()

    print("Remaining Students:")
    for row in rows:
        print(f"ID: {row[0]}, Name: {row[1]}, Age: {row[2]}, Grade: {row[3]}")

    cur.close()
    conn.close()

except psycopg2.Error as e:
    print("Database error:", e)

8. Tips and Common Pitfalls

Conclusion

Using DELETE in PostgreSQL with Python gives you the ability to clean up or manage your database efficiently. Just be sure to use WHERE carefully and always test before deleting important data.