Storing UUIDs in sqlite using Pylons and SQLalchemy

Question

I'm working on my first Pylons project using SQLalchemy and sqlite. I'd like to use UUIDs as the primary key for one of my tables. I found the question below and I used the code to add a custom UUID() type to SQLalchemy. (the second solution, I wasn't able to figure out how to use the suggestion by zzzeek.)

Random ids in sqlalchemy (pylons)

However, when I try it out, I get ValueError('bytes is not a 16-char string') I suspect this has to do with the fact that I'm trying to store this UUID in a sqlite VARCHAR type field. Does anyone know what type of field I should use in sqlite that would work for these UUIDs from SQLalchemy?

Thanks, Erik

Answer 1

You were right this just work for postgresql (db I use). but I dive in the post you provide Random ids in sqlalchemy (pylons), it really work in sqlite, just don't follow the zzzeek answer go to the Tom Willis solutions, just some things to take in consideration I follow complete this code but change some things: line by line:

#I delete follow line:
from sqlalchemy.databases.mysql import MSBinary

because in sqlalchemy 0.6 it's dialects.sqllite but I don't use that I use the:

from sqlalchemy.types import Binary

just change the follow line in the UUID class:

class UUID(types.TypeDecorator):
    impl = Binary # this one!

...
...
id_column_name = "id"

def id_column():
    import uuid
    return Column(id_column_name,UUID(),primary_key=True,default=uuid.uuid4)


#usage:
app_uuid = Column(u'app_uuid', UUID(), primary_key=True)

And that works for me, good luck!

previous answer

user = Table(
    'User',
    meta.metadata,
    Column('ID', UUID(as_uuid=True), primary_key=True),
    Column('Name', String(250), nullable=False, unique=True)
)

Answer 2

The sqlite module expects to work with unicode data (which it encodes as UTF-8 prior to storing in the database) for text fields. You can send binary data by enclosing the value in a buffer() call.