ON_DELETE="CASCADE" does not work with flask-sqlalchemy

Question

I am developing a WebApp in flask and using flask-sqlalchemy to define my Models and store them in an SQLite database. In short, I have the following 3 models (A Porject can have multiple files, and each file can have multiple results):

class Project(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False)

    files = db.relationship("File", backref="project", passive_deletes=True)

class File(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    path = db.Column(db.String, nullable=False)

    project_id = db.Column(
        db.Integer,
        db.ForeignKey("project.id", ondelete="CASCADE"),
        nullable=False)

    results = db.relationship("Result", backref="file", passive_deletes=True)

class Result(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.String, nullable=False)

    file_id = db.Column(
        db.Integer,
        db.ForeignKey("file.id", ondelete="CASCADE"),
        nullable=False)

Now the problem is, that when I create a project, create and assign some files to the project, create some results and assign them to some files, and finally delete the project, the cascade deleting of the files and the results does not work. I found the following post and implemented the db.relationship attributes as suggested there, but the problem remains the same.

P.S.: here is a minimal working example, that shows the problem.

P.P.S.: Can somebody confirm, that the problem is reproducible. Not that it only happens on my computer/my environment.

https://docs.sqlalchemy.org/en/14/dialects/sqlite.html#foreign-key-support — Gord Thompson, Jul 27 '21 at 12:04
ohhh... I see... I have to register this listener, so that for EVERY connection this pragma is executed >. — DiKorsch, Jul 27 '21 at 12:54

score 0 · Answer 1 · answered Jul 27 '21 at 13:04

Well, this answer goes in the category RTFM. As Gord Thompson "mentioned" with the link to the documentary of sqlalchemy, one needs to add the following lines somewhere reachable on flask app start:

from sqlalchemy.engine import Engine
from sqlalchemy import event

db = SQLAlchemy(app)

@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

(now I am asking myself, why not a single word is written about this in the flask-sqlalchemy docu :-/ )

ON_DELETE="CASCADE" does not work with flask-sqlalchemy

1 Answers1