Django: How to model a MySQL VARBINARY HEX Field?

Question

I am trying to model a VARBINARY MySQL field in Django v1.1.1. The binary field stores a hexadecimal representation of data (i.e. one would use INSERT INTO test(bin_val) VALUES X'4D7953514C')

Reading the Django documentation[1] I came up with this sollution:

class MyTest(models.Model):
    bin_val = BinValField()

class BinValField(models.Field):
    __metaclass__ = models.SubfieldBase

    def to_python(self, value):
        """ DB -> Python """
        return ''.join('%X%X' % ((ord(byte)>>4) & 0xF, ord(byte) & 0xF) for byte in value)

    def get_db_prep_value(self, value):
        """ Python -> DB """
        return a2b_hex(value).decode('latin1')

However this doesn't work correctly because:

• Django does a Unicode transformation of the binary data from MySQL
• When saving a new MyTest object, the get_db_prep_value() gets called twice (I think this is a bug in Django?)

The question is how would you model such a field?

PS: Related to this problem is this ticket[2] that is still opened after 3 years :(

[1] Django: Writing custom model fields

[2] http://code.djangoproject.com/ticket/2417

Answer 1

The problem was the way Django creates database tables and was also related to database collation.

The way I solved it was the following:

• changed the table charset to utf8 and the collation to utf8_bin
• changed the binary field from VARCHAR to VARBINARY in the MySQL table
• used in the to_python method return hexlify(value)