Sending enums values and saving them to db with flask + marshmallow + sqlalachemy?

Question

I am using flask + marshmallow + sqlalchemy.

I am required to:

To send an http request with this content:

{
    "first_name": "John"
    "last_name": "Doe"
    "gender": "male"
}

And save the value as an integer in the database, either 0 for male or 1 for female.

id	first_name	last_name	gender
42	John	Doe	0

The road so far:

requirements.txt

...
Flask==2.0.1
flask-marshmallow==0.14.0
Flask-SQLAlchemy==2.5.1
marshmallow==3.13.0
marshmallow-enum==1.5.1
marshmallow-sqlalchemy==0.26.1
SQLAlchemy==1.4.22
...

schema.py

from .models import Users, Genders
from .extensions import ma


class SignUpSchema(ma.SQLAlchemySchema):
    class Meta:
        model = Users

    first_name = ma.auto_field(data_key='firstName', required=True)
    last_name = ma.auto_field(data_key='lastName', required=True)
    gender = EnumField(enum=Genders, required=True)

views.py

from flask import request
from flask.views import MethodView
from .schemas import SignUpSchema
from .models import Users, Genders
from .extensions import db


class SignUp(MethodView):
    methods = ['POST']

    def post(self):
        json_data = request.get_json()
        schema = SignUpSchema()
        data = schema.load(json_data)
        user = Users(**data)
        db.session.add(user)
        db.session.commit()
        return {'id': user.id}

models.py

import enum
from sqlalchemy import Column,Integer, String, Enum
from .extensions import db


class Genders(enum.Enum):
    MALE = "male"
    FEMALE = "female"


class Users(db.Model):
    id = Column(Integer, primary_key=True)
    first_name = Column(String(100))
    last_name = Column(String(100))
    gender = Column(Enum(Genders))

Current state:

{
    "firstName": "john",
    "lastName": "doe",
    "gender": "MALE"
}

id	first_name	last_name	gender
42	john	doe	MALE

I just can't figure out how to change the enum value in the request and the db insert.

Perhaps this will help: [Sqlalchemy database int to python enum](https://stackoverflow.com/a/38786737/208880) — Ethan Furman, Sep 19 '21 at 21:47

sebsmgzz · Answer 1 · 2021-09-24T02:32:39.437

I managed to solve the issue by implementing a custom marshmallow field and changing the field in the sqlalchemy model to Integer rather than an Enum field. This does have the downside that the genders retrieved from the db cannot be serialized with this custom marshmallow GenderField.

from marshmallow.fields import Field


class GenderField(Field):

    def _serialize(self, value, attr, obj, **kwargs):
        return value.value if value is not None else ''

    def _deserialize(self, value, attr, data, **kwargs):
        options = [e.value for e in Genders]
        genders = {val: i for i, val in enumerate(options)}
        try:
            return genders[value]
        except KeyError as err:
            raise ValidationError(f'Value must be one of: {options}')

Edit:

Combining the marshmallow field with sqlalchemy TypeDecorator is another solution I like better. This way, we split the strings to be for marshmallow schemas as the second value in the enum tuple, the first value (the int) goes to the db and the whole type is handled as an enum for the flask app.

class Gender(Enum):
    MALE = 0, 'male'
    FEMALE = 1, 'female'

class TupleEnum(TypeDecorator):
    impl = Integer

    @property
    def python_type(self):
        return type(self.enum_type)

    def __init__(self, enum_type, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.enum_type = enum_type

    def process_bind_param(self, value: Enum, dialect):
        return value.value[0]

    def process_result_value(self, value: int, dialect):
        options = {e.value[0]: e for e in self.enum_type}
        return options[value]

    def process_literal_param(self, value: int, dialect):
        return self.process_bind_param(value, dialect)

score 0 · Answer 2 · answered Sep 19 '21 at 19:33

SQLAlchemy saves enumerations as strings, not as integers¹, so you cannot use an Enum if you are required to use an integer in the database. By default the strings used are the member names, but you can specify a function to use the values instead... converted to strings.

SQLAlchemy enum docs