Connecting to MySQL using Python on Linux and write a csv file from a query

I've been asked to create a ETL job from a Amazon RDS MySQL Database. Since I can't directly write in a Amazon RDS server (no rights to the local folders), I decided to use Python in another terminal to do this, but you will need a few steps before making the code work.

First, make sure that you have python and the dev dependencies:

apt-get install python
apt-get install python-dev

Then you will need some mysql stuff...

sudo apt-get install libmysqlclient-dev

Then you will need the PythonMysql gear, that you need to download from http://sourceforge.net/projects/mysql-python

gunzip MySQL-python-1.2.4.tar.gz
tar -xvf MySQL-python-1.2.4.tar
cd MySQL-python-1.2.4

Then just build and install:

python setup.py build
python setup.py install

And finally... create a empty file with the following code (I used pigments to make it nicer... ):

#!/usr/bin/python
import MySQLdb
import csv
import sys

db = MySQLdb.connect(host="yourserver", # your host, usually localhost
                     user="user", # your username
                      passwd="pass", # your password
                      db="databasename") # name of the data base

# create cursor
cur = db.cursor() 

# put your query here
cur.execute("SELECT * FROM  information_schema.KEY_COLUMN_USAGE limit 10")

# write to a csv file

rows = cur.fetchall()
fp = open('file.csv', 'w')
myFile = csv.writer(fp)
myFile.writerows(rows)
fp.close()

If you named your file as "hello", you can execute like this:
python hello

Check out my new book about R Language