I have this code for delete operation on a Postgresql DB:
@app.delete("/posts/{id}", status_code=status.HTTP_204_NO_CONTENT) def delete_post(id: int): print("ID IS ",id) cursor.execute("""DELETE FROM public."Posts" WHERE id = %s""", (str(id),)) deleted_post = cursor.fetchone() <--- Showing error for this line conn.commit() if deleted_post is None: raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail=f"Post with {id} not found") return Response(status_code=status.HTTP_204_NO_CONTENT)
The create and read operations work fine. If I pass an existing or a non-exsiting id to delete, I get a 500 Internal Server error. The row does get deleted from the table though.
If I comment this line deleted_post = cursor.fetchone()
, it works okay.
Here is the error traceback:
File "D:Python ProjectsFASTAPIvenvlibsite-packagesanyioto_thread.py", line 31, in run_sync return await get_asynclib().run_sync_in_worker_thread( File "D:Python ProjectsFASTAPIvenvlibsite-packagesanyio_backends_asyncio.py", line 937, in run_sync_in_worker_thread return await future File "D:Python ProjectsFASTAPIvenvlibsite-packagesanyio_backends_asyncio.py", line 867, in run result = context.run(func, *args) File "D:Python ProjectsFASTAPI.appmain.py", line 80, in delete_post deleted_post = cursor.fetchone() File "D:Python ProjectsFASTAPIvenvlibsite-packagespsycopg2extras.py", line 86, in fetchone res = super().fetchone() psycopg2.ProgrammingError: no results to fetch
What really is happening here??
Advertisement
Answer
The DELETE
query does not return any results, thus the fetchone()
call raises an error. Try adding a RETURNING
clause:
@app.delete("/posts/{id}", status_code=status.HTTP_204_NO_CONTENT) def delete_post(id: int): print("ID IS ",id) cursor.execute("""DELETE FROM public."Posts" WHERE id = %s RETURNING id""", (str(id),)) deleted_post = cursor.fetchone() <--- Showing error for this line conn.commit() if deleted_post is None: raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail=f"Post with {id} not found") return Response(status_code=status.HTTP_204_NO_CONTENT)