I had SQLalchemy error sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place?. I tried to fix it, and now it kind of works, but I’m interested in whether it works correctly?

An interesting thing (for me), data was changing, like I had error but in DB data changing

I just add one line code:

await db.refresh(review)

helped post – SQL Alchemy error: MissingGreenlet: greenlet_spawn has not been called

Seems like status is probably referenced during rendering the response but isn’t loaded during creation because just an id is passed.

This would seem to indicate that I am not getting user and reviews loaded during creation, but I am not entirely sure.

Can you please explain why it works? I’m new in this stuff so I want to understand how it’s work.

Below is my code

config.py:

import os
from dotenv import load_dotenv

from pathlib import Path

env_path = Path('.') / '.env'
load_dotenv(dotenv_path=env_path)


class Settings:
    DATABASE_URL = os.getenv('DB_LITE')
    SECRET_KEY = os.getenv('SECRET_KEY')
    ALGORITHM = os.getenv('ALGORITHM')


settings = Settings()


def get_auth_data():
    return {"secret_key": settings.SECRET_KEY, "algorithm": settings.ALGORITHM}

database.py:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession
from dotenv import load_dotenv

from backend.config import settings

load_dotenv()

SQLALCHEMY_DATABASE_URL = settings.DATABASE_URL

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

Base = declarative_base()


async def get_db():
    async with SessionLocal() as db:
        try:
            yield db
        finally:
            await db.close()

models.py:

from typing import List

from sqlalchemy import Column, String, Text, text, Integer, Boolean, ForeignKey, DateTime, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

from backend.src.database.database import Base


class User(Base):
    __tablename__ = 'users'

    id: Mapped[int] = mapped_column(primary_key=True, index=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String, nullable=False, unique=True, index=True)
    email: Mapped[str] = mapped_column(String, unique=True, nullable=False, index=True)
    password: Mapped[str] = mapped_column(String)

    is_user: Mapped[bool] = mapped_column(default=True, server_default=text("True"), nullable=False)
    is_admin: Mapped[bool] = mapped_column(default=False, server_default=text("False"), nullable=False)

    reviews: Mapped[List["Review"]] = relationship("Review", back_populates="user", lazy="selectin")


class Review(Base):
    __tablename__ = 'reviews'
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, index=True)
    created_by: Mapped[int] = mapped_column(ForeignKey("users.id"), nullable=False, index=True)
    reviewed_book_id: Mapped[int] = mapped_column(ForeignKey("books.id"), nullable=False, index=True)  # FK на Book

    reviewed_book_name: Mapped[str] = mapped_column(nullable=False, index=True)
    reviewed_book_author_name: Mapped[str] = mapped_column(nullable=False, index=True)

    review_title: Mapped[str] = mapped_column(String, nullable=True, index=True,)
    review_body: Mapped[str] = mapped_column(Text, nullable=False, index=True,)

    created: Mapped[DateTime] = mapped_column(DateTime, default=func.now())
    updated: Mapped[DateTime] = mapped_column(DateTime, default=func.now(), onupdate=func.now())

    user: Mapped["User"] = relationship("User", back_populates="reviews", lazy="selectin")


class Author(Base):
    __tablename__ = 'authors'
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, index=True)
    name: Mapped[str] = mapped_column(String, nullable=False, index=True)

    books: Mapped[List["Book"]] = relationship("Book", back_populates="author", lazy="selectin")


# TODO: Think about parse https://www.litres.ru/search/ for take book cover img
class Book(Base):
    __tablename__ = 'books'
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, index=True)
    book_name: Mapped[str] = mapped_column(String, nullable=False, index=True)
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"), nullable=False, index=True)
    book_description: Mapped[str] = mapped_column(Text, nullable=False, index=True)

    author: Mapped["Author"] = relationship("Author", back_populates="books", lazy="selectin")

shema.py:

from fastapi import Query
from pydantic import BaseModel, Field
from typing import Union


# User #
class User(BaseModel):
    name: Union[str, None] = Field(default=None, min_length=3, title="Имя пользователя")
    email: Union[str, None] = Field(default=None, title="Эл.почта пользователя")
    password: Union[str, None] = Field(default=None, min_length=4, title="Пароль пользователя")


# Review #
class Review(BaseModel):
    reviewed_book_name: Union[str] = Field(default=None, title="Название книги, на которую написан обзор")
    reviewed_book_author_name: Union[str] = Field(default=None, title="Имя автора книги, на которую написан обзор")

    review_title: Union[str] = Field(default=None, min_length=5, title="Заголовок обзора")
    review_body: Union[str] = Field(default=None, min_length=5, title="Обзор")


class FilteredReview(BaseModel):
    reviewed_book_name: Union[str, None] = Field(default=None, title="Название книги")
    reviewed_author_name: Union[str, None] = Field(default=None, title="Имя автора")


class ChangeReview(BaseModel):
    review_title: Union[str, None] = Field(default=None, min_length=5, title="Заголовок обзора")
    review_body: Union[str, None] = Field(default=None, min_length=5, title="Обзор")


# Author #
class Author(BaseModel):
    name: Union[str, None] = Field(default=None, title="Имя автора")


# Book #
class Book(BaseModel):
    book_name: Union[str, None] = Field(default=None, title="Название книги")
    author_id: Union[int, None] = Field(default=None, title="ID автора")
    book_description: Union[str, None] = Field(default=None, title="Описание книги")


# Token #
class Token(BaseModel):
    access_token: str
    token_type: str


class TokenData(BaseModel):
    email: Union[str, None] = None
    id: int

users_dao.py:

class UserDAO:
   # Some Code Here #
    @classmethod
    async def get_user_by_id(cls, db: AsyncSession, user_id: int):
        query = select(User).options(selectinload(User.reviews)).where(User.id == int(user_id))
        user = await db.execute(query)
        return user.scalars().first()
     

reviews_dao.py:

class ReviewDAO:
    # Some Code Here #

        @classmethod
    async def get_review_by_id(cls, db: AsyncSession, review_id: int):
        query = select(Review).options(selectinload(Review.user)).where(Review.id == int(review_id))
        review = await db.execute(query)
        return review.scalars().first()
    
    
    @classmethod
    async def change_review(cls, db: AsyncSession, review_id: int, data: dict):
        query = update(Review).where(Review.id == review_id).values(
            created_by=data["created_by"],
            reviewed_book_id=data["reviewed_book_id"],
            reviewed_book_name=data["reviewed_book_name"],
            reviewed_book_author_name=data["reviewed_book_author_name"],
            review_title=data["review_title"],
            review_body=data["review_body"]
        )

        await db.execute(query)
        await db.commit()

reviews_router.py:

@reviews_router.put("/change_review/{review_id}")
async def change_review(review_id: int,
                        new_review_title: str | None = None,
                        new_review_body: str | None = None,
                        db: AsyncSession = Depends(get_db),
                        token: str = Depends(get_token)):

    request = shema.ChangeReview(
        review_title=new_review_title,
        review_body=new_review_body)

    return await reviews_repository.change_review(review_id=int(review_id),
                                                  request=request,
                                                  db=db,
                                                  token=token)

reviews_repository.py:

async def change_review(review_id: int,
                        request: shema.ChangeReview,
                        db: AsyncSession = Depends(get_db),
                        token: str = Depends(get_token)):
    user_id = verify_token(token=token)
    user = await UserDAO.get_user_by_id(db=db, user_id=int(user_id))
    CheckHTTP404NotFound(founding_item=user, text="Пользователь не найден")

    review = await ReviewDAO.get_review_by_id(db=db, review_id=int(review_id))
    CheckHTTP404NotFound(founding_item=review, text="Обзор не найден")

    print("Review created by: ", review.created_by)
    print("User_id: ", user_id)

    if review.created_by != user.id:
        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="У Вас нет прав для изменения этого обзора")

    new_data = check_data_for_change_review(request=request, review=review)

    await ReviewDAO.change_review(db=db,
                                  review_id=review.id,
                                  data=new_data)

    # after I added this all works
    await db.refresh(review)

    return {
        'message': "Обзор обновлен успешно",
        'status_code': 200,
        'data': {
            'id': user_id,
            'Автор': review.reviewed_book_author_name,
            'Книга': review.reviewed_book_name,
            'Заголовок': new_data.get("review_title"),
            'Обзор': new_data.get("review_body")
        }
    }