SqlAlchemy Recipe:UUID Column
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 types from sqlalchemy.databases.mysql import MSBinary from sqlalchemy.schema import Column import uuid class 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 False id_column_name = "id" def id_column(): import uuid return Column(id_column_name,UUID(),primary_key=True,default=uuid.uuid4)
Usage
my_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.
Wondering about self.impl.length = 16
lengthproperty on the MSBinary class, and in my crude tests it doesn't appear to be necessary in any case. But I might be missing something.Now that you mention it, I'm
Now that you mention it, I'm not sure. I believe it came about while trying to get the column for tables and sqlalchemy to be the right length but the correct behavior may have been affected by some other change.
When I get a moment I'll see what happens when I remove it. At this point I have lots of code that depends on it and plenty of unit tests that indirectly put it through the ringer. So if there were any issues, I'd likely (hopefully) hit them and understand why.
Post new comment