In Django we can use very simple “choices” e.g.:

    ('M', 'Male'),
    ('F', 'Female'),
class Foo(models.Model):
    gender = models.CharField(max_length=1, choices=GENDER_CHOICES)

How to make something like this using SQLAlchemy?

I would probably go for sqlalchemy_utils

Use custom types.


import sqlalchemy.types as types

class ChoiceType(types.TypeDecorator):

    impl = types.String

    def __init__(self, choices, **kw):
        self.choices = dict(choices)
        super(ChoiceType, self).__init__(**kw)

    def process_bind_param(self, value, dialect):
        return [k for k, v in self.choices.iteritems() if v == value][0]

    def process_result_value(self, value, dialect):
        return self.choices[value]

The use of it would look like:

    class Entity(Base):
        __tablename__ = "entity"
        height = Column(
            ChoiceType({"short": "short", "medium": "medium", "tall": "tall"}), nullable=False

If you are using Python 3, you have to change iteritems() to items().

SQLAlchemy has an Enum type, which functions like Django’s “choices” field.

From the docs:

‘The Enum type will make use of the backend’s native “ENUM” type if one is available; otherwise, it uses a VARCHAR datatype and produces a CHECK constraint.’

import enum
class MyEnum(enum.Enum):
    one = 1
    two = 2
    three = 3

t = Table(
    'data', MetaData(),
    Column('value', Enum(MyEnum))

connection.execute(t.insert(), {"value": MyEnum.two})
assert connection.scalar( is MyEnum.two