一对多自引用关系SQLAlchemy

 收藏

I am pretty new in python (less than a day!). I am trying to build a Restful API using Flask. I have the class class TaskModel(db.Model): which has a self-referencing relation to the task. Actually, a task can have many predecessors (dependsOn field).

But when I add the relation below I got this error: sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship TaskModel.dependsOn - there are no foreign keys linking these tables.

我的整个Task类别如下:

class TaskModel(db.Model):
    """
    Task Model
    """

    # table name
    __tablename__ = 'tasks'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), nullable=False)
    department = db.Column(db.String(128), nullable=False)
    earliestStart = db.Column(db.String(10), nullable=False)
    latestEnd = db.Column(db.String(10), nullable=False)
    duration = db.Column(db.String(10), nullable=False)
    dnetworkId = db.Column(db.Integer, db.ForeignKey('dnetworks.id'), nullable=False)
    dependsOn = db.relationship('TaskModel', backref='tasks',remote_side=[id], lazy=True)


....

class TaskSchema(Schema):
    """
    Task Schema
    """
    id = fields.Int(dump_only=True)
    name = fields.Str(required=True)
    department = fields.Str(required=True)
    earliestStart = fields.Str(required=True)
    latestEnd = fields.Str(required=True)
    dnetworkId = fields.Int(required=True)
    duration = fields.Str(required=True)
    dependsOn = fields.Nested('self', many=True, exclude=('dependsOn',))
    # dependsOn = fields.Nested('self', exclude=('dependsOn',), default=None, many=True)

提前致谢 :)

回复
  • funde 回复

    在用于关系的SqlAlchemy中,您应该定义两种方式,以下是您可能寻找的完整工作示例:

    import os
    from sqlalchemy.orm import relationship, backref
    from flask import Flask
    from flask_sqlalchemy import SQLAlchemy
    
    BASE_DIR = os.path.dirname(os.path.abspath(__file__))
    app = Flask(__name__)
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////{}/my.db'.format(BASE_DIR)
    
    db = SQLAlchemy(app)
    
    
    class TaskModel(db.Model):
        __tablename__ = 'tasks'
    
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String(128), nullable=True)
        parent_id =  db.Column(db.Integer, db.ForeignKey("tasks.id"))
        children = relationship("TaskModel",
            backref=backref('parent', remote_side=[id])
        )
    
    
    if __name__ == "__main__":
        db.create_all()
        tm = TaskModel(name='Test1')
        db.session.add(tm)
        db.session.commit()
    
    注意parent_id和children,两者的结合将为您提供所需的信息,任务之间的自相关性,在数据库上将parent_id添加到表中。