def updateDelegationTable(bRelationList, table2Update):
# Create DB connection
cnx = dbConnection.connect()
# Create a cursor
cursor = cnx.cursor()
# Progressbar length and iteration value
l = len(bRelationList)
i = 0
# Create query to update table
for item in bRelationList:
updateQuery = createUpdateQuery(item, table2Update)
#print('{}'.format(updateQuery), end='\n')
try:
# Execute cursor
cursor.execute(updateQuery)
except Exception as err:
print(
'Can not update primary table with bsuiness relation info. ERROR - {}'
.format(err))
# Increase progressbar iterator and show progress bar
i += 1
progressBar.printProgressBar(i,
l,
prefix='Progress:',
suffix='Complete')
# Commit data to DATABASE
cnx.commit()
# Close Cursor
cursor.close()
def getDelegationdata(table2Update):
# Create DB connection
cnx = dbConnection.connect()
# Create a cursor for repeated query execution
cursor = cnx.cursor()
# Create a query to get deleation info
delegationQuery = createDelegationQuery(table2Update)
try:
# Execute SQL query
cursor.execute(delegationQuery)
delegationList = []
for row in cursor:
tmpList = []
delegator = row[0]
delegatee = row[1]
tmpList.append(delegator)
tmpList.append(delegatee)
if tmpList and tmpList not in delegationList:
delegationList.append(tmpList)
except:
# Print error message
print("Data update ERROR! on table -> [ {} ]".format(table2Update),
end='\n')
return delegationList
# Commit data to DATABASE
cnx.commit()
# Close Cursor
cursor.close()
def drop_tables():
try:
(cur, conn) = connect()
except Exception as e:
print(e)
else:
sql = get_drop_statement()
cur.execute(sql)
disconnect(cur, conn)
def executeThreads():
# Create connection to Database
print("\nConnecting to Databse.....", end='\n')
cnx = dbConnection.connect()
if cnx:
# Insert data from file to database
importCsv2Db.csvImport(cnx)
else:
print("Connection failed!", end='\n')
gc.collect()
def create_tables():
try:
(cur, conn) = connect()
except Exception as e:
print(e)
else:
sql_stmts = get_create_statements()
for sql in sql_stmts:
cur.execute(sql)
disconnect(cur, conn)
def reset_tables():
try:
(cur, conn) = connect()
except Exception as e:
print(e)
else:
sql_stmts = [get_drop_statement()]
sql_stmts.extend(get_create_statements())
for sql in sql_stmts:
cur.execute(sql)
disconnect(cur, conn)
def main():
# Connect to Database
connectionString = dbConnection.connect()
# Get custom date for processing files and data
dateSuffix = (date.today() - timedelta(1)).strftime('%Y%m%d')
# Select a vendor
vendorNames = [['Huawei', 'huawei_daily_3g_data_update'],
['Nokia', 'nokia_daily_3g_data_update']]
for vendor in vendorNames:
# Send the mail
notificationEmailSender.sendMail(connectionString, vendor[1],
dateSuffix, vendor[0])
def main():
argLen = len(sys.argv)
_ip = sys.argv[1]
if argLen == 2:
# Connect to Database and list available more specific prefixex
cnx = dbConnection.connect()
if cnx:
# Connection to DB is OK
__importPrefix(cnx, _ip)
else:
return False
else:
return False
def getBusinessRel(delegationList):
# Create DB connection
cnx = dbConnection.connect()
# Create a cursor
cursor = cnx.cursor()
# Create a list with business relation data
bRelationList = []
# Progressbar length/iteration value
l = len(delegationList)
i = 0
#progressBar.printProgressBar(i, l, prefix = 'Progress:', suffix = 'Complete', length = 50)
# Create a query for business raltionship
for item in delegationList:
tmpList = []
bRelQuery = createBRelQuery(item[0], item[1])
try:
# execute the query
cursor.execute(bRelQuery)
rows = cursor.fetchmany(size=1)
if rows:
for row in rows:
as_rel_type = row[0]
tmpList.append(item[0])
tmpList.append(item[1])
tmpList.append(as_rel_type)
else:
tmpList.append(item[0])
tmpList.append(item[1])
tmpList.append('undefined')
bRelationList.append(tmpList)
except Exception as err:
print(
'Cursor problem occured while retiving business ralation info. ERROR - {}'
.format(err))
# increase the progressbar iterator and show updated progressbar
i += 1
progressBar.printProgressBar(i,
l,
prefix='Progress:',
suffix='Complete')
return bRelationList
# Commit data to DATABASE
cnx.commit()
# Close Cursor
cursor.close()
def transferData(item):
# create the DB connection
cnx = dbConnection.connect()
# Prepare a cursor object using cursor() method
cursor = cnx.cursor()
# Create MYSQL database query string according to item
dataTransferQuery = createTransferQuery(item)
try:
# Execute SQL query using execute() method
cursor.execute(dataTransferQuery)
print("[ {} ] -> [ {} ] -> OK".format(item[0], item[1]), end='\n')
except Exception as err:
# Print Error message
print("Data transfer Error!{} from [ {} ] on table -> [ {} ]".format(
err, item[0], item[1]),
end='\n')
# Commit data to DATABASE
cnx.commit()
# Close Cursor
cursor.close()
def main():
# Start the program execution clock
program_start_time = time.time()
human_readable_time = time.strftime("%H:%M:%S", time.localtime(program_start_time))
print("\nProgram clock started at -> [ {} ]".format(str(human_readable_time)),end='\n')
# Print an initialization message
print("\nInitializing program..........\n",end='\n')
# Introduce input file locations
inputFileLocation = 'D:/3G_KPI/INPUT/' # Change this string according to input files' location
rawFileLocation = 'E:/3G_KPI/'
outputDirectory = 'D:/3G_KPI/OUTPUT' # No forward slash needed
# Get custom date for processing files and data
dateSuffix = (date.today()-timedelta(1)).strftime('%Y%m%d')
# Check whether files are present or not, if present then copy them to input directory
fileCheckStatus = fileChecker.checkFiles(rawFileLocation,inputFileLocation,dateSuffix)
if fileCheckStatus == 1:
# Create a database connection
print("\n****** Connecting to mySQL Database ******", end='\n')
# Connect to Database
connectionString = dbConnection.connect()
# Connection complete message
print("\n****** Connected to mySQL Database ******", end='\n\n')
# Call execute function to execute necessary threads
executeThreads(connectionString,inputFileLocation)
# Export results to csv files
export2CSV.exportData(connectionString,outputDirectory,dateSuffix)
else:
print('Files doesn\'t exists! Please upload the required files and try again later.')
# Close Database Connection
connectionString.close()
# Print program execution time message
program_elapsed_sec = (time.time() - program_start_time)
program_elapsed_min = round((program_elapsed_sec / 60),2)
print('\nProgram executed in [ {} ] minutes.'.format(program_elapsed_min), end='\n')
# Print exit message
print('Exiting..........', end='\n')
def connect(self):
self.cur, self.conn = connect()
self.engine = get_engine()