[Solved] SQLAlchemy Table Relationships

Python Help
1

Hello everyone. I’m struggling a bit with using SQLAlchemy to interface with a read-only Microsoft SQL server. Any help would be appreciated, thank you in advance.

Summary:
There are three tables, represented by DeclarativeBase inherited classes (MBOM, PPDBOM, Part). Two of the tables have columns that I want to build a direct relationship to (MBOM, PPDBOM) the same column in the third table (Part).

MBOM.Child => Part.PartNumber
PPDBOM.Child => Part.PartNumber

Below is the python showing successful relationship mapping of only the MBOM.Child to Part.PartNumber

class Base(DeclarativeBase):
    __abstract__ = True
    __allow_unmapped__ = True
    LogicalId:   Mapped[str] = mapped_column(primary_key=True)

class MBOM(Base):
    __tablename__ = 'MBOM'
    Parent: Mapped[str] = mapped_column()
    Child: Mapped[str] = mapped_column()
    BOMFeature: Mapped[str] = mapped_column()
    Part: Mapped['Part'] = relationship()

class PPDBOM(Base):
    __tablename__ = 'PPDBOM'
    Parent: Mapped[str] = mapped_column()
    Child: Mapped[str] = mapped_column()

class Part(Base):
    __tablename__ = 'PartMaster'
    PartNumber: Mapped[str] = mapped_column(ForeignKey('MBOM.Child')) 
    PartType: Mapped[str] = mapped_column()

engine = create_engine(connection_string)
Session = sessionmaker(bind=engine)
session = Session()

ret = session.query(MBOM).where(and_(MBOM.Parent=="5326010", MBOM.BOMFeature=='1')).first()
print(ret.Part.PartType)

The target is to be able to drill down to the associative part from MBOM.Part and PPDBOM.Part, using both PPDBOM and MBOM Child column to connect with Part.PartNumber.

Are association tables my best bet here? I’ve tried both association table approach and foreign_keys approach without much success. Any pointers would be helpful. If association tables are the way to go, I’ll post up my attempt at implementing them here.

I’m trying to avoid query time joins via the session for this and think it would be slick to just have these joined relationships just built into the tables so I can reference by MBOM.Part and PPDBOM.Part.

Thank you for your input and patience. Please note, this is a preexisting, read-only database that I’m working with.

2

I think I cracked the case, coming back to this fresh this morning! I was able to get the joins working the way I was hoping (via relationships vs chaining .join(s) in my session queries).

Instead of using the ForeignKey in the column, to drive the connection, I pushed this to a primary join in the relationship of the table wanting to link with. Makes sense now that I have it squared! Hopefully this will be a helpful reference for others.

class Base(DeclarativeBase):
    __abstract__ = True
    __allow_unmapped__ = True
    LogicalId:   Mapped[str] = mapped_column(primary_key=True)

class Part(Base):
    __tablename__ = 'PartMaster'
    PartNumber: Mapped[str] = mapped_column() # Maps to Child in both PPDBOM and MBOM
    PartType: Mapped[str] = mapped_column()

class MBOM(Base):
    __tablename__ = 'MBOM'
    Parent: Mapped[str] = mapped_column()
    Child: Mapped[str] = mapped_column()
    BOMFeature: Mapped[str] = mapped_column()
    Part: Mapped['Part'] = relationship('Part', foreign_keys='Part.PartNumber', primaryjoin=("MBOM.Child==Part.PartNumber"),  viewonly=True)

class PPDBOM(Base):
    __tablename__ = 'PPDBOM'
    Parent: Mapped[str] = mapped_column()
    Child: Mapped[str] = mapped_column()
    Part: Mapped['Part'] = relationship('Part', foreign_keys='Part.PartNumber', primaryjoin=("PPDBOM.Child==Part.PartNumber"), viewonly=True)