SQLite same-table ON DELETE CASCADE not working

Question

I'm building a web forum thing and using SQLite (with Python) to store data. Each forum has a table with the following create statement (defined via DB Browser for SQLite 3.12.2):

CREATE TABLE "forum{forum_id}" (
    "postId"    INTEGER NOT NULL UNIQUE,
    "parentPostId"  INTEGER,
    "authorId"  INTEGER NOT NULL,
    "content"   TEXT,
    "timestamp" INTEGER,
    "page"  INTEGER,
    FOREIGN KEY("parentPostId") REFERENCES "forum{forum_id}"("postId") ON DELETE CASCADE,
    FOREIGN KEY("authorId") REFERENCES "db_users"("id"),
    PRIMARY KEY("postId" AUTOINCREMENT)
);

What I want to achieve is delete all children (recursively) when a post gets deleted.

My original approach was to fetch all posts from the same page to Python, check which ones have the deleted post's ID in their ancestry and then delete everything relevant.

Then I learned about ON DELETE CASCADE which sounded like an ideal oneliner solution, except it doesn't seem to work - when I delete post X with children Y, Z it only deletes X and leaves Y and Z dangling in the database. The logic behind rendering posts on the website prevents them from ever showing up but I obviously don't want this anyway.

I do have the "enforce foreign keys" pragma enabled. What am I missing?

EDIT: When I run "DELETE FROM forum0 WHERE postId = {some_existing_post_id}" directly in DB Browser it works as expected so I'm assuming the problem is related to querying the database with the sqlite3 module in Python. The query I use is exactly the same though.

Answer 1

After some further testing I'm assuming the foreign_keys pragma set in DB Browser only applies within that browser and not to the database as such. Either that or sqlite3 overrides it by default. So this seems to solve it:

# ... c: sqlite3.Cursor
c.execute("PRAGMA foreign_keys = ON")
# ... any statements that rely on that pragma