Extracts the "Household CBMS Core Indicators_DATA" table from the CBMS database

cbms_table.py

#-------------------------------------------------------------------------------
# Name:        module1
# Purpose:
#
# Author:      Samsung-User
#
# Created:     15/03/2012
# Copyright:   (c) Samsung-User 2012
# Licence:     <your licence>
#-------------------------------------------------------------------------------
#!/usr/bin/env python

import csv
import os
import pyodbc

raw_head = "FEATURE_ID,LDATE,NRDB_ORDER,X,Y,FEATURE_ID,LDATE,NRDB_ORDER,Oto5MEM,Oto5MAL,HHWMAL,ChildDeath,HHWChDeath,DEATHPreg,HHWDthPREG,HH_NoH2O,HH_NoTOIL,HH_MShift,HH_Squat,MEM6to12,InES6to12,HHWNotInES,MEM13to16,InHS13to16,HHWNotInHS,MEM6to16,NOSCH6to16,HHWNOSCH,MEM10Up,InSCH10Up,HHWIlliter,MEM15Up,LaborForce,EMPLOY15Up,UNEMPLOYED,HHWNoEMPLO,HHWUNEMPLO,HHWBelPov,HHWBelFood,HHFoodShor,CrimeVic,HHWCrimeV,Oto4MEM,MEM6to11,MEM12to15,MEM6to15,HHW611NOES,Per611NOES,HHW1215NHS,Per1215NHS,HHW615NSCH,Per615NSCH"
header = raw_head.split(",")

def listMDB():
    with open('mdb.list.txt','w') as mdbList:
    #ascList = []
        for r,d,f in os.walk("."):
            for files in f:
                if files.endswith(".mdb"):
                    print os.path.join(r,files)
                    out = os.path.join(r,files) + "\n"
                    mdbList.write(out)


def readList():
    mdbFile = open('mdb.list.txt')
    mdbList = []
    for line in mdbFile.readlines():
        print "Adding %s" % line
        mdbList.append(line)


    return mdbList

def processMDB(MDB):
#MDB = 'NRDBTanauanTrapiche.mdb' #Variable
    #Declare database parameters
    DRV = '{Microsoft Access Driver (*.mdb)}'
    PWD = ''
    conn = pyodbc.connect('DRIVER=%s;DBQ=%s;PWD=%s' % (DRV,MDB,PWD))
    curs = conn.cursor()

    SQL = 'SELECT * FROM "HOUSEHOLD_DATA" LEFT JOIN "Household CBMS Core Indicators_DATA" ON "HOUSEHOLD_DATA".NRDB_ORDER = "Household CBMS Core Indicators_DATA".NRDB_ORDER;' # insert your query here
    curs.execute(SQL)

    rows = curs.fetchall()

    curs.close()
    conn.close()

    #Write to file
    #File name generator
    filename = "joined." + MDB[3:-4] + "csv"
    with open(filename, 'w') as f: #variable files
        csvwrite = csv.writer(f, lineterminator='\n')
        csvwrite.writerow(header)
        for row in rows:
            csvwrite.writerow(row)
            #print row
listMDB()
mdblist = readList()
for mdb in mdblist:
    print "Now processing %s" % mdb
    processMDB(mdb)
    print "Processing complete. \n"