SQL Alchemy enum validation conflict with validate function

user_13000695 0
Viewed 155

I am trying to use the sqlalchemy enum type without creating a native DB ENUM column but still having sqlalchemy validates enum inputs.

So I have this declaration :

from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import Enum as SaEnum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import validates
import enum

Base = declarative_base()

class DummyEnum(enum.Enum):
    NAME_1 = "NAME_1"
    NAME_2 = "NAME_2"

class SAEnumVarcharTable(Base):
    __tablename__ = "test_table"
    id = Column(Integer, primary_key=True)
    uut_column = Column(SaEnum(DummyEnum, native_enum=False, validate_strings=True), default=DummyEnum.NAME_1)

    @validates('uut_column')
    def validate_uut_column(self, key, uut_value):
        return uut_value.upper()

I want the validation returning the string in upper case and only then do the Enum validation... Is it possible?

because when I do :

connection.execute(insert(SAEnumVarcharTable), {"uut_column" : "name_1"})

it does raise a LookupError where I would expect to be accepted as valid input (first goes to the validates functions -> return an uppercase string -> do the Enum validation)...Is it somehow possible?

enums sqlalchemy python-3.x orm
1 Answers

Validators are only called when modifying an instance's attributes, e.g.:

instance = SAEnumVarcharTable()
instance.uut_column = "name_1"

In your case, you would need to override sqlalchemy.Enum's _db_value_for_elem:

class UpperCaseEnum(SaEnum):

    def _db_value_for_elem(self, elem):
        return super()._db_value_for_elem(elem.upper())

# uut_column = Column(SaEnum(...), ...)
uut_column = Column(UpperCaseEnum(...), ...)

Aside

Validator implementation

validate_uut_column is not implemented correctly.

instance = SAEnumVarcharTable()
instance.uut_column = "name_0"  # No error, expected LookupError

instance.uut_column = "name_1"  # Ok
print(instance.uut_column == DummyEnum.NAME_1)  # False, expected True
print(instance.uut_column.value == "NAME_1")    # AttributeError: 'str' object has no attribute 'value'

instance.uut_column = DummyEnum.NAME_1  # AttributeError: 'DummyEnum' object has no attribute 'upper'

Instead, it should be:

@validates('uut_column')
def validate_uut_column(self, key, uut_value):
    # return uut_value.upper()
    return uut_value if uut_value in DummyEnum else DummyEnum[uut_value.upper()]

instance = SAEnumVarcharTable()
instance.uut_column = "name_0"  # LookupError, as expected

instance.uut_column = "name_1"  # Ok
print(instance.uut_column == DummyEnum.NAME_1)  # True, as expected
print(instance.uut_column.value == "NAME_1")    # True, as expected

instance.validate_uut_column = DummyEnum.NAME_1  # Ok
user_86017600
Related