def write_cell(book_name, sheet_name, cell, value):
work_book = None
try:
work_book = load_workbook(Path.get_base_path() + book_name,
read_only=False,
keep_vba=True)
sheet = work_book[sheet_name]
sheet[cell] = value
logging.debug(
"Values of {} in sheet {} of book {} is set to {}".format(
cell, sheet_name, book_name, value))
work_book.save(Path.get_base_path() + book_name)
work_book.close()
except FileNotFoundError as e:
logging.error("File {} is not found.".format(book_name), e)
create_excel_workbook(book_name, sheet_name)
try:
write_cell(book_name, sheet_name, cell, value)
except Exception as e:
logging.error(
"Tried creating a new sheet and calling the method again. Still failed.",
e)
except Exception as e:
logging.error("Error while trying to write.", e)
if work_book is not None:
work_book.close()
def compare_file(scenario):
try:
temp_result_file = "temp_res.json"
exp_file = open(Path.get_base_path() + scenario.expected_file)
act_file = open(Path.get_base_path() + scenario.actual_file)
exp_header = exp_file.readline()
act_header = act_file.readline()
if act_header.rstrip() == exp_header.rstrip():
FileComparision.compare(scenario.expected_file,
scenario.actual_file, temp_result_file)
return GenerateReport.write_to_report(temp_result_file,
exp_header.rstrip(),
scenario.result_format,
scenario.result_file)
else:
logger.warning(
"Header didn't match.\nExpected : {}\nActual : {}".format(
exp_header, act_header))
return -1, 0
except Exception as e:
logger.error("Couldn't compare the given files.", e)
raise
finally:
if os.path.exists(Path.get_base_path() + "temp_res.json"):
os.remove(Path.get_base_path() + "temp_res.json")
def modify_data():
con = sqlite3.connect(Path.get_base_path() + 'local.db')
con.execute("update user SET name='Aingaran Elango' where id=1;")
con.execute("update user SET name='Avinash', id=6 where id=4;")
con.commit()
con.close()
def find_neighbors(loc, cluster_id, gap, kmap, cmap, neighbors, directions):
cmap[loc.y][loc.x] = cluster_id
used = [[False for j in range(2 * gap + 1)] for i in range(2 * gap + 1)]
q = []
index = 0
q.append(Path(loc))
while index < len(q):
path = q[index]
dest, steps = path.dest, path.steps
_y, _x = dest.y - loc.y + gap, dest.x - loc.x + gap
if steps and kmap[dest.y][dest.x] > 0:
neighbors[loc.y][loc.x].append(path)
if cmap[dest.y][dest.x] == -1:
find_neighbors(dest, cluster_id, gap, kmap, cmap, neighbors,
directions)
elif len(steps) < gap:
for d in directions:
ny, nx = _y + d.y, _x + d.x
if not used[ny][nx] and not out_of_bounds(path.dest + d, kmap):
used[ny][nx] = True
q.append(path + d)
index += 1
def create_excel_workbook(book_name, sheet_name):
work_book = None
try:
os.makedirs(os.path.dirname(Path.get_base_path() + book_name),
exist_ok=True)
work_book = Workbook()
work_sheet = work_book.active
work_sheet.title = sheet_name
work_book.save(filename=Path.get_base_path() + book_name)
work_book.close()
logging.debug(
"Work book with name {} with a sheet named {} created.".format(
book_name, sheet_name))
except Exception as e:
logging.error("Couldn't create a workbook.", e)
if work_book is not None:
work_book.close()
def generate_excel_report(result_file, header_string, out_file_name,
excel_sheet_name, report_format):
excel_report_template = 'template/Compare Cells - Template.xlsm'
try:
row_number = 1
if FILE_FORMAT.EXCEL_WITH_MACRO == report_format:
copyfile(Path.get_base_path() + excel_report_template,
Path.get_base_path() + out_file_name)
if FILE_FORMAT.EXCEL == report_format:
ReadWriteExcel.create_excel_workbook(out_file_name,
excel_sheet_name)
ReadWriteExcel.write_cell(out_file_name, excel_sheet_name,
'A' + str(row_number),
"Line Number".rstrip())
ReadWriteExcel.write_cell(out_file_name, excel_sheet_name,
'B' + str(row_number), "Expected".rstrip())
ReadWriteExcel.write_cell(out_file_name, excel_sheet_name,
'C' + str(row_number), "Actual".rstrip())
row_number += 1
ReadWriteExcel.write_cell(out_file_name,
excel_sheet_name, 'B' + str(row_number),
header_string.rstrip())
ReadWriteExcel.write_cell(out_file_name,
excel_sheet_name, 'C' + str(row_number),
header_string.rstrip())
row_number += 1
result_data = json.load(open(Path.get_base_path() + result_file, 'r'))
for item in result_data:
logger.debug("Line No. : " + str(item['LineNumber']).rstrip())
logger.debug("Expected : " + str(item['Expected']).rstrip())
logger.debug("Actual : " + str(item['Actual']).rstrip())
ReadWriteExcel.write_cell(out_file_name, excel_sheet_name,
'A' + str(row_number),
str(item['LineNumber']).rstrip())
ReadWriteExcel.write_cell(out_file_name, excel_sheet_name,
'B' + str(row_number),
str(item['Expected']).rstrip())
ReadWriteExcel.write_cell(out_file_name, excel_sheet_name,
'C' + str(row_number),
str(item['Actual']).rstrip())
row_number += 1
return row_number - 3, 'N/A'
except Exception as e:
logger.error(e)
return -1, 0
def create_table():
con = sqlite3.connect(Path.get_base_path() + 'local.db')
con.execute('create table user (id int, name text);')
con.execute("insert into user (id, name) values (1,'Aingaran');")
con.execute("insert into user (id, name) values (2,'Mark');")
con.execute("insert into user (id, name) values (3,'Jane');")
con.execute("insert into user (id, name) values (4,'Someone');")
con.commit()
con.close()
def archive_file(file, scenario_name, archive_file_type):
try:
pathlib.Path(
os.path.join(
os.path.join(
os.path.join(os.path.join(Path.get_base_path(), "Archive"),
scenario_name), str(datetime.date.today())),
archive_file_type)).mkdir(parents=True, exist_ok=True)
head, tail = os.path.split(Path.get_base_path() + file)
copyfile(
Path.get_base_path() + file,
os.path.join(
os.path.join(
os.path.join(
os.path.join(
os.path.join(Path.get_base_path(),
"Archive"), scenario_name),
str(datetime.date.today())), archive_file_type), tail))
except Exception as e:
logging.error("archive failed", e)
def save_query_to_file(connection, query, file, database_type):
os.makedirs(os.path.dirname(Path.get_base_path() + file), exist_ok=True)
if DATABASE_TYPE.MY_SQL == database_type:
cursor = connection.cursor()
return save_query_to_file_my_sql(cursor, query, file)
elif DATABASE_TYPE.SQLite == database_type:
cursor = connection.cursor()
return save_query_to_file_sqlite(cursor, query, file)
else:
# cursor = connection
return 'No driver found'
def compare(old_file, new_file, compare_result):
try:
logging.debug(os.path.dirname(Path.get_base_path() + compare_result))
os.makedirs(os.path.dirname(Path.get_base_path() + compare_result),
exist_ok=True)
data = []
line_number = 1
with open(Path.get_base_path() + compare_result, "w+") as result, \
open(Path.get_base_path() + old_file, 'r') as old, \
open(Path.get_base_path() + new_file, 'r') as new:
for old_line, new_line in zip(old, new):
if old_line.rstrip() != new_line.rstrip():
item = {
'LineNumber': line_number,
'Expected': old_line.rstrip(),
'Actual': new_line.rstrip()
}
data.append(item)
line_number += 1
json.dump(data, result)
except Exception as e:
logging.error(e)
def read_cell(book_name, sheet_name, cell):
work_book = None
try:
work_book = load_workbook(Path.get_base_path() + book_name)
sheet = work_book[sheet_name]
value = sheet[cell].value
logging.debug("Values of {} in sheet {} of book {} is {}".format(
cell, sheet_name, book_name, value))
work_book.close()
return value
except Exception as e:
logging.error("Couldn't get the value fo the cell {} in sheet {} of book {}" \
.format(cell, sheet_name, book_name), e)
if work_book is not None:
work_book.close()
return None
def create_file(scenario, database, file):
try:
if DATABASE_TYPE.MY_SQL == database.database_type:
connection = MySQL.get_connection(database.host_name,
database.user_name,
database.password,
database.database_name)
elif DATABASE_TYPE.SQLite == database.database_type:
connection = SQLite.get_connection(Path.get_base_path() +
database.database_name)
else:
connection = None
DatabaseToFile.save_query_to_file(connection, scenario.query, file,
database.database_type)
except Exception as e:
logger.error("Couldn't get the data from database.", e)
raise
def save_query_to_file_my_sql(connection, query, file):
with open(Path.get_base_path() + file, "w+") as out_file:
try:
connection.execute(query)
row = connection.fetchone()
while row is not None:
print(row[0])
out_file.write(row[0])
out_file.write('\n')
row = connection.fetchone()
except DatabaseError as e:
logging.error("Error: Problem with database", e)
except Exception as e:
logging.error("Error: Problem with something", e)
finally:
connection.close()
return "File Written"
def generate_html_report(result_file, header_string, out_file_name):
try:
mismatch_row_count = 0
mismatch_count = 0
doc, tag, text = Doc().tagtext()
logger.debug("Header : " + header_string.rstrip())
title = out_file_name.split(".")[0]
result_data = json.load(open(Path.get_base_path() + result_file, 'r'))
for item in result_data:
logger.debug("Line No. : " + str(item['LineNumber']).rstrip())
logger.debug("Expected : " + str(item['Expected']).rstrip())
logger.debug("Actual : " + str(item['Actual']).rstrip())
with tag('html'):
with tag('head'):
with tag('title'):
text(title)
with tag('body'):
with tag('table', style='width:100%', border="1"):
with tag('tr'):
with tag('th'):
text("Line Number")
for header in header_string.split(';'):
with tag('th'):
text(header.upper())
for item in result_data:
value_ref = {}
with tag('tr'):
with tag('td', rowspan='2', align="center"):
text(str(item['LineNumber']).rstrip())
mismatch_row_count += 1
col_num = 1
for column_value in str(
item['Expected']).rstrip().split(';'):
with tag('td', align="center"):
text(column_value)
value_ref[col_num] = column_value
col_num += 1
col_num = 1
with tag('tr'):
for column_value in str(
item['Actual']).rstrip().split(';'):
with tag('td', align="center"):
if column_value == value_ref[col_num]:
text(column_value)
else:
with tag('font', color="red"):
text(column_value)
mismatch_count += 1
col_num += 1
result = doc.getvalue()
logger.debug(result)
with open(Path.get_base_path() + out_file_name, 'w+') as html_file:
html_file.writelines("<!DOCTYPE html>")
html_file.writelines(result)
logger.debug(mismatch_row_count, mismatch_count)
return mismatch_row_count, mismatch_count
except Exception as e:
logger.error(e)
return -1, 0
import tkinter
from datetime import datetime
from tkinter import Tk, Button, messagebox, Radiobutton, scrolledtext
from archiever import FileArchiver
from comparer import FileComparision
from database_expert import MySQL, SQLite
from interactor import Scenario, Database
from reporter import GenerateReport
from retriever import DatabaseToFile
from utilities import ReadWriteExcel, RUN_MODE, DATABASE_TYPE, Path
timestamp = str(datetime.now().day) + '_' + str(datetime.now().month) + '_' + str(datetime.now().year) + '_' + \
str(datetime.now().hour) + '_' + str(datetime.now().minute) + '_' + \
str(datetime.now().second)
logging.basicConfig(filename=Path.get_base_path() +
'logs/TimeMachine_log_{}.log'.format(timestamp),
level=logging.INFO)
logger = logging.getLogger(__name__)
def open_user_interface():
window = Tk()
window.geometry('500x500')
txt = scrolledtext.ScrolledText(window, width=60, height=20)
txt.grid(column=0, row=5)
selected = tkinter.IntVar()
# rad1 = Radiobutton(window, text=RUN_MODE.CREATE_ACTUAL, value=1, variable=selected)