Wednesday, February 23, 2011

sqlalchemy UUID as primary key

I keep hearing about having uuid as primary keys into your database so I decided to give a try with sqlalchemy and python(of course).

So the plan is to have a table users that has the following fields

id - type UUID - 32
fname - varchar(50)
lname - varchar(50)

the code bellow builds the table for me.

1 from sqlalchemy import Table, Column, Integer, String, Sequence, MetaData, ForeignKey, CHAR
  2 from sqlalchemy.orm import mapper, sessionmaker, scoped_session
  3 from sqlalchemy import create_engine
  4 import uuid
  5 
  6 metadata = MetaData()
  7 
  8 users = Table('users', metadata,
  9               Column('id', CHAR(32), primary_key=True, autoincrement=True),
 10               Column('fname', String(50)),
 11               Column('lname', String(50))
 12              )
 13 # orm
 14 class Users(object):
 15     def __init__(self, fname, lname):
 16         assert isinstance(fname, str), 'fname is not a string'
 17         assert isinstance(lname, str), 'lname is not a string'
 18         self.fname = fname
 19         self.lname = lname
 20 
 21     
 22     
 23 mapper(Users, users, version_id_col=users.c.id, version_id_generator = lambda version:uuid.uuid4().hex)



line of interest will be
4 - import the uuid module (standard with python 2.6 or higher)
23 - the version_id_col=users.c.id and version_id_generator = lambda version:uuid.uuid4().hex)
the explanation for it is http://www.sqlalchemy.org/docs/orm/mapper_config.html?highlight=uuid#sqlalchemy.orm.mapper
basically you map a temporary integer used my sqlaclhemy and then you transform it into the UUID with a 32 chars in hex.

the rest of the program


24 
 25 engine = create_engine('sqlite:///:memory:', echo=True)
 26 Session = scoped_session(sessionmaker(bind=engine))
 27 metadata.drop_all(engine)
 28 metadata.create_all(engine)
 29 # my test 
 30 session = Session()
 31 
 32 
 33 u = Users('s', 'd');
 34 u1 = Users('s1', 'd2');
 35 
 36 session.add_all([u1, u])
 37 session.commit()
 38 
 39 session.query(Users).all()

if uuid are better then integers as primary keys I don't think so - at least with mysql taking
into consideration the following article http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-not-to-uuid/
but they 'hide' your data from outside and seem to do the job.

0 comments: