def edit_data(conn, cursor, file_number, user_name):
table = "Follow_up_Data"
col_list = names.name_follow_up()
enter = view_multiple(conn, table, col_list, file_number)
if enter == "Add data":
data = follow_up(file_number, user_name)
data.to_sql("Follow_up_Data", conn, index=False, if_exists="append")
if enter == "Re-enter data":
table = "Follow_up_Data"
col_list = ["File_number"] + names.name_follow_up()
sql = ('SELECT ' + ", ".join(col_list[:-2]) + " FROM '" + table +
"' WHERE File_number = '" + file_number + "'")
df = pd.read_sql(sql, conn)
follow_up_period = list(df.loc[:, "Follow_up_Period"])
delete_data = True
while delete_data:
check_delete = False
while not check_delete:
col_data = ask_y_n_statement.ask_option(
"Which entry do you want to modify?", follow_up_period)
check_delete = ask_y_n_statement.ask_y_n(
"Are you sure you want to delete data for follow-up period "
+ col_data)
delete_rows(cursor, table, "Follow_up_Period", col_data)
add_data(conn, file_number, user_name)
delete_data = ask_y_n_statement.ask_y_n(
"Do you want to re-enter another follow up period")
def follow_up(file_number, user_name):
follow = True
follow_index = 0
col_list = ["File_number"] + names.name_follow_up()
follow_up_data = pd.DataFrame(columns=col_list)
while follow:
check = False
while not check:
time_follow = ask_y_n_statement.ask_option("Follow-up Period", [
"3 months", "6 months", "9 months", "1 year",
"1 year, 3 months", "1 year, 6 months", "1 year, 9 months",
"2 years", "2 years, 6 months", "3 years", "3 years, 6 months",
"4 years", "4 years, 6 months", "5 years", "6 years",
"7 years", "8 years", "9 years", "10 years", "Other"
])
follow_notes = input("Details of follow up information: ")
follow_report = ask_y_n_statement.ask_y_n(
"Does follow-up contain other reports (USG/Mammography)?")
follow_mammo, follow_usg = ("NA", ) * 2
if follow_report:
follow_mammo = input("Results of Mammography: ")
follow_usg = input("Results of USG abdomen/Pelvis: ")
follow_other = ask_y_n_statement.ask_y_n(
"Are there other reports in follow-up?")
if not follow_other:
other_type, other_result = ("NA", ) * 2
else:
other_type_list = []
other_result_list = []
while follow_other:
other_type = input("Type of other report: ")
other_result = input("Result of " + other_type + ": ")
other_type_list.append(other_type)
other_result_list.append(other_result)
follow_other = ask_y_n_statement.ask_y_n(
"Add more reports?")
all_data = [other_type_list, other_result_list]
all_data = ask_y_n_statement.join_lists(all_data, "; ")
other_type, other_result = all_data
last_update = datetime.now().strftime("%Y-%b-%d %H:%M")
data_list = [
file_number, time_follow, follow_notes, follow_mammo,
follow_usg, other_type, other_result, user_name, last_update
]
follow_up_data.loc[follow_index] = data_list
check = review_df(follow_up_data.loc[follow_index])
follow_index = follow_index + 1
follow_up_period = list(follow_up_data.loc[:, "Follow_up_Period"])
print("Follow up periods added: " + "; ".join(follow_up_period))
follow = ask_y_n_statement.ask_y_n("Add another follow-up period?")
return follow_up_data
def db_dict(table, module):
db_tables = {
"Patient_Information_History": names.names_info(module),
"Biopsy_Report_Data": names.names_biopsy_new(module),
"Radiology": names.names_radio(module),
"Neo_Adjuvant_Therapy": names.names_nact(module),
"Surgery_Report": names.names_surgery_information(module),
"Surgery_Block_Report_Data": names.names_surgery(module),
"Adjuvant_ChemoTherapy": names.names_chemotherapy(module),
"Radiotherapy": names.names_radiation(),
"HormoneTherapy_Survival": names.names_longterm(module),
"Follow_up_Data": names.name_follow_up()
}
cols = db_tables.get(table)
return cols
def db_dict(table, module):
db_tables = {
"patient_information_history": names.names_info(module),
"radiology": names.names_radio(module),
'pet_reports': names.names_pet(module),
'biopsy_path_report_data': names.names_biopsy(module),
"neo_adjuvant_therapy": names.names_nact(module),
"surgery_report": names.names_surgery_information(module),
'surgery_path_report_data': names.names_surgery(module),
"adjuvant_chemotherapy": names.names_chemotherapy(module),
"radiotherapy": names.names_radiation(),
"hormonetherapy_survival": names.names_longterm(module),
"follow_up_data": names.name_follow_up(),
'block_list': names.block_list(module)
}
cols = db_tables.get(table)
return cols
cursor_all.execute('CREATE TABLE {tn}({nf})'\
.format(tn=table, nf=file_number))
module_names = ["bio_info", "phys_act", "habits", "nut_supplements", "family_details", "med_history",
"cancer_history", "family_cancer", "det_by", "breast_symptoms"]
for index in module_names:
col_name = pccm_names.names_info(index)
add_columns(cursor_all, table, col_name)
table = "Radiotherapy"
if table_check(cursor_all, table) == 0:
column = ", ".join(pccm_names.names_radiation())
cols_file = "File_number, "+column
cursor_all.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf=cols_file))
table = "Follow_up_Data"
if table_check(cursor_all, table) == 0:
column = ", ".join(pccm_names.name_follow_up())
cols_file = "File_number, " + column
cursor_all.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf=cols_file))
table = "HormoneTherapy_Recurrence_Survival"
if table_check(cursor_all, table) == 0:
column = "File_number"
cursor_all.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf=column))
module_names = ["hormone", "metastasis"]
for index in module_names:
col_name = pccm_names.names_longterm(index)
add_columns(cursor_all, table, col_name)
table = "General_Medical_History"
if table_check(cursor_all, table) == 0:
columns2 = "File_number, Condition, Diagnosis_date, Treatment"
col_list = ["File_number"] + names.names_radiation()
sql = ('SELECT ' + ", ".join(col_list[:-2]) + " FROM '" + table + "'")
df = pd.read_sql(sql, conn)
writer = pd.ExcelWriter(ex_path, engine='xlsxwriter')
df.to_excel(writer, sheet_name=table)
table = "HormoneTherapy_Recurrence_Survival"
col_list = [
"File_number"
] + names.names_longterm("hormone") + names.names_longterm("metastasis")
sql = ('SELECT ' + ", ".join(col_list[:-2]) + " FROM '" + table + "'")
df = pd.read_sql(sql, conn)
df.to_excel(writer, sheet_name="Hormone_RecurrenceSurvival")
table = "Follow_up_Data"
col_list = ["File_number"] + names.name_follow_up()
sql = ('SELECT ' + ", ".join(col_list[:-2]) + " FROM '" + table + "'")
df = pd.read_sql(sql, conn)
df.to_excel(writer, sheet_name=table)
table = "Patient_Information_History"
col_list_bio = names.names_info("bio_info")
col_list = ["File_number"] + col_list_bio + names.names_info("phys_act") + names.names_info("habits") + \
names.names_info("nut_supplements") + names.names_info("family_details") + names.names_info("med_history") + \
names.names_info("cancer_history") + names.names_info("family_cancer") + names.names_info("breast_symptoms")
sql = ('SELECT ' + ", ".join(col_list) + " FROM '" + table + "'")
df = pd.read_sql(sql, conn)
df.to_excel(writer, sheet_name=table)
writer.save()
last_update = datetime.now().strftime("%Y-%b-%d %H:%M")
module_names = ["surgery_block_information_1","surgery_block_information_2", "surgery_block_information_3",
"path_stage"]
col_list = ["File_number"]
for index in module_names:
col_list = col_list + pccm_names.names_surgery(index)
columns = ", ".join(col_list)
for index in range (0, len(data)):
data_list = list(data.loc[index])
data_list.append(update_by)
data_list.append(last_update)
sql.insert(conn_all, cursor_all, table, columns, data_list)
# get col_names from older pccm_names files:
# 2018-09-28
import modules.pccm_names as pccm_names
import sql.add_update_sql as sql
#read from excel with same col names and distribution as in table
table = 'Follow_up_Data'
file_to_read = "D:/OneDrive/iiser_data/Prashanti_docs/Database_files/2018_10_09/Rituja_data/" \
"PCCM_BreastCancerDB_Rituja_2018-10-11_2018-10-11.xlsx"
data = pd.read_excel(file_to_read, header=0, dtype = 'object' ,usecols= 'A:L', sheet_name='Follow_up_Data')
col_list = ["File_number"]
col_list = col_list + pccm_names.name_follow_up()
columns = ", ".join(col_list)
for index in range (0, len(data)):
data_list = list(data.loc[index])
sql.insert(conn_all, cursor_all, table, columns, tuple(data_list))