def __init__(
self,
wtserver,
wtdatabase,
wttable,
filepath=None,
filename=None,
file_delimiter="\t",
data_header_list=[],
data_contents_dictionary={},
):
self.wtserver = wtserver
self.wtdatabase = wtdatabase
self.wttable = wttable
self.filepath = filepath
self.filename = filename
self.file_delimiter = file_delimiter
self.data_header_list = data_header_list
self.data_contents_dictionary = data_contents_dictionary
easylogging.setupLogging(logoutput_path=logdirectory) # __logs file will be located in Q:\Efficiency
self.versatilelogger = logging.getLogger("versatile")
self.errorlogger = logging.getLogger("error")
self.consolelogger = logging.getLogger("consoleinfo")
# Standard output for multiple data input options. Create etl header and contents for sql upload from either a file or data dictionary {R:{C:V}}
self.header_list = []
self.contents_dictionary = {}
self.original_contents_dictionary = (
{}
) # Used in excpetion report output. Unaltered, excepted items will write to csv file
try:
if len(self.data_header_list) != 0 and len(self.data_contents_dictionary) != 0:
# ***Consider update. Data dictionary input is not refined. Assuming user input will enter 'Refined' data according to SQL standards.
self.header_list = self.data_header_list # [header.strip().title() for header in self.data_header_list]
self.contents_dictionary = (
self.data_contents_dictionary
) # {str(header).strip().title():self.data_contents_dictionary[header] for header in self.data_contents_dictionary}
self.original_contents_dictionary = (
self.data_contents_dictionary
) # {str(header).strip().title():self.data_contents_dictionary[header] for header in self.data_contents_dictionary}
elif self.filepath != None and self.filename != None:
file = Filer(self.filepath, self.filename)
self.header_list = file.fileExtract(output="GetRefinedHeader", delimiter=file_delimiter)
self.contents_dictionary = file.fileExtract(output="GetRefinedContents", delimiter=file_delimiter)
self.original_contents_dictionary = file.fileExtract(
output="GetRefinedContents", delimiter=file_delimiter
)
else:
self.versatilelogger.info("No input data defined")
except Exception as ex:
self.errorlogger.exception(ex)
self.versatilelogger.error(ex)
raise
def __init__(self,
wtserver,
wtdatabase,
wttable,
filepath=None,
filename=None,
file_delimiter='\t',
data_header_list=[],
data_contents_dictionary={}):
self.wtserver = wtserver
self.wtdatabase = wtdatabase
self.wttable = wttable
self.filepath = filepath
self.filename = filename
self.file_delimiter = file_delimiter
self.data_header_list = data_header_list
self.data_contents_dictionary = data_contents_dictionary
easylogging.setupLogging(
logoutput_path=logdirectory
) #__logs file will be located in Q:\Efficiency
self.versatilelogger = logging.getLogger("versatile")
self.errorlogger = logging.getLogger("error")
self.consolelogger = logging.getLogger("consoleinfo")
# Standard output for multiple data input options. Create etl header and contents for sql upload from either a file or data dictionary {R:{C:V}}
self.header_list = []
self.contents_dictionary = {}
self.original_contents_dictionary = {
} # Used in excpetion report output. Unaltered, excepted items will write to csv file
try:
if len(self.data_header_list) != 0 and len(
self.data_contents_dictionary) != 0:
#***Consider update. Data dictionary input is not refined. Assuming user input will enter 'Refined' data according to SQL standards.
self.header_list = self.data_header_list #[header.strip().title() for header in self.data_header_list]
self.contents_dictionary = self.data_contents_dictionary #{str(header).strip().title():self.data_contents_dictionary[header] for header in self.data_contents_dictionary}
self.original_contents_dictionary = self.data_contents_dictionary #{str(header).strip().title():self.data_contents_dictionary[header] for header in self.data_contents_dictionary}
elif self.filepath != None and self.filename != None:
file = Filer(self.filepath, self.filename)
self.header_list = file.fileExtract(output='GetRefinedHeader',
delimiter=file_delimiter)
self.contents_dictionary = file.fileExtract(
output='GetRefinedContents', delimiter=file_delimiter)
self.original_contents_dictionary = file.fileExtract(
output='GetRefinedContents', delimiter=file_delimiter)
else:
self.versatilelogger.info('No input data defined')
except Exception as ex:
self.errorlogger.exception(ex)
self.versatilelogger.error(ex)
raise
def insertTable(self,
tableexist,
action='Insert',
email='*****@*****.**',
additems_dictionary={}):
'''Map and load data from external source into SQL table.
Dependent on cubi.file, cubi.sql, cubi.sqlwriter, cubi.dataanalysis'''
#Set up logging to output and track all uncaught errors
vlogger = self.versatilelogger
elogger = self.errorlogger
clogger = self.consolelogger
#Begin main logic of function
try:
script_start = datetime.datetime.now()
server = self.wtserver
database = self.wtdatabase
table = self.wttable
parameter_dictionary = {
'Server': server,
'DataBase': database,
'Table': table,
'Filepath': self.filepath,
'Filename': self.filename,
'Delimiter': self.file_delimiter,
'data_header_list': self.data_header_list,
'data_contents_dictionary': self.data_contents_dictionary,
'TableExist': tableexist,
'Action': action,
'Delimiter': self.file_delimiter,
'Email': email,
'AddItems': additems_dictionary
}
vlogger.info('Method Start with parameters {0}'.format(
parameter_dictionary))
####
# Dependencies
####
Header = self.header_list
Contents = self.contents_dictionary
OriginalContents = self.original_contents_dictionary
refinedadditems_dictionary = {
header.strip().title(): additems_dictionary[header]
for header in additems_dictionary
}
# Add headers from additems_dictionary to header
for newheader in refinedadditems_dictionary:
Header.append(newheader)
# Add key:value pairs from additems_dictionary to contents
for updaterow in Contents:
Contents[updaterow].update(refinedadditems_dictionary)
#DataAnalysis Class
Data = DataAnalysis(Header, Contents)
InputHeaderDataTypes_Dictionary = Data.dataType(
output='GETSQLDATATYPES')
#SQL action. Process table actions prior to insert.
sql = SQL(server, database, table)
#Prompt to ask if SQL table already exists before adding conents. Will create table if prompted to.
##This will need to change to look for table automatically
''' Changed this logic for automation
TableExistPrompt = False
while TableExistPrompt == False:
TableExist = input('Does the table already exist? (Y/N) ').upper()
if TableExist == 'Y':
TableExistPrompt = True
continue
elif TableExist == 'N':
sql.createTableAdmin(Header, InputHeaderDataTypes_Dictionary)
TableExistPrompt = True
else:
print("I don't understand...Please enter 'Y' or 'N'")
'''
if tableexist.upper() == 'N':
sql.createTableAdmin(Header, InputHeaderDataTypes_Dictionary)
elif tableexist.upper() == 'Y' and action.upper() == 'REPLACE':
sql.alter_table('DELETE')
elif tableexist.upper() == 'Y':
pass
else:
vlogger.info(
'Did not properly define Table Exist parameter {0}'.format(
{'TableExist': tableexist}))
#sys.exit() #Do we want to exit program or try to find match if table is defined?
# SQL Class - This pulls information after table creation (if necessary) so that we can have most updated table info
sql1_object = SQL(server, database, table)
SQLHeader_List = sql1_object.tableLookup('GetInsertHeader')
SQLHeaderDataType_dictionary = sql1_object.tableLookup(
'GetSQLDataTypes')
#Initialize transformation list for user input mapping definitions. Define Update, Add, and Delete columns
#Need to lookup datatype to ensure if will match sql
Transform_List = []
for HeaderItem in Header:
ProperHeaderItem = HeaderItem.title()
Temp_Transform_Dict = {}
if ProperHeaderItem.strip() in SQLHeader_List:
Temp_Transform_Dict['Input'] = HeaderItem
vlogger.info(
'{0} column mapped. Continuing to next column'.format(
ProperHeaderItem.strip()))
# This logic will need to change for automation
else:
#Typo error catch. Restart AddColumn prompt if input is not y or n.
AddColumnAnswer = False
while AddColumnAnswer == False:
#print(CSVheader+': No match found')
##Question 1: Input Column
AddColumn = input(
'{0} is not defined in {1}. Would you like to input this column? (Y/N) '
.format(ProperHeaderItem, table)).upper()
#Map CSV column header names. Loop through mapping questions until questions are answered correctly
if AddColumn == 'Y':
#Typo error catch. Restart HeaderExists prompt if input not y or n
ColumnExistAnswer = False
while ColumnExistAnswer == False:
##Question 2: Header already in table
print(table + ' columns:', SQLHeader_List)
HeaderExists = input(
'Does {0} already exist in the SQL table? (Y/N) '
.format(ProperHeaderItem)).upper()
#UPDATE Dictionary {'Update':{ColumnBefore:ColumnAfter}}
if HeaderExists == 'Y':
HeaderMatchAnswer = False
while HeaderMatchAnswer == False:
MapHeader = input(
'Please enter SQL column name as you see it in defined SQL column list: '
).title().strip()
#Include if else to error if header is not in table
MapDict = {}
if MapHeader in SQLHeader_List:
MapDict[HeaderItem] = MapHeader
Temp_Transform_Dict[
'Update'] = MapDict
print(
'Mapped {0} to {1}. Continuing to next column.'
.format(
ProperHeaderItem,
MapHeader))
HeaderMatchAnswer = True
else:
print(
MapHeader.upper() +
' does not match a SQL column name. Please reenter.'
)
ColumnExistAnswer = True
#ADD Dictionary {'Add':{ColumnAdd:DataType}}
elif HeaderExists == 'N':
AlterTableAnswer = False
while AlterTableAnswer == False:
#print('Datatype listing:',DataTypeList)
#print('Suggested DataType: {0} {1}'.format(HeaderItem, InputHeaderDataTypes_Dictionary[HeaderItem]))
AddColumnType = 'VARCHAR(MAX)'
if HeaderItem in InputHeaderDataTypes_Dictionary:
AddColumnType = InputHeaderDataTypes_Dictionary[
HeaderItem]
else:
AddColumnType = input(
"Couldn't find a good data type for column. What data type would you like for new column {0}? "
.format(ProperHeaderItem))
VerifyAdd = input(
'Please verify. Adding {0} column with the datatype {1} into the {2} table. Would you like to continue table alter? (Y/N) '
.format(ProperHeaderItem.upper(),
AddColumnType.upper(),
table.upper())).upper()
if VerifyAdd == 'Y':
HeaderType_dict = {}
HeaderType_dict[
ProperHeaderItem.strip(
)] = AddColumnType
Temp_Transform_Dict[
'Add'] = HeaderType_dict
sql.alter_column(
'AddColumn', ProperHeaderItem,
AddColumnType)
print(
'Adding', ProperHeaderItem,
'. Continuing to next column')
AlterTableAnswer = True
elif VerifyAdd == 'N':
VerifyCancel = input(
'Cancelling table alter and {0} contents add. Continue with cancellation? (Y/N) '
.format(
ProperHeaderItem)).upper()
if VerifyCancel == 'Y':
print(
'Cancelled table alter. Column and contents will not be added to table.'
)
Temp_Transform_Dict[
'Delete'] = HeaderItem
AlterTableAnswer = True
else:
AlterTableAnswer = False
else:
print(
'Invalid response. Please enter Y or N'
)
AlterTableAnswer = False
ColumnExistAnswer = True
else:
print(
'Invalid response. Please enter Y or N'
)
ColumnExistAnswer = False
AddColumnAnswer = True
elif AddColumn == 'N':
print(
ProperHeaderItem +
' will not be mapped. Continuing to next column. '
)
Temp_Transform_Dict['Delete'] = HeaderItem
AddColumnAnswer = True
else:
print('Please enter Y or N')
Transform_List.append(Temp_Transform_Dict)
'''Input Load Contents'''
vlogger.info('Transform List: {0}'.format(Transform_List))
#Using transform_List. Complete actions on input dictionary. Logic is scrubbing contents columnwise
for action in Transform_List:
for item in Contents:
if 'Input' in action:
ActionItem = action['Input'].title()
for head in Contents[item]:
if ActionItem == head.title():
Contents[item][ActionItem.strip(
)] = Contents[item].pop(head)
#buildTranContents_Dictionary[ActionItem.strip()] = Contents[item][head] #Add file contents to new dictionary
elif 'Delete' in action and action['Delete'] in Contents[
item]:
ActionItem = action['Delete']
del Contents[item][ActionItem]
#pass
elif 'Update' in action:
for k, v in action['Update'].items():
RemoveKeyName = k
UpdateKeyName = v
Contents[item][UpdateKeyName.strip(
)] = Contents[item].pop(RemoveKeyName)
#buildTranContents_Dictionary[UpdateKeyName] = Contents[item][RemoveKeyName] #Add
elif 'Add' in action: #Add headers that don't exist in SQL table
ActionItem = None
for key in action[
'Add']: #Need to loop through action nested dictionary {Add: {Header: DataType}}
ActionItem = key
for head in Contents[item]:
if ActionItem == head.title():
Contents[item][ActionItem.strip(
)] = Contents[item].pop(head)
#buildTranContents_Dictionary[ActionItem.strip()] = Contents[item][head]
#This looks back to SQL table for any potential columns that may have been added
sql2_object = SQL(server, database, table)
UpdatedSQLHeaderDict = sql2_object.tableLookup('GetSQLDataTypes')
vlogger.info(
'UpdatedSQLHeaderDict: {0}'.format(UpdatedSQLHeaderDict))
vlogger.info('Beginning SQL Upload')
#Section will upload Input to SQL row by row. Use this portion to filter any unwanted items
query_start = datetime.datetime.now()
connection = pymssql.connect(server=server, database=database)
cursor = connection.cursor()
n = 0
exceptionrows_dictionary = {}
for row in Contents:
insert_list = []
insert_header = []
exception_WriterRow = {}
error_column = None
error_value = None
#exception_header = []
exceptionitem_dictionary = {}
try:
# need to error out if add statement doesnt work!!
for colname in UpdatedSQLHeaderDict:
if colname in Contents[row]:
insertColumn = colname.strip()
insertValue = Contents[row][colname]
error_value = Contents[row][colname]
error_column = colname
if len(str(insertValue)) > 0 and type(
insertValue).__name__ != 'NoneType':
if UpdatedSQLHeaderDict[
colname] == 'int' or UpdatedSQLHeaderDict[
colname] == 'smallint' or UpdatedSQLHeaderDict[
colname] == 'bigint' or UpdatedSQLHeaderDict[
colname] == 'tinyint':
insert_header.append(insertColumn)
#Clean and insert int values to SQL
ScrubbedInsertValue = None
if insertValue == '-':
ScrubbedInsertValue = 0
else:
ScrubbedInsertValue = int(insertValue)
insert_list.append(ScrubbedInsertValue)
#exception_header.append(colname)
#exception_list.append(int(InputFile_Dict[row][colname]))
elif UpdatedSQLHeaderDict[
colname] == 'decimal' or UpdatedSQLHeaderDict[
colname] == 'money':
insert_header.append(insertColumn)
ScrubbedInsertValue = None
if insertValue == '-':
ScrubbedInsertValue = 0
else:
ScrubbedInsertValue = float(
insertValue)
insert_list.append(ScrubbedInsertValue)
#exception_header.append(colname)
#exception_list.append(float(InputFile_Dict[row][colname]))
else:
insert_header.append(insertColumn)
if fnmatch.fnmatch(Contents[row][colname],
"*'*"):
insert_list.append(
(insertValue.replace("'",
" ").strip()))
else:
insert_list.append(insertValue)
#else:
#print('ColName', colname, 'Value', InputFile_Dict[row][colname])
#insert_header.append(colname)
#insert_list.append(InputFile_Dict[row][colname])
#else:
#print('In row {0}, {1} was not found. Contents will fill as NULL if possible'.format(row,colname))
insert = "INSERT INTO {0} ({1}) VALUES ({2})".format(
table, ', '.join(insert_header),
repr(insert_list).strip('[]'))
###Uncomment these when comfortable with upload
cursor.execute(insert)
connection.commit()
n += 1
except Exception as inst:
'''Grab row number, and contents, plug into exception rows dictionary to write to csv'''
exceptionrows_dictionary[row] = OriginalContents[row]
'''Is all this necessary if we are creating an exception file?'''
Error_Dict = {}
clogger.error(
'Error occurred on row: {0} Error {1}'.format(
row, inst))
#print( "Error Column:",error_column, "Error Value:",error_value,"Error Value Length:",len(error_value),"Error value type:",type(error_value))
for index in range(len(insert_list)):
cell_value = insert_list[index]
head_value = insert_header[index]
column_type = UpdatedSQLHeaderDict[head_value]
Error_Dict[head_value] = {}
#print('cell_value',cell_value,'head_value',head_value,'column_type',column_type)
if type(cell_value) == str:
Error_Dict[head_value][cell_value] = (
column_type, len(cell_value))
else:
Error_Dict[head_value][cell_value] = (
column_type, type(cell_value))
clogger.error('Values: {0}'.format(insert_list))
elogger.exception(inst)
vlogger.error(inst)
#clogger.error(Error_Dict)
#raise
if len(exceptionrows_dictionary) > 0:
# Create exception file with rows that were not transferred to table
exceptionfilename = self.filename.split('.')[0]
exception_file = Filer(
self.filepath, '{0}_Exceptions'.format(exceptionfilename))
exception_file.csvCreator(Header, exceptionrows_dictionary)
query_finish = datetime.datetime.now()
query_runtime = query_finish - query_start
connection.close()
script_finish = datetime.datetime.now()
python_runtime = (script_finish - script_start) - (query_finish -
query_start)
script_runtime = script_finish - script_start
vlogger.info(
"Upload to table {0} completed. Connection Closed.".format(
table))
vlogger.info(
"Input file size: {0} row(s). {1} row(s) imported successfully"
.format(len(Contents), n))
clogger.info(
'Program Runtime: {0} Query Runtime: {1} Python Runtime: {2}'.
format(script_runtime, query_runtime, python_runtime))
#Catch and log any errors that cause program to fail
except Exception as ex:
elogger.exception(ex)
vlogger.error(ex)
#Send email if error occurs
frm = '*****@*****.**'
to = email
subject = 'Error occurred in {0}'.format(__name__)
message = 'Exception error occurred within {1}.\n\nCheck logs for more info.\nTime: {0}\n\n{2}'.format(
str(datetime.datetime.now()), __file__, traceback.format_exc())
easylogging.sendMail(frm, to, subject, message)
raise
finally:
vlogger.info('Method Complete')
def insertTable(self, tableexist, action="Insert", email="*****@*****.**", additems_dictionary={}):
"""Map and load data from external source into SQL table.
Dependent on cubi.file, cubi.sql, cubi.sqlwriter, cubi.dataanalysis"""
# Set up logging to output and track all uncaught errors
vlogger = self.versatilelogger
elogger = self.errorlogger
clogger = self.consolelogger
# Begin main logic of function
try:
script_start = datetime.datetime.now()
server = self.wtserver
database = self.wtdatabase
table = self.wttable
parameter_dictionary = {
"Server": server,
"DataBase": database,
"Table": table,
"Filepath": self.filepath,
"Filename": self.filename,
"Delimiter": self.file_delimiter,
"data_header_list": self.data_header_list,
"data_contents_dictionary": self.data_contents_dictionary,
"TableExist": tableexist,
"Action": action,
"Delimiter": self.file_delimiter,
"Email": email,
"AddItems": additems_dictionary,
}
vlogger.info("Method Start with parameters {0}".format(parameter_dictionary))
####
# Dependencies
####
Header = self.header_list
Contents = self.contents_dictionary
OriginalContents = self.original_contents_dictionary
refinedadditems_dictionary = {
header.strip().title(): additems_dictionary[header] for header in additems_dictionary
}
# Add headers from additems_dictionary to header
for newheader in refinedadditems_dictionary:
Header.append(newheader)
# Add key:value pairs from additems_dictionary to contents
for updaterow in Contents:
Contents[updaterow].update(refinedadditems_dictionary)
# DataAnalysis Class
Data = DataAnalysis(Header, Contents)
InputHeaderDataTypes_Dictionary = Data.dataType(output="GETSQLDATATYPES")
# SQL action. Process table actions prior to insert.
sql = SQL(server, database, table)
# Prompt to ask if SQL table already exists before adding conents. Will create table if prompted to.
##This will need to change to look for table automatically
""" Changed this logic for automation
TableExistPrompt = False
while TableExistPrompt == False:
TableExist = input('Does the table already exist? (Y/N) ').upper()
if TableExist == 'Y':
TableExistPrompt = True
continue
elif TableExist == 'N':
sql.createTableAdmin(Header, InputHeaderDataTypes_Dictionary)
TableExistPrompt = True
else:
print("I don't understand...Please enter 'Y' or 'N'")
"""
if tableexist.upper() == "N":
sql.createTableAdmin(Header, InputHeaderDataTypes_Dictionary)
elif tableexist.upper() == "Y" and action.upper() == "REPLACE":
sql.alter_table("DELETE")
elif tableexist.upper() == "Y":
pass
else:
vlogger.info("Did not properly define Table Exist parameter {0}".format({"TableExist": tableexist}))
# sys.exit() #Do we want to exit program or try to find match if table is defined?
# SQL Class - This pulls information after table creation (if necessary) so that we can have most updated table info
sql1_object = SQL(server, database, table)
SQLHeader_List = sql1_object.tableLookup("GetInsertHeader")
SQLHeaderDataType_dictionary = sql1_object.tableLookup("GetSQLDataTypes")
# Initialize transformation list for user input mapping definitions. Define Update, Add, and Delete columns
# Need to lookup datatype to ensure if will match sql
Transform_List = []
for HeaderItem in Header:
ProperHeaderItem = HeaderItem.title()
Temp_Transform_Dict = {}
if ProperHeaderItem.strip() in SQLHeader_List:
Temp_Transform_Dict["Input"] = HeaderItem
vlogger.info("{0} column mapped. Continuing to next column".format(ProperHeaderItem.strip()))
# This logic will need to change for automation
else:
# Typo error catch. Restart AddColumn prompt if input is not y or n.
AddColumnAnswer = False
while AddColumnAnswer == False:
# print(CSVheader+': No match found')
##Question 1: Input Column
AddColumn = input(
"{0} is not defined in {1}. Would you like to input this column? (Y/N) ".format(
ProperHeaderItem, table
)
).upper()
# Map CSV column header names. Loop through mapping questions until questions are answered correctly
if AddColumn == "Y":
# Typo error catch. Restart HeaderExists prompt if input not y or n
ColumnExistAnswer = False
while ColumnExistAnswer == False:
##Question 2: Header already in table
print(table + " columns:", SQLHeader_List)
HeaderExists = input(
"Does {0} already exist in the SQL table? (Y/N) ".format(ProperHeaderItem)
).upper()
# UPDATE Dictionary {'Update':{ColumnBefore:ColumnAfter}}
if HeaderExists == "Y":
HeaderMatchAnswer = False
while HeaderMatchAnswer == False:
MapHeader = (
input(
"Please enter SQL column name as you see it in defined SQL column list: "
)
.title()
.strip()
)
# Include if else to error if header is not in table
MapDict = {}
if MapHeader in SQLHeader_List:
MapDict[HeaderItem] = MapHeader
Temp_Transform_Dict["Update"] = MapDict
print(
"Mapped {0} to {1}. Continuing to next column.".format(
ProperHeaderItem, MapHeader
)
)
HeaderMatchAnswer = True
else:
print(
MapHeader.upper() + " does not match a SQL column name. Please reenter."
)
ColumnExistAnswer = True
# ADD Dictionary {'Add':{ColumnAdd:DataType}}
elif HeaderExists == "N":
AlterTableAnswer = False
while AlterTableAnswer == False:
# print('Datatype listing:',DataTypeList)
# print('Suggested DataType: {0} {1}'.format(HeaderItem, InputHeaderDataTypes_Dictionary[HeaderItem]))
AddColumnType = "VARCHAR(MAX)"
if HeaderItem in InputHeaderDataTypes_Dictionary:
AddColumnType = InputHeaderDataTypes_Dictionary[HeaderItem]
else:
AddColumnType = input(
"Couldn't find a good data type for column. What data type would you like for new column {0}? ".format(
ProperHeaderItem
)
)
VerifyAdd = input(
"Please verify. Adding {0} column with the datatype {1} into the {2} table. Would you like to continue table alter? (Y/N) ".format(
ProperHeaderItem.upper(), AddColumnType.upper(), table.upper()
)
).upper()
if VerifyAdd == "Y":
HeaderType_dict = {}
HeaderType_dict[ProperHeaderItem.strip()] = AddColumnType
Temp_Transform_Dict["Add"] = HeaderType_dict
sql.alter_column("AddColumn", ProperHeaderItem, AddColumnType)
print("Adding", ProperHeaderItem, ". Continuing to next column")
AlterTableAnswer = True
elif VerifyAdd == "N":
VerifyCancel = input(
"Cancelling table alter and {0} contents add. Continue with cancellation? (Y/N) ".format(
ProperHeaderItem
)
).upper()
if VerifyCancel == "Y":
print(
"Cancelled table alter. Column and contents will not be added to table."
)
Temp_Transform_Dict["Delete"] = HeaderItem
AlterTableAnswer = True
else:
AlterTableAnswer = False
else:
print("Invalid response. Please enter Y or N")
AlterTableAnswer = False
ColumnExistAnswer = True
else:
print("Invalid response. Please enter Y or N")
ColumnExistAnswer = False
AddColumnAnswer = True
elif AddColumn == "N":
print(ProperHeaderItem + " will not be mapped. Continuing to next column. ")
Temp_Transform_Dict["Delete"] = HeaderItem
AddColumnAnswer = True
else:
print("Please enter Y or N")
Transform_List.append(Temp_Transform_Dict)
"""Input Load Contents"""
vlogger.info("Transform List: {0}".format(Transform_List))
# Using transform_List. Complete actions on input dictionary. Logic is scrubbing contents columnwise
for action in Transform_List:
for item in Contents:
if "Input" in action:
ActionItem = action["Input"].title()
for head in Contents[item]:
if ActionItem == head.title():
Contents[item][ActionItem.strip()] = Contents[item].pop(head)
# buildTranContents_Dictionary[ActionItem.strip()] = Contents[item][head] #Add file contents to new dictionary
elif "Delete" in action and action["Delete"] in Contents[item]:
ActionItem = action["Delete"]
del Contents[item][ActionItem]
# pass
elif "Update" in action:
for k, v in action["Update"].items():
RemoveKeyName = k
UpdateKeyName = v
Contents[item][UpdateKeyName.strip()] = Contents[item].pop(RemoveKeyName)
# buildTranContents_Dictionary[UpdateKeyName] = Contents[item][RemoveKeyName] #Add
elif "Add" in action: # Add headers that don't exist in SQL table
ActionItem = None
for key in action[
"Add"
]: # Need to loop through action nested dictionary {Add: {Header: DataType}}
ActionItem = key
for head in Contents[item]:
if ActionItem == head.title():
Contents[item][ActionItem.strip()] = Contents[item].pop(head)
# buildTranContents_Dictionary[ActionItem.strip()] = Contents[item][head]
# This looks back to SQL table for any potential columns that may have been added
sql2_object = SQL(server, database, table)
UpdatedSQLHeaderDict = sql2_object.tableLookup("GetSQLDataTypes")
vlogger.info("UpdatedSQLHeaderDict: {0}".format(UpdatedSQLHeaderDict))
vlogger.info("Beginning SQL Upload")
# Section will upload Input to SQL row by row. Use this portion to filter any unwanted items
query_start = datetime.datetime.now()
connection = pymssql.connect(server=server, database=database)
cursor = connection.cursor()
n = 0
exceptionrows_dictionary = {}
for row in Contents:
insert_list = []
insert_header = []
exception_WriterRow = {}
error_column = None
error_value = None
# exception_header = []
exceptionitem_dictionary = {}
try:
# need to error out if add statement doesnt work!!
for colname in UpdatedSQLHeaderDict:
if colname in Contents[row]:
insertColumn = colname.strip()
insertValue = Contents[row][colname]
error_value = Contents[row][colname]
error_column = colname
if len(str(insertValue)) > 0 and type(insertValue).__name__ != "NoneType":
if (
UpdatedSQLHeaderDict[colname] == "int"
or UpdatedSQLHeaderDict[colname] == "smallint"
or UpdatedSQLHeaderDict[colname] == "bigint"
or UpdatedSQLHeaderDict[colname] == "tinyint"
):
insert_header.append(insertColumn)
# Clean and insert int values to SQL
ScrubbedInsertValue = None
if insertValue == "-":
ScrubbedInsertValue = 0
else:
ScrubbedInsertValue = int(insertValue)
insert_list.append(ScrubbedInsertValue)
# exception_header.append(colname)
# exception_list.append(int(InputFile_Dict[row][colname]))
elif (
UpdatedSQLHeaderDict[colname] == "decimal"
or UpdatedSQLHeaderDict[colname] == "money"
):
insert_header.append(insertColumn)
ScrubbedInsertValue = None
if insertValue == "-":
ScrubbedInsertValue = 0
else:
ScrubbedInsertValue = float(insertValue)
insert_list.append(ScrubbedInsertValue)
# exception_header.append(colname)
# exception_list.append(float(InputFile_Dict[row][colname]))
else:
insert_header.append(insertColumn)
if fnmatch.fnmatch(Contents[row][colname], "*'*"):
insert_list.append((insertValue.replace("'", " ").strip()))
else:
insert_list.append(insertValue)
# else:
# print('ColName', colname, 'Value', InputFile_Dict[row][colname])
# insert_header.append(colname)
# insert_list.append(InputFile_Dict[row][colname])
# else:
# print('In row {0}, {1} was not found. Contents will fill as NULL if possible'.format(row,colname))
insert = "INSERT INTO {0} ({1}) VALUES ({2})".format(
table, ", ".join(insert_header), repr(insert_list).strip("[]")
)
###Uncomment these when comfortable with upload
cursor.execute(insert)
connection.commit()
n += 1
except Exception as inst:
"""Grab row number, and contents, plug into exception rows dictionary to write to csv"""
exceptionrows_dictionary[row] = OriginalContents[row]
"""Is all this necessary if we are creating an exception file?"""
Error_Dict = {}
clogger.error("Error occurred on row: {0} Error {1}".format(row, inst))
# print( "Error Column:",error_column, "Error Value:",error_value,"Error Value Length:",len(error_value),"Error value type:",type(error_value))
for index in range(len(insert_list)):
cell_value = insert_list[index]
head_value = insert_header[index]
column_type = UpdatedSQLHeaderDict[head_value]
Error_Dict[head_value] = {}
# print('cell_value',cell_value,'head_value',head_value,'column_type',column_type)
if type(cell_value) == str:
Error_Dict[head_value][cell_value] = (column_type, len(cell_value))
else:
Error_Dict[head_value][cell_value] = (column_type, type(cell_value))
clogger.error("Values: {0}".format(insert_list))
elogger.exception(inst)
vlogger.error(inst)
# clogger.error(Error_Dict)
# raise
if len(exceptionrows_dictionary) > 0:
# Create exception file with rows that were not transferred to table
exceptionfilename = self.filename.split(".")[0]
exception_file = Filer(self.filepath, "{0}_Exceptions".format(exceptionfilename))
exception_file.csvCreator(Header, exceptionrows_dictionary)
query_finish = datetime.datetime.now()
query_runtime = query_finish - query_start
connection.close()
script_finish = datetime.datetime.now()
python_runtime = (script_finish - script_start) - (query_finish - query_start)
script_runtime = script_finish - script_start
vlogger.info("Upload to table {0} completed. Connection Closed.".format(table))
vlogger.info("Input file size: {0} row(s). {1} row(s) imported successfully".format(len(Contents), n))
clogger.info(
"Program Runtime: {0} Query Runtime: {1} Python Runtime: {2}".format(
script_runtime, query_runtime, python_runtime
)
)
# Catch and log any errors that cause program to fail
except Exception as ex:
elogger.exception(ex)
vlogger.error(ex)
# Send email if error occurs
frm = "*****@*****.**"
to = email
subject = "Error occurred in {0}".format(__name__)
message = "Exception error occurred within {1}.\n\nCheck logs for more info.\nTime: {0}\n\n{2}".format(
str(datetime.datetime.now()), __file__, traceback.format_exc()
)
easylogging.sendMail(frm, to, subject, message)
raise
finally:
vlogger.info("Method Complete")
