CODEWITHSUNDEEP
pythonsqlalchemy
Tony
Tony

Reputation: 2107

Issue with SqlAlchemy - "Parent instance <SomeClass> is not bound to a Session; lazy load operation..."

I have a small thrift server in python that I use do some fast lookups. The server queries mysql via SqlAlchemy on the first request and shoves all returned objects into a dictionary so on subsequent requests no DB call is needed. I just get the object from the dict and then call some of the object methods needed to give the proper response.

Initially, everything is fine. However, after the server runs a while, I am getting this exception when accessing the sqlalchemy object methods:

Parent instance is not bound to a Session; lazy load operation of attribute 'rate' cannot proceed.

Strange, because I set eagerload('rate').

I cannot really see a pattern to this behavior, it only affects some objects. However, once it does affect an object it will continue to do so on each request until I restart my python server.

Any ideas?

Upvotes: 10

Views: 11675

Answers (2)

GeM
GeM

Reputation: 97

I faced this issue with SQLModel and async session connect to database. I had Usermodel and Role model and i wanted to include role_details with user. Here you should pay attention to your models Relations and response_model. In my case i forgot this: sa_relationship_kwargs={"lazy": "joined"} Example:
models.py


# Role model
class Role(SQLModel, table=True):
    role: str = Field(sa_column=Column(String, unique=True, nullable=False))
    name_tk: str = Field(sa_type=String)
    name_ru: str = Field(sa_type=String)

    users: List["UserModel"] = Relationship(
        back_populates="role_detail",
    )

    class Config:
        orm_mode = True
        from_attributes = True

# User model
class User(SQLModel, table=True):
    name: str = Field(sa_type=String)
    username: str = Field(sa_column=Column(String, unique=True, nullable=False))
    password: str = Field(sa_type=String, nullable=False)
    role: str = Field(sa_column=Column(ForeignKey("roles.role", ondelete="CASCADE", onupdate="CASCADE")))
    role_detail: RoleModel | None = Relationship(
        back_populates="users",
        sa_relationship_kwargs={"lazy": "joined"}
    )

    class Config:
        orm_mode = True
        from_attributes = True

schema.py

class UserPublicWithRolePermissions(User):
    role_detail: Role | None = None

router.py


@router.get(
    "/me/{eager}",
    status_code=status.HTTP_200_OK,
    response_model=UserPublicWithRolePermissions
)
async def me(eager: bool):
        async with async_session as async_session:
            try:
                query = select(self.model).filter(self.model.id == id)
                if eager:
                    for eager in getattr(self.model, "eagers", []):
                        query = query.options(joinedload(getattr(self.model, eager))).subquery()
                result = await async_session.execute(query)
                instance = result.scalar_one_or_none()

                if not instance:
                    raise NotFoundError(detail=f"Not found id: {id}")
                return instance
            except HTTPException as err:
                raise InternalError(detail=str(err))

Upvotes: 0

thule
thule

Reputation: 4232

You probably cache objects between the requests, and when the commit happens, session object is getting cleared, invalidating your objects. If you start your server via some multithreaded web server that starts workers as needed, that explains why there's no pattern. If you dont want to get the bottom of this and just need a quick fix, this will always work: 

if obj not in session:
    obj = session.query(ObjClass).get(obj.id)

The proper solution would be to make sure you don't cache objects between requests.

Upvotes: 10

Related Questions