How to solve sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize [closed]

Question

I have 3 models in models.py, and the relationship is many Products to many Sellers and one Category can have many Products:

association_table = db.Table('association', db.metadata,
    db.Column('products_id', db.Integer, db.ForeignKey('products.id')),
    db.Column('sellers_id', db.Integer, db.ForeignKey('sellers.id'))
)


class Product(db.Model):

    __tablename__ = 'products'

    id = db.Column(db.Integer, primary_key=True)
    product_name = db.Column(db.String(60), index=True)
    sellers_id = db.relationship('Seller', secondary=association_table)
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
    categories = db.relationship('Category', back_populates='products')

    def __repr__(self):
        return f'{self.product_name}'


class Seller(db.Model):

    __tablename__ = 'sellers'

    id = db.Column(db.Integer, primary_key=True)
    seller_name = db.Column(db.String(60), index=True)
    email = db.Column(db.String(60))
    phone = db.Column(db.String(60))
    site = db.Column(db.String(60))

    def __repr__(self):
        return f'{self.seller_name}'


class Category(db.Model):

    __table__name = 'categories'

    id = db.Column(db.Integer, primary_key=True)
    category_name = db.Column(db.String(60), index=True)
    products = db.relationship('Product', back_populates='category')

    def __repr__(self):
        return f'{self.category_name}'

And I'm getting the following error:

Traceback (most recent call last):
  File "/home/andressa/MEGAsync/Code/product-catalog/tests/test_models.py", line 7, in setUp
    self.u = User(username='u', password='cat')
  File "<string>", line 2, in __init__
  File "/home/andressa/anaconda3/envs/product-catalog/lib/python3.6/site-packages/sqlalchemy/orm/instrumentation.py", line 376, in _new_state_if_none
    state = self._state_constructor(instance, self)
  File "/home/andressa/anaconda3/envs/product-catalog/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 883, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/andressa/anaconda3/envs/product-catalog/lib/python3.6/site-packages/sqlalchemy/orm/instrumentation.py", line 202, in _state_constructor
    self.dispatch.first_init(self, self.class_)
  File "/home/andressa/anaconda3/envs/product-catalog/lib/python3.6/site-packages/sqlalchemy/event/attr.py", line 322, in __call__
    fn(*args, **kw)
  File "/home/andressa/anaconda3/envs/product-catalog/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 3392, in _event_on_first_init
    configure_mappers()
  File "/home/andressa/anaconda3/envs/product-catalog/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 3276, in configure_mappers
    raise e
sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class Category->category'. Original exception was: Could not determine join condition between parent/child tables on relationship Category.product - there are no foreign keys linking these tables.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

How can I fix this error?

Answer 1

The error message was very clear, the relationships between tables weren't well written. This is the solution that I found.

class Product(db.Model):

    __tablename__ = 'products'

    id = db.Column(db.Integer, primary_key=True)
    product_name = db.Column(db.String(60), index=True)
    sellers = db.relationship('Seller', secondary=association_table, backref=db.backref('products', lazy='dynamic'), lazy='dynamic')
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))

    def __repr__(self):
        return f'{self.product_name}'


class Seller(db.Model):

    __tablename__ = 'sellers'

    id = db.Column(db.Integer, primary_key=True)
    seller_name = db.Column(db.String(60), index=True)
    email = db.Column(db.String(60))
    phone = db.Column(db.String(60))
    site = db.Column(db.String(60))

    def __repr__(self):
        return f'{self.seller_name}'


class Category(db.Model):

    __tablename__ = 'categories'

    id = db.Column(db.Integer, primary_key=True)
    category_name = db.Column(db.String(60), index=True)
    products = db.relationship('Product', backref='category', lazy=True)

    def __repr__(self):
        return f'{self.category_name}'