sqlmodel StrEnum vs. Enum processing

[danmurphy1217]

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:

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
            ]
        )

[CaselIT]

Hi,

It's really hard to follow what you are doing. What is Field, what is AutoString?
There is no usage of the sqlalchemy Enum in your code, so I don't know how that comes in play.

[danmurphy1217]

Hi @CaselIT, i've included the relevant imports below:

from enum import Enum as PyEnum
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

both Field and AutoString are imported from sqlmodel:

from sqlmodel import AutoString, Field

There is no usage of the sqlalchemy Enum in your code, so I don't know how that comes in play.

My hypothesis was formed primarily by following the stacktrace and digging into some of the details of how enums are handled by sqlalchemy. Hopefully you can shine a bit more light on this for us 😄

[CaselIT]

Thanks for providing more details.
This suggest that whatever sqlmodel is doing it not correct for StrEnum. I would suggest asking there.
This is an example using only sqlalchemy:

import enum
import sqlalchemy as sa

engine = sa.create_engine("sqlite://", echo=True)

class E(enum.StrEnum):
    foo = "bar"

t = sa.Table("t", sa.MetaData(), sa.Column("c", sa.Enum(E)))

t.metadata.create_all(engine)
with engine.connect() as c:
    c.execute(t.insert(), {"c": E.foo})
    print(c.execute(t.select()).one())