MySQL for Python

MySQLdb is the Python DB API-2.0 interface. This module has proven very useful for coding db queries into procedures in Python (my preferred coding language). Below is a simple procedure using this module that performs a query and writes the result to a file, where it can be used for analysis in R:

#!/usr/bin/python

import commands
import os
import sys
import MySQLdb

try:
    connection = MySQLdb.connect(db="yourdb",read_default_file="~/.my.cnf" )
except MySQLdb.Error, e:
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exist (1)

query = "select subject, vertex, speech_lag, emblem_lag, embspeech_lag from ccf_phase2_lh where seed_region = 'IDEAL' and vertex between 1 and 5;"

outfile = os.getcwd()+"/test_aov.lh.txt"
cursor = connection.cursor()
cursor.execute(query)
result = list(cursor.fetchall())
bully = ''
for rr in list(result):
    bully += str(rr[0])+" "+str(rr[1])+" "+str(rr[2])+" "+str(rr[3])+" "+str(rr[4])+"\n"

file = open(outfile,'w' )
file.write(bully)
file.close()

MySQLdb can be found here.