support option persisting of enum values, not just the keys

Issue #3906 resolved
Wayne Werner
created an issue

Here's some sample code:

import enum
import sqlalchemy as sa

engine = sa.create_engine('postgresql+psycopg2://{}'.format('your database goes here'))

class Status(enum.Enum):
    new = 'N'
    active = 'A'
    inactive = 'I'
    suspended = 'S'

engine.execute(sa.text('CREATE TEMPORARY TABLE fnord (name text, status text);'))
fnord = sa.Table(
    sa.Column('name', sa.Text),
    sa.Column('status', sa.Enum(Status, native_enum=False)),

engine.execute(sa.insert(fnord), [
    {'name': 'new', 'status': 'N'},
    {'name': 'new2', 'status':},
    {'name': 'active', 'status': 'A'},
    {'name': 'active', 'status':},
    {'name': 'inactive', 'status': 'I'},
    {'name': 'inactive', 'status': Status.inactive},
    {'name': 'suspended', 'status': 'S'},
    {'name': 'suspended', 'status': Status.suspended},

for result in engine.execute(sa.text('select * from fnord;')):
    print('{} - {0.status}'.format(result))

query =[,


for result in engine.execute(query):
    print('{} - {0.status} - {0.is_active}'.format(result))

My expectation is that SQLAlchemy would be inserting the values of the Enum. But it doesn't - it inserts the keys instead. The documentation unfortunately uses this ambiguous example:

class MyEnum(enum.Enum):
    one = "one"
    two = "two"
    three = "three"

So I had no idea that it would perform exactly backwards from how I expected. In my example you'll get this output:

new - N
new2 - new
active - A
active - active
inactive - I
inactive - inactive
suspended - S
suspended - suspended

Followed by an exception as SQLAlchemy tries to find the Enum Status.N, rather than Status('N'). You can work around this if you have enum values that are valid Python identifiers by aliasing your Enum values like so:

class Status(enum.Enum):
    new = 'N'
    N = 'N'

The order is important here.

An alternative workaround, rather than using sa.Enum:

class Enum(sa.types.TypeDecorator):
    impl = sa.Text
    def __init__(self, enumtype, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self._enumtype = enumtype

    def process_bind_param(self, value, dialect):
        return value.value

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

Though you can't insert to the table using 'N', you have to use the enum, which is totally fine.

I think at the very least the documentation needs to be updated to something less ambiguous. Does SQLAlchemy use the value of the enum at all? Something like

class Numbers(enum.Enum):
    one = "one"
    two = "two"
    five = "three"

connection.execute(t.insert(), {"value": MyEnum.five})
assert connection.scalar('SELECT value FROM data) == 'five'

Would at least make it possible to gather from the documentation what to expect.

For my own edification - why does SQLAlchemy insert the key of the enum instead of the value? I haven't been able to find any discussion/rationale - the only thing I can come up with is that it's sort of consistent with the DeclEnum example. Obviously it's not what I expected, though.

Comments (6)

  1. Michael Bayer repo owner

    well we go straight from pep435 ( and use __members__ generically as possible. Because note, the "values" of the Enum can be any Python object, not just a string:

    >>> class MyNonPersistableThing:
    ...     def __init__(self, value):
    ...             self.value = value
    >>> import enum
    >>> class MyEnum(enum.Enum):
    ...     one = MyNonPersistableThing("one")
    ...     two = MyNonPersistableThing("two")
    >>> MyEnum.__members__
    mappingproxy(OrderedDict([('one', < <__main__.MyNonPersistableThing object at 0x7fae12f22438>>), ('two', <MyEnum.two: <__main__.MyNonPersistableThing object at 0x7fae0aa638d0>>)]))

    if you want the values to be persisted, I'd favor a new flag added to Enum to support this. Would need new docs, tests, etc.

  2. Michael Bayer repo owner


    for the docs, these do need to have a little bit of clarification, something along the lines of "note that only the enum keys are actually persisted; the values of each enum object are ignored". PRs welcome for those in the interim.

    to get what you want right now you'd need to make a new object that has some alternate style of __members__:

    class MyEnum(enum.Enum):
        key_one = "value_one"
        key_two = "value_two"
        key_three = "value_three"
    class Thing(object):
        __name__ = 'MyEnum'
        __members__ = dict([
            (enum.value, enum) for name, enum in MyEnum.__members__.items()
    class MyMapping(Base):
        # ...
        data = Column(Enum(Thing(), native_enum=False))
  3. Michael Bayer repo owner

    Add values_callable feature to Enum

    Added support for :class:.Enum to persist the values of the enumeration, rather than the keys, when using a Python pep-435 style enumerated object. The user supplies a callable function that will return the string values to be persisted. This allows enumerations against non-string values to be value-persistable as well. Pull request courtesy Jon Snyder.

    Pull-request: Fixes: #3906 Change-Id: Id385465d215d1e5baaad68368b168afdd846b82c

    → <<cset a54b3bb0a8a3>>

  4. Log in to comment