How to create a relation between a SQLAlchemy model and a table with no model

Question

I am working on micro-services, the base service is in Django and other services are in FastAPI. All the services will share the same PostgreSQL database, the Employee model is in Django and and I want to establish a relation between the model Leave in FastApI with the Employee Model.

from sqlalchemy import Column, ForeignKey, Integer, JSON
from sqlalchemy.orm import relationship
from sqlalchemy.sql import table, column

existing_employee_table = table('employee', column('id', Integer), )


class Leave(Base):
    __tablename__ = "leaves"

    id = Column(Integer, primary_key=True)
    leave_count = Column(JSON, nullable=False)

    employee_id = Column(Integer, ForeignKey("employee.id"), nullable=False)
    employee = relationship("employee", back_populates="leaves")

Answer 1

The table needs to be in the same metadata instance as the model. Then the mapper can find its details to construct the relationship. You could declare it like this:

import sqlalchemy as sa
from sqlalchemy import orm

engine = sa.create_engine(...)

Base = orm.declarative_base()

# Reflect the table into the metadata instance that contains the model.
existing_employee_table = sa.Table('employee', Base.metadata, autoload_with=engine)

class Leave(Base):
    ...

Base.metadata.create_all(engine)