FastAPI / SQL MODEL / Create Multiple INSERTS

Question

I am working in an API with FastAPI and SQLModel and like database PostgreSQL and I want to insert data like a bulk and I want to use the method bulk_save_objects but I got the following error message:

File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3620, in bulk_save_objects
for (mapper, isupdate), states in itertools.groupby(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3605, in
obj_states = (attributes.instance_state(obj) for obj in objects)
AttributeError: 'DialplanCreate' object has no attribute '_sa_instance_state'

I have the following model:

class DialplanBase(SQLModel):
    country: str
    site: str
    landline: str
    extension: int


class Dialplan(DialplanBase, table=True):
    __tablename__ = "t_admin_dialplan"
    __table_args__ = (UniqueConstraint("landline"), UniqueConstraint("extension"),)

    pkid: Optional[uuid.UUID] = Field(primary_key=True, nullable=False, default_factory=uuid.uuid4)


class DialplanCreate(DialplanBase):
    pass

and the next endpoint:

@router.post("/dp/batch")
def createbulk_dp(*, session: Session = Depends(get_session), dpbatch: List[DialplanCreate]):
    session.bulk_save_objects(dpbatch)
    session.commit()
    return {"ok": True}

I have read that can be related with that the pkid field (primary key) is not create yet but I don't have idea how solve this problem. The workaround is insert the data one by one, but is not good from point of view performance:

@router.post("/dp/batch")
def createbulk_dp(*, session: Session = Depends(get_session), dpbatch: List[DialplanCreate]):
    for dp in dpbatch:
        db_dp = Dialplan.from_orm(dp)
        session.add(db_dp)
        session.commit()
        session.refresh(db_dp)
    return {"ok": True}

I'm starting a new project and like to use FastAPI/SQLModel as well, did you find other issues? Would you suggest to use SQLModel or SQLAlchemy? — Paolo, Jun 23 '22 at 00:44

OlafdeL · Answer 1 · 2022-08-31T11:14:27.970

I'm currently having this issue too and by reading the documentation thoroughly it makes a bit sense to me to a certain level.

I think it all comes down to this part: https://sqlmodel.tiangolo.com/tutorial/fastapi/multiple-models/#the-herocreate-data-model

Your DialPlanCreate model is not a SqlModel but only a Pydantic model. I assume that is the reason Sqlalchemy throws this error. I have the same setup in my own project and I tried to use session.bulk_insert_mappings with the actual Sqlmodel, which is in your case DialPlan. Then this works perfectly fine.

What I did is the following:

@router.post("/dp/batch")
def createbulk_dp(*, session: Session = Depends(get_session), dpbatch: List[DialplanCreate]):
    dp_objects = [dp_object.dict() for dp_object in dpbatch]
    session.bulk_insert_mappings(DialPlan, dp_objects)
    session.commit()

Like that, you have the benefits of the Pydantic model DialPlanCreate in your input schema and you can use the bulk_insert_mappings on the actual database model.

FastAPI / SQL MODEL / Create Multiple INSERTS

1 Answers1