Search code examples
pythonpython-3.xsqlitesqlalchemyfastapi

Python SQLAlchemy Update Operation Yielded Unexpected Result

In Python(FastAPI) SQlAlchemy(sqlite) Update Operation Yielded Unexpected Result. here the code

# schemas.py
class User(BaseModel):
    uid: int
    num: int

# model.py
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    uid = Column(Integer, unique=True, index=True)
    num = Column(Integer)

# crud.py
def getdata(db: Session, user: int):
    return db.query(model.User).filter(model.User.uid == user).first()     # output: uid: 0, id: 1, num: 50

def updatedata(db: Session, user: schemas.User):                # output: user = uid: 0, id: 1, num: 10
    nuser = db.query(model.User).filter(model.User.uid == user.uid).one_or_none()   #output: uid: 0, id: 1, num: 10.     # expect output: uid: 0, id: 1, num: 50?
    nuser.num -= user.num            # output: nuser.num: 10, user.num: 10 = 0,
                                     # expect: 50-10=40
    db.add(nuser)
    db.commit()
    db.refresh(nuser)
    nuser1 = db.query(model.User).filter(model.User.uid == user.uid).one_or_none()  # output: uid: 0, id: 1, num: 0
    return nuser1 # output: uid: 0, id: 1, num: 0

# main.py
@app.post("/test/{user}")
def cal(user: int, db: Session = Depends(get_db)):
    userdata = crud.getdata(db, user=user)   # output: userdata = uid: 0, id: 1, num: 50
    userdata.num = 10                    # output: now userdata = uid: 0, id: 1, num: 10
    user = crud.updatedata(db, userdata)
    return user # output: uid: 0, id: 1, num: 0

if in main.py crud.getdata(db, user=user) not called and manually provide the value when req then nuser = db.query(model.User).filter(model.User.uid == user.uid).one_or_none() working as expected.

so the main problem is userdata.balance set to 10 or any value, nuser also set the same value rather then get the data from db.

is this expected behavior? or problem in my code? can someone please explain what is problem here.

I am newbie in python and stuff also not good in english so sorry if there any mistakes. Thanks

Solution

  • The problem is that both user and nuser in updatedata() are the same object because the are both fetched into the same sqlalchemy Session using the same uid. When you query an object using db.query it loads it into a session until you commit the changes.

    You can see some explanation of this behavior when the docs talk about overriding it with: populate-existing Specifically this:

    Normally, ORM objects are only loaded once, and if they are matched up to the primary key in a subsequent result row, the row is not applied to the object. This is both to preserve pending, unflushed changes on the object as well as to avoid the overhead and complexity of refreshing data which is already there. The Session assumes a default working model of a highly isolated transaction, and to the degree that data is expected to change within the transaction outside of the local changes being made, those use cases would be handled using explicit steps such as this method.

    I'm not sure what you intended to actual do here or if you were just testing but here are some other suggestions to improve readability:

    • suffix identifiers so they are different than the objects themselves
      • for example rename user in cal() to user_uid and in crud.py:getdata() rename user to user_uid
    • avoid using two classes with the exact same name
      • ie. maybe leave model.py:User but rename schemas.py:User to schemas.py:UserSchema.
    • avoid generic names like updatedata()/getdata() because it isn't easy to tell what is going on
      • Try using get_user/update_user
    # schemas.py
class UserSchema(BaseModel):
    uid: int
    num: int

# model.py
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    uid = Column(Integer, unique=True, index=True)
    num = Column(Integer)

# crud.py
def get_user(db: Session, user_uid: int):
    return db.query(model.User).filter(model.User.uid == user_uid).first()     

def update_user(db: Session, user_schema: UserSchema):
    # !!!! You are using a `user` as `user_schema`, it isn't a schema at all.
    user = db.query(model.User).filter(model.User.uid == user_schema.uid).one_or_none()
    user.num -= user_schema.num 

    # You don't need to add it unless it is new
    #db.add(user)
    db.commit()
    db.refresh(nuser)
    # nuser1 is the same as user (was nuser)
    # nuser1 = db.query(model.User).filter(model.User.uid == user.uid).one_or_none()
    return user

# main.py
@app.post("/test/{user_uid}")
def cal(user_uid: int, db: Session = Depends(get_db)):
    user = crud.get_user(db, user_uid=user_uid)
    user.num = 10
    # !!! Right here you pass a user object from the database in as if
    # it is a schema when it is not.
    user = crud.update_user(db, user)
    return user
    Schemas/Validation

    As far as I understand it validation does not occur automatically unless you set up things to be validated like in the examples below. A type checker such as mypy might catch the you pass a User in where a UserSchema is expected and that would trigger an error you could detect.

    Examples of validation when entering application: