fastapi: sqlalchemy: 在model类中用异步方法查询数据

一，代码：

用户模型:

# app/models/users.py
from typing import Optional

from sqlalchemy.ext.asyncio import AsyncSession

from app.models.assets import Asset
from app.models.mybasemodel import MyBaseModel
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy import String, BigInteger, ForeignKey
from sqlalchemy.future import select

# 用户模型
class User(MyBaseModel):
    __tablename__ = "users"

    username: Mapped[str] = mapped_column(String(100),server_default="''", comment='用户名')
    nickname: Mapped[str] = mapped_column(String(100), server_default="''", comment='昵称')
    password: Mapped[str] = mapped_column(String(100), server_default="''", comment='密码')

    avatar_id: Mapped[Optional[BigInteger]] = mapped_column(ForeignKey("assets.id"), nullable=True,comment='头像id')

    # 核心：增加异步实例方法
    async def get_avatar_url(self, db: AsyncSession) -> str:

        DEFAULT_AVATAR = "http://127.0.0.1:8008/logo_300.png"
        FILE_HOST = "http://127.0.0.1:8008"
        # 1. 如果 avatar_id 为空，直接返回默认头像，省去一次数据库查询
        if not self.avatar_id:
            return DEFAULT_AVATAR

        # 2. 异步查询 assets 表
        try:
            stmt = select(Asset.full_path).where(Asset.id == self.avatar_id)
            result = await db.execute(stmt)
            path = result.scalar_one_or_none()

            return FILE_HOST+path if path else DEFAULT_AVATAR
        except Exception:
            # 生产环境建议加上日志记录（如 logger.error）
            return DEFAULT_AVATAR

调用:

            async with AsyncSessionLocal() as db:
                # 1. 查询用户是否存在
                stmt = select(User).where(User.username == current_user['username'])
                result = await db.execute(stmt)
                user = result.scalars().first()

                if not user:
                    # raise HTTPException(status_code=404, detail="User not found")
                    return JSONResponse(status_code=404, content={"detail": "用户数据有误"})

                # 主动调用 Model 的异步方法获取头像地址
                avatar_url = await user.get_avatar_url(db)

                print("user:",user)
                print("avatar_url:", avatar_url)