FlaskSQLAlchemy如何在MySQL（InnoDB）中用cascade实现约束外键？

Question

我一直在寻找在下面的UsersAccessMapping模型中使用PyMySQL驱动程序和MariaDB 10.0在数据库中使用InnoDB实现对DELETE CASCADE的约束外键。在

Python = 3.5.2  
SQLAlchemy = 1.1.13  
Flask-SQLAlchemy = 2.2  

SQL：

^{pr2}$

我现在的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语法有问题，但我还没有找到任何关于它应该如何的示例。我发现了一个非常相似的参数，但是第三个参数是一个空dict。如何添加？在

运行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"改成dict {"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'

Answer 1

好吧，经过一些测试和阅读，答案是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那样直接在表上执行。在

外键的名称似乎也是由SQLAlchemy自动生成的。以下是数据库中的外观：