Flask-SQLAlchemy - Foreign key associated with column x could not find table y with which to generate a foreign key to target column y

Question

For some reason when I am trying to insert data into Postgres, I get a message that says my table is not there. However, when I run \dt the table is present in Postgres CLI. In addition, when I run select * from rates;, I see that there is data present in the table.

from app import db

class Car(db.Model):
    __tablename__ = 'car'
    ...
    rates_id = db.Column(db.Integer, db.ForeignKey('rates.rates_id'), nullable=True)
    owner = db.relationship('Owner', backref='car', lazy=True)

class Rates(db.Model):
    __tablename__ = 'rates'
    rates_id =  db.Column(db.Integer, primary_key=True)
    ...
    cars = db.relationship('Car', backref='rates', lazy=True)    

class Owner(db.Model):
    ...


@app.route('/add-car', methods=["POST"])
def add_car():
    ...
    car = Car(...)
    db.session.add(car)
    owner = Owner(...)
    db.session.add(owner)
    db.session.commit()

Above shows the models used and an example endpoint I'm trying to reach. The function should add_car will create entries into Car and Owner. In addition, I want rates_id in the Car model to be null (at least initially).

The error, I'm getting is:

Error: Foreign key associated with column 'car.rates_id' could not find table 'rates' with which to generate a foreign key to target column 'rates_id'

Most of the postings I see relating to this problem uses the same general guideline as me. However, maybe I am missing something that makes it not work?

Thanks.

Answer 1

The problem doesn't have anything to do with my code, but rather how I structured it. Here is the SO post that lead to fixing the issue.

-- api

---- models

-------- _ _ init _ _.py

------ Car.py

------ Rates.py

------ Owner.py

---- routes

---- forms

So within api/models/__init__.py, I added the following:

from .Car import Car
from .Rates import Rates
from .Owner import Owner

And everything seems to work now.

Cheers.