delete()

Safely remove database records with precise filtering and cascading considerations across all supported databases.

Overview

The delete() method removes records from database tables based on filter conditions. It provides safe deletion with mandatory filtering to prevent accidental data loss and returns the number of records actually deleted.

⚠️ Important Safety Notice

Delete operations are permanent and irreversible. Always backup important data and test delete queries carefully before running them on production data.

Signature

delete(table_name: str, filters: Dict[str, Any]) -> int

Parameters

table_name

Type: str

Name of the table from which to delete records. The table must exist.

filters

Type: Dict[str, Any]

Dictionary mapping column names to values for identifying which records to delete. All conditions are combined with AND logic. Must be non-empty to prevent accidental deletion of all table data.

🚨 Critical: Empty filters are rejected to prevent accidental table truncation. Use explicit conditions to identify the exact records to delete.

Returns

Type: int

Number of records that were actually deleted from the table. Returns 0 if no records matched the filter conditions.

Examples

Basic Deletion

Delete Single Record
1from akron import Akron
2
3db = Akron("sqlite:///example.db")
4
5# Setup: Create table and insert sample data
6db.create_table("users", {
7 "id": "int",
8 "username": "str",
9 "email": "str",
10 "age": "int",
11 "active": "bool",
12 "created_date": "str"
13})
14
15# Insert test users
16db.insert("users", {"username": "alice", "email": "alice@example.com", "age": 28, "active": True, "created_date": "2024-01-01"})
17db.insert("users", {"username": "bob", "email": "bob@example.com", "age": 32, "active": False, "created_date": "2023-12-15"})
18db.insert("users", {"username": "charlie", "email": "charlie@example.com", "age": 25, "active": False, "created_date": "2023-11-20"})
19
20print("Before deletion:")
21all_users = db.find("users")
22for user in all_users:
23 print(f" {user['id']}: {user['username']} ({user['email']})")
24
25# Delete a specific user by ID
26deleted_count = db.delete("users", {"id": 2})
27
28print(f"\nDeleted {deleted_count} user(s)")
29
30print("\nAfter deletion:")
31remaining_users = db.find("users")
32for user in remaining_users:
33 print(f" {user['id']}: {user['username']} ({user['email']})")
Expected Output
Before deletion:
  1: alice (alice@example.com)
  2: bob (bob@example.com)
  3: charlie (charlie@example.com)

Deleted 1 user(s)

After deletion:
  1: alice (alice@example.com)
  3: charlie (charlie@example.com)

Conditional Deletion

Delete multiple records based on specific criteria:

Delete Inactive Users
1# Delete all inactive users created before 2024
2deleted_count = db.delete("users", {"active": False})
3
4print(f"Deleted {deleted_count} inactive user(s)")
5
6# Verify deletion
7remaining_users = db.find("users")
8print(f"\nRemaining users: {len(remaining_users)}")
9for user in remaining_users:
10 status = "Active" if user["active"] else "Inactive"
11 print(f" {user['username']}: {status}")
Expected Output
Deleted 1 inactive user(s)

Remaining users: 1
  alice: Active

Complex Filtering

Use multiple conditions for precise deletion:

Delete with Multiple Conditions
1# Setup: Create orders table with more test data
2db.create_table("orders", {
3 "id": "int",
4 "customer_id": "int",
5 "total": "float",
6 "status": "str",
7 "created_date": "str"
8})
9
10# Insert test orders
11db.insert("orders", {"customer_id": 1, "total": 49.99, "status": "cancelled", "created_date": "2023-12-01"})
12db.insert("orders", {"customer_id": 1, "total": 149.99, "status": "completed", "created_date": "2024-01-05"})
13db.insert("orders", {"customer_id": 2, "total": 25.50, "status": "cancelled", "created_date": "2023-11-15"})
14db.insert("orders", {"customer_id": 2, "total": 89.99, "status": "pending", "created_date": "2024-01-10"})
15db.insert("orders", {"customer_id": 3, "total": 15.00, "status": "cancelled", "created_date": "2023-10-20"})
16
17print("All orders:")
18all_orders = db.find("orders")
19for order in all_orders:
20 print(f" Order " + str(order['id']) + ": Customer " + str(order['customer_id']) + ", $" + str(order['total']) + ", " + order['status'])
21
22# Delete small cancelled orders (under $30) from 2023
23deleted_count = db.delete("orders", {
24 "status": "cancelled",
25 "total": 25.50 # This will match the exact amount
26})
27
28print(f"\nDeleted {deleted_count} small cancelled order(s)")
29
30# Show remaining orders
31remaining_orders = db.find("orders")
32print(f"\nRemaining orders: {len(remaining_orders)}")
33for order in remaining_orders:
34 print(f" Order " + str(order['id']) + ": Customer " + str(order['customer_id']) + ", $" + str(order['total']) + ", " + order['status'])
Expected Output
All orders:
  1: Customer 1, $49.99, cancelled
  2: Customer 1, $149.99, completed
  3: Customer 2, $25.5, cancelled
  4: Customer 2, $89.99, pending
  5: Customer 3, $15.0, cancelled

Deleted 1 small cancelled order(s)

Remaining orders: 4
  Order 1: Customer 1, $49.99, cancelled
  Order 2: Customer 1, $149.99, completed
  Order 4: Customer 2, $89.99, pending
  Order 5: Customer 3, $15.0, cancelled

Deletion with Foreign Key Considerations

Handle deletions that may affect related records:

Safe Foreign Key Deletion
1# Create related tables
2db.create_table("categories", {
3 "id": "int",
4 "name": "str",
5 "description": "str"
6})
7
8db.create_table("products", {
9 "id": "int",
10 "name": "str",
11 "category_id": "int->categories.id",
12 "price": "float",
13 "in_stock": "bool"
14})
15
16# Insert test data
17cat1_id = db.insert("categories", {"name": "Electronics", "description": "Electronic devices"})
18cat2_id = db.insert("categories", {"name": "Books", "description": "Physical and digital books"})
19
20prod1_id = db.insert("products", {"name": "Laptop", "category_id": cat1_id, "price": 999.99, "in_stock": True})
21prod2_id = db.insert("products", {"name": "Phone", "category_id": cat1_id, "price": 699.99, "in_stock": False})
22prod3_id = db.insert("products", {"name": "Novel", "category_id": cat2_id, "price": 19.99, "in_stock": True})
23
24print("Before deletion:")
25print("Categories:")
26categories = db.find("categories")
27for cat in categories:
28 print(f" {cat['id']}: {cat['name']}")
29
30print("\nProducts:")
31products = db.find("products")
32for prod in products:
33 print(f" {prod['id']}: {prod['name']} (category: {prod['category_id']})")
34
35# First, delete products that reference the category we want to remove
36# Delete out-of-stock electronics
37deleted_products = db.delete("products", {"category_id": cat1_id, "in_stock": False})
38print(f"\nDeleted {deleted_products} out-of-stock electronics")
39
40# Verify products were deleted
41remaining_products = db.find("products")
42print(f"\nRemaining products: {len(remaining_products)}")
43for prod in remaining_products:
44 print(f" {prod['id']}: {prod['name']} (category: {prod['category_id']})")
Expected Output
Before deletion:
Categories:
  1: Electronics
  2: Books

Products:
  1: Laptop (category: 1)
  2: Phone (category: 1)
  3: Novel (category: 2)

Deleted 1 out-of-stock electronics

Remaining products: 2
  1: Laptop (category: 1)
  3: Novel (category: 2)

Database-Specific Behavior

🗄️ SQLite

Uses standard SQL DELETE statements with WHERE clauses:

1# Generated SQL for SQLite
2DELETE FROM users WHERE id = ?
3# Parameters: [2]
4# Returns: cursor.rowcount

🐬 MySQL

Uses MySQL DELETE syntax with parameter binding:

1# Generated SQL for MySQL
2DELETE FROM users WHERE id = %s
3# Parameters: [2]
4# Returns: cursor.rowcount

🐘 PostgreSQL

Uses PostgreSQL DELETE syntax:

1# Generated SQL for PostgreSQL
2DELETE FROM users WHERE id = %s
3# Parameters: [2]
4# Returns: cursor.rowcount

🍃 MongoDB

Translates to MongoDB deleteMany operation:

1# MongoDB equivalent
2db.users.deleteMany({"id": 2})
3# Returns: result.deleted_count

Error Handling

Handling Deletion Errors
1from akron import Akron
2from akron.exceptions import AkronError, TableNotFoundError
3
4db = Akron("sqlite:///example.db")
5
6try:
7 # This will fail if table doesn't exist
8 result = db.delete("nonexistent_table", {"id": 1})
9
10except TableNotFoundError as e:
11 print(f"Table not found: {e}")
12
13except AkronError as e:
14 print(f"Delete error: {e}")
15
16# Safe deletion with validation
17def safe_delete(table_name, filters):
18 try:
19 # Validate that we have filters (prevent mass deletion)
20 if not filters:
21 print("Error: Filters required for deletion - refusing to delete all records")
22 return 0
23
24 # Show what will be deleted (optional safety check)
25 matching_records = db.find(table_name, filters)
26 if len(matching_records) == 0:
27 print("No records match the deletion criteria")
28 return 0
29
30 print(f"Found {len(matching_records)} record(s) matching deletion criteria")
31
32 # Perform the deletion
33 count = db.delete(table_name, filters)
34 print(f"Successfully deleted {count} record(s)")
35 return count
36
37 except TableNotFoundError:
38 print(f"Table '{table_name}' does not exist")
39 return 0
40 except AkronError as e:
41 print(f"Deletion failed: {e}")
42 return 0
43
44# Safe usage examples
45safe_delete("users", {"id": 99}) # Safe - specific ID
46safe_delete("users", {}) # Prevented - no filters
47safe_delete("missing_table", {"id": 1}) # Handled - missing table
Expected Output
Table not found: no such table: nonexistent_table
No records match the deletion criteria
Error: Filters required for deletion - refusing to delete all records
Table 'missing_table' does not exist

Best Practices

🛡️ Data Safety

  • Always backup critical data before performing deletions
  • Test deletion queries on development data first
  • Use specific filters to target exact records for deletion
  • Check the returned count to verify expected number of deletions
  • Consider soft deletes (marking as deleted) instead of hard deletes

🔗 Foreign Key Management

  • Delete child records before deleting parent records
  • Consider the impact on related tables
  • Use cascading deletes carefully (when supported)
  • Verify referential integrity after deletions

⚡ Performance

  • Use indexed columns in filter conditions for better performance
  • Delete in batches for very large datasets
  • Consider the impact on database locks and concurrent operations
  • Monitor database performance during large deletion operations

📋 Auditing

  • Log deletion operations for audit trails
  • Record what was deleted and when
  • Consider implementing soft delete patterns for important data
  • Maintain deletion logs for compliance requirements

Common Patterns

Deletion Patterns and Examples
1# Pattern 1: Delete by primary key (safest)
2deleted = db.delete("users", {"id": user_id})
3
4# Pattern 2: Delete by status or flag
5count = db.delete("orders", {"status": "cancelled"})
6
7# Pattern 3: Delete old records by date
8db.delete("logs", {"created_date": "2023-01-01"}) # Exact date match
9
10# Pattern 4: Safe cleanup with verification
11def cleanup_old_data(table_name, date_column, cutoff_date):
12 # First, check what would be deleted
13 to_delete = db.find(table_name, {date_column: cutoff_date})
14
15 if len(to_delete) == 0:
16 print("No old records to delete")
17 return 0
18
19 print(f"About to delete {len(to_delete)} old records")
20
21 # Perform deletion
22 deleted = db.delete(table_name, {date_column: cutoff_date})
23 print(f"Successfully deleted {deleted} records")
24 return deleted
25
26# Pattern 5: Cascading deletion (manual)
27def delete_user_and_data(user_id):
28 # Delete in correct order to maintain referential integrity
29 deleted_orders = db.delete("orders", {"customer_id": user_id})
30 deleted_sessions = db.delete("user_sessions", {"user_id": user_id})
31 deleted_user = db.delete("users", {"id": user_id})
32
33 print(f"Deleted user: {deleted_user}, orders: {deleted_orders}, sessions: {deleted_sessions}")
34
35# Pattern 6: Conditional deletion with business logic
36def archive_inactive_users():
37 # Find users inactive for over a year
38 inactive_users = db.find("users", {"active": False})
39
40 archived_count = 0
41 for user in inactive_users:
42 # Move to archive table before deleting
43 db.insert("archived_users", user)
44 db.delete("users", {"id": user["id"]})
45 archived_count += 1
46
47 return archived_count

Alternative: Soft Deletion

Consider implementing soft deletion for critical data:

Implementing Soft Deletes
1# Add deleted_at column to tables for soft deletion
2db.create_table("users", {
3 "id": "int",
4 "username": "str",
5 "email": "str",
6 "deleted_at": "str" # NULL means not deleted
7})
8
9# Soft delete function
10def soft_delete_user(user_id):
11 from datetime import datetime
12 timestamp = datetime.now().isoformat()
13
14 updated = db.update("users",
15 {"id": user_id, "deleted_at": None}, # Only update if not already deleted
16 {"deleted_at": timestamp}
17 )
18
19 return updated > 0
20
21# Query active (non-deleted) users
22def get_active_users():
23 # Find users where deleted_at is None/null
24 return db.find("users", {"deleted_at": None})
25
26# Restore soft-deleted user
27def restore_user(user_id):
28 updated = db.update("users",
29 {"id": user_id},
30 {"deleted_at": None}
31 )
32 return updated > 0
33
34# Permanently delete soft-deleted records (cleanup)
35def permanent_cleanup():
36 # Delete records that were soft-deleted more than 90 days ago
37 from datetime import datetime, timedelta
38 cutoff = datetime.now() - timedelta(days=90)
39 cutoff_str = cutoff.isoformat()
40
41 # This would need custom logic to compare dates
42 # For now, manual cleanup approach
43 old_deleted = db.find("users") # Get all and filter in Python
44 to_delete = []
45
46 for user in old_deleted:
47 if user["deleted_at"] and user["deleted_at"] < cutoff_str:
48 to_delete.append(user["id"])
49
50 deleted_count = 0
51 for user_id in to_delete:
52 deleted_count += db.delete("users", {"id": user_id})
53
54 return deleted_count

Next Steps

After deleting records, you might want to:

Close Connection

Properly close database connections when done

→ learn about close()

Query Remaining Data

Verify deletions by querying remaining records

→ learn about find()