Random identifiers in sqlalchemy (pylons)

Question

I use pylons and sqlalchemy, and I was wondering how I can have randoms ids like primary_key.

Answer 1

Years later with this problem again to share your current answer. I ended up on the Backend-agnostic GUID Type , but I wanted to use the binary instead of CHAR (32). I also wanted to deal with hexadecimal strings directly, not UUID objects for easy printing for user / URL.

import binascii import uuid from sqlalchemy.types import TypeDecorator, BINARY class GUID(TypeDecorator): impl = BINARY def load_dialect_impl(self, dialect): return dialect.type_descriptor(BINARY(16)) def process_bind_param(self, value, dialect): if value is None: return value if not isinstance(value, bytes): if isinstance(value, uuid.UUID): value = value.bytes elif isinstance(value, str): value = binascii.unhexlify(value) else: raise TypeError('Invalid GUID value.') return value def process_result_value(self, value, dialect): if value is None: return value return binascii.hexlify(value).decode('ascii') 

I also added a helper function to streamline the UUIDs to improve indexing performance based on this blog post . If you are using MySQL 8, check UUID_TO_BIN () with the swap flag and BIN_TO_UUID ().

 def order_uuid(uuid): uuid_str = str(uuid).replace('-', '') ordered_uuid = ''.join([uuid_str[12:16], uuid_str[8:12], uuid_str[0:8], uuid_str[16:]]) return ordered_uuid def revert_order_uuid(o_uuid_str): original_uuid_str = ''.join([o_uuid_str[8:16], o_uuid_str[4:8], o_uuid_str[0:4], o_uuid_str[16:]]) return uuid.UUID(original_uuid_str) 

Answer 2

the best way is to use randomly generated UUIDs:

 import uuid id = uuid.uuid4() 

uuid datatypes are available initially in some databases, such as Postgresql (for this purpose SQLAlchemy has its own uuid data type for PG - in 0.5 it is called sqlalchemy.databases.postgres.PGUuid ). You should also be able to store uuid in any 16-byte CHAR field (although I have not tried this specifically for MySQL or others).

Answer 3

I use this template and it works very well. a source

 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))) 

Although zzzeek, ​​I believe, is the author of sqlalchemy, so if it is wrong, he will know, and I will listen to him.

Answer 4

Or with ORM mapping:

 import uuid from sqlalchemy import Column, Integer, String, Boolean def uuid_gen(): return str(uuid.uuid4()) Base = declarative_base() class Device(Base): id = Column(String, primary_key=True, default=uuid_gen) 

This saves it as a string, providing better database compatibility. However, you lose the ability of the database to store and use uuid more optimally.