python-3.x 使用JOIN表的SQLAlchemy

我为冗长的帖子道歉，但我试图找出我的问题，并需要提供所有的代码来找出我错在哪里。我试图使用SQLAlchemy与FastAPI + Pydantic返回以下数据集，但不断得到一个错误。

错误

fastapi.exceptions.ResponseValidationError: 18 validation errors:
  {'type': 'missing', 'loc': ('response', 0, 'role', 'permissions', 0, 'name'), 'msg': 'Field required', 'input': <src.schemas.roles_permissions.RolesPermissions object at 0x7ff3001e72b0>, 'url': 'https://errors.pydantic.dev/2.5/v/missing'}
  {'type': 'missing', 'loc': ('response', 0, 'role', 'permissions', 0, 'slug'), 'msg': 'Field required', 'input': <src.schemas.roles_permissions.RolesPermissions object at 0x7ff3001e72b0>, 'url': 'https://errors.pydantic.dev/2.5/v/missing'}
  {'type': 'missing', 'loc': ('response', 0, 'role', 'permissions', 0, 'description'), 'msg': 'Field required', 'input': <src.schemas.roles_permissions.RolesPermissions object at 0x7ff3001e72b0>, 'url': 'https://errors.pydantic.dev/2.5/v/missing'}

字符串
这是我试图基于Pydantic模型返回的对象。

返回对象

[
  {
    "uuid": "86c1f82b-5f3f-4294-a1b8-096ecdc78f9f",
    "id": 0,
    "created_at": "2023-11-18T06:14:01.214Z",
    "updated_at": "2023-11-18T06:14:01.214Z",
    "role_uuid": "stringstringstringstringstringstring",
    "firstname": "string",
    "lastname": "string",
    "email": "[email protected]",
    "active": 1,
    "role": {
      "uuid": "86c1f82b-5f3f-4294-a1b8-096ecdc78f9f",
      "id": 0,
      "created_at": "2023-11-18T06:14:01.214Z",
      "updated_at": "2023-11-18T06:14:01.214Z",
      "name": "string",
      "slug": "string",
      "description": "string",
      "active": 1,
      "permissions": [
        {
          "uuid": "86c1f82b-5f3f-4294-a1b8-096ecdc78f9f",
          "id": 0,
          "created_at": "2023-11-18T06:14:01.214Z",
          "updated_at": "2023-11-18T06:14:01.214Z",
          "name": "string",
          "slug": "string",
          "description": "string",
          "active": 1
        }
      ]
    }
  }
]

型
以下是我的SQLAlchemy数据库模式。

数据库架构

class Permissions(Base):
    __tablename__ = "permissions"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, nullable=False)
    uuid: Mapped[str] = mapped_column(String(36), nullable=False)
    name: Mapped[str] = mapped_column(String(64), nullable=False)
    slug: Mapped[str] = mapped_column(String(64), nullable=False)
    description: Mapped[str] = mapped_column(String, nullable=False)
    active: Mapped[bool] = mapped_column(Boolean(True), nullable=False, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=func.now())

class Roles(Base):
    __tablename__ = "roles"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, nullable=False)
    uuid: Mapped[str] = mapped_column(String(36), nullable=False)
    name: Mapped[str] = mapped_column(String(64), nullable=False)
    slug: Mapped[str] = mapped_column(String(64), nullable=False)
    description: Mapped[str] = mapped_column(String, nullable=True, default=None)
    active: Mapped[bool] = mapped_column(Boolean(True), nullable=False, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=func.now())

    permissions: Mapped[list["RolesPermissions"]] = relationship()

class RolesPermissions(Base):
    __tablename__ = "roles_permissions"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, nullable=False)
    role_uuid: Mapped[str] = mapped_column(String(36), ForeignKey("roles.uuid"), nullable=False)
    permission_uuid: Mapped[str] = mapped_column(String(36), ForeignKey("permissions.uuid"), nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=func.now())

    permission = relationship("Permissions", foreign_keys=[permission_uuid])

class Users(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, nullable=False)
    uuid: Mapped[str] = mapped_column(String(36), nullable=False)
    role_uuid: Mapped[str] = mapped_column(String(36), ForeignKey("roles.uuid"), nullable=False)
    firstname: Mapped[str] = mapped_column(String(32), nullable=False)
    lastname: Mapped[str] = mapped_column(String(32), nullable=False)
    email: Mapped[str] = mapped_column(String(128), nullable=False)
    password: Mapped[str] = mapped_column(String(128), nullable=False)
    active: Mapped[bool] = mapped_column(Boolean(True), nullable=False, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=func.now())

    role: Mapped["Roles"] = relationship(foreign_keys=[role_uuid])

型
当我使用下面的代码并试图传递要返回的数据集时，我得到了原始的错误：

results = session.scalars(select(Users).order_by(Users.firstname)).all()

型
基本上，我如何在SQLAlchemy中使用JOIN表？它正在查找的字段属于Permissions而不是RolesPermissions。我不关心返回RolesPermissions，只是该角色的Role和Permissions。
谢谢你，谢谢

首先，确保在SQLAlchemy模型中定义了正确的关系。修改RolesPermissions关系中的Roles类：

class Roles(Base):
    # ...

    permissions: Mapped[list["RolesPermissions"]] = relationship("RolesPermissions")

class RolesPermissions(Base):
    # ...

    role = relationship("Roles", back_populates="permissions")
    permission = relationship("Permissions")

字符串
现在，在查询数据时，您可以使用SQLAlchemy中的joinedload函数来连接相关的表。下面是一个示例查询：

from sqlalchemy.orm import joinedload

results = (
    session.query(Users)
    .options(
        joinedload(Users.role).joinedload(Roles.permissions).joinedload(RolesPermissions.permission)
    )
    .order_by(Users.firstname)
    .all()
)

型
在本例中，joinedload函数用于为每个用户急切地加载相关的Roles和Permissions。根据您的实际关系调整路径。
现在，当返回数据时，你可以使用Pydantic模型来表示你想要的结构：

from typing import List
from pydantic import BaseModel

class PermissionsModel(BaseModel):
    uuid: str
    id: int
    created_at: str
    updated_at: str
    name: str
    slug: str
    description: str
    active: int

class RolePermissionsModel(BaseModel):
    uuid: str
    id: int
    created_at: str
    updated_at: str
    name: str
    slug: str
    description: str
    active: int
    permissions: List[PermissionsModel]

class UserModel(BaseModel):
    uuid: str
    id: int
    created_at: str
    updated_at: str
    role_uuid: str
    firstname: str
    lastname: str
    email: str
    active: int
    role: RolePermissionsModel

型
然后，将查询结果转换为Pydantic模型：

from typing import List

user_models: List[UserModel] = [UserModel.from_orm(user) for user in results]

型
现在，您可以在FastAPI路由中返回user_models。
注意事项：根据实际的数据结构和关系调整Pydantic模型。此外，确保Pydantic模型中的created_at和updated_at字段的类型为str，如示例所示，以匹配预期响应中的格式。

python-3.x 使用JOIN表的SQLAlchemy

1条答案

相关问题

热门标签

最新问答