When to use

1. Only works with Pythons’s enumeration classes, or at least one with a behavior similar to enum.Enum. Does not work with collections of arbitrary entries.

2. Only works with SqlAlchemy’s declarative ORM system. If you only use SqlAlchemy Core… deal with it.

3. Better used for frequently updated enumeration classes.

4. Do not use with another package that provides op.enum_insert and op.enum_delete operations in Alembic.

How to use with SqlAlchemy

import enum
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

import enumtables as et

# Create the Python enumeration class
class MyEnum(enum.Enum):
    HELLO = "HELLO"
    WORLD = "WORLD"

Base = declarative_base()

# Create the enumeration table
# Pass your enum class and the SQLAlchemy declarative base to enumtables.EnumTable
MyEnumTable = et.EnumTable(MyEnum, Base)

# Create a model class that uses the enum
class MyModel(Base):
    __tablename__ = "my_model"
    # Pass the enum table (not the enum class) to enumtables.EnumColumn
    # It replaces sqlalchemy.Column, but aside from the enum table,
    # it can take the same parameters.
    # It will automatically create a ForeignKeyConstraint referencing the enum table.
    enum_value = et.EnumColumn(MyEnumTable, primary_key = True)

    # When valued (on an instance of MyModel), enum_value will be an instance of MyEnum.

First, the EnumTable factory takes the enum class and the declarative base class to create the actual ORM class. Then this ORM class is passed to the EnumColumn class to create the column linked to the enum table. The column behaves just as if it had SqlAlchemy’s own Enum type.

On the implementation side, EnumTable is not a class, it’s a factory function that performs Python black magic to create a subclass of the declarative base, and set it up to be a DB table containing the enum items (actually it just has one column item_id of type String).

EnumColumn is a subclass of SqlAlchemy’s Column that gets initialized with a custom type and a foreign key to the enum table.

How to use with Alembic

First add:

import enumtables

at the begining of your env.py file, then add the same line in the imports of your script.py.mako file. The package uses Alembic’s standard hooks to take care of migration generation.

Don’t forget to review the migrations afterwards. Especially make sure that, if the table did not exist before, the op.enum_insert commands are located after the corresponding op.create_table command.

Other uses

enum_query = session.query(MyEnumTable)
result = enum_query.first()

# The column item_id stores the name of the enum item as a string
enum_name = result.item_id

BetterEnumTable = et.EnumTable(
    MyEnum,
            Base,

    # tablename is turned into __tablename__
    tablename = "better_enum",

    # Let's add a new column!
    order = sa.Column(sa.Integer, nullable = False),

    # And since it's an ordering number, let's make it unique too.
    __table_args__ = (
        sa.UniqueConstraint('order'),
    ),
)