def writeMode():
'''Starts the writing script mode of the application.
:return: NONE
'''
# displays how Excel spreadsheet should be laid out
gui.createPopUpBox(TEMPLATE_DESCRIPTION, "600x500") # tkinter dialog box
output_string = "Choose the Excel workbook you'd like to make scripts for."
workbook = gui.openExcelFile(output_string)
validate_with_sql, additional_box_val = gui.createTwoChoiceBox(
'Would you like to validate Workbook with SQL table or generic validation?',
'Generic', 'SQL')
write_to_sql = 'SQL'
write_to_excel = 'Excel'
description = 'Would you like to write the sql scripts to a ".sql" file or to an Excel spreadsheet?'
write_to, additional_box_val = gui.createTwoChoiceBox( # write scripts to new SQL or Excel file
description, write_to_sql, write_to_excel)
if write_to == 'SQL':
save_file = writeToSQL(workbook, validate_with_sql)
elif write_to == 'Excel':
save_file = writeToExcel(workbook, validate_with_sql)
if save_file == '': # no scripts were written because there were no valid worksheets
output_string = "No files were changed. Closing program."
gui.createPopUpBox(output_string) # tkinter dialog box
def validWorkbook(workbook, validate_with_sql):
'''Cycles through worksheets in a workbook checking if they're valid.
:param1 workbook: dict
:param2 validate_with_sql: str
'''
any_valid_sheets = False # False if all spreadsheets fail validation
all_valid_sheets = True # True if all spreadsheets pass validation
additional_box_val = 0
write_script_for = "Yes"
for worksheet in workbook:
# check if worksheet is is valid and if user wants to write scripts for them
valid_worksheet, additional_box_val, write_script_for = excel_global.validWorksheet(
workbook[worksheet], validate_with_sql, worksheet,
additional_box_val, write_script_for)
# True if spreadsheet passes validation
all_valid_sheets = valid_worksheet and all_valid_sheets
if valid_worksheet: # only write to Excel if the Excel spreadsheet is a valid format
if not additional_box_val:
output_string = "VALID. This worksheet will function properly with the 'Write SQL script' mode of this program."
gui.createPopUpBox(output_string) # tkinter dialog box
any_valid_sheets = True # changes were made and need to be saved
return any_valid_sheets, all_valid_sheets
def displayWorkbookValidationResult(any_valid_sheets, all_valid_sheets):
'''Generates a window displaying the status of the completed validation.
:param1 any_valid_sheets: bool
:param2 all_valid_sheets: bool
'''
if all_valid_sheets:
output_string = "SUCCESS. All sheets hae been successfully validated."
elif not any_valid_sheets:
output_string = "FAILURE. No sheets could be successfully validated. Please review rules."
else: # some but not all spreadsheets in workbook pass validation
output_string = "CAUTION. Care must be taken building scripts with this workbook because not all sheets are in a valid form."
gui.createPopUpBox(output_string)
def validWorksheet(worksheet, validate_with_sql, title, skip_popup,
write_script_for):
'''Calls the correct function to validate the passed worksheet based on
whether a user wants to connect to SQL or not.
:param1 worksheet: pandas.core.frame.DataFrame
:param2 validate_with_sql: str
:param3 title: str
:return: bool
'''
description = "Would you like to validate/create scripts for " + \
title + " worksheet?"
yes = "Yes"
no = "No"
if not skip_popup:
write_script_for, skip_popup = gui.createTwoChoiceBox(
description,
yes,
no,
additional_box=(True, 'Do this for all spreadsheets.'))
print(write_script_for, skip_popup)
valid_template = True
if validate_with_sql == 'Generic':
if write_script_for == yes: # if the user says to write scripts for this sheet
valid_template = validateWorksheetGeneric(
worksheet) and valid_template
else:
valid_template = False
if not skip_popup:
gui.createPopUpBox(
'Validation failed. Scripts will not be written for ' +
title)
elif validate_with_sql == 'SQL':
if write_script_for == yes: # if the user says to write scripts for this sheet
valid_template = validateWorksheetSQL(worksheet) and valid_template
else:
valid_template = False
if not skip_popup:
gui.createPopUpBox(
'Validation failed. Scripts will not be written for ' +
title)
return valid_template, skip_popup, write_script_for
def saveToSQL(text_file):
'''Saves the string to a SQL file.
:param1 text_file: str
'''
file = tkinter.Tk()
# opens file explorer so user can choose file to write to
file.filename = tkFileDialog.asksaveasfilename(
initialdir="C:/",
title="Select/create file to save/write to",
defaultextension=".sql")
f = open(file.filename, 'w')
f.write(text_file)
f.close()
file.destroy()
output_string = "Scripts saved to: '" + \
str(file.filename) + "'"
gui.createPopUpBox(output_string) # tkinter dialog box
def validationMode():
'''Runs through the validation mode of the application.
:return: NONE
'''
# displays how Excel spreadsheet should be laid out
gui.createPopUpBox(TEMPLATE_DESCRIPTION, "600x500") # tkinter dialog box
workbook = gui.openExcelFile(
"Choose the Excel workbook you'd like to validate.")
validate_with_sql, additional_box_val = gui.createTwoChoiceBox( # tkinter dialog box that asks user if they want to connect to a SQL database to validate spreadsheet
'Would you like to validate Workbook with SQL table or generic validation?',
'Generic', 'SQL')
any_valid_sheets, all_valid_sheets = validWorkbook(workbook,
validate_with_sql)
displayWorkbookValidationResult(any_valid_sheets, all_valid_sheets)
def validateWorksheetSQL(worksheet):
'''Validates the data in the passed in worksheet based on a SQL table from an
open SQL connection.
:param1 worksheet: pandas.core.frame.DataFrame
:return: bool
'''
valid_template = True
tables, cursor, sql_database_name = connectToSQLServer()
if worksheet.loc['info'][0] == None or worksheet.loc['info'][
0] not in tables:
valid_template = False
gui.createPopUpBox(
'You have not specified a valid SQL table name in cell "A1"')
gui.createPopUpBox('Cannot continue SQL validation.')
return valid_template
if worksheet.loc['info'][1] not in TYPE_OF_SCRIPTS_AVAILABLE:
valid_template = False
gui.createPopUpBox(
'You have not specified a valid script type in cell "B1"')
sql_column_names, sql_column_types, column_is_nullable, column_is_identity = getSQLTableInfo(
worksheet.loc['info'][0], cursor)
for i in range(len(worksheet.loc['names'])):
if (worksheet.loc['names'][i] == None
or worksheet.loc['names'][i] not in sql_column_names) and (
worksheet.loc['include'][i] == 'include'
or worksheet.loc['where'][i] == 'where'):
valid_template = False
gui.createPopUpBox(
'You have not entered a column name where one is required in cell '
+ getExcelCellToInsertInto(i, COLUMN_NAMES_ROW_INDEX))
for i in range(len(worksheet.loc['types'])):
type = re.sub("[\(\[].*?[\)\]]", "", str(worksheet.loc['types'][i]))
if type not in SQL_STRING_TYPE and type not in SQL_NUMERIC_TYPE and type not in SQL_DATETIME_TYPE and type not in SQL_OTHER_TYPE:
if (worksheet.loc['include'][i] == 'include'
or worksheet.loc['where'][i] == 'where'):
valid_template = False
gui.createPopUpBox(
'You have not entered a supported SQL type where one is required in cell '
+ getExcelCellToInsertInto(i, COLUMN_DATA_TYPE_ROW_INDEX))
column_name = worksheet.loc['names'][i]
if column_name in sql_column_names:
sql_name_index = sql_column_names.index(column_name)
if type != sql_column_types[sql_name_index]:
valid_template = False
gui.createPopUpBox(
'The type in your spreadsheet for ' + column_name +
', does not match the type of the column in SQL in cell ' +
getExcelCellToInsertInto(i, COLUMN_DATA_TYPE_ROW_INDEX))
for i in range(len(worksheet.loc['include'])):
if worksheet.loc['include'][i] != None and worksheet.loc['include'][
i] != 'include':
valid_template = False
gui.createPopUpBox(
'You have not entered an valid string in cell ' +
getExcelCellToInsertInto(i, INCLUDE_ROW_INDEX) +
'. Valid string for row 4 is "include" or leave blank')
if worksheet.loc['info'][1] != 'delete':
if column_is_identity[i] == 0:
# if script type is insert, and column cannot be null then automatically select
if column_is_nullable[i] == 'NO' and worksheet.loc['info'][
1] not in ('select', 'update'):
if worksheet.loc['include'][i] != 'include':
valid_template = False
gui.createPopUpBox(
'You have entered an invalid string in cell ' +
getExcelCellToInsertInto(i, INCLUDE_ROW_INDEX) +
'. This column must be included')
else: # column is identity column so cannot be updated or inserted into.
# insert/update on identity column is NOT allowed
if worksheet.loc['info'][1] != 'select':
if worksheet.loc['include'][i] == 'include':
valid_template = False
gui.createPopUpBox(
'You have entered an invalid string in cell ' +
getExcelCellToInsertInto(i, INCLUDE_ROW_INDEX) +
'. This column cannot be included')
for i in range(len(worksheet.loc['where'])):
if worksheet.loc['where'][i] != None and worksheet.loc['where'][
i] != 'where':
valid_template = False
gui.createPopUpBox(
'You have not entered an valid string in a cell in cell ' +
getExcelCellToInsertInto(i, WHERE_ROW_INDEX) +
'. Valid string for row 5 is "where" or leave blank')
return validateData(worksheet) and valid_template
def validateWorksheetGeneric(worksheet):
'''Validates the data in the passed in worksheet based on a generic SQL table.
:param1 worksheet: pandas.core.frame.DataFrame
:return: bool
'''
valid_template = True
if pd.isnull(worksheet.loc['info'][0]):
valid_template = False
gui.createPopUpBox(
'You have not specified a SQL table name in cell "A1"')
if worksheet.loc['info'][1] not in TYPE_OF_SCRIPTS_AVAILABLE:
valid_template = False
gui.createPopUpBox(
'You have not specified a valid script type in cell "B1"')
for i in range(len(worksheet.loc['names'])):
if pd.isnull(worksheet.loc['names'][i]) and (
worksheet.loc['include'][i] == 'include'
or worksheet.loc['where'][i] == 'where'):
valid_template = False
gui.createPopUpBox(
'You have not entered a column name where one is required in cell '
+ getExcelCellToInsertInto(i, COLUMN_NAMES_ROW_INDEX))
for i in range(len(worksheet.loc['types'])):
type = re.sub("[\(\[].*?[\)\]]", "", str(worksheet.loc['types'][i]))
if type not in SQL_STRING_TYPE and type not in SQL_NUMERIC_TYPE and type not in SQL_DATETIME_TYPE and type not in SQL_OTHER_TYPE:
if (worksheet.loc['include'][i] == 'include'
or worksheet.loc['where'][i] == 'where'):
valid_template = False
gui.createPopUpBox(
'You have not entered a supported SQL type where one is required in cell '
+ getExcelCellToInsertInto(i, COLUMN_DATA_TYPE_ROW_INDEX))
for i in range(len(worksheet.loc['include'])):
if not (pd.isnull(worksheet.loc['include'][i])
) and worksheet.loc['include'][i] != 'include':
valid_template = False
gui.createPopUpBox(
'You have not entered an valid string in cell ' +
getExcelCellToInsertInto(i, INCLUDE_ROW_INDEX) +
'. Valid string for row 4 is "include" or leave blank')
for i in range(len(worksheet.loc['where'])):
if not (pd.isnull(worksheet.loc['where'][i])
) and worksheet.loc['where'][i] != 'where':
valid_template = False
gui.createPopUpBox(
'You have not entered an valid string in a cell in cell ' +
getExcelCellToInsertInto(i, WHERE_ROW_INDEX) +
'. Valid string for row 5 is "where" or leave blank')
return validateData(worksheet) and valid_template