How can I use UUIDs in SQLAlchemy?

Question

Is there a way to define a column (primary key) as uuid in sqlalchemy if using postgresql?

Answer 1

You could try writing a custom type, for instance:

import sqlalchemy.types as types

class UUID(types.TypeEngine):
    def get_col_spec(self):
        return "uuid"

    def bind_processor(self, dialect):
        def process(value):
            return value
        return process

    def result_processor(self, dialect):
        def process(value):
            return value
        return process

table = Table('foo', meta,
    Column('id', UUID(), primary_key=True),
)

Answer 2

In addition to Florian's answer, there's also this blog entry. It looks similar except that it subclasses types.TypeDecorator instead of types.TypeEngine. Does either approach have an advantage or disadvantage over the other one?

Answer 3

I wrote this

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.