👀 Views: 0 💬 Answers: 1 📅 Created: 2025-06-13

sqlalchemy fastapi orm Python

I'm dealing with I'm optimizing some code but I'm upgrading from an older version and I'm running into issues when trying to set up a many-to-many relationship between two models in FastAPI using SQLAlchemy... I have two models, `Author` and `Book`, where an `Author` can write multiple `Books`, and a `Book` can have multiple `Authors`. I want to eagerly load related `Books` when querying for an `Author`, but I'm working with the following behavior: `sqlalchemy.exc.InvalidRequestError: When eager-loading, 'Book.authors' is not present in the 'FROM' clause`. I've defined my models like this: ```python from sqlalchemy import Column, Integer, String, Table, ForeignKey from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() association_table = Table('association', Base.metadata, Column('author_id', Integer, ForeignKey('authors.id')), Column('book_id', Integer, ForeignKey('books.id')), ) class Author(Base): __tablename__ = 'authors' id = Column(Integer, primary_key=True) name = Column(String) books = relationship('Book', secondary=association_table, back_populates='authors') class Book(Base): __tablename__ = 'books' id = Column(Integer, primary_key=True) title = Column(String) authors = relationship('Author', secondary=association_table, back_populates='books') ``` When I try to query for an author with eager loading like this: ```python from sqlalchemy.orm import Session, selectinload def get_author_with_books(author_id: int, db: Session): return db.query(Author).options(selectinload(Author.books)).filter(Author.id == author_id).first() ``` I get the mentioned behavior. I've verified that the relationship and the association table are set up correctly. I even tried using `join` instead of `options`, but I received the same behavior. Any advice on how to resolve this or what I might be missing in my setup? I'm using FastAPI version 0.68.0 and SQLAlchemy version 1.4.23. I'd really appreciate any guidance on this. What's the best practice here? Am I approaching this the right way? Hoping someone can shed some light on this. Any feedback is welcome!