Creating Relationships in vanilla SQLAlchemy-CodePudding

I recently started using SQLAlchemy for a database application I'm working on. Things were going fine until I required relationships. I have looked at the documentation and found out what to do. However, it only works when my models are in the same .py file. That is a bit messy to me so I tried to separate them out into their own model_name.py files. eg:

frame_model.py:

from __future__ import annotations

from .db_connector import master_session, db_engine
from sqlalchemy import Column, String, Integer, Date, Computed, ForeignKey, cast, Index
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import TSVECTOR
from sqlalchemy.types import TypeDecorator
from datetime import datetime

# Create ts_vector type.
class TSVector(TypeDecorator):
    impl = TSVECTOR
    cache_ok = True


base = declarative_base()


class FrameModel(base):
    __tablename__ = "inventory"

    frame_id: int = Column(Integer, primary_key=True, autoincrement=True)
    frame_name: str = Column(String(140), nullable=False)
    quantity: int = Column(Integer, nullable=False)
    frame_size: str = Column(String(45), nullable=False)
    colour: str = Column(String(100), nullable=False)
    price: int = Column(Integer, nullable=False)
    frame_category: str = Column(
        String(100),
        ForeignKey("categories.category", ondelete="CASCADE"),
        nullable=False,
    )
    date_entered: Date = Column(
        Date, default=datetime.today().strftime("%Y-%m-%d"), nullable=False
    )
    frame_code: str = Column(String(50), nullable=False)
    __ts_vector__ = Column(
        TSVector(),
        Computed(
            "to_tsvector('english', frame_name || ' ' || frame_size || ' ' || colour || ' ' || frame_category || ' ' || frame_code )",
            persisted=True,
        ),
    )

    sale = relationship("SaleModel")

    __table_args__ = (Index("idx_frame_fts", __ts_vector__, postgresql_using="gin"),)

    def __repr__(self) -> str:
        return f"<{self.__class__.__name__}: {self.frame_name}, Category: {self.frame_category}>"

    def save_to_db(self):
        master_session.add(self)
        master_session.commit()

    def delete_from_db(self):
        master_session.delete(self)
        master_session.commit()

base.metadata.create_all(db_engine)

sale_model.py:

from __future__ import annotations

from sqlalchemy.orm import relationship

from .db_connector import db_engine, master_session

from sqlalchemy import (
    Column,
    String,
    Integer,
    Date,
    UniqueConstraint,
    ForeignKey,
    Computed,
    cast,
    Index,
    UniqueConstraint,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import TSVECTOR
from sqlalchemy.types import TypeDecorator
from datetime import datetime


class TSVector(TypeDecorator):
    impl = TSVECTOR
    cache_ok = True


base = declarative_base()


class SaleModel(base):
    __tablename__ = "sales"

    order_id = Column(Integer, primary_key=True, autoincrement=True)
    frame = Column(String(100), nullable=False)
    quantity_sold = Column(Integer, nullable=False)
    customer = Column(String(200), nullable=False)
    receipt_num = Column(String(50), nullable=False)
    frame_id = Column(Integer, ForeignKey("inventory.frame_id", ondelete="SET NULL"))
    frame_code = Column(String(50), nullable=False)
    item_price = Column(Integer, nullable=False)
    category = Column(String(100), nullable=False)
    colour = Column(String(100), nullable=False)
    size = Column(String(45), nullable=False)
    date_sold = Column(
        Date, default=datetime.today().strftime("%Y-%m-%d"), nullable=False
    )
    __ts_vector__ = Column(
        TSVector(),
        Computed(
            "to_tsvector('english', frame || ' ' || frame_code || ' ' || receipt_num || ' ' || customer)",
            persisted=True,
        ),
    )

    frame = relationship("FrameModel")

    __table_args__ = (
        Index("sale_idx", __ts_vector__, postgresql_using="gin"),
        UniqueConstraint("receipt_num", name="_receipt_num_uc"),
    )

    def __repr__(self) -> str:
        return f"<Receipt number: {self.receipt_num}, customer: {self.customer}, frame: {self.frame}>"

    # Add and update the sale
    def save_to_db(self):
        master_session.add(self)
        master_session.commit()

    # Delete the sale
    def delete_from_db(self):
        master_session.delete(self)
        master_session.commit()

base.metadata.create_all(db_engine)

But this keeps giving me the error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'sales.frame_id' could not find table 'inventory' with which to generate a foreign key to target column 'frame_id'

I've tried everything from importing the parent class into the child class and creating a relation referencing the child in the parent class (sale = relationship("sale", backref="inventory) but nothing has worked thus far. Any guidance would be appreciated.

NB: the .py files are in the folder called "models" which itself has an init.py file that is blank.

CodePudding user response：

Thanks to @snakecharmerb's advice, and a bit of extra work I was able to resolve the issue.

In sale_model.py I imported the FrameModel class and added the line:

frame = relationship("FrameModel", back_populates="sale")