In this chapter, we’ll explore how to delete records from a database using SQLAlchemy. Deleting records is crucial when managing data, especially when removing outdated or unnecessary entries. SQLAlchemy offers simple yet powerful tools for handling deletions in both single and bulk operations.

Deleting a Single Record by ID

The most common way to delete a record is by identifying it through a specific criterion, such as its ID. Let’s see how to delete a product using its ID.

product = session.query(Product).filter(Product.id == 1).first()

# Check if the product exists
if product:
    # Delete the product
    session.delete(product)
    session.commit()

The code fetches a product with ID 1, verifies its existence, marks it for deletion with session.delete(product), and applies the change using session.commit().

Deleting Multiple Records

Sometimes, you need to delete multiple records at once based on specific conditions. This is useful for tasks like removing out-of-stock items or products below a price threshold.

session.query(Product).filter(Product.is_in_stock == False).delete(synchronize_session="fetch")

# Commit the changes
session.commit()

The query filters out products that are out of stock, deletes them using .delete(synchronize_session="fetch"), and ensures that the session stays synchronized with the database.

Bulk Deletions

For large datasets, bulk deletions are efficient. This method directly modifies database records without loading them into memory, saving time and resources.

session.query(Product).filter(Product.price < 100).delete(synchronize_session="fetch")

# Commit the changes
session.commit()

This example filters products priced below $100 and removes them using the .delete() method, providing a fast and resource-efficient solution for large tables.

Deleting All Records in a Table

In cases where you need to clear a table completely, SQLAlchemy makes it easy with a single query.

session.query(Product).delete(synchronize_session="fetch")

# Commit the changes
session.commit()

The .delete(synchronize_session="fetch") method removes all records from the Product table, which is useful for resetting data or clearing test environments.