SQLAlchemy, MySQL 16 bit UUID, Custom Data Type

sqlalchemy_mysql_binary_uuid.py

import UUID

from sqlalchemy.dialects.mysql import BINARY
from sqlalchemy.types import TypeDecorator


class BinaryUUID(TypeDecorator):
    '''Optimize UUID keys. Store as 16 bit binary, retrieve as uuid.
    inspired by:
        http://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/
    '''
    
    impl = BINARY(16)
    
    def process_bind_param(self, value, dialect):
        try:
            return value.bytes
        except AttributeError:
            try:
                return UUID(value).bytes
            except TypeError:
                # for some reason we ended up with the bytestring
                # ¯\_(ツ)_/¯
                # I'm not sure why you would do that,
                # but here you go anyway.
                return value
                
    def process_result_value(self, value, dialect):
        return UUID(bytes=value)

usage.py

from uuid import uuid4

from sqlalchemy import Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_mysql_binary_uuid import BinaryUUID


Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    
    id = Column('id', BinaryUUID, primary_key=True, default=uuid4)
    # ...