Flask SQLAlchemy throws "NoReferencedTableError" when arranging codes in package structure

Question

I am learning flask and trying to arrange my codes in the "package structure" according to some online tutorials. I have put together some minimal codes to replicate my problem. First the tree structure of my codes is

.
└── test001
    ├── __init__.py
    ├── models.py
    └── views.py

The source codes for each file:

"__init__.py":

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SECRET_KEY'] = 'somerandomnumber'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
db = SQLAlchemy(app)

from test001 import views

Source for "models.py":

from test001 import db,app

class User(db.Model):
    id         = db.Column(db.Integer, primary_key=True)
    username   = db.Column(db.String(20), unique=True, nullable=False)
    email      = db.Column(db.String(120), unique=True, nullable=False)
    password   = db.Column(db.String(60), nullable=False)

    favoritejob_id = db.Column(db.Integer, db.ForeignKey('favoritejob.id'), nullable=True)
    favoritejob    = db.relationship("FavoriteJob", backref=db.backref("users"), lazy=True)

class FavoriteJob(db.Model):
    id   = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), nullable=False)

    def __repr__(self):
        return f"<FavoriteJob ID={self.id} Name={self.name}>"

Source for "views.py" (putting all routes together):

from test001.models import User, FavoriteJob

# @app.route("/register", methods=['GET', 'POST'])
# def register():
#    PLACE HOLDER FOR USER REGISTRATION

With the codes above, I then go to python3 shell and to the following:

from test001 import db
db.create_all()

and I get the following errors:

---------------------------------------------------------------------------
NoReferencedTableError                    Traceback (most recent call last)
<ipython-input-2-653042798025> in <module>
----> 1 db.create_all()
...
...
~/.local/lib/python3.6/site-packages/sqlalchemy/sql/schema.py in column(self)
   2045                     "foreign key to target column '%s'"
   2046                     % (self.parent, tablekey, colname),
-> 2047                     tablekey,
   2048                 )
   2049             elif parenttable.key not in parenttable.metadata:

NoReferencedTableError: Foreign key associated with column 'user.favoritejob_id' could not find table 'favoritejob' with which to generate a foreign key to target column 'id'

I did quite a bit searching about this error but couldn't find anything wrong with models.py. Then I removed the following line from views.py, which makes views.py an empty file

from test001.models import User, FavoriteJob

Then "db.create_all()" starts working.

However I can't leave views.py empty as I will put all the views/url routes in the file and will need to use the User and FavoriteJob classes.

I couldn't figure out why the import line in views.py would cause db.create_all() to fail. I am quite new to flask and sqlalchemy so sorry if there is something obviously wrong with my setup. I appreciate your help.

heemayl heemayl · Accepted Answer · 2020-01-12T18:57:43

Flask-SQLAlchemy defines the table names from descriptive base models (unless you've defined one yourself). And if a model name is e.g. CamelCase, it sets the table name as camel_case, not camelcase.

In your model definitions, you have a related field on User model -- favoritejob_id, which is related to FavoriteJob model (as per the relationship declaration favoritejob). As you don't have any explicit table name for the FavoriteJob model, Flask-SQLAlchemy will name it as favorite_job. But your related field definition has:

db.ForeignKey('favoritejob.id')

as the table favoritejob does not exist, you're getting the error (as expected).

You have a couple of options to handle this:

The easiest option probably is to rename the related table name:
```
db.ForeignKey('favorite_job.id')
```

Or give the table name for FavoriteJob model as favoritejob:

class FavoriteJob(db.Model):
    __tablename__ = 'favoritejob'

Flask SQLAlchemy throws "NoReferencedTableError" when arranging codes in package structure

1 Answers