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
  6 metadata = MetaData()
  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
 23 mapper(Users, users,, 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 and version_id_generator = lambda version:uuid.uuid4().hex)
the explanation for it is
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

 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()
 33 u = Users('s', 'd');
 34 u1 = Users('s1', 'd2');
 36 session.add_all([u1, u])
 37 session.commit()
 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
but they 'hide' your data from outside and seem to do the job.

Wednesday, February 16, 2011

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
    >>> import sys
    >>> sys.maxint
  • 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/", line 19, in 
        import _mysql
    ImportError: dlopen(/Users/silviud/PROGS/PYTHON/Environments/2.6/lib/python2.6/site-packages/, 2): Library not loaded: libmysqlclient.16.dylib
      Referenced from: /Users/silviud/PROGS/PYTHON/Environments/2.6/lib/python2.6/site-packages/
      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

to have it system wide available with
defaults write 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!