Tag: MySQL

How to easily set up a campaign finance database (well, kind of) or Make Python work with MAMP via MySQLdb

Posted on by ow3n

Picture 15

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

  1. 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.

  2. Install XCode

    Past installs are available on Apple Developer website.

  3. 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

  4. Install the MySQLdb driver

    After downloading and unzipping, from the directory:

    ~$ python setup.py build
    ~$ sudo python setup.py install

    Continue 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_config

    to

    /Applications/MAMP/Library/bin/mysql_config

    Especially useful is his test script for making sure that Python is indeed accessing MySQL.

  5. 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

  6. Create a directory and put the two Python files in it.
  7. 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.
  8. Install pyExcelerator

    Error: ImportError: No module named pyExcelerator

    I had to install this module next.

  9. Comment out multiple lines

    Error: NameError: name 'BaseCommand' is not defined

    In download.py comment out the following:

    The line: from django.core.management.base import BaseCommand, CommandError

    Everything from class CRPDownloadCommand(BaseCommand): to the end of the document.

  10. From the command line, run the script by typing, from the proper directory: Python sun-crp.py.
  11. 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.
-->