Deleting data

The delete function removes rows from a table and returns the deleted rows. Its second argument is a Dict[str, Any] describing the WHERE clause: each entry is one AND-ed condition. The dict key is the column name, and the dict value is either:

• a plain value — interpreted as an equality check (=),
• None — interpreted as IS NULL,
• or a comparator like is_eq, is_gt, is_in, is_ilike, … for anything else.

warning

delete requires at least one entry in the filter. Calling delete("users", {}) raises an error — to wipe an entire table use run_sql('DELETE FROM "users"') deliberately.

Basic Delete

You can delete rows by calling the delete function like this:

from abstra.tables import delete

deleted = delete("users", {"id": 123})
deleted # [{"id": 123, "name": "foo", "email": "bar"}]

Which is equivalent to

DELETE FROM users
WHERE id = 123
RETURNING *

delete always returns a list of the deleted rows (possibly empty), so you can use it to confirm what was removed:

from abstra.tables import delete

deleted = delete("users", {"email": "ghost@example.com"})

if not deleted:
    print("Nothing to delete — no user with that email.")
else:
    print(f"Removed {len(deleted)} user(s).")

Delete by ID

If you only need to set the id in the WHERE filter, you can use delete_by_id instead. It returns the deleted row directly (or None if no row matched):

from abstra.tables import delete_by_id

deleted = delete_by_id("users", id=123)
deleted # {"id": 123, "name": "foo", "email": "bar"}

Which is equivalent to

DELETE FROM users
WHERE id = 123
RETURNING *

Delete with multiple conditions

Passing multiple keys in the filter combines them with AND:

from abstra.tables import delete

delete(
    "users",
    {"status": "inactive", "newsletter_opt_in": False},
)

Which is equivalent to

DELETE FROM users
WHERE status = 'inactive' AND newsletter_opt_in = false
RETURNING *

Delete using None (IS NULL)

None in the filter is interpreted as IS NULL, so it's the right way to clean up rows missing a value.

from abstra.tables import delete

delete("users", {"verified_at": None})

Which is equivalent to

DELETE FROM users
WHERE verified_at IS NULL
RETURNING *

Delete using comparators

You can use comparators (is_gt, is_lt, is_in, is_between, is_ilike, etc.) inside the filter to express any condition the WHERE clause needs. See Using comparators for the full reference and more examples.

from abstra.tables import delete, is_lt, is_in, is_ilike
from datetime import date, timedelta

# Delete logs older than 30 days
delete(
    "logs",
    {"created_at": is_lt((date.today() - timedelta(days=30)).isoformat())},
)

# Delete users in a specific list of statuses
delete(
    "users",
    {"status": is_in(["banned", "deleted", "spam"])},
)

# Delete users whose email matches a case-insensitive pattern
delete(
    "users",
    {"email": is_ilike("%@tempmail.com")},
)