我有代表4个表格的模型:User,Test,Area,Issue
一个用户有多个测试,每个测试有多个区域,每个区域有多个问题
我想重构代码(可以正常工作)以使用棉花糖序列化我的SLQ-Alchemy对象

使用棉花糖的拳头方法行之有效。
但是,我在尝试返回一项测试时遇到问题,包括所有方面和问题。

因此,这是详细信息:

这是我的model.py文件

db = SQLAlchemy()
ma = Marshmallow()


class User(db.Model):

    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(250), nullable=False)
    email = db.Column(db.String(250), nullable=False)

class Test(db.Model):

    __tablename__ = 'test'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, ForeignKey('user.id'))
    user = relationship(User, backref=backref('tests'))

class Area(db.Model):

    __tablename__ = 'area'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), nullable=False)
    test_id = db.Column(db.Integer, ForeignKey('test.id'))
    test = relationship(Test,
                        backref=backref('areas', cascade='all, delete-orphan')
                        )
    user_id = db.Column(db.Integer, ForeignKey('user.id'))
    user = relationship(User, backref=backref('areas'))

class Issue(db.Model):

    __tablename__ = 'issue'
    name = db.Column(db.String(80), nullable=False)
    id = db.Column(db.Integer, primary_key=True)
    area_id = db.Column(db.Integer, ForeignKey('area.id'))
    area = relationship(Area,
                        backref=backref('issues', cascade='all, delete-orphan')
                        )
    user_id = db.Column(db.Integer, ForeignKey('user.id'))
    user = relationship(User, backref=backref('issues'))


class UserSchema(ma.ModelSchema):
    class Meta:
        model = User
    # A User has a list of tests
    test = ma.Nested('TestSchema', many=True)


class TestSchema(ma.ModelSchema):
    class Meta:
        model = Test
    # Each test belongs to one user
    user = ma.Nested('UserSchema')
    # Each test has a list of areas
    area = ma.Nested('AreaSchema', many=True)


class AreaSchema(ma.ModelSchema):
    class Meta:
        model = Area
    # Each Area belongs to one test
    test = ma.Nested('TestSchema')
    # Each Area has a list of issues
    issue = ma.Nested('IssueSchema', many=True)


class IssueSchema(ma.ModelSchema):
    class Meta:
        model = Issue
    # Each issue belongs to one area
    area = ma.Nested('AreaSchema')


所以现在这是使用棉花糖完美工作的资源。
这将返回所有测试,没有其区域,没有其问题
这工作正常:

# This works perfectly .
# It returns all tests for the user without its Areas and without Issues
# See line 26 the use of tests = tests_schema.dump(results).data

tests_schema = TestSchema(many=True)

class Tests(Resource):
    """ This method return the list of tests for the user
    We expect a valid JWT token from the user   that was already
    validated thorugh the decorator we created: token_required"""
    @token_required
    def get(self, *args, **kwargs):
        jwt_data = kwargs['jwt_data']
        if jwt_data:
            # The JWT payload contains the "user"
            user_name = jwt_data["user"]
            logger.info("User from JWT payload data is  %s" % user_name)
            userId = modelUser.getUserIDFromName(user_name)
            user = modelUser.getUserInfo(userId)
            results = modelTest.getAllTestsForUser(userId)
            logger.info(results)
            tests = tests_schema.dump(results).data
            logger.info(tests)
            if tests:
                return jsonify(tests)
            else:
                response = make_response(json.dumps(
                                        'You do not have any test'), 204)
                response.headers['Content-Type'] = 'application/json'
                return response


这是我遇到的问题
我在得到一个空字典:

结果= test_schema.dump(testWithAreasAndIssues).data

# This returns just one test with ALL its Areas and ALL ISSUES
# The value returned from the DB is correct.I'm just refactoring to use Marshmallow
# line  19 returns empty

class Test(Resource):
    """ GET DELETE AND PUT(modifies) a Test /api/test/<int:test_id>"""
    @token_required
    def get(self, test_id, *args, **kwargs):
        logger.info("GET for /api/test/test_id= %s" % test_id)
        jwt_data = kwargs['jwt_data']
        test = getTestForJWT(jwt_data, test_id)
        logger.info('test.id= %s for jwt=%s is %s' % (test_id, jwt_data, test))
        logger.info('test= %s' % test)
        logger.info('Calling getTestWithAreasAndIssues')
        testWithAreasAndIssues = modelTest.getTestWithAreasAndIssues(
                                        test.id)
        logger.info('FullTest for testid =%s is %s' % (
                                    test.id, testWithAreasAndIssues))
        result = test_schema.dump(testWithAreasAndIssues).data
        logger.info(jsonify(result))


为什么我要输入空字典
结果= test_schema.dump(testWithAreasAndIssues).data


这是模型中的函数,可以对其所有区域和问题进行测试。

def getTestWithAreasAndIssues(id):
        """ This method will return a table containing a test
        with all its areas and each areas with all its issues.
        The result are unserialized object in a table with 3 columns
        So we need to later serialize them and convert them
        to a herarquick view using a python dictionary"""
        test = (db.session.query(Test, Area, Issue)
                .join(Area)
                .join(Issue)
                .options(
                    joinedload(Test.areas)
                    .joinedload(Area.issues)
                )
                .filter(Test.id == id)
                .filter(Test.id == Area.test_id)
                .filter(Area.id == Issue.area_id)
                ).all()
         return test


这是此函数的输出:

[(<Test 4>, <Area 28>, <Issue 17>),
 (<Test 4>, <Area 29>, <Issue 18>),
 (<Test 4>, <Area 36>, <Issue 19>),
 (<Test 4>, <Area 36>, <Issue 20>),
 (<Test 4>, <Area 36>, <Issue 21>)]


在使用棉花糖之前,我创建了一个函数,该函数使用此SQLAlchemy表并将其转换为python对象。

最佳答案

您应使用getTestWithAreasAndIssues()要使用的其他架构。
从拥有与您的TestSchema模型正确对应的Test开始:

class TestSchema(ma.ModelSchema):
    class Meta:
        model = Test


我还建议您检查模型,您的User模型不包含与TestAreaIssue的关系。查看here以正确定义与SQLAlchemy的关系。

然后,您可以为getTestWithAreasAndIssues()返回的结果创建一个Schema:

class TestSchema(ma.ModelSchema):
    test = ma.Nested('TestSchema')
    user = ma.Nested('UserSchema')
    area = ma.Nested('AreaSchema')

关于python - flask 棉花糖表序列化失败,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/52547705/

10-10 11:40