Many to many bidirectional relationship is not JSON serializable

[scottwernervt]

When I try to access a model with a bidirectional many to many relationship, I get the error: TypeError: <sqlalchemy.orm.dynamic.AppenderBaseQuery object at 0x2a01b50> is not JSON serializable The error makes sense, as rv = {..., 'users': <sqlalchemy.orm.dynamic.AppenderBaseQuery object at 0x2758b50>}.

How would I go about executing that query so it becomes serialized?

• Do I create a __json_modifiers__ for 'users' and run a lambda to convert it to a list/json?
• Am I missing something in sqlalchemy that can resolve this?

projects_users = db.Table('projects_users',
    db.Column('id', db.Integer, primary_key=True),
    db.Column('user_id', db.Integer, db.ForeignKey('users.id')),
    db.Column('project_id', db.Integer, db.ForeignKey('projects.id'))
)

class UserJsonSerializer(JsonSerializer):
    pass

class User(UserJsonSerializer, UserMixin, db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    projects = db.relationship('Project', secondary=projects_users,
        backref=db.backref('users', lazy='dynamic'))

class ProjectJsonSerializer(JsonSerializer):
    # __json_hidden__ = ['users']
    #  __json_modifiers__  = {
    #    'users': lambda x, self: x.all()
    # }
    pass

class Project(ProjectJsonSerializer, db.Model):
    __tablename__ = 'projects'

    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(255))

Thanks so much for your flask skeleton project. It has cleared up so many questions I had about how to scale large projects.

[NickWoodhams]

I also noticed this, I ended up adding all the relational items to json_hidden

I would be interested in your answer as well.

Nick

[mattupstate]

I understand that the JsonSerializer class has some pitfalls when it comes to relationship fields with SQLAlchemy. In your case the users attribute, which is added via the backref, is not a list but an AppenderBaseQuery. In this case you can use the __json_modifiers__ attribute to marshal that field in the way you wish. For example:

class ProjectJsonSerializer(JsonSerializer):
    __json_modifiers__ = {
        'users': lambda users, _: [dict(id=user.id) for user in users]
    }

This will render a JSON response like such:

{
    "id": 1,
    "name": "project name",
    "users": [
        {
            "id": 1
        },
        {
            "id": 2
        }
    ]
}

Adjust the modifier to your liking to render the appropriate JSON object.

[scottwernervt]

Thanks Matt, that worked perfectly! I now have a better understanding of lambda after reading some tutorials and from your answer.

[NickWoodhams]

Great answer by the way. Thanks for sharing.

[boxcarcoder]

Hi Matt, firstly I appreciate you making this template an open source project. It has really helped me understand serializing alembic models into JSON format.

I am trying to better understand your JSONSerializer class and the JSON modifier you provided in this issue. Can you tell me if I am understanding the logic for the to_json() function correctly? Given the following classes:

class JsonSerializer(object):
    
    __json_modifiers__ = None


    def to_json(self):

        modifiers = self.__json_modifiers__ or dict()

        rv = dict()

        for key, modifier in modifiers.items():
            value = getattr(self, key)
            rv[key] = modifier(value, self)

        return rv

# My own modified json serializer
class SubredditJsonSerializer(JsonSerializer):
    """
    Extend the JsonSerializer class to change the users attribute from type
    AppenderBaseQuery to type list.
    """
    __json_modifiers__ = {
        'users': lambda users, _: [dict(id=user.id, username=user.username, phone_num=user.phone_num) for user in users]
    }

The following is my understanding of how the code is executing:

    def to_json(self):

        # modifiers == {
        #   'users': lambda users, _: [dict(id=user.id, username=user.username, phone_num=user.phone_num) for user in users]
        # }
        modifiers = self.__json_modifiers__ or dict()

        rv = dict()


        # modifiers.items() == [
        #   ('users', lambda users, _: [dict(id=user.id, username=user.username, phone_num=user.phone_num) for user in users])
        # ]
        for key, modifier in modifiers.items():

            # key == 'users'
            # value == lambda users, _: [dict(id=user.id, username=user.username, phone_num=user.phone_num) for user in users]
            value = getattr(self, key)


            ## This is the step I am having trouble understanding. How is modifier being used as a function if it is a key-value pair?

            # modifier == ('users', lambda users, _: [dict(id=user.id, username=user.username, phone_num=user.phone_num) for user in users])
            # rv['users'] = ???
            rv[key] = modifier(value, self)

        return rv

Am I understanding the execution correctly?