I’m getting a 505 error “detail”: “Failed to create review. Unexpected error: A transaction is already begun on this Session. This is happening after I correctly log in with username and password and then I try creating an entry for a table in db.

INFO:     Started server process [16658]
INFO:     Waiting for application startup.
INFO:     Application startup complete.
INFO:     127.0.0.1:59188 - "GET /docs HTTP/1.1" 200 OK
INFO:     127.0.0.1:59188 - "GET /openapi.json HTTP/1.1" 200 OK
INFO:     127.0.0.1:59189 - "POST /coffees-madrid/auth/token HTTP/1.1" 200 OK

This is how I’m getting the db:

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

I also think this is correct:

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Finally, this is my end point:

@router.post("/")
def create_review_for_coffeeshop(
    user: user_dependency, db: db_dependency, review_request: CoffeeReviewsRequest
):
    service = ReviewService(db)
    user_id = user.get("id")
    return service.create_review(review_request, user_id)

and the function that is being called:

    def create_review(self, review_request: CoffeeReviewsRequest, user_id: int):
        normalized_coffeeshop = self.normalize_name(review_request.coffeeshop)
        shop_id = self.get_shop_id(normalized_coffeeshop)
        
        # check if user has already written a review for that coffee shop:
        existing_review = (
            self.db.query(CoffeeReviews)
            .filter(CoffeeReviews.coffee_shop_id == shop_id)
            .filter(CoffeeReviews.user_id == user_id)
            .first()
        )
        if existing_review:
            raise HTTPException(
                status_code=status.HTTP_409_CONFLICT,
                detail="A review by the current user already exists. Please update it instead.",
            )

        # INSERT INTO reviews (col1, col2....) VALUES (val1, val2...)
        # exclude coffeeshop because the SQLAlchemy model doesnt have this field, only coffee shop id
        review = CoffeeReviews(
            **review_request.dict(exclude={"coffeeshop"}),
            coffee_shop_id=shop_id,
            user_id=user_id,
            timestamp=datetime.now().isoformat()
        )
        try: 
            with self.db.begin():
                self.db.add(review)
                self.db.refresh(review)

                # update aggregated reviews
                updated_agg_rating = self.update_aggregated_rating(shop_id, review.rating)
            return {
                "Review Created": review,
                "Updated aggregated rating": updated_agg_rating,
            }

        except IntegrityError as e:
        # Handle specific database errors (like unique constraint violation)
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"Failed to create review. Database integrity error: {str(e)}"
            )
        except Exception as e:
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail=f"Failed to create review. Unexpected error: {str(e)}"
            )

I am not sure how the session is not being closed correctly… especially after just two requests… thoughts? Maybe it is on the authentication side? this is the token function:

@router.post("/token", response_model=Token)
def login_for_access_token(
    db: db_dependency, form_data: Annotated[OAuth2PasswordRequestForm, Depends()]
):  # same as saying OAuth2PasswordRequestForm = Depends()
    user = authenticate_user(form_data.username, form_data.password, db)
    if not user:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Invalid credentials. User not found or authentication failed",
        )
    else:
        token = create_access_token(user.username, user.id, timedelta(minutes=20))
        return {
            "access_token": token,
            "token_type": "bearer",
        }  # successfully return token