Unable to insert values

[Shaun Marchant]

Hi,
I'm very new to Python and have been relying on shared code off the internet. I have been able to get my python script to run OK and it appears to be connecting to Mysql OK, reading/writing files and generate SQL statements which if I copy and paste into the mysqladmin tool they work fine. Issue is when I run the python script nothing gets written to the database, other than validating the SQL statement I don't know what other steps to take to troubleshoot this issue... any suggestions?

#!/usr/bin/env python

import MySQLdb, sys, os
import string
import traceback
import logging
import zipfile

# Variables

#LOGGING=CRITICAL,ERROR,WARNING,INFO,DEBUG
loggingLevel=logging.INFO

# Log file path
logFilePath='G:/PBX CDR/CDRApp/calldata-import.log'

# Path to CDR files
cdr_path='G:/PBX CDR/'

# Database name to export data into
database='db_cdr'

# Database server
dbhost='localhost'

# User to access the database
dbuser='root'

# Password to access the database
dbpassword='removed'

# Database table to insert date into
table='calldetails'

# Zip file to archive data to
fileArchive=cdr_path+'/archive.zip'

######## Functions ############
def createCdrSQL():
# TODO:  Refactor this so it's not CDR specific.  We'll need
# to also import CMR data as well
    totalRecordCount=0
    logging.debug("Creating CDR SQL")
    cdrListing = listCallFiles(cdr_path,"cdr")
    startSQL="INSERT INTO " + table + " ("
    fileCount=0

    logging.info("Parsing %d files" % len(cdrListing))
    for file in cdrListing:
        columns=""
        fileRecordCount=0
        try:
            logging.debug("Opening file %d of %d: %s" % (fileCount,len(cdrListing),file))
            cdrFile = open(cdr_path+"/"+file,'r')
            try:
                for line in cdrFile:

                    if line.startswith('"cdrRecordType"'):
                        logging.debug("Building columns...")
                        headers = line.split(',')
                        for header in headers:
                            newHeader=header.replace('"','')
                            columns+=newHeader+','
                        #There will be a trailing comma at the end.  Remove.
                        cleanedColumns=columns.rstrip(',')
                        cleanedColumns+=")"
                        logging.debug("Column data='" + cleanedColumns + "'")

                    elif not line.startswith('INTEGER'):
                        logging.debug("Building values...")
                        values=""
                        splitLineArray = line.split(',')
                        for value in splitLineArray:
                            if value == '""':
                                value = "null"
                            newValue=value.replace('"',"'")
                            values+=newValue+','

                        #Remove the trailing comma at the end
                        cleanedValues=values.rstrip(',')
                        cleanedValues+=");"
                        logging.debug("Value data='" + cleanedValues+"'")

                        logging.debug("Building full SQL statement")
                        fullSQL=startSQL+cleanedColumns+" VALUES (" + cleanedValues
                        logging.debug("Generated SQL is: " + fullSQL)

                        logging.debug("Inserting to DB")
                        try:
                            conn.query(fullSQL)
                            fileRecordCount+=1
                        except:
                            logging.critical("Unable to insert the following SQL: " + fullSQL)
                            logging.error(traceback.print_exc(file=sys.stdout))  

            except:
                logging.warning("Could not enumerate files")
                cdrFile.close()
                logging.error(traceback.print_exc(file=sys.stdout))
        except:
            logging.critical("Could not open File")
        fileCount+=1
        totalRecordCount+=fileRecordCount
        logging.info("Inserted %d records for file %d of %d: %s" % (fileRecordCount,fileCount,len(cdrListing),file))
        logging.debug("closing file: %s" % (file))
        cdrFile.close()
        logging.info("Adding file to zip archive")
        addFileToZip(file)
    conn.close()
    logging.info("Inserted %d records total" % (totalRecordCount))         

def listCallFiles(dir,type):
    fileListing=[]
    files = os.listdir(dir)
    for file in files:
        if file.startswith(type):
            fileListing.append(file)
    return fileListing

def addFileToZip(file):
    logging.debug("Adding file %s to archive %s" % (file,fileArchive))

    #If the zip file exists, add to it, otherwise, create it.
    if os.path.exists(fileArchive):
        zippedFile=zipfile.ZipFile(fileArchive,'a')
    else:
        zippedFile=zipfile.ZipFile(fileArchive,'w')
    try:
        zippedFile.write(cdr_path+'/'+file,file)
        logging.debug("File: %s was successfully added to archive" % (file))
        zippedFile.close()
        os.remove(cdr_path+'/'+file)
    except:
        logging.error("Could not write to zip archive")
        zippedFile.close()

######### Enable Logging ###############
logging.basicConfig(level=loggingLevel,
                    format='%(asctime)s %(levelname)-8s %(message)s',
                    datefmt='%a, %d %b %Y %H:%M:%S',
                    filename=logFilePath,
                    filemode='a')

logging.info("Starting call-data import..")

########## Try to connect to the Database #############
try:
    conn = MySQLdb.connect(host=dbhost,user=dbuser,passwd=dbpassword,db=database)
except:
    # Ooops... couldn't connect
    logging.critical("Unable to connect to the database.  Terminating..")
    logging.error(traceback.print_exc(file=sys.stdout))
    sys.exit(2)

############ Insert SQL ####################################

createCdrSQL()