我想在peewee(通过MySQL)创建一个自定义UUID字段.

在python中,我使用UUID作为一个六角形字符串,例如：

uuid =’110e8400-e29b-11d4-a716-446655440000′

但是我想将它存储在数据库中的BINARY(16)类型的列中以节省空间.

MySQL内置了HEX()和UNHEX()方法,可以在字符串和二进制文件之间来回转换.

所以我的问题是如何告诉peewee生成使用内置函数的SQL？这是我想要的代码的想法：

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)"?

请注意,我特别询问如何让peewee在自定义SQL中包装或解包值.我意识到我可能完全在python中进行值转换,但我正在寻找更通用的基于SQL的解决方案.

编辑：为了将来的参考,这里是我如何使它在python中进行转换.它虽然没有回答这个问题,所以任何想法都值得赞赏！

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

解决方法:

使用SelectQuery,您可以调用内部SQL functions,如下所示：

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