• 22-Jan-2023
Lightrun Team
Author Lightrun Team
Share
This article is about fixing How to use pydantic and sqlalchemy models with relationship in tiangolo FastAPI

How to use pydantic and sqlalchemy models with relationship in tiangolo FastAPI

Lightrun Team
Lightrun Team
22-Jan-2023

Explanation of the problem

I am currently attempting to pass pydantic models to sqlalchemy models as specified in the sql-databases documentation. While this is successful for single models, it is failing when trying to handle relationships. I am expecting to receive an object that nests several other objects in my endpoint.

I am wondering if there is a way to accomplish this using the BaseModel.dict() method as outlined in the documentation, or if I need to map my pydantic model to my sqlalchemy model in order to achieve the desired outcome.

Below is an example of my current implementation, including the relevant imports and classes, as well as the specific endpoint that is causing issues:

from typing import Optional, List

from fastapi import Depends, FastAPI, HTTPException

from pydantic import BaseModel

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, sessionmaker, relationship
from sqlalchemy import ForeignKey, Column, Integer, String

Base = declarative_base()

class Parent(Base):
    __tablename__ = "parents"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), index=True)

    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = "children"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), index=True)
    parent_id = Column('parent_id', Integer(), ForeignKey('parents.id'), nullable=False)

    parent = relationship("Parent", back_populates="children")

class ChildSchema(BaseModel):
    id: Optional[int]
    name: str

    class Config:
        orm_mode = True

class ParentSchema(BaseModel):
    id: Optional[int]
    name: str

    children: List[ChildSchema] = []

    class Config:
        orm_mode = True

SQLALCHEMY_DATABASE_URL = "sqlite:///./test_app.db"
engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base.metadata.create_all(bind=engine)

api = FastAPI()

def get_db():
    try:
        db = SessionLocal()
        yield db
    finally:
        db.close()

@api.post("/parents/", response_model=ParentSchema)
def post_parent(parent: ParentSchema, db: Session = Depends(get_db)):
    db_parent = Parent(**parent.dict())
    db.add(db_parent)
    db.commit()
    db.refresh(db_parent)
    return ParentSchema.from_orm(db_parent)

In this example, the data1 request works as expected, but the data2 request raises an `AttributeError: ‘dict’ object has no attribute ‘_sa_instance_state’

Troubleshooting with the Lightrun Developer Observability Platform

Getting a sense of what’s actually happening inside a live application is a frustrating experience, one that relies mostly on querying and observing whatever logs were written during development.
Lightrun is a Developer Observability Platform, allowing developers to add telemetry to live applications in real-time, on-demand, and right from the IDE.

  • Instantly add logs to, set metrics in, and take snapshots of live applications
  • Insights delivered straight to your IDE or CLI
  • Works where you do: dev, QA, staging, CI/CD, and production

Start for free today

Problem solution for How to use pydantic and sqlalchemy models with relationship in tiangolo FastAPI

It appears that the current approach to passing pydantic models to sqlalchemy models is not handling relationships correctly. When trying to handle nested relationships, the jsonable_encoder method is not working properly and results in the error ‘attributeError: ‘dict’ object has no attribute ‘_sa_instance_state’’.

The root cause of this issue is that, when trying to convert the pydantic model to a dictionary using jsonable_encoder, the relationships defined in the pydantic model are lost, and the resulting dictionary is not suitable to be used to create the corresponding sqlalchemy object.

One solution to this issue is to use the ORM mode feature of Pydantic, which allows you to define the relationship fields in the pydantic model using the orm attribute and ForeignKey fields. When you pass the pydantic model to the ORM, Pydantic will automatically create the relationship and it will handle the nested relationships correctly.

Here’s an example of how you can use the ORM mode of Pydantic

from pydantic import BaseModel, OrmModel
from sqlalchemy import Column, Integer, String

class Parent(Base):
    __tablename__ = "parents"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), index=True)

    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = "children"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    parent_id = Column('parent_id', Integer(), ForeignKey('parents.id'), nullable=False)

    parent = relationship("Parent", back_populates="children")

class ChildSchema(OrmModel):
    id: Optional[int]
    name: str

    class Config:
        orm_mode = True

class ParentSchema(OrmModel):
    id: Optional[int]
    name: str
    children: List[ChildSchema] = None
    class Config:
        orm_mode = True

You can use the ParentSchema and ChildSchema to handle the nested relationships correctly, when you pass the ParentSchema to the ORM Pydantic will automatically create the relationship between the parent and children.

Other popular problems with FastAPI

Problem: Handling Nested Relationships when passing pydantic models to sqlalchemy models

A common issue when working with FastAPI is handling nested relationships when passing pydantic models to sqlalchemy models. The jsonable_encoder method, which is commonly used to convert the pydantic model to a dictionary, doesn’t handle relationships correctly and results in the error ‘attributeError: ‘dict’ object has no attribute ‘_sa_instance_state’’.

Solution:

One solution to this issue is to use the ORM mode feature of Pydantic, which allows you to define the relationship fields in the pydantic model using the orm attribute and ForeignKey fields.

from pydantic import BaseModel, OrmModel
from sqlalchemy import Column, Integer, String

class Parent(Base):
    __tablename__ = "parents"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), index=True)

    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = "children"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    parent_id = Column('parent_id', Integer(), ForeignKey('parents.id'), nullable=False)

    parent = relationship("Parent", back_populates="children")

class ChildSchema(OrmModel):
    id: Optional[int]
    name: str

    class Config:
        orm_mode = True

class ParentSchema(OrmModel):
    id: Optional[int]
    name: str
    children: List[ChildSchema] = None
    class Config:
        orm_mode = True

You can use the ParentSchema and ChildSchema to handle the nested relationships correctly, when you pass the ParentSchema to the ORM Pydantic will automatically create the relationship between the parent and children.

Problem: Handling File Uploads

Another common issue when working with FastAPI is handling file uploads. The default behavior of FastAPI is to receive files as bytes, but in some cases, you may need to receive files as file objects.

Solution:

One solution to this issue is to use the open function to open the file in binary mode and pass the file object to the endpoint.

from fastapi import FastAPI, File, UploadFile

app = FastAPI()

@app.post("/uploadfile/")
async def create_upload_file(file: bytes = File(...)):
    return {"filename": file.filename}

@app.post("/uploadfileobj/")
async def create_upload_file(file: UploadFile = File(...)):
    return {"filename": file.filename}

the first example will receive files as bytes and the second one will receive files as file objects.

Problem: Handling CORS

Another common issue when working with FastAPI is handling CORS(Cross-Origin Resource Sharing). By default, FastAPI does not handle CORS and you need to install an additional library or to add the headers manually.

Solution:

One solution to this issue is to use the FastAPI-CORS library that can be installed via pip and then use it to configure the CORS settings.

from fastapi import FastAPI
from fastapi_cors import FastAPICors

app = FastAPI()

cors = FastAPICors()
cors.add(
    app,
    routes=["/items/", "/items/{item_id}"],
    allow_origin="*",
    allow_methods=["*"],
    allow_headers=["*"],
)

This will add the necessary headers to handle CORS and allow requests from any origin, methods, and headers for the routes /items/ and /items/{item_id}.

A brief introduction to FastAPI

FastAPI is a modern, fast (high-performance), web framework for building APIs with Python 3.6+ based on standard Python type hints. It is built on top of Starlette for the web parts and Pydantic for the data parts. It is designed to be easy to use and to provide high performance by using async/await and asynchronous generators.

FastAPI also provides built-in support for OAuth2 authentication and OpenAPI documentation. It allows for the use of dependency injection and includes a convenient way to handle request and response data using Pydantic models. This allows for easy input validation, serialization and deserialization, and automatic documentation generation. Additionally, it supports many features such as WebSocket, GraphQL, background tasks, and more. It makes it easy to build robust and scalable APIs quickly and efficiently.

Most popular use cases for FastAPI

  1. Building RESTful APIs: FastAPI allows developers to easily build and expose RESTful APIs. With the use of Pydantic models, input validation, serialization and deserialization, and automatic documentation generation are made easy. The following code block illustrates a simple example of a RESTful API endpoint that accepts a request payload, validates it and returns a response:
    from fastapi import FastAPI, Body
    from pydantic import BaseModel
    
    app = FastAPI()
    
    class Item(BaseModel):
        name: str
        description: str
    
    @app.post("/items/")
    def create_item(item: Item = Body(..., embed=True)):
        return {"item": item}
    
  2. Real-time communication: FastAPI has built-in support for WebSockets, which allows for real-time communication between the server and the clients. This makes it possible to use FastAPI to build real-time applications such as chat systems, real-time notifications, and more.
  3. Handling large data sets: FastAPI allows developers to handle large data sets efficiently by using asynchronous generators. This allows for the streaming of large data sets without the need to load the entire dataset into memory. This feature is particularly useful when working with large files or when processing data streams.
Share

It’s Really not that Complicated.

You can actually understand what’s going on inside your live applications. It’s a registration form away.

Get Lightrun

Lets Talk!

Looking for more information about Lightrun and debugging?
We’d love to hear from you!
Drop us a line and we’ll get back to you shortly.

By submitting this form, I agree to Lightrun’s Privacy Policy and Terms of Use.