Django: How to model a MySQL VARBINARY HEX Field? (solved)

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

Solution to the problem:

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)

Answer 1

The same ticket you referred to also has a patch which should give some pointers towards implementing this field.

In any case whats your real problem to store it in a CharField?

Even if you were to implement a bin field, you could just extend the models.CharField and cast it to hex in the to_python method.

Also the same question has also been answered earlier: http://stackoverflow.com/questions/517349/binaryfields-in-django-models

Answer 2

MySQL neither store hexadecimal representation for VARBINARY fields nor require it in INSERT statement. The only difference from VARCHAR is that MySQL uses binary collation for it. You can pass any 8-bit string as parameter for it.