Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Could not refresh instance #536

Open
8 tasks done
AshikurRahman-sec opened this issue Jan 25, 2023 · 10 comments
Open
8 tasks done

Could not refresh instance #536

AshikurRahman-sec opened this issue Jan 25, 2023 · 10 comments
Labels
question Further information is requested

Comments

@AshikurRahman-sec
Copy link

First Check

  • I added a very descriptive title to this issue.
  • I used the GitHub search to find a similar issue 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

from typing import Optional  
from sqlmodel import Field, SQLModel

class Server(SQLModel, table=True):  
    id: Optional[int] = Field(default=None, primary_key=True)  
    ip: str  
    health_status: Optional[str] = None 
    cpu_cores: Optional[int] = None

@router.post("/add")
def add_server(item:Server):
    return server_action.add_server(item)

def add_server(item): 
    with Session(engine) as session:  
        session.add(item) 
        session.commit()
        session.refresh(item) # could not refresh instance
        return item

Description

In sql model documentation I saw an example of session.refresh() but when I am trying to implement this in my fastapi project this not working and raise exception that is could not refresh instance and also I am trying to print item.id, this is not also working, raise exception that is expired.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.10.6

Additional Context

raise sa_exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: Could not refresh instance '<Server at 0x7f69b549f500>'

@AshikurRahman-sec AshikurRahman-sec added the question Further information is requested label Jan 25, 2023
@AshikurRahman-sec
Copy link
Author

database is mysql

@JeseYe
Copy link

JeseYe commented Feb 2, 2023

del session.refresh(item)
add session.flush()
maybe work

@AshikurRahman-sec
Copy link
Author

@JeseYe thanks but I want to know why session.refresh not refresh item.

@fishfacegit
Copy link

fishfacegit commented Apr 3, 2023

You cannot refresh the item because there is no database instance connected with it.
To do that you need to retrieve the item from the database using an select statement.
You can use refresh in the following:

  1. retrieve a instance from the database hero = session.get(Hero, {'name': 'Hans'}) or a select statement
  2. change attributes hero.age=42
  3. add to session session.add(hero)
  4. write changes session.commit()
  5. refresh the instance collected from 1. session.refresh(hero)

@starryknight64
Copy link

I'm running into this problem myself as well. Pardon my ignorance, but shouldn't SQLModel do this for us? Or is this just something that must be done when using MySQL (assuming this isn't a problem for other DB types)?

@starryknight64
Copy link

I discovered a solution for this:

As it turns out, the object I was passing to the REST endpoint had an ID specified (the ID was 0). When I either don't specify an ID or set it to None prior to adding/committing/refreshing then everything works as desired.

def add_server(item): 
    with Session(engine) as session:
        if item.id == 0:
            item.id = None
        session.add(item)
        session.commit()
        session.refresh(item) # now refreshes properly!
        return item

@starryknight64
Copy link

Although... again... shouldn't SQLModel (or FastAPI?) determine that since default=None is specified that it pass id=None inside the /docs endpoint? Right now, it passes id=0 for the primary key when I assumed it would've passed id=None...

    id: Optional[int] = Field(default=None, primary_key=True)

@thebirdgr
Copy link

Depends on what the default value in the Item schema is, whether 0 or None. However, we shouldn't be having to do this. Using an if condition to check for an id that is passed from a request seems like hacky fix to this problem. If the id is set as the primary key in the models it should be auto incremented by the orm.

@makisukurisu
Copy link

makisukurisu commented Mar 19, 2024

I also have this (or, rather, a similar) issue. But in my case I do have both defaults that are not none, and a record with the same primary key in DB.

Code that doesn't work (right now at least)

Models

import uuid

import pydantic
from sqlmodel import Field, Relationship, SQLModel

from .faculty import FacultyModel

class CreateDepartment(SQLModel):
    name: str = Field(unique=True, index=True)
    description: str | None = Field(default=None, nullable=True)


class DepartmentRepresentation(BaseDepartment):
    department_id: pydantic.UUID4
    faculties: list[FacultyModel]


class DepartmentModel(BaseDepartment, table=True):
    """
    Describes a department in an educational institution.
    Highest level of organization in an educational institution.

    Departments have a list of associated faculties.
    """

    department_id: pydantic.UUID4 = Field(
        default_factory=uuid.uuid4,
        primary_key=True,
        index=True,
    )

    faculties: list[FacultyModel] = Relationship(back_populates="department")

Endpoint

Code sample:

@admin_router.put(
    "/department/{department_id}",
    description="Update a department",
)
async def update_department(
    db: Annotated[database_session_type, Depends(database_session)],
    department_id: str,
    department: Annotated[CreateDepartment, Body()],
    _: Annotated[DepartmentModel, Depends(department_exists)],
) -> DepartmentRepresentation:
    department_obj = DepartmentModel.model_validate(department)
    department_obj.department_id = department_id
    return update_entity(
        db=db,
        entity=department_obj,
    )

Basically - it takes in the model for a department, and spits out it's representation from the database by performing an update.
I'm using model validation to convert input to the DB model, and set the foreign key to the one specified in path.
department_exists just verifies that specified department_id is valid and is present in the DB

Update call

from sqlalchemy.exc import IntegrityError, InvalidRequestError
from sqlmodel import Session, SQLModel

from samurai_backend.errors import SamuraiIntegrityError

...

def update_entity(
    db: Session,
    entity: SQLModel,
) -> SQLModel:
    try:
        db.add(entity)
        db.commit()
        db.refresh(entity)

        return entity
    except (IntegrityError, InvalidRequestError) as e:
        db.rollback()
        raise SamuraiIntegrityError from e

Error

I get exception InvalidRequestError that looks like this:

(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "departmentmodel_pkey"\nDETAIL:  Key (department_id)=(1523095d-3d9d-46a7-bed5-2e19d3314712) already exists.\n\n[SQL: INSERT INTO departmentmodel (name, description, department_id)...

There's a lengthy INSERT SQL query, which is performed in db.commit() line. Which is expected, I guess, but not helpful in this case.

Code that does work (right now)

Update the update_entity and call to it by adding primary_key parameter. In my case it's department_id, and the method now looks like this:

from sqlalchemy.exc import IntegrityError, InvalidRequestError
from sqlmodel import Session, SQLModel, update

from samurai_backend.errors import SamuraiIntegrityError

...

def update_entity(
    db: Session,
    entity: SQLModel,
    primary_key: str,
) -> SQLModel:
    try:
        entity_class = entity.__class__
        update_query = (
            update(entity_class)
            .where(getattr(entity_class, primary_key) == getattr(entity, primary_key))
            .values(**entity.model_dump(exclude={primary_key}))
        )
        db.exec(update_query)
        db.commit()

        return entity
    except (IntegrityError, InvalidRequestError) as e:
        db.rollback()
        raise SamuraiIntegrityError from e

Right now I see this as my only solution, but if you have different ideas, please, let me (or, rather - us) know.

P.S. Just using refresh won't do, since then you will get an error that this object is not in registry yet.

Thanks!

Additional info

I'm using sqlmodel 0.0.16, the latest version at the moment of writing this comment.

@AbirAbbas
Copy link

had a similar issue, setting auto_increment=True on primary_key id solved it

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
question Further information is requested
Projects
None yet
Development

No branches or pull requests

7 participants