def mysql_table_update(input_file, output_file):

##################################
    
    #file check variables
    column_length_counter = 0
    check_line_length_list = []
    check_blank_entry_list = []
    check_column_label_flag = False
    check_column_labels = []
    check_column_label_index = []
    missing_column_label = []
    
    #post file check variables
    matrix = {}
    rows = 0
    columns = 4
    failure_counter = 0
    failure_list = []
    
##################################
    #macros
    column_label_mysql = [ "table_name", "marker_id", "field", "new_value"]
    excel_column_label_dictionary = {1:'A', 2:'B', 3:'C', 4:'D'}
    field = []    
    
    try:
        check_file = open(input_file)


        while True:
            check_line = check_file.readline()
            if check_line == '':
                break
            if '\n' in check_line:
                check_line = check_line[:-1]
            if '\r' in check_line:
                check_line = check_line[:-1]
            check_line = check_line.split('\t')
            check_line_length_list.append(len(check_line))

            
            for i in range (0,len(check_line)):
                if (check_line[i] == ''):
                    check_blank_entry =  column_length_counter +1, i+1
                    check_blank_entry_list.append(check_blank_entry)

            
            if (column_length_counter == 0):
                check_column_labels = check_line
                for i in range (0, len(check_line) ):
                    if (check_line[i] in column_label_mysql):
                        continue
                    else:
                        check_column_label_index.append(i)
                        check_column_label_flag = True



            column_length_counter = column_length_counter + 1

    except:
        print "DID NOT FIND FILE!:  " + input_file
        return
    
    check_file.close()

    #Blank entries were found
    if (len(check_blank_entry_list) != 0):
        print "\nBlank entries were found at the following locations\n\n"
        for i in range (0, len(check_blank_entry_list)):
            print "Row", repr(check_blank_entry_list[i][0]), ", Column" , excel_column_label_dictionary[check_blank_entry_list[i][1]]
        return

    #the following code checks that all lines in the file have an equal length.
    #Matrices handed to the script must be rectangular
    for i in range (0, len(check_line_length_list)):
        for j in range (i+1 , len(check_line_length_list)):
            if (check_line_length_list[i] != check_line_length_list[j]):
                print "\nThe matrix is not rectangular check the list of lines for discrepancies\n\n"
                for k in range (0, len(check_line_length_list)):
                    print "line", k+1, "has", check_line_length_list[k], "entries" 
                print "All lines must have the same number of entries"
                return


    #The column labels do not match the defined column labels in column_label_lgm1
    if (check_column_label_flag == True):
        print "\nThe following column labels are incorrect\n\n"
        
        for i in check_column_label_index:        
            print "Column", excel_column_label_dictionary[i+1], "The label is", check_column_labels[i], "it should be", column_label_mysql[i], '\n'
        return

    #Checks that all columns are in the file 
    for i in column_label_mysql:
        if i in check_column_labels:
            continue
        else:
            check_column_label_flag = True
            missing_column_label.append(i)
    if (check_column_label_flag == True):
        print "\nThe following columns are missing or they are not in the correct order.\n\n"
        for i in range (0,len(missing_column_label)):
            print missing_column_label[i]
        return



    #After file is checked for serious errors 
    #The file is read line by line into a 2d matrix
    try:
        text_file = open(input_file)
        
        while True:
            line = text_file.readline()
            if line == '':
                break
            if '' in line:
                line = line[:-1]
            if '\r' in line:
                line = line[:-1]
            line = line.split('\t')

            #removes all whitespace characters from the front and rear of the entry
            for i in range (0, len(line)):
                line[i].lstrip()
                line[i].rstrip()
            if (line[0] != '-'):
                for i in range (0,len(line)):
                    matrix[rows,i] = line[i]
                                 
            else:
                failure_string = (rows + 1) , "Row " +  repr(rows + 1)  +  " was ignored because a table name was not specified" 
                failure_list.append(failure_string)
                failure_counter = failure_counter + 1
                for j in range(0,columns):
                    matrix[rows, j] = "ignored_row"
            rows = rows + 1
        text_file.close()
    except:
            print "DID NOT FIND FILE:  " + input_file
            return



    #marker_id
    for i in range(1, rows):
        if (matrix[i,0] == "ignored_row"):
            continue

        if matrix[i,1] == '-':
            failure_string = (i+1) , "Row " + repr(i+1)  + ", test_table " +  matrix[i,0] + " has a marker_id which is not recognized."
            failure_list.append(failure_string)
            failure_counter = failure_counter + 1
    
##    #field 
##    for i in range(1, rows):
##        if (matrix[i,0] == "ignored_row"):
##            continue
##
##        if matrix[i,2]in field:
##            continue
##        else:
##            failure_string = (i+1) , "Row " + repr(i+1)  + ", test_table " +  matrix[i,0] + " has a field which is not recognized."
##            failure_list.append(failure_string)
##            failure_counter = failure_counter + 1
        

##
##    #new_value
##    for i in range(1, rows):
##        if (matrix[i,0] == "ignored_row"):
##            continue
##
##        if matrix[i,3]in new_value:
##            continue
##        else:
##            failure_string = (i+1) , "Row " + repr(i+1)  + ", test_table " +  matrix[i,0] + " has a new_value which is not recognized."
##            failure_list.append(failure_string)
##            failure_counter = failure_counter + 1
    


    #Newline
    if (os.name == 'nt'):
        _newline = '\r\n'
    elif (os.name == 'mac'):
        _newline = '\r'
    else:
        _newline = '\n'
                  
                
    #Stops file output and outputs failures to the screen       
    if (failure_counter != 0):
        failure_list.sort()
        for i in range (0,len(failure_list)):
            print failure_list[i][1]
            print _newline
        print "\nOutput file was not created, check the lines above for data errors."
        failure_file.close()
        return

##   #Create table source output file
##    table_source_output = open(output_file + "_bb" + ".source", "wbU")
##
##    table_source_output.write("CREATE TABLE IF NOT EXISTS ")
##    table_source_output.write(output_file)
##    table_source_output.write("_bb")
##    table_source_output.write(_newline)
##    table_source_output.write("( marker_id VARCHAR(64) ,")
##    table_source_output.write(_newline)
##    table_source_output.write("  field_name VARCHAR(64),")
##    table_source_output.write(_newline)
##    table_source_output.write("  old_value VARCHAR(128),")
##    table_source_output.write(_newline)
##    table_source_output.write("  new_value VARCHAR(128),")
##    table_source_output.write(_newline)
##    table_source_output.write("  date VARCHAR(24)")
##    table_source_output.write(_newline)
##    table_source_output.write(");")
##
##    table_source_output.close()

    





   #Create output file
    file_output = open(output_file + ".source", "wbU")
    
    for i in range (1,rows):
	if matrix[i,2] == "cross":
	    matrix[i,2] = "cross_type"
        #obtaining old value and storing in @temp
        bb_table_name= matrix[i,0] + "_bb"
        file_output.write("SELECT @temp:=")
        file_output.write(matrix[i,2])#field
        file_output.write(" FROM ")
        file_output.write(matrix[i,0])
        file_output.write(" WHERE marker_id='")
        file_output.write(matrix[i,1])
        file_output.write("';")
        file_output.write(_newline)

        #code to write entire line into bb_table
        file_output.write("INSERT INTO ")
        file_output.write(bb_table_name)
        file_output.write(" VALUES(NULL, '")
        file_output.write(matrix[i,1])
        file_output.write("', '")
        file_output.write(matrix[i,2])
        file_output.write("' , @temp, '")
        file_output.write(matrix[i,3])
        file_output.write("', NOW() );")
        file_output.write(_newline);

        #code to write updated value into table
        file_output.write("UPDATE ")
        file_output.write(matrix[i,0])
        file_output.write(" SET ")
        file_output.write(matrix[i,2])
        file_output.write("='")
        file_output.write(matrix[i,3])
        file_output.write("' WHERE marker_id='")
        file_output.write(matrix[i,1])
        file_output.write("';")
        file_output.write(_newline)
    file_output.close()
    print "\nprogram exited succesfully"
    






import array
import string
import os
import sys
import string
if __name__ == "__main__":
        
    if len(sys.argv) <= 2 or len(sys.argv) > 3:
        print "Program usage: "
        print "Input_file, Output_file"
	exit
    else:
        input_file = sys.argv[1]
	output_file = sys.argv[2]
        mysql_table_update(input_file, output_file)