烧瓶SQL炼金术| MySql-多个外键问题 [英] Flask SQL-Alchemy | MySql - Multiple Foreign Keys issues

问题描述

class Role(db.Model):
    __tablename__ = 'roles'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    default = db.Column(db.Boolean, default=False, index=True)
    permissions = db.Column(db.Integer)


class Devices(db.Model):
    __tablename__ = 'devices'
    id = db.Column(db.Integer, primary_key=True)
    purpose = db.Column('purpose', db.String(64))
    type = db.Column('type', db.String(64))
    name = db.Column('name', db.String(64))
    channel = db.Column('channel', db.Integer)
    role_id = db.Column('role_id', db.Integer)
    role_permissions = db.Column('role_permissions', db.Integer)
    role = db.ForeignKeyConstraint(['role_id', 'role_permissions'], ['roles.id', 'roles.permissions'])

那我希望它能起作用:

dev = Devices(purpose="lights",type="tcp",name="zeus",channel=8)
role = Role.query.first()
dev.role = role
db.session.add(dev)
db.session.commit()

但是一旦持久化，role_id和role_permissions将获得空值.为什么?什么是正确的方法?

But once persisted, role_id and role_permissions get null value. Why? Whats the right way to do this??

推荐答案

除了外键之外，您还需要定义一个关系.

You need to define a relationship in addition to the foreign key.

外键只是数据库级别的约束，可以确保您不能引用不存在的行(此外，它可以帮助SQLAlchemy建立关系，而无需您指定其他时间如何链接两个表).

A foreign key is just a database-level constraint to ensure you cannot reference rows that don't exist (additionally, it helps SQLAlchemy setup a relationship without you specifying another time how the two tables are linked).

您要在模型中使用它:

class Devices(db.Model):
    __table_args__ = (db.ForeignKeyConstraint(['role_id', 'role_permissions'], ['roles.id', 'roles.permissions']),)
    # ...
    role = db.relationship('Role', backref=db.backref('devices'))

这样做，device.role = some_role将正确填充外键，此外，每个Role实例将具有一个devices集合，使您可以访问其关联的设备.

By doing so, device.role = some_role will properly populate the foreign keys, and in addition each Role instance will have a devices collection that gives you access to its associated devices.

SQLAlchemy教程还包含有关关系的部分: http://docs.sqlalchemy.org/en/rel_1_0 /orm/tutorial.html#building-a-relationship

The SQLAlchemy tutorial also has a section about relationships: http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#building-a-relationship

您几乎可以遵循它； Flask-SQLAlchemy和普通的SQLalchemy并没有什么真正的区别-Flask-SQLAlchemy只是通过db对象使许多东西可以访问，从而避免显式导入它们.

You can pretty much follow it; Flask-SQLAlchemy and plain SQLalchemy don't really differ - Flask-SQLAlchemy simply makes many things accessible via the db object to avoid importing them explicitly.

顺便说一句，由于Role.id是主键，因此您不需要在外键中包含role_permissions-您不能同时拥有多个具有相同ID的角色，因为主键始终是唯一的.这使您的模型更加简单:

By the way, since Role.id is the primary key, you don't need to include role_permissions in the foreign key - you cannot have more than one role the same ID since the primary key is always unique. This makes your model even easier:

class Devices(db.Model):
    # ...
    role_id = db.Column('role_id', db.Integer, db.ForeignKey('roles.id'))
    role = db.relationship('Role', backref=db.backref('devices'))

您还可以摆脱Devices模型中的role_permissions列(顺便说一下，应将其命名为Device).如果需要权限，只需从角色中获取权限(如果通常需要，将lazy=False添加到外键，然后查询设备将始终加入角色表，以避免额外的查询)

You can also get rid of the role_permissions column in your Devices model (which, by the way, should be named Device). If you need the permissions, simply get it from the role (if you usually need it, add lazy=False to the foreign key, then querying a device will always join the role table to avoid extra queries)

这篇关于烧瓶SQL炼金术| MySql-多个外键问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！