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.

MySQLdb (mysql-python) install on OSX 10.6 Snow Leopard (32 bits)

Ok - you have mysql server installed into /usr/local/mysql and you are thinking - yes I can connect from python to it like on my linux box ... but on 10.6 OSX is a bit different.
First a bit of light of what is happening:

• the python you run from /usr/bin/python is compiled for 64 and 32 bits ! that is a fat binary as is called. do a file /usr/bin/python and you will see something like

  usr/bin/python: Mach-O universal binary with 3 architectures
  /usr/bin/python (for architecture x86_64): Mach-O 64-bit executable x86_64
  /usr/bin/python (for architecture i386): Mach-O executable i386
  /usr/bin/python (for architecture ppc7400): Mach-O executable ppc
  

  
  
• the mysql server that you installed is 32 bits only !
  
  
• the code for MySQLdb can be compiled for either architecture but not two at ones as into the fat binary above
  

Steps to instal

• have the mysql server installed - source, archive or dmg - the best location to install is /usr/local/mysql
  
• if you use virtual environment it is best to extract the 32 bits version from the fat python into your environment. same goes for 64 bits if you use it.
  to extract do something like this after you have your virtual environment -
  

  cp /my_virtual/env/bin/python /my_virtual/env/bin/python.fat
  lipo -remove x86_64 /my_virtual/env/bin/python.fat -output /my_virtual/env/bin/python
  
  -- to check if you are using 32 bits
  python
  >>> import sys
  >>> sys.maxint
  2147483647
  

• install mysql-python wit pip/easy_install or from source
  

errors you may see and how to solve them

• >>> import MySQLdb
  Traceback (most recent call last):
    File "", line 1, in 
    File "/Users/silviud/PROGS/PYTHON/Environments/2.6/lib/python2.6/site-packages/MySQLdb/__init__.py", line 19, in 
      import _mysql
  ImportError: dlopen(/Users/silviud/PROGS/PYTHON/Environments/2.6/lib/python2.6/site-packages/_mysql.so, 2): Library not loaded: libmysqlclient.16.dylib
    Referenced from: /Users/silviud/PROGS/PYTHON/Environments/2.6/lib/python2.6/site-packages/_mysql.so
    Reason: image not found
  

  This is because the dynamic loader can not find the library libmysqlclient.16.dylib which is located into /usr/local/mysql/lib - to solve it add this to your .profile file
  
  

  export DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:/usr/local/mysql/lib
  

  
  

This is what I have done to make it work !
I've seen other solutions where you would have to choose the python architecture with an environment variable as

export VERSIONER_PYTHON_PREFER_32_BIT=yes

or
to have it system wide available with

defaults write com.apple.versioner.python Prefer-32-Bit -bool yes

but NONE worked for me except what I shown above.
I even tried to load static the mysql library into the mysql-python by changing the site.cfg from the dist but no luck.

In any case I don't suggest you do this for the a system wide installation - use virtual environment!