Flask SQLAlchemy 'dict' object has no attribute '_sa_instance_state'

Question

I am getting the following error when trying to create a new document and associated relationship with an array of counterparties.

AttributeError: 'dict' object has no attribute '_sa_instance_state'

I think the issue must exist with my model definition, if I remove "backref="documents" for the counterparties relationship I get the same error, but on the next line as it tries to add the document.

Database Model:

documents_counterparties = Table(
    "documents_counterparties",
    Base.metadata,
    Column("document_id", ForeignKey("documents.id"), primary_key=True),
    Column("counterparty_id", ForeignKey(
        "counterparties.id"), primary_key=True)
)


class Document(Base):
    __tablename__ = "documents"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    start_date = Column(Date)
    end_date = Column(Date)
    owner_id = Column(Integer, ForeignKey("users.id"))

    owner = relationship("User", back_populates="documents")

    counterparties = relationship(
        "Counterparty", secondary=documents_counterparties, backref="documents"
    )

Resolver:

def create_document(db: Session, document: DocumentCreate, user_id: int):
    db_document = models.Document(**document.dict(), owner_id=user_id) #<- errors here
    db.add(db_document)
    db.commit()
    db.refresh(db_document)
    return db_document

EDIT:

DocumentCreate

class DocumentBase(BaseModel):
    name: str
    start_date: datetime.date
    end_date: datetime.date


class DocumentCreate(DocumentBase):
    counterparties: "list[CounterpartyClean]"

Answer 1

An alternate way of handling one to many relationships...

# main.py

@app.post("/parent") # post parent that has children in the json
def create_parent(parent: schemas.Parent, db: Session = Depends(get_db)):
    return crud.create_parent(db, parent) # check the code block in crud.py below


@app.get("/parent")
def get_parent(parent_id: int, skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    parents = crud.get_parent(db=db, parent_id=parent_id, skip=skip, limit=limit) # returns [parent] list of len 1 if parent_id exists  
    for parent in parents: # skip this if you don't need a hydrated object back
        n_children = []
        for child in parent.children:
            n_children.append(child)
        parent.children = n_children # hydrate parent
    return parents # hydrated parents - includes children in response

Non atomic create operations

• Create parent first
• Create child with parent's id next

# crud.py

def create_parent(db: Session, parent: schemas.Parent):
    chidlren = parent.children
    parent.children = [] # set children to none
    # save just the parent (not children here)
    db_parent = models.Parent(**parent.dict())
    db.add(db_parent)
    db.commit()
    db.refresh(db_parent)

    for child in children:
        db_child = models.Children(**child.dict())
        db_child.parent_id = db_parent.id # add the foreign key here
        db.add(db_child)
        db.commit() # save child here
                                                                                   
    return db_parent

Hope this helps !!

Answer 2

I share an improved implementation of code from @Steven that I have used. Works well for any model that has Meta.orm_model defined. In case it doesn't, it additionally provides the information about the model that is missing the definition - much better than generic mapping failed.

def is_pydantic(obj: object):
    """ Checks whether an object is pydantic. """
    return type(obj).__class__.__name__ == "ModelMetaclass"


def model_to_entity(schema):
    """
        Iterates through pydantic schema and parses nested schemas
        to a dictionary containing SQLAlchemy models.
        Only works if nested schemas have specified the Meta.orm_model.
    """
    if is_pydantic(schema):
        try:
            converted_model = model_to_entity(dict(schema))
            return schema.Meta.orm_model(**converted_model)

        except AttributeError:
            model_name = schema.__class__.__name__
            raise AttributeError(f"Failed converting pydantic model: {model_name}.Meta.orm_model not specified.")

    elif isinstance(schema, list):
        return [model_to_entity(model) for model in schema]

    elif isinstance(schema, dict):
        for key, model in schema.items():
            schema[key] = model_to_entity(model)

    return schema

Answer 3

As @MatsLindh alluded to the issue is with types. The solution is here:

How to use nested pydantic models for sqlalchemy in a flexible way

Edit to include solution used:

Credit to Daan Beverdam:

I gave every nested pydantic model a Meta class containing the corresponding SQLAlchemy model. Like so:

from pydantic import BaseModel
from models import ChildDBModel, ParentDBModel

class ChildModel(BaseModel):
    some_attribute: str = 'value'
    class Meta:
        orm_model = ChildDBModel

class ParentModel(BaseModel):
    child: ChildModel

That allowed me to write a generic function that loops through the pydantic object and transforms submodels into SQLAlchemy models:

def is_pydantic(obj: object):
    """ Checks whether an object is pydantic. """
    return type(obj).__class__.__name__ == "ModelMetaclass"


def parse_pydantic_schema(schema):
    """
        Iterates through pydantic schema and parses nested schemas
        to a dictionary containing SQLAlchemy models.
        Only works if nested schemas have specified the Meta.orm_model.
    """
    parsed_schema = dict(schema)
    for key, value in parsed_schema.items():
        try:
            if isinstance(value, list) and len(value):
                if is_pydantic(value[0]):
                    parsed_schema[key] = [schema.Meta.orm_model(**schema.dict()) for schema in value]
            else:
                if is_pydantic(value):
                    parsed_schema[key] = value.Meta.orm_model(**value.dict())
        except AttributeError:
            raise AttributeError("Found nested Pydantic model but Meta.orm_model was not specified.")
    return parsed_schema

The parse_pydantic_schema function returns a dictionary representation of the pydantic model where submodels are substituted by the corresponding SQLAlchemy model specified in Meta.orm_model. You can use this return value to create the parent SQLAlchemy model in one go:

parsed_schema = parse_pydantic_schema(parent_model)  # parent_model is an instance of pydantic ParentModel 
new_db_model = ParentDBModel(**parsed_schema)
# do your db actions/commit here

If you want you can even extend this to also automatically create the parent model, but that requires you to also specify the Meta.orm_model for all pydantic models.