sqlalchemy relationship select from other table instead of insert

Question

I'm having difficulties in relationships. I have users and roles and defined model and schema for them.

the problem is when I try to add a new user with a previously defined role (I have its ID and name)it will try to update/insert the role table by the value the user specifies. but I only want to select from roles and specify that as a user role and not updating the role table(if role not found return error).

what I want to achieve is how to limit SQLalchemy in updating related tables by the value that the user specifies.

here is my models:

class User(db.Model):
"""user model
"""
__tablename__ = 'user'
id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True, nullable=False)
username = db.Column(db.String(40), unique=True, nullable=False)       
password = db.Column(db.String(255), nullable=False)
role_id = db.Column(UUID(as_uuid=True), db.ForeignKey('role.id') , nullable=False)

class Role(db.Model):
"""role model
"""
__tablename__ = 'role'
id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True, nullable=False)
name = db.Column(db.String(40), unique=True, nullable=False)
perm_add = db.Column(db.Boolean, default=False)
perm_edit = db.Column(db.Boolean, default=False)
perm_del = db.Column(db.Boolean, default=False)

here is the schema that I defined:

class UserSchema(ma.SQLAlchemyAutoSchema):

password = ma.String(load_only=True, required=True)
email = ma.String(required=True)
role = fields.Nested("RoleSchema", only=("id", "name"), required=True)

class Meta:
    model = User
    sqla_session = db.session
    load_instance = True

and I grab user input which is checked by schema and commit it to DB.

    schema = UserSchema()
    user = schema.load(request.json)
    db.session.add(user)
    try:
      db.session.commit()

the point is here I could not change anything regarding role name or ID as it seems it is changed by schema even before applying to DB (I mean request.json)

Answer 1

In my example, I am using the additional webargs library. It facilitates validation on the server side and enables clean notation. Since marschmallow is based on webargs anyway, I think the addition makes sense.
I have based myself on your specifications. Depending on what you intend to do further, you may need to make adjustments.

I added a relationship to the user model to make the role easier to use.

class User(db.Model):
    """user model"""

    # ...

    # The role is mapped by sqlalchemy using the foreign key 
    # as an object and can be reached via a virtual relationship. 
    role = db.relationship('Role')

I have allowed the foreign key as a query parameter in the schema and limited the nested schema to the output. The email is assigned to the username.

class RoleSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Role
        load_instance = True

class UserSchema(ma.SQLAlchemyAutoSchema):
    # The entry of the email is converted into a username.
    username = ma.String(required=True, data_key='email')
    password = ma.String(required=True, load_only=True)
    
    # The foreign key is only used here for loading. 
    role_id = ma.Integer(required=True, load_only=True)
    # The role is dumped with a query. 
    role = ma.Nested("RoleSchema", only=("id", "name"), dump_only=True)

    class Meta:
        model = User
        load_instance = True
        include_relationships = True

It is now possible to query the role from the database and react if it does not exist. The database table for the roles is no longer updated automatically.

from flask import abort
from sqlalchemy.exc import SQLAlchemyError
from webargs.flaskparser import use_args, use_kwargs

# A schema-compliant input is expected as JSON 
# and passed as a parameter to the function. 
@blueprint.route('/users/', methods=['POST'])
@use_args(UserSchema(), location='json')
def user_new(user):
    # The role is queried from the database and assigned to the user object.
    # If not available, 404 Not Found is returned. 
    user_role = Role.query.get_or_404(user.role_id)
    user.role = user_role
    
    # Now the user can be added to the database. 
    db.session.add(user)
    try:
        db.session.commit()
    except SQLAlchemyError as exc:
        # If an error occurs while adding to the database, 
        # 422 Unprocessable Entity is returned 
        db.session.rollback()
        abort(422)
    
    # Upon successful completion, the new user is returned 
    # with the status code 201 Created. 
    user_schema = UserSchema()
    user_data = user_schema.dump(user)
    return jsonify(data=user_data), 201