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)

In your version, a transient object is always used as the basis for updating an existing object or creating a new one. Why don't you use the foreign key column in your UserSchema instead of a nested object to specify the role? Starting from this point, it is possible to query the role from the database and to react if it does not exist. — Detlef, Feb 13 '21 at 11:16
@Detlef thank you, I want to do that but I'm new to sqlalchemy and don't know how to modify the schema and specify the foreign key instead of nested schema could you provide a sample or any document? thanks in advance — imax, Feb 14 '21 at 08:01
This platform lives from the fact that there are volunteers who are willing to sacrifice their time and use their knowledge to help others to solve their problems. In order to give these people feedback on the success of their measures, the users are given a wide variety of means. It would be nice if the questioner would use this to rate the answer and / or mark the question as answered. Even if you have received an answer, it is always possible to clarify any resulting ambiguities with comments. I hope that you understand my concerns and thank you for your attention. — Detlef, Feb 24 '21 at 00:26

score 0 · Answer 1 · answered Feb 14 '21 at 13:45

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

sqlalchemy relationship select from other table instead of insert

1 Answers1