Mapping sqlalchemy Enum('true,'false') to Pydantic bool

Hi, greater Python community. I’ve been beating my head all day on something that I feel like should be simple and I’m overlooking something obvious. I’ve Googled, spent hours on Stack Overflow, asked ChatGPT. No luck at all. Here’s hoping a real human can help!

I have a MySQL database whose schema I can’t control. In one table, there are a bunch of fields that are enum(‘yes’, ‘no’).

In my Pydantic model, I’d like to be able to treat this as a bool so that FastAPI will generate a proper REST endpoint for me accepting true and false.

I’ve got so far as creating an Enum class like this:

class TFEnum(enum.Enum):
        yes = True
        no  = False

And then in my Pydantic class, I can retrieve a bool like this:

        attach: bool
        @validator("attach", pre=True, always=True)
        def attach_bool(cls, value):
                return value == TFEnum.yes

I doubt that’s the best way, and it doesn’t handle setting, though.

Is there a better way? If so, please point me in the right direction! If not, does anyone have a good suggestion to handle setting?

Thanks so much!

Oooh, I found something that works… perhaps the community can tell me if there’s a less copy/paste code replication way of accomplishing this.

In my Pydantic class, I got rid of the @validator and function… it’s now just:
attach: bool

In my SQL Alchemy class, now, we have all the decoration:

        _attach = Column("attach", Enum(TFEnum), nullable=False)
        @property
        def attach(self):
                return self._attach == TFEnum.yes
        @attach.setter
        def attach(self, value):
                self._attach= TFEnum.yes if value else TFEnum.no

Now this is pretty darn verbose and there are lots of fields to do this for, so perhaps there’s still a better way?

Thanks for any help y’all can offer!

This strategy seems reasonable. I would just encapsulate it somehow so you don’t have to repeat it for all the enum fields. The first thing that came to mind for me is to implement a Descriptor. I’m not sure if that might interfere with SQLAlchemy though.

Actually, skimming the docs for SQLAlchemy 1.4, it looks like you may want to create a TypeDecorator. This will let you add the conversions between bool and enum “around” the existing SQLAlchemy Enum, and yhen to use it you just use your TypeDecorator subclass in place of SQLAlchemy’s built in Enum.

1 Like

Thanks for the tip! It looks like this TypeDecorator to replace the Enum is exactly what I’m after!

In case this is helpful for others… I found TypeDecorator too challenging to figure out, but did come up with a cleaner version of what I initially made work…

Two functions that create getter & setter functions:

def _set(attr):
        def set_any(self, value):
                setattr(self, attr, TFEnum.yes if value else TFEnum.no)
        return set_any
def _get(attr):
        def get_any(self):
                return getattr(self, attr) == TFEnum.yes
        return get_any

Then on each yes/no database field:

        _attach = Column("attach", Enum(TFEnum), nullable=False)
        attach = property(_get('_attach'), _set('_attach'))

And finally in the Pydantic model, just a bool value:

        attach: bool

I feel like cleaner is still possible, but I’m happy with this. At least until I learn a bit more about these frameworks.

In your application, do you intend to assign TFEnum instances or bool instances to the attach attribute? You mention setting so I think you intend the former, but why not do the latter instead?

That is, in your application, you write this:

obj.attach = True

and not this:

obj.attach = TFEnum.yes

Therefore you only ever need to worry about conversion between TFEnum and bool at the “edges” of your application, where it interacts with the database.

TFEnum itself can be defined as a string, rather than a boolean:

class TFEnum(enum.StrEnum):
    yes = enum.auto()
    no = enum.auto()

Deserialization from the database representation can be handled by a @validator similar to your example above:

class AppThing:
    attach: bool

    @validator("attach")
    def attach_bool(cls, value: str | TFEnum) -> bool:
        try:
            return {TFEnum.yes: True, TFEnum.no: False}[value}
        except KeyError:
            raise ValidationError(f"Invalid boolean string: {value!r}.")

And serialization back to the database can be done by overriding .dict():

    def dict(self, *args: Any, **kwargs: Any) -> dict[str, Any]:
        result = super().dict(*args, **kwargs)
        if "attach" in result:
            result["attach"] = TFEenum.yes if result["attach"] else TFEenum.no
        return result

Note that I am familiar with Pydantic 1.x but I have not started working through the 2.x changes yet, so there might be some additional functionality available for this kind of thing.

As for doing the deserialization in SQLAlchemy vs. Pydantic, that’s not a bad idea either, but I think it kind of defeats the purpose of having two separate representations. The SQLAlchemy class represents entities in your database and the Pydantic class represents entities in your application. Conversion between database and application should happen at the boundary between those two systems, i.e. when converting from the SQLAlchemy object to the Pydantic object or vice versa. Doing the conversion inside the SQLAlchemy class seems like doing it too early in the process. However you were relying entirely on SQLAlchemy ORM objects to represent data throughout the application, then yes I think it’s a good idea to perform the conversion there.

That’s right, I sure do want to write:

obj.attach = True

And I see your point that it’d be more logical to do this translation on the Pydantic side, not the SqlAlchemy side, that way letting SqlAlchemy just describe the situation in the database.

It looks like I could pretty easily extend your dict() override idea to support the obscene number of these columns, but I may be too new to Python to see an obvious way to expand the attach_bool without a pile of copy/paste. I may have some reading to do there.

I appreciate the suggestion!

class AppThing:
    attach: bool
    revert: bool
    defer: bool

    @validator("attach", "revert", "defer")
    def convert_db_bool(cls, value: str | TFEnum) -> bool:
        try:
            return {TFEnum.yes: True, TFEnum.no: False}[value}
        except KeyError:
            raise ValidationError(f"Invalid boolean string: {value!r}.")

Pydantic lets you assign a validator to more than one field, maybe that helps.