python - Flask-SQLAlchemy如何在MySQL(InnoDB)中使用级联约束外键？

我一直在寻找在SQLAlchemy中使用PyMySQL驱动程序和在数据库中使用InnoDB在MariaDB 10.0中使用下面的UsersAccessMapping模型中的CONSTRAINT FOREIGN KEY ON DELETE CASCADE的方法。

Python = 3.5.2
SQLAlchemy = 1.1.13
Flask-SQLAlchemy = 2.2

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
);

我现在在models.py中拥有的是：

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"
        )
    )

table_args语法有问题，但是我无法找到任何有关语法的示例。我发现一个非常相似，但是第三个参数是空字典。但是，我想使用ondelete =“ CASCADE”。如何添加？

运行python3 manage.py db init时，它将引发以下情况：

  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

我尝试将ondelete="cascade"更改为字典{"ondelete": "cascade"}，但这也不起作用。它给出与上述相同的错误。

更新：
问题在于ondelete应该位于元组之外，如下所示：

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

但是，此更改仍然存在语法错误，因为未定义ondelete="CASCADE"。将其更改为dict {"ondelete": "cascade"}会引发以下情况：

  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'

最佳答案

好的，经过一些测试和阅读之后，答案是SQLAlchemy做了一些内部魔术来实现它。因此，这将完成与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
    )

约束等通过列定义中的db.ForeignKey()参数自动处理。不需要像在SQL中那样直接在Table上完成。

外键的名称似乎也是由SQLAlchemy自动生成的。这是数据库中的样子：

关于python - Flask-SQLAlchemy如何在MySQL(InnoDB)中使用级联约束外键？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46012785/