flask-sqlachemy foreign keys not working with models

Question

I have two models in my flask application User and Employee.

User class:


    from flask_login import UserMixin
    from . import db


    class User(UserMixin, db.Model):
        id = db.Column(db.Integer, primary_key=True)  # primary keys are required in SQLAlchemy
        email = db.Column(db.String(100), unique=True)
        password = db.Column(db.String(100), nullable=False)
        name = db.Column(db.String(1000), nullable=False)
        user_type = db.Column(db.Enum("Candidate", "Employee", name="user_type"))

        def get_id(self):
            return self.id

Employee class:


    from . import db
    from .model_user import User


    class Employee(db.Model):
        employee_id = db.Column(db.Integer, primary_key=True)
        employee_title = db.Column(db.String(100), nullable=False)
        employee_permissions = db.Column(db.String(10000), nullable=True)
        user_id = Column('user_id', Integer, ForeignKey("user.id"))
        user = relationship("User", backref=backref("user", uselist=False))

        def get_id(self):
            return self.employee_id

I tried using the

db.create_all(app=create_app())

where db = SQLAlchemy() and create_app() function returns the flask app

I am getting the following error

Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\schema.py", line 926, in create
    bind._run_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 2104, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1663, in _run_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\visitors.py", line 144, in traverse_single
    return meth(obj, **kw)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\ddl.py", line 827, in visit_table
    self.connection.execute(
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1020, in execute
    return meth(self, multiparams, params)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1071, in _execute_ddl
    compiled = ddl.compile(
  File "<string>", line 1, in <lambda>
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\elements.py", line 476, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\ddl.py", line 29, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\compiler.py", line 319, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\compiler.py", line 350, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\visitors.py", line 95, in _compiler_dispatch
    return meth(self, **kw)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\compiler.py", line 2914, in visit_create_table
    const = self.create_table_constraints(
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\compiler.py", line 2963, in create_table_constraints
    return ", \n\t".join(
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\compiler.py", line 2963, in <genexpr>
    return ", \n\t".join(
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\compiler.py", line 2966, in <genexpr>
    self.process(constraint)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\compiler.py", line 350, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\visitors.py", line 95, in _compiler_dispatch
    return meth(self, **kw)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\compiler.py", line 3214, in visit_foreign_key_constraint
    remote_table = list(constraint.elements)[0].column.table
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\langhelpers.py", line 883, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "C:\Users\krish\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\schema.py", line 2125, in column
    raise exc.NoReferencedTableError(
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'employee.user_id' could not find table 'User' with which to generate a foreign key to target column 'id'

I clearly have the user table and the id column. i have even tried by manually creating the user table in database but i still get the same error. Not sure where i am going wrong. I have also tried the solutions provided on SO

One-to-one relationship in Flask

https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#one-to-one

but still am unable to solve this error.

You have here `ForeignKey("user.id")`, but the error looks like you really have `ForeignKey("User.id")`. — Ilja Everilä, Jun 04 '20 at 08:20
@IljaEverilä i've tried both User.id and user.id... still getting the same error — krishna, Jun 04 '20 at 09:25

above_c_level · Answer 1 · 2020-06-04T11:51:14.037

0

Flask can't find the table because you have not defined tablenames.

Example:

class Employee(db.Model):
    __tablename__ = 'employees'  # <-- whatever table name you want 
    employee_id = db.Column(db.Integer, primary_key=True)

class User(UserMixin, db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)

This SO post gives more details.

edited Jun 04 '20 at 11:51

answered Jun 04 '20 at 11:08

above_c_level

3,579
3
22
37

I have a typo. Changed my answer from "\__tablename__ = 'users'" to "\__tablename__ = 'user'" – above_c_level Jun 04 '20 at 11:52
I have tried both single quotes and double quotes. I am still getting the same error – krishna Jun 04 '20 at 13:47
My typo was the "s" in users. Single and double quotes should make no difference. – above_c_level Jun 04 '20 at 13:48
Flask-SQLAlchemy `Model` automatically defines the `table_name` based on the `TableName` from the class name. – Ilja Everilä Jun 05 '20 at 06:34

flask-sqlachemy foreign keys not working with models

1 Answers1