support option persisting of enum values, not just the keys

[Wayne Werner]

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(
    'fnord',
    sa.MetaData(),
    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': Status.new},
    {'name': 'active', 'status': 'A'},
    {'name': 'active', 'status': Status.active},
    {'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.name} - {0.status}'.format(result))

query = sa.select([
    fnord.c.name,
    fnord.c.status.in_((Status.new, Status.active)).label('is_active'),
    fnord.c.status,
]).select_from(fnord)

print(Status('N'))

for result in engine.execute(query):
    print('{0.name} - {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
Status.new

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.

[Mike Bayer]

well we go straight from pep435 (https://www.python.org/dev/peps/pep-0435/) 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', <MyEnum.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.

[Mike Bayer]

tentative.

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

[Mike Bayer]

nevermind the doc part I added a line