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]"

Your `counterparties` is a list of dictionaries, not a list of SQLAlchemy objects. SQLAlchemy has no idea how to store dicts. — MatsLindh, Jul 26 '22 at 15:09

score 4 · Answer 1 · edited Nov 06 '22 at 09:22

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.

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. — Tyler2P, Jul 27 '22 at 10:18

score 2 · Answer 2 · answered Nov 25 '22 at 08:22

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

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

2 Answers2