def load(fileName, fieldNames, delimeter='\t', encoding='utf-8-sig'):
while True:
try:
with open(fileName, 'r', encoding=encoding) as fileCsv:
print('\r load ' + fileName + ' ')
Counter = counter(
len(
io.open(fileName, 'r',
encoding='utf-8-sig').readlines()) - 1,
0.2)
innerData = csv.reader(fileCsv, delimiter=delimeter)
outStrings = list()
for string in innerData:
Counter.step('data load...')
#string = string[0].split('\t')
outString = dict()
for i in range(0, 7):
outString.update({fieldNames[i]: string[i]})
outStrings.append(outString)
Counter.lastTell(' data loaded')
#outStrings = sortingByFieldNames(outStrings, fieldNames)
return outStrings
except PermissionError:
print('\r!!!Please, close the file ' + fileName, end='')
sleep(1)
def save(self, innerStrings, fileNameOut, fieldNames):
while True:
try:
with open('./output/' + fileNameOut,
'w',
newline='',
encoding='utf-8-sig') as outFile:
print('\r unload to ' + fileNameOut +
' ')
Counter = counter(len(innerStrings), 0.2)
writer = csv.DictWriter(outFile,
delimiter=';',
fieldnames=fieldNames)
writer.writeheader()
for string in innerStrings:
Counter.step('data unload...')
inner_dict = string
writer.writerow(inner_dict)
Counter.lastTell('data unloaded')
outFile.close()
print(' complete')
break
except PermissionError:
print('\r!!!Please, close the file ' + fileNameOut, end='')
sleep(1)
def sortingByFieldNames(innerList, fieldNames):
params = fieldNames.copy()
Counter = counter(len(params), 5.0)
params.reverse()
innerList = dedublicateListDict(innerList)
for param in params:
Counter.step('sorting...')
innerList.sort(key=lambda k: k[param])
Counter.lastTell('sorted')
return innerList
def deduplicate(self, originatorsList):
originatorsDict = dict()
Counter = counter(len(originatorsList), 0.5)
for originator in originatorsList:
Counter.step('deduplicate...')
key = ';'.join([
str(originator.Originator),
str(originator.ServiceTypeId),
str(originator.OperatorGroupId)
])
Originator = originatorsDict.get(key)
if Originator != None:
if Originator.getStatusPriority(
) >= originator.getStatusPriority():
continue
originatorsDict.update({key: originator})
Counter.lastTell('deduplicated')
return list(originatorsDict.values())
def generateInsertSql(table, suffix, innerData, maxStrings):
print('generate insert SQL ' + table + ' ' + suffix)
data = innerData.copy()
try:
keys = list(data[0].keys())
query = str()
Counter = counter(len(innerData), 0.2)
while len(data):
queryList = list([
'INSERT INTO ' + table,
' (' + ', '.join(keys) + ') ',
'VALUES '
])
strings = list()
i = int()
while i < maxStrings and len(data):
Counter.step('generate...')
dataString = data.pop(0)
params = list()
for key in keys:
try:
if type(dataString[key]) == type(str()):
dataString[key] = dataString[key].replace('\'', '\'\'')
params.append('\'' + dataString[key] + '\'')
else:
params.append(str(dataString[key]))
except KeyError:
print(dataString)
None
strings.append('(' + ', '.join(params) + ')')
i += 1
queryList.append(', '.join(strings))
query = '\n'.join([query, ''.join(queryList) + ';'])
Counter.lastTell('generated')
f = open('./sql_queries/' + table + '_' + suffix + '.sql', 'w')
f.write(query)
print(' complete')
return query
except IndexError:
print(' fail!')
def csvLoad(fileName, fieldNames):
while True:
try:
with io.open(fileName, 'r', encoding='utf-8-sig') as fileCsv:
print('\r load ' + fileName + ' ')
Counter = counter(len(io.open(fileName, 'r', encoding='utf-8-sig').readlines()) - 1, 0.2)
innerData = csv.DictReader(fileCsv, delimiter=';')
outStrings = list()
for string in innerData:
Counter.step('data load...')
outString = dict()
for stringKey in string.keys():
if stringKey in fieldNames:
if string[stringKey] == 'NULL':
string[stringKey] = ''
outString.update({stringKey: str(string[stringKey])})
outStrings.append(outString)
Counter.lastTell(' data loaded')
outStrings = sortingByFieldNames(outStrings, fieldNames)
return outStrings
except PermissionError:
print('\r!!!Please, close the file ' + fileName, end='')
sleep(1)
def sortOriginators(self, params):
Counter = counter(len(params), 5.0)
for param in params:
Counter.step('sorting...')
self.Originators.sort(key=attrgetter(param))
Counter.lastTell('sorted')