How to easily set up a campaign finance database
By Luke Rosiak Mar 26 2010 2:34 p.m. 3 commentsNo one does federal-level campaign finance better than the Center for Responsive Politics, and for the last year or so, they've outdone themselves by making all of their databases--millions of records that a staff of human beings tirelessly cleans up--available, for free, in their entirety.
The only downside? The scale of CRP's research is so wide that we're talking dozens of frequently-updated tables with hundreds of fields. Downloading each CSV and importing into a database with fields properly named and sized is tedious and time-consuming.
The Sunlight Foundation has written a simple script that allows reporters and database researchers to download everything they need by running one command. You can run it periodically, and it checks whether your databases are outdated, re-downloading them only if necessary.
You need Python and MySQL installed on your computer or server to use it. 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.
Then, 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.
IMPORTANT: To avoid overloading CRP's servers with these very large files, please run this script only late at night or other off-peak times.
If you want to manipulate the data with Microsoft Access, here's how:
- Set up an ODBC connection by going to Start-> Control Panel -> Administrative Tools -> Data Sources (ODBC). Add new source.
- On the 'create new data source' dialog window, scroll through the list of drivers to select MySQL ODBC near the end. If it's not there, you need to download the mysql connector. Then, it should appear. Fill in the connection information--the same MySQL connection info you supplied in the python file.
- In Microsoft Access, go to the External Data tab, click more, and ODBC. When it asks you if you want to import or link, select link, then select all the tables.
If you're unfamiliar with Python or MySQL: You can download Python and MySQL for Windows for free. (If you're on Linux, you already have them.) To create a new default MySQL user, go to the Command Line terminal and type:
> mysql
> create database sun_crp;
Your username is the default "root" then, and your password is blank; the database is sun_crp. You open up the sun_crp.py file in Notepad and change those settings accordingly.
Then from a command line in the folder housing the two python files, simply type
> python sun-crp.py
Again, you can download the two files here. Enjoy!
Search the Blog
Related Content
Real Time Ticker
Recent Posts
Reporting we're watching
- OpenSecrets: Millionaire Freshmen Make Congress Even Wealthier
- Sunlight Foundation: Why does the IRS regulate political groups? A look at the complex world of campaign finance
- Sunlight Foundation: Update on FedBizOpps data
- OpenSecrets: Site Spotlight #3: Anomaly Tracker
- Sunlight Foundation: OpenGov Voices: Day of Action on Sunday: Know the influence behind your grocery purchase
I am trying to use this script to sync data. but facing following error. $ python sun-crp.py Traceback (most recent call last): File "sun-crp.py", line 15, in ? from download import CRPDownloader File "/home/muhammad/opensecrets/python/download.py", line 8, in ? from django.core.management.base import BaseCommand, CommandError ImportError: No module named django.core.management.base $ Please help how to fix it. Regards
Muhammad, good question. That's a bit of Django which isn't really necessary, and probably shouldn't have been included for simplicity's sake. You should be able to comment out or delete the line "from django.core..." as well as everything beginning "class CRPDownloadCommand(BaseCommand):" near the end of download.py and it should work. Another alternative would be to install Django.
Thanks a lot for providing this. Finally got this working and made a post about it here: http://owenmundy.com/blog/2010/08/how-to-easily-set-up-a-campaign-finance-database-well-kind-of/ The script is erroring on the members table. Not sure why... INFO:root:This FAILED:INSERT INTO members VALUES ( %s, %s, %s, %s);['', 'CRPName', 'Party', 'Office'] INFO:root:This FAILED:INSERT INTO members VALUES ( %s, %s, %s, %s);['N00007665', 'Abercrombie, Neil', 'D', 'HI01']