Insert object with Many-to-many relatioship via sqlalchemy

[micoloth]

Hi! I'm trying to understand if fastapi_crudrouter supports inserting object with nested fields that represent many-to-many relatioships, via the Sqlalchemy ORM.

This is the code I have:

from sqlalchemy import create_engine, Column, Integer, String, Table, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, joinedload

# Make the engine
engine = create_engine("sqlite+pysqlite:///:memory:", future=True, echo=True,
                       connect_args={"check_same_thread": False})

# Make the DeclarativeMeta
Base = declarative_base()

# ------------------------------- SqlAlchemy classes with many to many relationship----------------------------


# Declare Classes / Tables
book_authors = Table('book_authors', Base.metadata,
    Column('book_id', ForeignKey('books.id'), primary_key=True),
    Column('author_id', ForeignKey('authors.id'), primary_key=True)
)



class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    title = Column(String, nullable=False)
    authors = relationship("Author", secondary="book_authors", back_populates='books')

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    books = relationship("Book", secondary="book_authors", back_populates='authors')

# Create the tables in the database
Base.metadata.create_all(engine)

# ------------------------------- Pydantic classes ----------------------------

from typing import List
from pydantic import BaseModel

class AuthorBase(BaseModel):
    id: int
    name: str

    class Config:
        orm_mode = True

class BookBase(BaseModel):
    id: int
    title: str

    class Config:
        orm_mode = True

class BookSchema(BookBase):
    authors: List[AuthorBase]

class AuthorSchema(AuthorBase):
    books: List[BookBase]


from fastapi import FastAPI, Depends


# ------------------------------- Connect to DB ----------------------------

app = FastAPI(title="Bookipedia")

def get_db():
    db = Session(bind=engine)
    try:
        yield db
    finally:
        db.close()


# ------------------------------- Routers ----------------------------


from fastapi_crudrouter import SQLAlchemyCRUDRouter


routers = [SQLAlchemyCRUDRouter(schema=AuthorSchema, create_schema=AuthorSchema, db_model=Author, db=get_db),
          SQLAlchemyCRUDRouter(schema=BookSchema, create_schema=BookSchema, db_model=Book, db=get_db)]


app.include_router(routers[0])
app.include_router(routers[1])



import uvicorn
if __name__ == "__main__":
  uvicorn.run('app:app', port=8080, reload=True, debug=True)


# Go to: http://127.0.0.1:8000/docs

But if one goes to http://127.0.0.1:8000/docs and then to POST/ Books, in the "Try it out" feature, and adds this set of data:

{
  "id": 0,
  "title": "moby_dick",
  "authors": [
    {
      "id": 0,
      "name": "MELLEVILLE"
    }
  ]
}

It doesn't work, it returns this error:

File "C:\Python310\lib\site-packages\sqlalchemy\orm\attributes.py", line 1765, in emit_backref_from_collection_append_event
    child_state, child_dict = instance_state(child), instance_dict(child)
AttributeError: 'dict' object has no attribute '_sa_instance_state'

I wanted to understand if I'm doing something wrong, or this use case is not supported by fastapi_crudrouter.

Thanks!

[nuno-andre]

Adding another example with SQLModel (which subclasses Pydantic's BaseModel and SQLAlchemy's DeclarativeMeta avoiding the need to declare model and schema separately).

The above example would look something like this. The tables are set up properly, but the API doesn't show neither authors nor books fields.

from sqlmodel import SQLModel, Field, Relationship

# link table
class BookAuthor(SQLModel, table=True):
    book:   int = Field(primary_key=True, foreign_key='book.id')
    author: int = Field(primary_key=True, foreign_key='author.id')

class Book(SQLModel, table=True):
    id:      int = Field(primary_key=True)
    title:   str
    authors: list['Author'] = Relationship(
        back_populates='books',
        link_model=BookAuthor,
    )
 
class Author(SQLModel, table=True):
    id:    int = Field(primary_key=True)
    name:  str
    books: list[Book] = Relationship(
        back_populates='authors',
        link_model=BookAuthor,
    )

[miguelebf]

Hi, i have the same issue, did you find any solution?