query error

[downdawn]

Trying to use the table structure of fastapi_sqlalchemy_mysql-async-Plus, an exception occurs when querying user information

sqlalchemy.exc.StatementError: (sqlalchemy.exc.MissingGreenlet) greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place?

how did you solve it
https://github.com/sqlalchemy/sqlalchemy/discussions/7835

[downdawn]

Hi, @wu-clan

Why does role need noload so that no error will be reported? What should I do if role information is required?

    async def get_userinfo_by_id(self, db: AsyncSession, user_id: int) -> User | None:
        user = await db.execute(
            select(self.model)
            .options(selectinload(self.model.department))
            .options(noload(self.model.roles))
            .where(self.model.id == user_id)
        )
        return user.scalars().first()

selectinload also does not work

[wu-clan]

Hi, @downdawn

This is a precipitated problem, I've asked questions here but didn't find a good solution, in SQLAlchemy 1.4.x they worked fine, but in 2.0, there were some problems and I was forced to come and solve it temporarily by noload()

You can look here：sqlalchemy/sqlalchemy#9094

In addition, there are other solutions：https://gitee.com/wu_cl/fastapi_sqlalchemy_mysql/issues/I6HVO2

[downdawn]

Version 2.0 does have a lot of problems and headaches.

doesn't work, same error

.options(selectinload(self.model.roles).selectinload(Role.menus))

[downdawn]

In addition to menus and permissions in the role table, they are all many-to-many relationships

[downdawn]

Both the synchronous and asynchronous tests are running fine, so it's likely a minor configuration that's affecting it.

from sqlalchemy import Column, create_engine, select, String
from sqlalchemy import Table
from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, sessionmaker, selectinload, backref
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    pass


sys_user_role = Table(
    'sys_user_role',
    Base.metadata,
    Column('user_id', ForeignKey('sys_user.id', ondelete='CASCADE'), primary_key=True, comment='用户id'),
    Column('role_id', ForeignKey('sys_role.id', ondelete='CASCADE'), primary_key=True, comment='角色id'),
)


class Department(Base):
    __tablename__ = 'sys_department'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(32), unique=True, comment='部门名称')
    parent_id: Mapped[int | None] = mapped_column(ForeignKey('sys_department.id'), default=None, comment='父级部门id')


class User(Base):
    __tablename__ = 'sys_user'

    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(String(20), unique=True, index=True, comment='用户名')
    department_id: Mapped[int] = mapped_column(ForeignKey('sys_department.id'), comment='部门关联id')
    # 多对一
    department: Mapped['Department'] = relationship(backref=backref('users', cascade='all, delete-orphan'))
    # 多对多
    roles: Mapped[list['Role']] = relationship(secondary='sys_user_role', back_populates='users',
                                               cascade='all, delete')


class Role(Base):
    __tablename__ = 'sys_role'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(32), unique=True, comment='角色名称')
    # 多对多
    users: Mapped[list['User']] = relationship(secondary='sys_user_role', back_populates='roles',
                                               passive_deletes=True)


engine = create_engine('sqlite:///test3.db', echo=True, future=True)
session = sessionmaker(bind=engine, autoflush=False, expire_on_commit=False)


def create_tables():
    Base.metadata.create_all(engine)


def add_data():
    with session.begin() as db:
        department = Department(id=1, name='部门1')
        db.add(department)
        user = User(username='用户1', department_id=department.id)
        role = Role(name='角色1')
        user.roles.append(role)
        db.add(user)


def query_data():
    with session.begin() as db:
        user = db.execute(
            select(User)
            .options(selectinload(User.department))
            .options(selectinload(User.roles))
        ).scalars().first()
        print(user.id, user.username)
        for role in user.roles:
            print(role.id, role.name)
        print(user.department.id, user.department.name)


if __name__ == '__main__':
    # create_tables()
    # add_data()
    query_data()

[wu-clan]

expire_on_commit is useful to turn on if we use the service layer

[wu-clan]

Using casbin seems to make things easier if a custom license is needed, perhaps a new branch or a new repository

[downdawn]

Consider caching to improve authentication speed, such as using Redis.

[wu-clan]

I thought about this, but caching seems to have an impact because for permissions, the cached data is likely not updated in real time

I searched for a day for casbin's python implementation, there is sqlalchemy asynchronous implementation, but they both only work for version 1.4.xx

The official asynchronous version seems to have no progress

[downdawn]

Yeah, so I probably won't use Casbin in my current actual project.

If using synchronous Casbin, it's even more important to consider caching permissions.

Clear the corresponding cache when updating permissions, so the next time permission is checked, it will be queried again instead of reading from the cache.

[wu-clan]

Move casbin question to Plan

[wu-clan]

Hi, @downdawn

In the latest code, noload is no longer used

But this error I was not able to solve directly, make people headache, here is an old tool, but he is very useful

My solution.

fastapi_best_architecture/backend/app/api/v1/user.py

Line 31 in e1edcad

data = GetUserInfo(**select_to_json(current_user))

[downdawn]

So it seems like this is caused by pre-parsing in pydantic?

When using the methods from ResponseBase, the schema structure needs to correspond with the model.

class GetUserInfo(GetUserInfoNoRelation):
    dept: GetAllDept | None = None
    roles: list[GetAllRole]

    class Config:
        orm_mode = True

[downdawn]

Why use response_200 when there is already success?

[wu-clan]

response_200 fixed return code, only this

[wu-clan]

I'm considering deleting it, it's not very cool to look at

[downdawn]

Yes, it is enough to keep success and fail