Search code examples
sqlalchemypydantic

Optional id parameter in Pydantic model caused SQLAlchemy Session to build a query using two different ID parameters

Question

I am not sure where id_1 is coming from in the WHERE portion of this query:

[SQL: UPDATE posts_2 SET id=%(id)s, title=%(title)s, content=%(content)s, published=%(published)s WHERE posts_2.id = %(id_1)s]

Background

I have a model for a post:

class Post(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True, nullable=False)
    title = Column(String, nullable=False)
    content = Column(String, nullable=False)
    published = Column(Boolean, server_default="True", nullable=False)
    created_at = Column(TIMESTAMP(timezone=True),
                        server_default=text('now()'), nullable=False)

and I am trying to use an update method:

@app.put('/posts/{post_id}')
def update_post(post_id: int, post: Post, db: Session = Depends(get_db)):
    updated_post = db.query(models.Post).filter(models.Post.id == post_id)
    if updated_post == None:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND, detail="Post Not Found.")
    updated_post.update(post.dict()) # Update post based from postman (see body for postman below)
    db.commit()
    return {"message": updated_post.first()}

This is the Body from the Postman request:

{
    "title": "updated title 5", 
    "content": "new content 5",
    "published": false
}

However I get an Internal Server Error. When logging out I noticed that the updated_post query uses id to set the id and id_1 as for the WHERE statement:

[SQL: UPDATE posts_2 SET id=%(id)s, title=%(title)s, content=%(content)s, published=%(published)s WHERE posts_2.id = %(id_1)s]

and the parameters use and id and and id_1:

[parameters: {'id': None, 'title': 'updated title 5', 'content': 'new content 5', 'published': False, 'id_1': 5}]

This is what the table looks like in my data base (note there is no id_1): enter image description here

I would like the query to use id instead of id_1 but I am not sure why it is using id_1 to start with.

Thank you for your time and any information you can provide!

Solution

  • Okay writing it out was like rubber ducking and I figured out the issue. I was Pydantic Model where the id was optional:

    class Post(BaseModel):
    title: str
    content: str
    published: bool
    id: Optional[int]

    I think since I am setting the id by default through postgres, having it as an optional parameter on the Pydantic model was forcing another id param. After I commented it out:

    class Post(BaseModel):
    title: str
    content: str
    published: bool
    # id: Optional[int]

    the query works and the content gets update as expected. This was the SQL query that got logged out after the change:

    SELECT posts.id AS posts_id, posts.title AS posts_title, posts.content AS posts_content, posts.published AS posts_published, posts.created_at AS posts_created_at
FROM posts
WHERE posts.id = %(id_1)s