How to get rid of sqlalchemy.exc.NoReferencedTableError?

Here's some code related to the problem:

db_session.py

import sqlalchemy as sa
import sqlalchemy.orm as orm
import sqlalchemy.ext.declarative as dec

SqlAlchemyBase = dec.declarative_base()

__Session = None


def create_session():
    global __Session
    global_init()
    return __Session()


def global_init():
    global __Session

    if __Session:
        return

    conn_str = f'sqlite:///sqlite.db'

    engine = sa.create_engine(conn_str, echo=True, future=True)
    __Session = orm.sessionmaker(bind=engine)

    SqlAlchemyBase.metadata.create_all(engine)

User.py (error comes from User.department_id column)

import sqlalchemy
from db_session import SqlAlchemyBase


class User(SqlAlchemyBase):
    __tablename__ = 'users'

    id = sqlalchemy.Column(sqlalchemy.Integer,
                           primary_key=True, autoincrement=True)
    department_id = sqlalchemy.Column(sqlalchemy.Integer, 
                                      sqlalchemy.ForeignKey('departments.id'), nullable=True)

Departments.py

import sqlalchemy
from db_session import SqlAlchemyBase


class Departments(SqlAlchemyBase):
    __tablename__ = 'departments'

    id = sqlalchemy.Column(sqlalchemy.Integer, 
                           primary_key=True, autoincrement=True)
    chief = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey("users.id"))
    members = sqlalchemy.orm.relationship("User", foreign_keys="User.department_id")

The error occurs whenever I try to access the 'users' table. Here's the error text:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'users.department_id' could not find table 'departments' with which to generate a foreign key to target column 'id'

I've checked the sqlite.db file with database management software and there definitely is 'departments' table. I've printed the output of

SqlAlchemyBase.metadata.tables.keys()

and the 'departments' table is there, too, and even has the right name. I've tried to add something to the 'departments' table, but that also didn't help. All tables' names are set explicitly with tablename property, so sqlalchemy's automatic table naming isn't the problem. How do I get rid of this error?

1 answer

  • answered 2022-05-07 17:12 marteg

    Thanks to @snakecharmerb for the solution!

    Turns out, I had to import the Departments class everywhere I used the Users model for sqlalchemy to find all tables.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum