Sqlalchemy Error: sqlalchemy.exc.NoReferencedTableError

August 4, 2022

I need use sqlalchemy to create 4 table, student, subject, exam and score.

Execept score, another can create. I was use argument back_populates and backref, but both can’t work.

It show this error

sqlalchemy.exc.NoReferencedTableError: 
    Foreign key associated with column 'score.subject_id' could not find table 'subject' with 
    which to generate a foreign key to target column 'id'

There is my code
first import and somebase

from sqlalchemy import Column, ForeignKey, Integer, String, Boolean, Date, Float, Table
from sqlalchemy import UniqueConstraint, create_engine
from sqlalchemy.orm import relationship, declarative_base, sessionmaker


StudentBase = declarative_base()
SubjectBase = declarative_base()
ExamBase = declarative_base()
ScoreBase = declarative_base()

Table: student

class Student(StudentBase):
    __tablename__ = 'student'

    id = Column(Integer, primary_key=True)
    student_id = Column(String(8), unique=True, nullable=False)
    name = Column(String(20), nullable=False)
    number = Column(Integer, nullable=False)
    grade = Column(Integer, nullable=False)
    class_ = Column("class", Integer, nullable=False)

    UniqueConstraint(number, grade, class_, name='basic_info')
    stid = relationship("Score", back_populates="strs")

    def __init__(self, student_id, name, number, grade, class_):
        self.student_id = student_id
        self.name = name
        self.number = number
        self.grade = grade
        self.class_ = class_

    def __repr__(self):
        return "<student('%s', '%s', '%d', '%d')>" % (self.student_id, self.name, self.grade, self.class_)

Table: subject

class Subject(SubjectBase):
    __tablename__ = 'subject'

    id = Column(Integer, primary_key=True)
    name = Column(String(30), nullable=False)
    grade = Column(Integer, nullable=False)

    UniqueConstraint(name, grade, name='subject_limit')
    suid = relationship("Score", back_populates="surs")

    def __init__(self, name, grade):
        self.name = name
        self.grade = grade
    
    def __repr__(self):
        return "<subject(%s, %d)>" % (self.name, self.grade)

Table: exam

class Exam(ExamBase):
    __tablename__ = "exam"

    id = Column("id", Integer, primary_key=True)
    name = Column("name", String(30), nullable=False)

    exid = relationship("Score", back_populates="exrs")

    def __init__(self, name):
        self.name = name
    
    def __repr__(self):
        return "<exam(%s)>" % (self.name)


class Score(ScoreBase):
    __tablename__ = "score"

    id = Column(Integer, primary_key=True)
    student_id = Column(ForeignKey("student.id"), nullable=False)
    subject_id = Column(ForeignKey("subject.id"), nullable=False)
    exam_id = Column(ForeignKey("exam.id"), nullable=False)
    score = Column(Float, nullable=False)

    strs = relationship("Student", back_populates="stid")
    surs = relationship("Subject", back_populates="suid")
    exrs = relationship("Exam", back_populates="exid")
    

    def __init__(self, student_id, subject_id, exam_id ,score):
        self.student_id = student_id
        self.subject_id = subject_id
        self.exam_id = exam_id
        self.score = score
    
    def __repr__(self):
        return "<score(%d, %d, %d, %.2f)>" % (self.student_id, self.subject_id, self.exam_id, self.score)

main

if __name__ == "__main__":
    engine = create_engine('some sqlinfo')
    
    StudentBase.metadata.create_all(engine)
    SubjectBase.metadata.create_all(engine)
    ExamBase.metadata.create_all(engine)
    ScoreBase.metadata.create_all(engine)

How can I do to solve this problem, thanks.