时间戳

首页 > 解决方案 > 无法确定父/子表之间的连接条件错误 - SQLAlchemy

python-3.x - 无法确定父/子表之间的连接条件错误 - SQLAlchemy

问题描述

我正在尝试调用一个基于报告模型返回数据的 API，但是我遇到了以下错误：

sqlalchemy.exc.InvalidRequestError：一个或多个映射器无法初始化 - 无法继续初始化其他映射器。触发映射器：'映射类 Report->Report'。最初的例外是：无法确定关系 Report.User 上的父/子表之间的连接条件 - 有多个外键路径链接这些表。指定“foreign_keys”参数，提供那些列的列表，这些列应该被视为包含对父表的外键引用。// Werkzeug 调试器

from marshmallow import validates, ValidationError, validates_schema
from marshmallow import fields
from sqlalchemy.dialects import mssql
import re

from src.base_enum import BaseEnum
from src.database import db
from src.models import ma
from src.models.user import UserDisplayInfoSchema

class User(db.Model):
  __tablename__ = "User"

    id = db.Column(db.Integer, primary_key=True)
    OID = db.Column(mssql.UNIQUEIDENTIFIER, nullable=False, unique=True)
    Display_Name = db.Column(db.String, nullable=False)
    User_Name = db.Column(db.String, nullable=False)
    Role = db.Column(db.SmallInteger, server_default=str(UserRoleEnum.CLIENT_READ_ONLY_USER), nullable=False)
    ClientID = db.Column(
        db.Integer, db.ForeignKey("Client.id", name="fk_user_client_id")
    )

class UserDisplayInfoSchema(ma.ModelSchema):
    class Meta:
        model = User
        include_fk = True
        sqla_session = db.session
        fields = ('Display_Name', 'User_Name')


class ReportTypeEnum(BaseEnum):
    Report = "Report"
    Dashboard = "Dashboard"


 class Report(db.Model):
    __tablename__ = "Report"
    __table_args__ = {'extend_existing': True}

    id = db.Column(db.Integer, primary_key=True)
    Name = db.Column(db.String(75), nullable=False)
    Description = db.Column(db.String(256), nullable=True)
    Type = db.Column(db.String, nullable=False)
    ClientID = db.Column(db.Integer, db.ForeignKey("Client.id"), nullable=False)
    State = db.Column(db.Integer, default=0, nullable=False)
    updated_by_id = db.Column(db.Integer, db.ForeignKey("User.id"))
    BI_DashboardID = db.Column(mssql.UNIQUEIDENTIFIER(), nullable=True)
    deleted_at = db.Column(db.DateTime, default=None)
    deleted_by_id = db.Column(db.Integer, db.ForeignKey("User.id"))
    updated_at = db.Column(db.DateTime, default=db.func.now())
    User = db.relationship("User")

    db.UniqueConstraint("BI_DashboardID")
    db.CheckConstraint("(deleted_at IS NULL AND deleted_by_id IS NULL) "
                       "OR (deleted_at IS NOT NULL AND deleted_by_id IS NOT NULL)")
    db.CheckConstraint(f"(Type = '{PortfolioReportTypeEnum.Report}' AND BI_DashboardID IS NULL) OR ("
                       f"Type = '{PortfolioReportTypeEnum.Dashboard}' AND BI_DashboardID IS NOT NULL)")

    def __repr__(self):
        return "<Report %r: %r>" % (self.id, self.Name)


# Function to validate GUID
# (Globally Unique Idenftifier)
def isValidGUID(str):
    # Regex to check valid
    # GUID (Globally Unique Identifier)
    regex = "^[{]?[0-9a-fA-F]{8}" + "-([0-9a-fA-F]{4}-)" + "{3}[0-9a-fA-F]{12}[}]?$"

    # Compile the ReGex
    p = re.compile(regex)

    # If the string is empty
    # return false
    if str is None:
        return False

    # Return if the string
    # matched the ReGex
    if re.search(p, str):
        return True
    else:
        return False


class ReportSchema(ma.ModelSchema):
    class Meta:
        model = Report
        include_fk = True
        sqla_session = db.session
        fields = ["id", "Name", "Description", "updated_by_id", "User"]

    Name = fields.String(required=True)
    BI_DashboardID = fields.UUID()
    User = ma.Nested(UserDisplayInfoSchema)

    @validates("Name")
    def validate_name(self, name):
        if len(name) > 75:
            raise ValidationError("must not be longer than 75 characters")

    @validates("BI_DashboardID")
    def validate_dashboard_id(self, BI_DashboardID):
        if not isValidGUID(str(BI_DashboardID)):
            raise ValidationError("Invalid BI_DashboardID type")

我该如何解决这个问题？请指教。

标签： python-3.xsqlalchemyflask-sqlalchemy

解决方案

推荐阅读