How to use UUIDs instead of integers in MySQL DB

Question

I would like to use python's uuid() function to assign my MySQL id's instead of just an integer and AUTOINCREMENT.

However, it would also be nice if that uuid() was generated when an object is created. I've not worked extensively with SQL before. So, the only way I can see to do this is when a new object is created in the python code, run uuid() and just assign it manually, but that seems unnecessary.

Is there a way to integrate this into the MySQL DB?

If so, what datatype do I assign to that column? varchar?

Answer 1

MySQL does not have real UUID support – you can store UUIDs in CHAR(32) columns, although your indexes probably won't like it.

The SQLAlchemy docs provide the following recipe for using Python's uuid with any database:

from sqlalchemy.types import TypeDecorator, CHAR
from sqlalchemy.dialects.postgresql import UUID
import uuid

class GUID(TypeDecorator):
    """Platform-independent GUID type.

    Uses Postgresql's UUID type, otherwise uses
    CHAR(32), storing as stringified hex values.

    """
    impl = CHAR

    def load_dialect_impl(self, dialect):
        if dialect.name == 'postgresql':
            return dialect.type_descriptor(UUID())
        else:
            return dialect.type_descriptor(CHAR(32))

    def process_bind_param(self, value, dialect):
        if value is None:
            return value
        elif dialect.name == 'postgresql':
            return str(value)
        else:
            if not isinstance(value, uuid.UUID):
                return "%.32x" % uuid.UUID(value)
            else:
                # hexstring
                return "%.32x" % value

    def process_result_value(self, value, dialect):
        if value is None:
            return value
        else:
            return uuid.UUID(value)

By using this snippet, you will also keep the possibility open to switch to Postgres later on, a database which does have native UUID support.

As for initializing objects: you are right to assign a new uuid.uuid4() when you create a new object; the database (especially a database without UUID support) can't do that for you.