Potential Bug in handling of StrEnum vs. Enum

[danmurphy1217]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

included in description

Description

I opened up a discussion with sqlalchemy, context is here. They seem to believe this is an issue with SqlModel and not SqlAlchemy. Pasting my initial comment below:

---

We have the following Str Enums:

class ChequeUserStatus(enum.StrEnum):
	ACTIVE = "Active"
        ...

class ChequeAccountStatus(enum.StrEnum):
	ACTIVE = "Active"
        ...

we typed the columns in our respective tables as such:

...cheque_accounts...
vendor_status: ChequeAccountStatus | None = base.EnumStrField(
    ChequeAccountStatus, nullable=True
)

...cheque_users...
vendor_status: ChequeUserStatus | None = base.EnumStrField(
    ChequeUserStatus, nullable=True
)

base.EnumStrField is defined as this:

from typing import Any

from sqlalchemy import TypeDecorator
from sqlmodel import AutoString, Field

def EnumStrField(enum: Any, **kwargs: Any) -> Any:
    if "sa_type" in kwargs:
        raise ValueError("sa_type is not allowed for EnumStrField")

    return Field(
        sa_type=EnumAsString(enum_class=enum),  # type: ignore
        **kwargs,
    )

 
# Define a custom type decorator to serialize the Enum as a string
class EnumAsString(TypeDecorator):  # type: ignore
    """
    Customer type to serialize the Enum as a string,
    and deserialize the string back to the Enum.
    """

    impl = AutoString
    cache_ok = True

    def __init__(self, enum_class: type[PyEnum] | None = None, *args, **kwargs) -> None:  # type: ignore
        # NOTE: for auto-gen migrations we support None
        self.enum_class = enum_class
        super().__init__(*args, **kwargs)

    def process_bind_param(self, value: PyEnum | None, dialect: Any) -> str | None:
        if value is None:
            return None
        elif isinstance(value, str):
            # ensure valid enum
            self.enum_class(value)  # type: ignore
            return value
        return value.value  # type: ignore

    def process_result_value(self, value: str, dialect: Any) -> PyEnum | None:
        if value is not None:
            return self.enum_class(value)  # type: ignore
        return None

when querying for data from our tables, we get the following error:

File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/sql/sqltypes.py", line 1611, in _object_value_for_elem
raise LookupError(
LookupError: 'Active' is not among the defined enum values. Enum name: chequeaccountstatus. Possible values: ACTIVE, INACTIVE...

The full stacktrace is:

/sqlalchemy/engine/result.py:1810 - one()
/sqlalchemy/engine/result.py:749 - _only_one_row
/sqlalchemy/engine/result.py:1673 - _fetchone_impl
/sqlalchemy/engine/result.py:2259 - _fetchone_impl
/sqlalchemy/orm/loading.py:219 - chunks
/sqlalchemy/engine/result.py:541 - _raw_all_rows
/sqlalchemy/sql/sqltypes.py:1729 - process
/sqlalchemy/sql/sqltypes.py:1611 - _object_value_for_elem

Changing the typing of the enums to Enum solved the problem. It seems like there is an issue on how StrEnums vs. Traditional Enums are processed/handled.

Is this a known issue? Is there something specific to our implementation that might be incorrect?

I followed thru the stacktrace and it seems like the issue could be with how the "valid_lookup" is performed in sqlalchemy:

    def _setup_for_values(self, values, objects, kw):
        self.enums = list(values)

        self._valid_lookup = dict(zip(reversed(objects), reversed(values)))

        self._object_lookup = dict(zip(values, objects))

        self._valid_lookup.update(
            [
                (value, self._valid_lookup[self._object_lookup[value]])
                for value in values
            ]
        )

Operating System

Linux, macOS

Operating System Details

No response

SQLModel Version

0.0.16

Python Version

3.12.7

Additional Context

No response

[danmurphy1217]

hey @tiangolo are you able to look into this please? Or if there is someone else supporting the project who can 🙏