Is there a better way to store a list of integers in a MySQL Model?

I'd like to store a list of integers in a MySQL field.

My current workaround:

import datetime
from django.db import models

class myModel(models.Model):
    testList = models.CharField()

    def set_testList(self,data):
        self.testList = ','.join(map(str, data))

    def get_testList(self):
        return list(map(int, self.testField.split(',')))

This works fine as long as I go through set_testList and get_testList to set and retrieve the field.

This get particularly annoying as I have 4-5 such fields in some models, and having to set and retrieve every field through their own set and get methods makes the code much less readable and increases db queries.

Is it possible to create a solution where I wouldn't have to go through custom methods to achieve this?

The optimal case would be to set the field using: myModel.objects.create(testField=[1,2,3,4]); and retrieve it using myModelobjects.get(pk=1).values() and have the conversion occur 'behind the scenes'.

Is something like this possible (without having to migrate to PostgreSQL)?

Solution

You can define your own Django model field, like:

# app/fields.py

from django.db import models

class IntegerListField(models.CharField):

    description = 'list of integers'

    def from_db_value(self, value, expression, connection):
        if value is None:
            return None
        return list(map(int, value.split(',')))

    def to_python(self, value):
        if isinstance(value, list):
            return value
        if value is None:
            return None
        return list(map(int, value.split(',')))

    def get_prep_value(self, value):
        if value is None:
            return None
        return ','.join(map(str, value))

Then you can use that field in your model:

# app/models.py

import datetime
from django.db import models
from app.fields import IntegerListField

class myModel(models.Model):
    testList = IntegerListField(max_length=255)

So now Django will automatically wrap the list of integers between the Python world, and the database world.

The above is of course a raw sketch. You probably should read the documentation on Writing custom model fields.

So "under the hood" at the database side, we still use a VARCHAR or whatever CharField is using here. We just have added some extra logic here, that automatically converts values in the database to a list of integers, and it will wrap these to strings before storing these in the database. We thus did not construct a new database type. I think however it is more convenient that you can use a list of integers on your model.