Flask-appbuilder many-to-may relationship sqlalchemy.exc.NoReferencedTableError Error

Question

I've read through all of these (https://stackoverflow.com/search?q=sqlalchemy.exc.NoReferencedTableError%3A), the Flask-appbuilder docs, the sqlalchemy docs, and the Flask-sqlalchemy docs and more. Unfortunately, I can't find any full examples of a many-to-many sqlalchemy relationship.

I have a python Flask app using flask-appbuilder (which relies on flask-sqlalchemy). My app/model.py file has this:

field_feature_association = Table('field_feature_association',Base.metadata,
                                            Column('field_id', Integer, ForeignKey('field.id')),
                                            Column('feature_id',Integer, ForeignKey('feature.id')),
                                            schema="main"
                                        )

class field(Model):
    __tablename__ = 'field'
    id = Column(Integer, primary_key=True)
    name = Column(String(70), nullable=False)
    database_type = Column(String(70)) #varchar(255), text, int
    joinable_to = Column(Text()) 
    notes = Column(Text()) #don't use this for X 
    table_id = Column(Integer, ForeignKey('table.id'))
    table = relationship("table")
    features = relationship("feature",
                    secondary = field_feature_association,
                    backref = backref('fields'), 
                    )

    def __repr__(self):
        return self.name

class feature(Model):
    __tablename__ = 'feature'
    id = Column(Integer, primary_key=True)
    name = Column(String(70), unique = True, nullable=False)
    field_id = Column(Integer, ForeignKey('field.id'))
    #field = relationship("field")

    def __repr__(self):
        return self.name

It's generating this error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'field_feature_association.feature_id' could not find table 'feature' with which to generate a foreign key to target column 'id'

Thoughts on how to fix this error?

Answer 1

Here is a working sample of many-to-many in SQLAlchemy. Moreover I modified your model and it works fine:

field_feature_association = db.Table('field_feature_association', db.Model.metadata,
                                     db.Column('field_id', db.Integer, db.ForeignKey('field.id')),
                                     db.Column('feature_id', db.Integer, db.ForeignKey('feature.id')),
                                          schema="main"
        )


class Field(db.Model):
    __tablename__ = 'field'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(70), nullable=False)
    database_type = db.Column(db.String(70))  # varchar(255), text, int
    joinable_to = db.Column(db.Text())
    notes = db.Column(db.Text())  # don't use this for X
    features = db.relationship("Feature",
                            secondary=field_feature_association,
                            backref=db.backref('fields'),
    )

    def __repr__(self):
        return self.name


class Feature(db.Model):
    __tablename__ = 'feature'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(70), unique=True, nullable=False)
    field_id = db.Column(db.Integer, db.ForeignKey('field.id'))
    # field = relationship("field")

    def __repr__(self):
        return self.name

and this is how to use it:

field = Field()
field.name="filed1"
feature = Feature()
feature.name = "feature1"
field.features.append(feature)
db.session.add(field)
db.session.commit()

My database object is imported as ’db’ and I have used it explicitely to refer to other types.