Equality SQLModels

[PhoenixNazarov]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

@pytest.mark.asyncio
async def test_find_by_id(entity, async_service_impl):
    e = await async_service_impl.find_by_id(-1)
    assert e is None

    e_saved = await async_service_impl.save(entity())
    assert e_saved.id is not None
    e_find = await async_service_impl.find_by_id(e_saved.id)
    assert e_find.dict() == e_saved.dict()  # True
    assert e_find == e_saved  # False

Description

When I manipulate with sqlalchemy:

model = (await session.execute(statement)).first()

sqlalchemy change value _sa_instance_state in model

And when I check on equal it call pydantic __eq__()

    def __eq__(self, other: Any) -> bool:
        if isinstance(other, BaseModel):
            # When comparing instances of generic types for equality, as long as all field values are equal,
            # only require their generic origin types to be equal, rather than exact type equality.
            # This prevents headaches like MyGeneric(x=1) != MyGeneric[Any](x=1).
            self_type = self.__pydantic_generic_metadata__['origin'] or self.__class__
            other_type = other.__pydantic_generic_metadata__['origin'] or other.__class__

            return (
                self_type == other_type
                and self.__dict__ == other.__dict__
                and self.__pydantic_private__ == other.__pydantic_private__
                and self.__pydantic_extra__ == other.__pydantic_extra__
            )
        else:
            return NotImplemented  # delegate to the other item in the comparison

Here are self.__dict__ and other.__dict__ contain different key _sa_instance_state, or if Model does not manipulate with SqlAlchemy this field may no exist.

Also I'm not sure about the __pydantic_private__ and __pydantic_extra__.

I think SQLModel need self method __eq__

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.14

Python Version

Python 3.9.6

Additional Context

No response

[KnightArthurRen]

+1 here, I think the issue is related to the object fetched from queries aren't properly initialized Pydantic object, more specifically here _pydantic_private__ and __pydantic_extra__ fields are not set (even though they are None in this content), which resulted in the exception here.

One thing I tried is to do to confirm this is have pydantic_entity = Entity(**query_entity.model_dump()) and then pydantic_entity is properly initialized with all the proper fields.

[volfpeter]

This is definitely a bug, here's a simple example that reproduces it:

from sqlmodel import Field, Session, SQLModel, create_engine


class Bug(SQLModel, table=True):
    __tablename__ = "bugs"

    code: str = Field(primary_key=True)


engine = create_engine("sqlite://")

SQLModel.metadata.create_all(engine)

bug_code = "pydantic-private"

with Session(engine) as session:
    session.add(Bug(code=bug_code))
    session.commit()

    bug_1 = session.get(Bug, bug_code)
    bug_2 = session.get(Bug, bug_code)

    print(f"{bug_1 == bug_2=}")

#> AttributeError: 'Bug' object has no attribute '__pydantic_private__'. Did you mean: '__pydantic_complete__'?

[PhoenixNazarov]

This works for me:

from typing import Any, Optional

from pydantic import BaseModel
from sqlmodel import SQLModel, Field


def __eq__(self, other: Any) -> bool:
    if isinstance(other, BaseModel):
        # When comparing instances of generic types for equality, as long as all field values are equal,
        # only require their generic origin types to be equal, rather than exact type equality.
        # This prevents headaches like MyGeneric(x=1) != MyGeneric[Any](x=1).
        self_type = self.__pydantic_generic_metadata__['origin'] or self.__class__
        other_type = other.__pydantic_generic_metadata__['origin'] or other.__class__

        dict1 = self.__dict__
        dict2 = other.__dict__

        if '_sa_instance_state' in dict1:
            dict1.pop('_sa_instance_state')
        if '_sa_instance_state' in dict2:
            dict2.pop('_sa_instance_state')

        return (
                self_type == other_type
                and dict1 == dict2
        )
        # and self.__pydantic_private__ == other.__pydantic_private__
        # and self.__pydantic_extra__ == other.__pydantic_extra__
    else:
        return NotImplemented  # delegate to the other item in the comparison


SQLModel.__eq__ = __eq__

[Freshpinguin]

This works, but will remove the "_sa_instance_state" field of your objects. So i suggest exchanging this

        dict1 = self.__dict__
        dict2 = other.__dict__

        if '_sa_instance_state' in dict1:
            dict1.pop('_sa_instance_state')
        if '_sa_instance_state' in dict2:
            dict2.pop('_sa_instance_state')

with something like:

          dict1 = {
              key: val
              for key, val in self.__dict__.items()
              if not key == "_sa_instance_state"
          }
          dict2 = {
              key: val
              for key, val in other.__dict__.items()
              if not key == "_sa_instance_state"
          }

[nwilson314]

More broadly, this problem has consequences outside of equality scenarios. We have run into this issue when attempting to pickle SQLModel objects after being pulled from our database. When pickling, the __getstate___ method is called which assumes the existence of both __pydantic_private__ and __pydantic_extra__.

On simple objects without any nested back references simply dumping and destructuring the dumped object back into the constructor works as @KnightArthurRen points out (pydantic_entity = Entity(**query_entity.model_dump()). However, if you are relying on nested back references later (as we are), you have to do this operation on every nested field. This is obviously not super tenable.

One solution, though I have no idea about the validity of this method, is to explicitly set both __pydantic_private__ and __pydantic_extra__ in the SQLModel definition for any class that needs their definition.

class TestClass(SQLModel):
  __pydantic_private__ = {}
  __pydantic_extra__ = {}
  ...

This works for our use case because we are not utilizing either of those fields, but this feels not good.

[nwilson314]

Coming back to this, I do want to note for anyone stumbling across this thread, that my solution does not solve the equality problem. Even if it does solve the problem with pickling.

[imankulov]

I came across the issue in SQLModel 0.0.14, but the issue seems to be solved by 0.0.16 (current latest), likely by this PR: #807