How to get value or name of enum from SQLAlchemy result query?

Question

I want to build an API for my project and return everything as JSON using Flask and SQLAlchemy. Unfortunately, SQLAlchemy did not return the query as JSON Seriazeble, so I'm using data classes to solve that problem. The code working and it returns JSON as I wanted. The problem occurs when I try to implement enum column like gender, because the enum column returns an enum object, so it's not JSON Seriazeble again. This is the code:

ActivityLevel.py

class GenderEnum(Enum):
    
    p = 0
    l = 1

@dataclass
class ActivityLevel(db.Model):
    __tablename__ = "activity_level"

    id: int
    name: str
    gender: GenderEnum
    activity_score: float
    date_created: str

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    gender = db.Column(db.Enum(GenderEnum), nullable=False)
    activity_score = db.Column(
        db.Float(precision=3, decimal_return_scale=2), nullable=False
    )
    date_created = db.Column(db.DateTime, default=datetime.utcnow)

ActivityLevelController.py

from flask import jsonify
from flask_restful import Resource
from models.ActivityLevel import ActivityLevel

class ActivityLevelController(Resource):
    def get(self):
        try:
            activity = ActivityLevel().query.all()
            result = {
                "activity": activity
            }
            
            print(activity)
            
            return jsonify(result)
        
        except Exception as e:
            print(e)
            return jsonify({"message": "Error again"})

And this is the result of print(activity)

[
ActivityLevel(id=1, name='asdfasdf', gender=<GenderEnum.p: 0>, activity_score=12.0, date_created=datetime.datetime(2022, 8, 12, 10, 54, 58)), 
ActivityLevel(id=2, name='qwerqwer', gender=<GenderEnum.l: 1>, activity_score=13.0, date_created=datetime.datetime(2022, 8, 12, 10, 54, 58))
]

As you can see, gender did not return l or p, and it return <GenderEnum.l: 1>. Which is correct as the documentation says https://docs.python.org/3/library/enum.html, when i call GenderEnum.l it will result just like the response.

What I want to ask is something like this:

1. Is there a way to override the return value of GenderEnum.l to the value or name by doing something in GenderEnum class?
2. Is there a way I can get the value or name of GenderEnum when I query the data from the database?
3. Or a way to make the query call the gender value or name as the default instead the enum object?

Thank you very much.

Answer 1

1. Is there a way to override the return value of GenderEnum.l to the value or name by doing something in GenderEnum class?

That's not really what Enums are meant to do, but you can access the name and value of your enum via the name and value attributes. In fact, SQLAlchemy will store the name of the Enum in the database in most cases.

2. Is there a way I can get the value or name of GenderEnum when I query the data from the database?

You define your model with an gender as an enum, so when loading, that is what you will get.

3. Or a way to make the query call the gender value or name as the default instead the enum object?

Same answer as above, you define gender as an enum, so SQLAlchemy gives it to you.

But to fix your problem, I can only recommend you look into dependency inversion.

Have a model which corresponds to your logic and allows you to solve the problem, then this model can be mapped to an entity, which allows you to store the model in whatever persistance layer, and finally a converter/serialiser for your model to load from/dump to your API.

This way, your model is not tied to where you store it or how you recieve it.

Slightly simplified example from your code:

# model.py
from dataclasses import dataclass, field
from enum import Enum, auto


class GenderEnum(Enum):
    p = auto()
    l = auto()


@dataclass
class ActivityLevel:
    id: int = field(init=False)  # NOTE add `repr=False` if trying to display instances
    name: str
    gender: GenderEnum

# orm.py
from sqlalchemy import Column, Enum, ForeignKey, Integer, String, Table
from sqlalchemy.orm import registry

from .model import ActivityLevel

mapper_registry = registry()

activity_level = Table(
    "activity_level",
    mapper_registry.metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(100), nullable=False),
    Column("gender", Enum(GenderEnum), nullable=False),
)


def start_mapper():
    mapper_registry.map_imperatively(ActivityLevel, activity_level)

# serialiser.py
from json import JSONDecoder, JSONEncoder

from .model import ActivityLevel, GenderEnum


class ActivityLevelJSONEncoder(JSONEncoder):
    def default(self, o):
        try:  # NOTE duck typing and asking for forgiveness not permission
            return {
                "name": o.name,
                "gender": o.gender.name,
            }
        except AttributeError:
            return super().default(self, o)


class ActivityLevelJSONDecoder(JSONDecoder):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, object_hook=self._object_hook, **kwargs)

    @staticmethod
    def _object_hook(d: dict) -> ActivityLevel:
        return ActivityLevel(
            name=d["name"],
            gender=GenderEnum[d["gender"]],
        )

Then in during application startup, start the mappers, and whenever needed json.dumps your ActivityLevel instances with the kwarg cls=ActivityLevelJSONEncoder.

Answer 2

This is what I found to temporarily fix my problem. I found this post and answer and follow the answer of @Vlad Bezden that uses the f-string method to override the return value of enum object when I call it like this GenderEnum.l from this <GenderEnum.l: 1> to just the value like 0 or 1. This is what I change in the enum class

class GenderEnum(int, Enum):
    
    p = 0
    l = 1
    

I add additional int. From what I noticed in the query behavior, the query just essentially calls enum object like this GenderEnum.l so that's why I got the response like that, just like what ljmc's says in the comment of his/her answer. That code initially overrides the response to the value of the enum object. If you want to return the name of the enum object, I'm terribly sorry, I can't seem to find a way to accomplish that.

After that it change the result to this

{
    "activity": [
        {
            "date_created": "Fri, 12 Aug 2022 17:22:03 GMT",
            "gender": 0,
            "id": 1,
            "name": "asdf",
            "activity_score": 12.0
        },
    ],
}

It works and I can return it as JSON. In contrast to that, I really not recommended this way to solve this problem, cause I find a hard way to do some relationship calls like join, and I really recommend anyone to follow Ijmc's answer to solving this problem, that's why I gonna accept Ijmc's answer as the correct one.

Thank you very much for helping me Ijmc for giving me insight, so I can come up with this little solution.

---

In any case of you guys wondering example how to do a join so i can return it as json, link to this post