PostgreSQL Question posted in
The official documentation can be found here.

the values of ENUM type in SQLAlchemy with PostgreSQL is the variable not the value?

so my problem is i am trying to make an enum with int values but it didn’t work.

first of all, this is the code.

class UserRole(enum.IntEnum):
    publisher = 4
    editor = 3
    manager = 2
    admin = 1 


class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, nullable=False, index=True)
    username = Column(String, unique=True, nullable=False, index=True)
    hashed_password = Column(String, nullable=False)
    user_role = Column(Enum(UserRole), default=UserRole.reader)

now when i create user, go to see user_role i found it admin not 1
i though it might be a cache so i deleted all cache and recreated the database.

until i tried to change one of variables and found that the values of enum that stored on database are the variables itself not the value.

please help me how i can store the enum data as an int on database.

my last attempt was

class UserRole(enum.IntEnum):
    reader: int = 7
    Pro_reader: int = 6
    publisher: int = 5
    editor: int = 4
    small_manager: int = 3
    big_manager: int = 2
    admin: int = 1

and didn’t work.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    normally, enum accept string only. so to change it i have to made a custom type and bellow is the code for it

    custom_types.py

    from sqlalchemy import Integer, TypeDecorator


class IntEnum(TypeDecorator):
    """
    Enables passing in a Python enum and storing the enum's *value* in the db.
    The default would have stored the enum's *name* (ie the string).
    """

    impl = Integer

    def __init__(self, enumtype, *args, **kwargs):
        super(IntEnum, self).__init__(*args, **kwargs)
        self._enumtype = enumtype

    def process_bind_param(self, value, dialect):
        if isinstance(value, int):
            return value

        return value.value

    def process_result_value(self, value, dialect):
        return self._enumtype(value)

    add custom type to models.py

    from custom_types import IntEnum

class User(Base):
    __tablename__ = "user"

    user_role = Column(IntEnum(UserRole), default=UserRole.reader,)

    also i notice that user_role field on database is stored as integer not as enum.

    another sloution i didn't try and don't know if it will work was ChoiceType from SQLAlchemy-Utils library.


  2. 0

    To use values of Enum you need set values_callable parameter:

    user_role = Column(
    Enum(UserRole, values_callable=lambda choices: [item.value for item in choices]),
    default=UserRole.reader
)

    Look up bottom of this section in SQLAlchemy docs. You will found:

    In order to persist the values and not the names, the Enum.values_callable parameter may be used…. For a simple enumeration that uses string values, a callable such as lambda x: [e.value for e in x] is sufficient.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search