How to easily set up a campaign finance databaseBy Luke Rosiak Mar 26 2010 2:34 p.m. 3 comments
No 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:
> 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
Real Time Ticker
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