Access MAMP’s MySQL database from Python

I posted a while ago about running a locally hosted server using MAMP, and I have been successfully using a PHP script on this server to read and write to a database.

Then a friend suggested I could use Python instead of PHP, e.g. using “CherryPy” as a light-weight server.  I am a big fan of python, so I want to try this.

The basic overview is:

  • Use the CherryPy python library to run the server (though more recently I have switched to Django)
  • Use the MySQLdb python library to interface with MySQL
  • Use the MAMP-installed instance of MySQL

Here’s how I made it work:

    • Download CherryPy (or Django or any other Python-based framework) from the link above.  I had to use sudo in front of the python build command.
    • Download MySQLdb from the link above.
    • Check it works by going to the terminal, typing python and then import MySQLdb
    • It doesn’t work, for several reasons:
      • It can’t find the “mysql_config” file associated with MAMP.  To fix this, open site.cfg (in the directory you installed MySQLdb) and add the line below. Note this points to the MAMP installation of my_sql, not the default of /usr/local/bin:
          mysql_config = /Applications/MAMP/Library/bin/mysql_config
      • It couldn’t find llvm. To solve this, open XCode, go to Preferences, choose the Components tab, and install the command line tools.
      • MAMP does not include the required .h files.  This post describes how to get around this: I downloaded the 64bit MySQL from here, and copied files from its include directory into the new directory MAMP/Library/include, and from its lib directory into MAMP/Library/lib. (Don’t use the 32bit files.)
      • The final magic touch is you need to type
          export DYLD_LIBRARY_PATH=/Applications/MAMP/Library/lib
        into the terminal before you go into python. (But note – this will mess up git.  So when you need git, type export DYLD_LIBRARY_PATH=''. Urrggh!)
    • Now both import MySQLdb and import _mysql work in python.
    • To access MySQL from the terminal, open MAMP and start the servers.  Then type
        /Applications/MAMP/Library/bin/mysql --host localhost -uroot -proot
      You can now do things like show databases; to look at the databases you have available.
    • So far so good, but when I try to connect to a database, I got the error “Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)” (error number 2002).  The solution is nicely explained in this post, and is:
        sudo ln -s /Applications/MAMP/tmp/mysql/mysql.sock /tmp/mysql.sock
    • Strangely, when I came back to this a week later, I got an error: “Library not loaded: libmysqlclient.18.dylib”.  I found this works:
        sudo ln -s /Applications/MAMP/Library/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib
      I also found I had to repeat the previous point to recreate the /tmp/mysql.sock file.

To summarise the on-going usage:

  • Before using python, you need to type export DYLD_LIBRARY_PATH=/Applications/MAMP/Library/lib
  • In order to use MySQL in the terminal, you need to type /Applications/MAMP/Library/bin/mysql --host -localhost -uroot -proot

In Django, you will need to use the following DATABASE values in

    'HOST': '/Applications/MAMP/tmp/mysql/mysql.sock',
    'PORT': '8888',

One more thing. When I write apps that interface with the MAMP localhost, I need to replace localhost with the local IP address (from the Mac Network Utility program), and append port :8888.  However the CherryPy localhost has the local IP address (and  :8080). Why are the two local IP addresses different?

From here:

Any thoughts?