SQLAlchemy import tables with relationships

Question

I have problem with separating tables with relationships in different files. I want the tables below to be in three separate files and to import TableA in third party page, but I can not manage the load order.

In most of the time I'm receiving the following error.

sqlalchemy.exc. InvalidRequestError: When initializing mapper Mapper|TableA|tablea, expression 'TableB' failed to locate a name ("name 'TableB' is not defined"). If this is a class name, consider adding this relationship() to the class after both dependent classes have been defined.

class TableA(Base):
    __tablename__ = "tablea"
   id = Column(Integer, primary_key=True)
   name = Column(String)

   tableB = relationship("TableB", secondary = TableC.__table__)

class TableB(Base):
   __tablename__ = "tableb"
   id = Column(Integer, primary_key=True)
  name = Column(String)

class TableC(Base):
   __tablename__ = "tableab"
   tableAId = Column("table_a_id", Integer, ForeignKey("TableA.id"), primary_key=True)
   tableBId = Column("table_b_id", Integer, ForeignKey("TableB.id"), primary_key=True)

van · Answer 1 · 2022-02-16T03:12:25.070

This should work (note that the TableC.table is replaced with the name of the table to avoid circular module loading):

### base.py
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base(bind=engine)

### classA.py
from base import Base
from classB import TableB

class TableA(Base):
    __tablename__ = 'tablea'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    tableBs = relationship("TableB", secondary="tableab")
    #tableBs = relationship("TableB", secondary=TableC.__table__)

### classB.py
from base import Base

class TableB(Base):
    __tablename__ = 'tableb'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))

### classC.py
from base import Base
from classA import TableA
from classB import TableB

class TableC(Base):
    __tablename__ = 'tableac'
    tableAId = Column(Integer, ForeignKey("tablea.id"), primary_key=True, )
    tableBId = Column(Integer, ForeignKey("tableb.id"), primary_key=True, )

### main.py
from base import Base, Session, engine
from classA import TableA
from classB import TableB
from classC import TableC
Base.metadata.create_all(engine)

Also I believe that the ForeignKey parameter is case sensitive, so you code might not work because "TableA.id" doe snot match "tablea" name when case-sensitive.

Do you have to import the class to be able to use it in a relationship? — Balthazar Rouberol, Mar 18 '13 at 12:50
@BalthazarRouberol, After testing, yes you do need the imports in each class — Rohlex32, Oct 04 '18 at 00:10
Confirmed after I asked on IRC. I'll submit a doc patch for this, because it wasn't clear to me either. — antgel, Mar 16 '19 at 12:25
Why TableC has "tableab" for the `__tablename__` attribute ? — jossefaz, Feb 14 '22 at 16:46
@jossefaz: just a typo (which I will correct now), thank you for noticing it. — van, Feb 16 '22 at 03:12

score 1 · Answer 2 · answered Jan 01 '19 at 10:19

from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()


class Parent(Base):
    __tablename__ = 'Parent'
    ParentID = Column(Integer, primary_key=True)
    Description = Column(String)

    def __init__(self, ParentID, Description):
        self.ParentID = ParentID
        self.Description = Description
----------------------------------------------------------------------

from sqlalchemy import Column, String, Integer, ForeignKey
import Parent
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()


class Child(Base):
    __tablename__ = "Child"
    ChildID = Column(Integer, primary_key=True)
    Description = Column(String)
    ParentID = Column('CompanyID', Integer, ForeignKey(Parent.ParentID))

    def __init__(self, ChildID, Description,ParentID):
        self.ChildID = ChildID
        self.Description = Description
        self.ParentID=ParentID

SQLAlchemy import tables with relationships

2 Answers2

Linked