support option persisting of enum values, not just the keys
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.
Comments (3)
-
repo owner -
repo owner - changed milestone to 1.3
- changed component to schema
- changed title to support option persisting of enum values, not just the keys
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))
-
repo owner nevermind the doc part I added a line
- Log in to comment
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: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.