SqlAlchemy error: Foreign key could not find table

So I’m building an app and I’m trying to save new changes to my database but when I try to commit the changes in the flask using db.session.commit() it returns me the following error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'products.country_id' could not find table 'countries' with which to generate a foreign key to target column 'id' 

In my models.py I have the following:

from app import db from . import db  from datetime import datetime  def now():     return datetime.now()  class Countries(db.Model):     __tablename__ = 'countries'     __table_args__ = {'schema': 'products_data'}      id = db.Column(db.Integer, primary_key=True, unique=True, autoincrement=True)     name = db.Column(db.String(255))     code = db.Column(db.String(45))      def __repr__(self):         return f'Id {self.id}'  class Categories(db.Model):     __tablename__ = 'categories'     __table_args__ = {'schema': 'products_data'}      id = db.Column(db.Integer, primary_key=True, unique=True, autoincrement=True)     name = db.Column(db.String(255))      def __repr__(self):         return f'Id {self.id}'  class Brands(db.Model):     __tablename__ = 'brands'     __table_args__ = {'schema': 'products_data'}      id = db.Column(db.Integer, primary_key=True, unique=True, autoincrement=True)     name = db.Column(db.String(255))     logo = db.Column(db.String(5000))     feed = db.Column(db.String(5000))     feed_type = db.Column(db.String(45))     category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))     country_id = db.Column(db.Integer, db.ForeignKey('countries.id'))     awinmid = db.Column(db.Integer)      def __repr__(self):         return f'Id {self.id}'  class Products(db.Model):     __tablename__ = 'products'     __table_args__ = {'schema': 'products_data'}      id = db.Column(db.Integer, primary_key=True, unique=True, autoincrement=True)     name = db.Column(db.String(255))     url = db.Column(db.Text)     category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))     country_id = db.Column(db.Integer, db.ForeignKey('countries.id'))     price = db.Column(db.Float)     currency = db.Column(db.String(45))     discount_price = db.Column(db.Float)     shipping = db.Column(db.Float)     brand_id = db.Column(db.Integer, db.ForeignKey('brands.id')) 

Am I doing anything wrong when associating a column in products with a foreign key? This is the first time I encounter this error so I’m really lost on what to do right now.

Add Comment
2 Answer(s)

To fix I just added the schema to the db.ForeignKey and it worked

Example:

db.ForeignKey('products_data.countries.id') 

PS:

Not my idea. Just wanted to post the answer in case someone visits the post later with the same problem.

Gord Thompson thanks for the help!

Add Comment

First of all, I don t see any table Categories. Secondly, you copy pasted your schema from the Products table into your Countries one.

PS: By default sqlalchemy gives the tables the name of the class (lower cased). So your __tablename__='products' does nothing actually.

EDIT:

The problem with your code lies in how you set the __table_args__ attribute. You assign an object to it, which by their specifications is wrong.

Take a look at the following example and modify your code accordingly

__table_args__ = ({'schema': 'products_data'}) 

Also for further reference, take a look at this https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/table_config.html

Answered on July 16, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.