How to easily set up a campaign finance database (well, kind of) or Make Python work with MAMP via MySQLdb
Monday, August 9th, 2010
I’ve been trying for a few hours to run a Python script from The Sunlight Foundation Labs which downloads (and updates) a campaign finance database from the Center for Responsive Politics. See their original post for more information.
In the process of getting this working I accidentally broke a working copy of MySQL and overwrote a database installed on my MBP (which I had stupidly not backed-up since last year). FYI, you can rebuild any MySQL database with the original .frm, .MYD, and .MYI files if you 1. Recreate the database in the new install of MySQL and 2. Drag the files into the mysql data folder.
I struggled quite a bit getting Python to work with MySQL via MySQLdb. I’m documenting some of the headaches and resolutions here in case they are useful. I’ve tried to include error messages for searches as well.
The Sunlight Foundation instructions require Python and MySQL, but don’t mention you have to have already wrestled with the madness involved in installing Django on your machine. Here is what I did to get it working on my MacBook Pro Intel Core 2 Duo. I’ve included their original instructions with my own (and a host of others).
Instructions
- Install MAMP.
While I had working installations of MySQL and Python (via installers on respective sites), I couldn’t get Python to connect to MySQL via MySQLdb. I decided to download and try MAMP for a clean start.
- Install XCode
Past installs are available on Apple Developer website.
- Install setuptools
Required for the MySQLdb driver. Remove the .sh extension from the filename (setuptools-0.6c11-py2.7.egg.sh) and in a shell:
~$ chmod +x setuptools-0.6c11-py2.7.egg
~$ ./setuptools-0.6c11-py2.7.egg - Install the MySQLdb driver
After downloading and unzipping, from the directory:
~$ python setup.py build
~$ sudo python setup.py installContinue following the advice of this post to the end How to install Django with MySQL on Mac OS X.
I also followed another piece of advice in Python MySQL on a Mac with MAMP to change the mysql_config.path from:
/usr/local/mysql/bin/mysql_configto
/Applications/MAMP/Library/bin/mysql_configEspecially useful is his test script for making sure that Python is indeed accessing MySQL.
- Create a symbolic link between Python and MySQL in MAMP
This is required in order to use a socket to connect to the MySQL. See How to install MySQLdb on Leopard with MAMP for more information.
~$ sudo ln -s /Applications/MAMP/tmp/mysql/mysql.sock /tmp/mysql.sock - Create a directory and put the two Python files in it.
- Modify the top of the sun_crp.py file to set certain parameters–your login credentials for the CRP download site and your MySQL database information.
- Install pyExcelerator
Error:
ImportError: No module named pyExceleratorI had to install this module next.
- Comment out multiple lines
Error:
NameError: name 'BaseCommand' is not definedIn download.py comment out the following:
The line:
from django.core.management.base import BaseCommand, CommandErrorEverything from
class CRPDownloadCommand(BaseCommand):to the end of the document. - From the command line, run the script by typing, from the proper directory: Python sun-crp.py.
- It will take several hours to download and extract the data, especially the first time it’s run. But after that, you’re good to go.
