Flask-SQLAlchemy how to do constraint foreign key with cascade in MySQL (InnoDB)?

Question

I've been looking for ways to implement the CONSTRAINT FOREIGN KEY ON DELETE CASCADE in the below UsersAccessMapping model in SQLAlchemy with PyMySQL driver and MariaDB 10.0 with InnoDB in the database.

Python = 3.5.2  
SQLAlchemy = 1.1.13  
Flask-SQLAlchemy = 2.2  

The SQL:

CREATE TABLE Users (
    UserID int AUTO_INCREMENT,
    Name varchar(200) NOT NULL,
    Email varchar(200),
    Username varchar(200) NOT NULL,
    Password text NOT NULL,
    Created datetime,
    Updated datetime,
    PRIMARY KEY (UserID)
);

CREATE TABLE UsersAccessLevels (
    UsersAccessLevelID int AUTO_INCREMENT,
    LevelName varchar(100) NOT NULL,
    AccessDescription text,
    PRIMARY KEY (UsersAccessLevelID)
);

CREATE TABLE UsersAccessMapping (
    UsersAccessMappingID int AUTO_INCREMENT,
    UserID int NOT NULL,
    UsersAccessLevelID int NOT NULL,
    PRIMARY KEY (UsersAccessMappingID),
    CONSTRAINT fk_useraccess FOREIGN KEY (UserID)
        REFERENCES Users(UserID) ON DELETE CASCADE,
    CONSTRAINT fk_useraccess_level FOREIGN KEY (UsersAccessLevelID)
        REFERENCES UsersAccessLevels(UsersAccessLevelID) ON DELETE CASCADE
);

What I have in my models.py now:

from app import db


class Users(db.Model):
    """All users' information is stored here"""
    __tablename__ = "Users"
    UserID = db.Column(db.Integer(), primary_key=True)
    Name = db.Column(db.String(200), nullable=False)
    Email = db.Column(db.String(200))
    Username = db.Column(db.String(200), nullable=False)
    Password = db.Column(db.Text, nullable=False)
    Created = db.Column(db.DateTime)
    Updated = db.Column(db.DateTime)


class UsersAccessLevels(db.Model):
    """This defines the various access levels users can have"""
    __tablename__ = "UsersAccessLevels"
    UsersAccessLevelID = db.Column(db.Integer, primary_key=True)
    LevelName = db.Column(db.String(100), nullable=False)
    AccessDescription = db.Column(db.Text)


class UsersAccessMapping(db.Model):
    """Each users' access level is defined here"""
    __tablename__ = "UsersAccessMapping"
    UsersAccessMappingID = db.Column(db.Integer, primary_key=True)
    UserID = db.Column(db.Integer, nullable=False)
    UsersAccessLevelID = db.Column(db.Integer, nullable=False)
    __table_args__ = (
        db.ForeignKeyConstraint(
            ["fk_useraccess", "fk_useraccess_level"],
            ["Users.UserID", "UsersAccessLevels.UsersAccessLevelID"],
            ondelete="CASCADE"
        )
    )

There is something wrong with the table_args syntax, but I haven't been able to find any examples on how it should be. I found one that was very similar, but in that the third parameter was an empty dict. However, I want to use the ondelete="CASCADE". How would that be added?

When running the python3 manage.py db init, it throws this:

  File "/srv/vortech-backend/venv/lib/python3.5/site-packages/sqlalchemy/ext/declarative/base.py", line 196, in _scan_attributes
    "__table_args__ value must be a tuple, "
sqlalchemy.exc.ArgumentError: __table_args__ value must be a tuple, dict, or None

I tried changing ondelete="cascade" to a dict {"ondelete": "cascade"}, but that doesn't work either. It gives the same error as above.

Update: The problem was that the ondelete is supposed to be outside of the tuple, like this:

__table_args__ = (
    db.ForeignKeyConstraint(
        ["fk_useraccess", "fk_useraccess_level"],
        ["Users.UserID", "UsersAccessLevels.UsersAccessLevelID"]
    ),
    ondelete="CASCADE"
)

However, with this change there is still a syntax error, as ondelete="CASCADE" is not defined. Changing it to a dict {"ondelete": "cascade"} throws this:

  File "/srv/vortech-backend/venv/lib/python3.5/site-packages/sqlalchemy/sql/base.py", line 282, in _validate_dialect_kwargs
    "named <dialectname>_<argument>, got '%s'" % k)
TypeError: Additional arguments should be named <dialectname>_<argument>, got 'ondelete'

Answer 1

Okay, after some testing and reading, the answer is that SQLAlchemy does some internal magic to achieve it. So, this will accomplish the same result as the SQL:

from app import db  # The value is from: db = SQLAlchemy(app)


class Users(db.Model):
    """All users' information is stored here"""
    __tablename__ = "Users"
    UserID = db.Column(db.Integer(), primary_key=True)
    Name = db.Column(db.String(200), nullable=False)
    Email = db.Column(db.String(200))
    Username = db.Column(db.String(200), nullable=False)
    Password = db.Column(db.Text, nullable=False)
    Created = db.Column(db.DateTime)
    Updated = db.Column(db.DateTime)


class UsersAccessLevels(db.Model):
    """This defines the various access levels users can have"""
    __tablename__ = "UsersAccessLevels"
    UsersAccessLevelID = db.Column(db.Integer, primary_key=True)
    LevelName = db.Column(db.String(100), nullable=False)
    AccessDescription = db.Column(db.Text)


class UsersAccessMapping(db.Model):
    """Each users' access level is defined here"""
    __tablename__ = "UsersAccessMapping"
    UsersAccessMappingID = db.Column(db.Integer, primary_key=True)
    UserID = db.Column(
        db.Integer, db.ForeignKey("Users.UserID", ondelete="CASCADE"), nullable=False
    )
    UsersAccessLevelID = db.Column(
        db.Integer,
        db.ForeignKey("UsersAccessLevels.UsersAccessLevelID", ondelete="CASCADE"),
        nullable=False
    )

The Constraints and such are automagically handled with the db.ForeignKey() parameters in the column definition. It does not need to be done on the Table directly, like in SQL.

The names for the foreign keys appear to be automatically generated by SQLAlchemy also. Here's how it looks like in the database: