Specifying a mySQL ENUM in a Django model

Question

How do I go about specifying and using an ENUM in a Django model?

Solution 1

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

Solution 2

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

Solution 3

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.

Solution 4

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

Solution 5

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

View more solutions

Comments

[dguaraglia]

Steve, if you meant using the MySQL ENUM type, then you are out of luck, as far as I know Django doesn't provide support for that (that feature is not available in all DBs supported by Django). The answer provided by Paul works, but it won't define the type in the DB.

[Strayer]

This does not prevent "false" values from being saved if not cleaned before, does it?

[Hans Lawrenz]

As of django 1.2, you'll need to add a second parameter, connection, to the db_type def.

[Danny Staple]

Whatever happened to codecatelog then? Lokos like it could have been a good idea.... I get a 404 now - even for the root page.

[moriesta]

@Strayer yes, I guess this is useful only for using model forms

[Grijesh Chauhan]

Very Helpful Answer! But this will not works for PostgreSQL. The reason is PostgreSQL ENUM doesn't support default. In PostgreSQL first we have to create CREATE DOMAIN or CREATE TYPE. Reff 8.7. Enumerated Types I tried @David's trick and It is working fine with MySQL but in PostgrSQL work end-up with an error 'type "enum" does not exist LINE 1: ....tablename" ADD COLUMN "select_user" ENUM('B', ...'.

[Michael Scheper]

Note that recommended Django style implies that the characters should be constants: docs.djangoproject.com/en/dev/internals/contributing/…

[Ariel]

As @Carl Meyer said in his answer, this DOES NOT create an ENUM column in the database. It creates a VARCHAR or INTEGER column, so it doesn't really answer the question.

[Ilyas karim]

Can I add choices feature with integer field? @fulmicoton