Creating Association Object Through Flask-sql Alchemy Works As Expected In Shell, But Produces Json Error In Flask Route

I'm trying to create an association object UserRelationship which defines a 'follow' relationship between User and User. When I interface with this object through shell, it behaves

Solution 1:

Actually, the db.Model class doesn't provides a method to serialize its instance in JSON format. For, instance, the following code, extracted from the Flask-SqlAlchemy quick start fails for the same reason.

import json

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)


classUser(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)


if __name__ == '__main__':
    db.create_all()
    admin = User(username='admin', email='admin@example.com')

    json.dumps(admin)

You get:

Traceback (most recent call last):
  [...]
TypeError: Object of type User isnot JSON serializable

So, your UserRelationship class, which inherits db.Model, is not JSON serializable.

The problem occurs when the server wants to save the user state in the current session. It dumps the User instance and then its UserRelationship.

There must be a way to avoid storing the relationships in the session. But that needs more investigation.

EDIT

IMO, the UserRelationship should be a Association Object between a User and it-self. It's a kind of self referential Many-to-many relationship with extra column.

Based on this this answer, you should define it like that:

classUserRelationship(db.Model):
    fk_user_from = db.Column(db.Integer, db.ForeignKey("user.id"), primary_key=True)
    fk_user_to = db.Column(db.Integer, db.ForeignKey("user.id"), primary_key=True)
    bank = db.Column(db.String(100))


classUser(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    followings = db.relationship(
        "UserRelationship", backref="followed", primaryjoin=id == UserRelationship.fk_user_to
    )
    followers = db.relationship(
        "UserRelationship", backref="following", primaryjoin=id == UserRelationship.fk_user_from
    )

Could you retry with this schema?