peewee + MySQL, How to create a custom field type that wraps SQL-built ins?

Question

I'd like to create a custom UUID field in peewee (over MySQL).

In python, I'm using the UUID as a hexified string, e.g.:

uuid = '110e8400-e29b-11d4-a716-446655440000'

But I want to store it in the database to a column of type BINARY(16) to save space.

MySQL has built-in HEX() and UNHEX()methods to convert back and forth between a string and binary.

So my question is how do I tell peewee to generate SQL that uses a built-in function? Here's an idea for the code I want to work:

class UUIDField(Field):
    db_field='binary(16)'

    def db_value(self, value):
        if value is not None:
            uuid = value.translate(None, '-')   # remove dashes
            # HERE: How do I let peewee know I want to generate
            # a SQL string of the form "UNHEX(uuid)"?

    def python_value(self, value):
        if value is not None:
            # HERE: How do I let peewee know I want to generate
            # a SQL string of the form "HEX(value)"?

Note that I'm specifically asking how to get peewee to wrap or unwrap a value in custom SQL. I realize I could probably do the value conversion entirely in python, but I'm looking for the more general-purpose SQL-based solution.

EDIT: For future reference, here is how I made it work doing the conversions in python. It doesn't answer the question though, so any ideas are appreciated!

import binascii
from peewee import *

db = MySQLDatabase(
    'database',
    fields={'binary(16)': 'BINARY(16)'}     # map the field type
)

# this does the uuid conversion in python
class UUIDField(Field):
    db_field='binary(16)'

    def db_value(self, value):
        if value is None: return None

        value = value.translate(None, '-')
        value = binascii.unhexlify(value)

        return value

    def python_value(self, value):
        if value is None: return None

        value = '{}-{}-{}-{}-{}'.format(
            binascii.hexlify(value[0:4]),
            binascii.hexlify(value[4:6]),
            binascii.hexlify(value[6:8]),
            binascii.hexlify(value[8:10]),
            binascii.hexlify(value[10:16])
        )

        return value

Answer 1

Using a SelectQuery you can invoke internal SQL functions like so:

from peewee import SelectQuery

# this does the uuid conversion in python
class UUIDField(Field):
    db_field = 'binary(16)'

    def db_value(self, value):
        if value is None: return None

        value = value.translate(None, '-')
        query = SelectQuery(self.model_class, fn.UNHEX(value).alias('unhex'))
        result = query.first()
        value = result.unhex
        return value

    def python_value(self, value):
        if value is None: return None
        query = SelectQuery(self.model_class, fn.HEX(value).alias('hex'))
        result = query.first()
        value = '{}-{}-{}-{}-{}'.format(
            result.hex[0:8],
            result.hex[8:12],
            result.hex[12:16],
            result.hex[16:20],
            result.hex[20:32]
        )
        return value