[Django]-Specifying a mySQL ENUM in a Django model

113👍

From the Django documentation:

MAYBECHOICE = (
    ('y', 'Yes'),
    ('n', 'No'),
    ('u', 'Unknown'),
)

And you define a charfield in your model :

married = models.CharField(max_length=1, choices=MAYBECHOICE)

You can do the same with integer fields if you don’t like to have letters
in your db.

In that case, rewrite your choices:

MAYBECHOICE = (
    (0, 'Yes'),
    (1, 'No'),
    (2, 'Unknown'),
)

37👍

from django.db import models

class EnumField(models.Field):
    """
    A field class that maps to MySQL's ENUM type.

    Usage:

    class Card(models.Model):
        suit = EnumField(values=('Clubs', 'Diamonds', 'Spades', 'Hearts'))

    c = Card()
    c.suit = 'Clubs'
    c.save()
    """
    def __init__(self, *args, **kwargs):
        self.values = kwargs.pop('values')
        kwargs['choices'] = [(v, v) for v in self.values]
        kwargs['default'] = self.values[0]
        super(EnumField, self).__init__(*args, **kwargs)

    def db_type(self):
        return "enum({0})".format( ','.join("'%s'" % v for v in self.values) )

35👍

Using the choices parameter won’t use the ENUM db type; it will just create a VARCHAR or INTEGER, depending on whether you use choices with a CharField or IntegerField. Generally, this is just fine. If it’s important to you that the ENUM type is used at the database level, you have three options:

  1. Use “./manage.py sql appname” to see the SQL Django generates, manually modify it to use the ENUM type, and run it yourself. If you create the table manually first, “./manage.py syncdb” won’t mess with it.
  2. If you don’t want to do this manually every time you generate your DB, put some custom SQL in appname/sql/modelname.sql to perform the appropriate ALTER TABLE command.
  3. Create a custom field type and define the db_type method appropriately.

With any of these options, it would be your responsibility to deal with the implications for cross-database portability. In option 2, you could use database-backend-specific custom SQL to ensure your ALTER TABLE is only run on MySQL. In option 3, your db_type method would need to check the database engine and set the db column type to a type that actually exists in that database.

UPDATE: Since the migrations framework was added in Django 1.7, options 1 and 2 above are entirely obsolete. Option 3 was always the best option anyway. The new version of options 1/2 would involve a complex custom migration using SeparateDatabaseAndState — but really you want option 3.

11👍

Setting choices on the field will allow some validation on the Django end, but it won’t define any form of an enumerated type on the database end.

As others have mentioned, the solution is to specify db_type on a custom field.

If you’re using a SQL backend (e.g. MySQL), you can do this like so:

from django.db import models


class EnumField(models.Field):
    def __init__(self, *args, **kwargs):
        super(EnumField, self).__init__(*args, **kwargs)
        assert self.choices, "Need choices for enumeration"

    def db_type(self, connection):
        if not all(isinstance(col, basestring) for col, _ in self.choices):
            raise ValueError("MySQL ENUM values should be strings")
        return "ENUM({})".format(','.join("'{}'".format(col) 
                                          for col, _ in self.choices))


class IceCreamFlavor(EnumField, models.CharField):
    def __init__(self, *args, **kwargs):
        flavors = [('chocolate', 'Chocolate'),
                   ('vanilla', 'Vanilla'),
                  ]
        super(IceCreamFlavor, self).__init__(*args, choices=flavors, **kwargs)


class IceCream(models.Model):
    price = models.DecimalField(max_digits=4, decimal_places=2)
    flavor = IceCreamFlavor(max_length=20)

Run syncdb, and inspect your table to see that the ENUM was created properly.

mysql> SHOW COLUMNS IN icecream;
+--------+-----------------------------+------+-----+---------+----------------+
| Field  | Type                        | Null | Key | Default | Extra          |
+--------+-----------------------------+------+-----+---------+----------------+
| id     | int(11)                     | NO   | PRI | NULL    | auto_increment |
| price  | decimal(4,2)                | NO   |     | NULL    |                |
| flavor | enum('chocolate','vanilla') | NO   |     | NULL    |                |
+--------+-----------------------------+------+-----+---------+----------------+

10👍

http://www.b-list.org/weblog/2007/nov/02/handle-choices-right-way/

class Entry(models.Model):
    LIVE_STATUS = 1
    DRAFT_STATUS = 2
    HIDDEN_STATUS = 3
    STATUS_CHOICES = (
        (LIVE_STATUS, 'Live'),
        (DRAFT_STATUS, 'Draft'),
        (HIDDEN_STATUS, 'Hidden'),
    )
    # ...some other fields here...
    status = models.IntegerField(choices=STATUS_CHOICES, default=LIVE_STATUS)

live_entries = Entry.objects.filter(status=Entry.LIVE_STATUS)
draft_entries = Entry.objects.filter(status=Entry.DRAFT_STATUS)

if entry_object.status == Entry.LIVE_STATUS:

This is another nice and easy way of implementing enums although it doesn’t really save enums in the database.

However it does allow you to reference the ‘label’ whenever querying or specifying defaults as opposed to the top-rated answer where you have to use the ‘value’ (which may be a number).

6👍

If you really want to use your databases ENUM type:

  1. Use Django 1.x
  2. Recognize your application will only work on some databases.
  3. Puzzle through this documentation page:http://docs.djangoproject.com/en/dev/howto/custom-model-fields/#howto-custom-model-fields

Good luck!

3👍

There’re currently two github projects based on adding these, though I’ve not looked into exactly how they’re implemented:

  1. Django-EnumField:
    Provides an enumeration Django model field (using IntegerField) with reusable enums and transition validation.
  2. Django-EnumFields:
    This package lets you use real Python (PEP435-style) enums with Django.

I don’t think either use DB enum types, but they are in the works for first one.

3👍

Django 3.0 has built-in support for Enums

From the documentation:

from django.utils.translation import gettext_lazy as _

class Student(models.Model):

    class YearInSchool(models.TextChoices):
        FRESHMAN = 'FR', _('Freshman')
        SOPHOMORE = 'SO', _('Soph*m*re')
        JUNIOR = 'JR', _('Junior')
        SENIOR = 'SR', _('Senior')
        GRADUATE = 'GR', _('Graduate')

    year_in_school = models.CharField(
        max_length=2,
        choices=YearInSchool.choices,
        default=YearInSchool.FRESHMAN,
    )

Now, be aware that it does not enforce the choices at a database level this is Python only construct. If you want to also enforce those value at the database you could combine that with database constraints:

class Student(models.Model):
    ...

    class Meta:
        constraints = [
            CheckConstraint(
                check=Q(year_in_school__in=YearInSchool.values),
                name="valid_year_in_school")
        ]

1👍

If you have to have enum type in MySQL table and not a "fake" enum (only visible on python side) then you can try Django-MySQL extension and its EnumField.

The models:

from django.db import models
from django_mysql.models import EnumField


class IceCreamFlavor(models.TextChoices):
    CHOCOLATE = "chocolate"
    VANILLA = "vanilla"


class IceCream(models.Model):
    price = models.DecimalField(max_digits=4, decimal_places=2)
    ice_cream_flavor = EnumField(choices=IceCreamFlavor.choices)

Resulting table:

mysql> SHOW COLUMNS IN myapp.db_icecream;
+------------------+-----------------------------+------+-----+---------+----------------+
| Field            | Type                        | Null | Key | Default | Extra          |
+------------------+-----------------------------+------+-----+---------+----------------+
| id               | int                         | NO   | PRI | NULL    | auto_increment |
| price            | decimal(4,2)                | NO   |     | NULL    |                |
| ice_cream_flavor | enum('chocolate','vanilla') | NO   |     | NULL    |                |
+------------------+-----------------------------+------+-----+---------+----------------+

-2👍

A the top of your models.py file, add this line after you do your imports:

    enum = lambda *l: [(s,_(s)) for s in l]
👤Kenzo

Leave a comment