Delete Records

Learn how to safely remove specific rows from a database table using Python's SQL parameterization to prevent injection attacks.

What it is

Deleting records in Python involves executing an SQL DELETE statement through a database cursor. The core concept is identifying exactly which rows to remove using a WHERE clause. Crucially, you must use parameterized queries (placeholders like %s) rather than string formatting to insert values into the query. This ensures that user input is treated strictly as data, not executable code.

Why it matters

• Security: Parameterization prevents SQL Injection attacks where malicious input could alter the query logic.
• Data Integrity: Precise WHERE clauses ensure only intended records are removed, avoiding accidental mass deletions.
• Performance: Prepared statements can be optimized by the database engine for repeated execution with different parameters.
• Consistency: Standardizes how data manipulation occurs across different database drivers (PostgreSQL, MySQL, SQLite).

Syntax or steps

1. Establish a connection and create a cursor object.
2. Define the SQL string with placeholders (e.g., %s for PostgreSQL/MySQL, ? for SQLite).
3. Execute the command using cursor.execute(sql, params), passing values as a tuple.
4. Commit the transaction to make changes permanent.
5. Close the cursor and connection.

Example

import psycopg2

# Connect to database
conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()

# Define the query with a placeholder
sql = "DELETE FROM users WHERE id = %s"

# Execute with parameters passed as a tuple
user_id_to_delete = 42
cur.execute(sql, (user_id_to_delete,))

# Commit the change
conn.commit()

# Check how many rows were affected
print(f"{cur.rowcount} record(s) deleted.")

# Cleanup
cur.close()
conn.close()

Part-by-part explanation:

• sql = "DELETE FROM users WHERE id = %s": The %s is a placeholder. Do not put quotes around it; the driver handles escaping.
• cur.execute(sql, (user_id_to_delete,)): The second argument must be a tuple. Note the trailing comma (value,). If you pass just (value), Python treats it as an integer, causing an error.
• conn.commit(): Without this, the deletion is rolled back when the connection closes.
• cur.rowcount: Returns the number of rows actually deleted, useful for verification.

Common mistakes

• Missing the trailing comma: Passing (1) instead of (1,) causes a type error because (1) is just an integer, not a tuple.
• Omitting the WHERE clause: Running DELETE FROM users without conditions wipes the entire table. Always double-check your filters.
• String concatenation: Using f"DELETE FROM users WHERE id = {id}" is vulnerable to SQL injection. Never do this.
• Forgetting to commit: Changes are not saved until conn.commit() is called. If the script crashes before this, no data is lost.

When to use it

Practice

Guided Exercise: Write a script that deletes all users whose email ends with "@test.com". Use a wildcard in the WHERE clause.

Hint: Use LIKE %s and pass ("%@test.com",).

Challenge: Modify the example to delete a user only if their status is 'inactive'. Print a message if no rows were deleted.

Solution Hint: Check if cur.rowcount == 0 after execution.

Quick check

Q: Why is cur.execute("DELETE FROM t WHERE id = %s", (5,)) safer than cur.execute(f"DELETE FROM t WHERE id = {5}")?

A: The first uses parameterization, ensuring the value 5 is treated strictly as data. The second uses string interpolation, which allows malicious inputs to inject additional SQL commands if the variable contained user input.

Summary

Deleting records requires precise SQL syntax combined with secure parameter binding. Always use tuples for parameters and verify row counts to ensure your operations affect the intended data scope.