How can I use UUIDs in SQLAlchemy?

The sqlalchemy postgres dialect supports UUID columns. This is easy (and the question is specifically postgres) -- I don't understand why the other answers are all so complicated.

Here is an example:

from sqlalchemy.dialects.postgresql import UUIDfrom flask_sqlalchemy import SQLAlchemyimport uuiddb = SQLAlchemy()class Foo(db.Model):    id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)

Be careful not to miss passing the callable uuid.uuid4 into the column definition, rather than calling the function itself with uuid.uuid4(). Otherwise, you will have the same scalar value for all instances of this class. More details here:

A scalar, Python callable, or ColumnElement expression representing the default value for this column, which will be invoked upon insert if this column is otherwise not specified in the VALUES clause of the insert.

I wrote this and the domain is gone but here's the guts....

Regardless of how my colleagues who really care about proper database design feel about UUID's and GUIDs used for key fields. I often find I need to do it. I think it has some advantages over autoincrement that make it worth it.

I've been refining a UUID column type for the past few months and I think I've finally got it solid.

from sqlalchemy import typesfrom sqlalchemy.dialects.mysql.base import MSBinaryfrom sqlalchemy.schema import Columnimport uuidclass UUID(types.TypeDecorator):    impl = MSBinary    def __init__(self):        self.impl.length = 16        types.TypeDecorator.__init__(self,length=self.impl.length)    def process_bind_param(self,value,dialect=None):        if value and isinstance(value,uuid.UUID):            return value.bytes        elif value and not isinstance(value,uuid.UUID):            raise ValueError,'value %s is not a valid uuid.UUID' % value        else:            return None    def process_result_value(self,value,dialect=None):        if value:            return uuid.UUID(bytes=value)        else:            return None    def is_mutable(self):        return Falseid_column_name = "id"def id_column():    import uuid    return Column(id_column_name,UUID(),primary_key=True,default=uuid.uuid4)# Usagemy_table = Table('test',         metadata,         id_column(),         Column('parent_id',            UUID(),            ForeignKey(table_parent.c.id)))

I believe storing as binary(16 bytes) should end up being more efficient than the string representation(36 bytes?), And there seems to be some indication that indexing 16 byte blocks should be more efficient in mysql than strings. I wouldn't expect it to be worse anyway.

One disadvantage I've found is that at least in phpymyadmin, you can't edit records because it implicitly tries to do some sort of character conversion for the "select * from table where id =..." and there's miscellaneous display issues.

Other than that everything seems to work fine, and so I'm throwing it out there. Leave a comment if you see a glaring error with it. I welcome any suggestions for improving it.

Unless I'm missing something the above solution will work if the underlying database has a UUID type. If it doesn't, you would likely get errors when the table is created. The solution I came up with I was targeting MSSqlServer originally and then went MySql in the end, so I think my solution is a little more flexible as it seems to work fine on mysql and sqlite. Haven't bothered checking postgres yet.

If you are happy with a 'String' column having UUID value, here goes a simple solution:

def generate_uuid():    return str(uuid.uuid4())class MyTable(Base):    __tablename__ = 'my_table'    uuid = Column(String, name="uuid", primary_key=True, default=generate_uuid)