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'

Interestingly, in http://docs.sqlalchemy.org/en/latest/core/constraints.html#on-update-and-on-delete, it shows exactly the way I had it originally (which does not work). — Juha Untinen, Sep 02 '17 at 11:17
You've linked to Core docs on how to pass a foreign key constraint as an argument to a `Table` constructor. It is very different from the Declarative class definition you have, which should have a sequence or mapping of arguments as `__table_args__`, if passing such (just like the error says). Normally a tuple or a dict is passed. You've passed a single `ForeignKeyConstraint` instead, because you've forgot to add a comma after it, so you have an expression in redundant parentheses. And the ondelete keyword argument would belong in the `ForeignKeyConstraint` call, not outside of it. — Ilja Everilä, Sep 02 '17 at 17:39
A lot more also wrong with the first attempt, like trying to create a composite foreign key to 2 different tables. — Ilja Everilä, Sep 02 '17 at 17:45
@IljaEverilä Thanks. How about the answer I posted below? Would that be the correct way to do it? It seems to work at least. — Juha Untinen, Sep 02 '17 at 19:38
Looks good. In case you're not yet that familiar with how Declarative and Core combine, the Declarative classes actually create a `Table` for you when the class definition is evaluated. Hence the `__table_args__` attribute for passing additional arguments. If this is old news to you, I apologize for being patronizing. — Ilja Everilä, Sep 02 '17 at 20:04

Juha Untinen · Answer 1 · 2017-09-07T10:30:05.577

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:

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

1 Answers1