Issue while trying to set enum data type in MySQL database

user_726802 0
Viewed 283

What am I trying to do?

Django does not support setting enum data type in mysql database. Using below code, I tried to set enum data type.

Error Details

_mysql.connection.query(self, query) django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL, created_at datetime(6) NOT NULL, user_id bigint NOT NULL)' at line 1")

Am I missing anything?

Enumeration class with all choices

class enumTokenTypes(models.TextChoices):
    Registration = "Registration"
    ForgotPassword = "Forgot Password"

User Token class in model

class tblusertokens(models.Model):
    token_id = AutoField(primary_key=True)
    token_type = EnumField(max_length=20, choices=enumTokenTypes.choices)
    created_at = DateTimeField(auto_now_add=True, blank=True)
    user = ForeignKey(tblusers, on_delete = models.CASCADE)

User token create model in migration

class EnumField(CharField):
    def db_type(self, connection):
        return "enum"


migrations.CreateModel(
    name='tblusertokens',
    fields=[
        ('token_id', models.AutoField(primary_key=True, serialize=False)),
        ('token_type', clientauth.models.EnumField(choices=[('Registration', 'Registration'), ('Forgot Password', 'Forgotpassword')], max_length=20)),
        ('created_at', models.DateTimeField(auto_now_add=True)),
        ('user', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='clientauth.tblusers')),
    ],
)

Bounty Question

set 2 parameters to the function to pass comma separated values and a default value.

django-3.2 python-3.9 python
3 Answers

The data type should be enum('Registration', 'Forgot Password') instead of just enum.

class EnumField(CharField):

    def db_type(self, connection):
        if connection.vendor == 'mysql':
            return 'enum({0})'.format(','.join("'%s'" % value for value, label in self.choices))
        return super().db_type(connection)

Reference: https://dev.mysql.com/doc/refman/8.0/en/enum.html

DB default

While not explicitly mentioned in the MySQL 8.0 docs above, you can also specify a DB default.

class EnumField(CharField):

    def __init__(self,  *args, **kwargs):
        self.db_default = kwargs.pop('db_default', None)
        super().__init__(*args, **kwargs)

    def db_type(self, connection):
        if connection.vendor == 'mysql':
            if self.db_default is not None:
                return "enum({0}) DEFAULT '{1}'".format(','.join("'%s'" % value for value, label in self.choices), self.db_default)
            return 'enum({0})'.format(','.join("'%s'" % value for value, label in self.choices))
        return super().db_type(connection)

    def deconstruct(self):
        name, path, args, kwargs = super().deconstruct()
        if self.db_default:
            kwargs['db_default'] = self.db_default
        return name, path, args, kwargs

Usage:

token_type = EnumField(max_length=20, choices=enumTokenTypes.choices, db_default=enumTokenTypes.ForgotPassword)

About deconstruct() method

From https://docs.djangoproject.com/en/3.2/howto/custom-model-fields/#field-deconstruction:

The counterpoint to writing your __init__() method is writing the deconstruct() method. It’s used during model migrations to tell Django how to take an instance of your new field and reduce it to a serialized form - in particular, what arguments to pass to __init__() to re-create it.

If you add a new keyword argument, you need to write code in deconstruct() that puts its value into kwargs yourself.

user_86017600

You can print out the sql for that migration to see specifically whats wrong, but defining db_type to return "enum" is definitely not the right way to approach it.

    ('token_type', CharField(choices=enumTokenTypes.choices, max_length=22)),

Does the recommended syntax from the docs on Enumeration types not work for you for some reason?

user_22599340

Response to Bounty Question

For setting default value: add the default parameter in EnumField. Below example I have set up enumTokenTypes Registration to be its default value. Look into Django documentation for enum implementation with examples

     class tblusertokens(models.Model):
          token_id = AutoField(primary_key=True)
          token_type = EnumField(max_length=20, choices=enumTokenTypes.choices, default=enumTokenTypes.Registration )
          created_at = DateTimeField(auto_now_add=True, blank=True)
          user = ForeignKey(tblusers, on_delete = models.CASCADE)
user_10410850
Related