How do I map Enum with SQLAlchemy to a Column that has special characters?

I have a database (in Postgresql) that has a table with a column of type enum. This enums receive only the values of 'Hired', 'Possible Hire' and 'Non-hired'.

The DDL would be:

CREATE TYPE enum_type AS ENUM('Hired', 'Possible Hire', 'Non-hired');

CREATE TABLE Status (
    ID VARCHAR(63) PRIMARY KEY,
    type enum_type NOT NULL
)

The class Enum in Python:

import enum

class TypeEnum(str, enum.Enum):
    Hired = 'hired'
    PossibleHire = 'possible hire'
    Nonhired = 'non-hired'

And the SQLAlchemy model:

from sqlalchemy import Column, Enum, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class StatusModel(Base):
    __tablename__ = "status"

    id = Column(String(63), primary_key=True, index=True)
    type = Column("type", Enum(TypeEnum))

But the problem is that I can't put 'Non-hired' in the variable name of a class.

I researched and found this article: https://www.notinventedhere.org/articles/python/how-to-use-strings-as-name-aliases-in-python-enums.html

That says that you need to use functional API of Enum.

So, I wrote the code:

TypeEnum = enum.Enum(
    value="TypeEnum",
    names=[
        ('Hired', 'hired'),
        ('Possible hire', 'possible hire'),
        ('Non-hired', 'non-hired'),
    ]
)

But with this functional syntax, the SQLAlchemy just passes the value of enum. For example, when StatusModel is initialized, I put 'hired' in the type and in Session.add arrives 'hired' instead of 'Hired'. In other words: If I pass "hired" it is rejected by the database as an invalid value for the enum. SQLAlchemy never interferes with the value being passed. Maybe the problem is how I'm passing the arguments in the column of the table? type = Column("type", Enum(TypeEnum))

I want to do an update to the problem:

I passed it the same Enum but from a schema in Pydantic

from enum import Enum
from pydantic import BaseModel

TypeEnumSchema = Enum(
value="TypeEnum",
names=[
   ('Hired', 'hired'),
   ('PossibleHire', 'possible hire'),
   ('Non-hired', 'non-hired'),
  ]
)

class StatusSchema(BaseModel):
id: str
type: TypeEnumSchema  # values: hired, non-hired
class Config:
    orm_mode = True

The data flow is:

I have this status_mock.json:

{
    "data": [
      {
        "id": "63dd55b2-a99f-4e7b-985f-22c7bf80ab42",
        "type": "hired"
      },
      {
        "id": "41f98875-06c7-4cb1-b3d0-07f7ae192c0c",
        "type": "non-hired"
      },
    ]
}

Then I read it and take the data key:

data_object = read_json('./status_mock.json')["data"]

I get the list of data, pass each item to the schema and then to the model:

list_of_data: list[StatusSchema] = [StatusSchema(**item) for item in data_object]

records = StatusModel(id=data.id, type=data.type)

And then I write the code.

db.bulk_data(records)

def bulk_data(self, data):
        with Session(self.engine) as session:
            session.add_all(data)
            session.commit()

But now it generates the next error:

sqlalchemy.exc.StatementError: (builtins.LookupError) 'TypeEnum.Hired' is not among the defined enum values. Enum name: typeenum. Possible values: Hired, Possible hired, Non-hired

Basically, with the functional syntax I had to change the SQLAlchemy model from:

class StatusModel(Base):
    __tablename__ = "status"

    id = Column(String(63), primary_key=True, index=True)
    type = Column("type", Enum(TypeEnum))

To:

class StatusModel(Base):
    __tablename__ = "status"

    id = Column(String(63), primary_key=True, index=True)
    type = Column("type", Enum(TypeEnum).value_callables)

Adding .value_callables to the Enum()