def update_from_excel (file_name_excel, sheet, conn_new, cursor_new, col_names_new, col_names_old):
df_old = pd.read_excel(file_name_excel, sheet_name=sheet, index_col=0)
df_old = df_old.astype(str)
file_number_old = list(df_old.index)
table_name = Tables.sheet_name[sheet]
print(table_name)
sql_statement = 'SELECT file_number from ' + table_name
file_number = cursor_new.execute(sql_statement)
file_number_sql = file_number.fetchall()
file_list_sql = []
for file in file_number_sql:
file_var = file[0]
file_list_sql.append(file_var)
file_to_add = list(set(file_number_old) - set(file_list_sql))
if len(file_to_add)>0:
for file in file_to_add:
print(file)
sql.add_pk_fk_to_table(conn_new, cursor_new, col_name='file_number', pk=file, table=table_name)
data = df_old.loc[file]
for col in range(0, len(col_names_old)):
sql.update_single(conn_new, cursor_new, table_name, col_names_new[col], file,
data.loc[col_names_old[col]])
print(file, ' added to ', table_name, col_names_new[col])
else:
print('No new files to add')
def check_path_report_entry(conn, cursor, file_number, table_to_check, pk,
block_id, number_of_blocks, user_name):
from reports.biopsy_report import BiopsyData
from reports.surgery_block import SurgeryBlockData
table_data = BiopsyData(conn, cursor, file_number, pk, block_id,
number_of_blocks, user_name)
col_name = 'pk'
if 'surgery' in table_to_check:
table_data = SurgeryBlockData(conn, cursor, file_number, pk, block_id,
number_of_blocks, user_name)
col_filter = 'fk'
pk_fk_present = sql.check_pk_fk_exist(cursor, col_name, pk, table_to_check)
if not pk_fk_present:
sql.add_pk_fk_to_table(conn, cursor, table_to_check, col_name, pk)
print("This block_id -'" + block_id + "' for " + file_number +
" does not exist in table " + table_to_check +
". Enter new record")
table_data.add_data()
else:
todo = ask.ask_option(
file_number + " already exists in table " + table_to_check + ".",
["Edit record", "Edit None"])
if todo == "Edit record":
table_data.edit_data()
return
def add_select_data_to_main_tables(self, file_list, table):
for file in file_list:
sql.add_pk_fk_to_table(self.conn_new,
self.cursor_new,
col_name='file_number',
pk=file,
table=table)
self.add_old_data_to_new_table(table, file_list)
def add_data(self):
pk = uuid.uuid4().hex
sql.add_pk_fk_to_table(self.conn,
self.cursor,
self.table_name,
col_name='pk',
pk=pk)
pet_present = ask.ask_y_n(
"Does the patient file contain a PET/CT report to be entered?")
if not pet_present:
data_length = len(self.col_list_all) - 3
data = [self.file_number
] + ['pet_report_not_present'] * data_length + [
self.user_name, sql.last_update()
]
sql.update_multiple_key(self.conn,
self.cursor,
self.table_name,
self.col_list_all,
key_name='pk',
key_value=pk,
data=data)
else:
print(self.print_statements[0])
col_list = names(self.module_list[0])
enter = ask.ask_y_n("Enter " + self.print_statements[0])
if enter:
data = self.pet_report_identifier()
sql.update_multiple_key(self.conn,
self.cursor,
self.table_name,
col_list,
key_name='pk',
key_value=pk,
data=data)
col_list = names(self.module_list[1])
enter = ask.ask_y_n("Enter " + self.print_statements[1])
if enter:
data = self.pet_report_findings()
sql.update_multiple_key(self.conn,
self.cursor,
self.table_name,
col_list,
key_name='pk',
key_value=pk,
data=data)
enter = ask.ask_y_n("Enter " + self.print_statements[2])
col_list = names(self.module_list[2])
if enter:
data = self.pet_breast_cancer()
sql.update_multiple_key(self.conn,
self.cursor,
self.table_name,
col_list,
key_name='pk',
key_value=pk,
data=data)
def add_gen_info(conn, cursor, file_number, user_name):
table = "patient_information_history"
sql.add_pk_fk_to_table(conn,
cursor,
table=table,
col_name='file_number',
pk=file_number)
age, data = bio_info(file_number)
data = [str(dat) for dat in data]
sql.update_multiple(conn, cursor, table, pccm_names.names_info("bio_info"),
file_number, data)
data = phys_act(file_number)
data = [str(dat) for dat in data]
sql.update_multiple(conn, cursor, table, pccm_names.names_info("phys_act"),
file_number, data)
data = habits(file_number, age)
data = [str(dat) for dat in data]
sql.update_multiple(conn, cursor, table, pccm_names.names_info("habits"),
file_number, data)
data = nut_supplements(file_number)
data = [str(dat) for dat in data]
sql.update_multiple(conn, cursor, table,
pccm_names.names_info("nut_supplements"), file_number,
data)
data = family_details(file_number, age)
data = [str(dat) for dat in data]
sql.update_multiple(conn, cursor, table,
pccm_names.names_info("family_details"), file_number,
data)
data = med_history(file_number)
data = [str(dat) for dat in data]
sql.update_multiple(conn, cursor, table,
pccm_names.names_info("med_history"), file_number,
data)
data = cancer_history(file_number)
data = [str(dat) for dat in data]
sql.update_multiple(conn, cursor, table,
pccm_names.names_info("cancer_history"), file_number,
data)
data = family_cancer(file_number)
data = [str(dat) for dat in data]
sql.update_multiple(conn, cursor, table,
pccm_names.names_info("family_cancer"), file_number,
data)
data = det_by(file_number)
data = [str(dat) for dat in data]
sql.update_multiple(conn, cursor, table, pccm_names.names_info("det_by"),
file_number, data)
data = breast_symptoms(file_number, user_name)
data = [str(dat) for dat in data]
sql.update_multiple(conn, cursor, table,
pccm_names.names_info("breast_symptoms"), file_number,
data)
data = other_test(file_number, user_name)
data = [str(dat) for dat in data]
sql.update_multiple(conn, cursor, table,
pccm_names.names_info("other_test"), file_number, data)
def add_file_number (file_number_name, table_old, cursor_old, conn_new, cursor_new, table):
sql_statement = 'SELECT ' + file_number_name + ' from ' + table_old
file_number = cursor_old.execute(sql_statement)
file_number = file_number.fetchall()
file_list = []
for file in file_number:
file_var = file[0]
sql.add_pk_fk_to_table(conn_new, cursor_new, col_name='file_number', pk=file_var, table=table)
file_list.append(file_var)
return file_list
def add_new_pk(self, file_number, block_type):
data, pk = self.update_block_id(file_number, block_type)
sql.add_pk_fk_to_table(self.conn,
self.cursor,
self.table_name,
col_name='pk',
pk=pk)
sql.update_multiple_key(self.conn,
self.cursor,
self.table_name,
columns=self.columns,
key_name='pk',
key_value=pk,
data=data)
def add_data(self, file_number):
add_block = True
while add_block:
data, pk = self.add_block_id(file_number)
sql.add_pk_fk_to_table(self.conn,
self.cursor,
self.table_name,
col_name='pk',
pk=pk)
sql.update_multiple_key(self.conn,
self.cursor,
self.table_name,
columns=self.columns,
key_name='pk',
key_value=pk,
data=data)
add_block = ask.ask_y_n('Add another block?')
def add_radio(self):
rep = gf.get_number(4)
for i in range(rep):
pk = uuid.uuid4().hex
sql.add_pk_fk_to_table(self.conn,
self.cursor,
self.table,
col_name='pk',
pk=pk)
col_list = names_radio(self.table)
# report = Mammography(self.conn, self.cursor, self.file_number, self.user_name)
data = self.report()
for col, dat in zip(col_list, data):
print(col, dat)
sql.update_multiple_key(self.conn,
self.cursor,
self.table,
col_list,
key_name='pk',
key_value=pk,
data=data)
def add_radio(self, table):
pk = uuid.uuid4().hex
sql.add_pk_fk_to_table(self.conn,
self.cursor,
table,
col_name='pk',
pk=pk)
print(table)
enter = ask.ask_y_n("Enter " + table)
col_list = names(table)
if enter:
data = self.table_fx().get(table)
try:
sql.update_multiple_key(self.conn,
self.cursor,
table,
col_list,
key_name='pk',
key_value=pk,
data=data)
except TypeError:
print(data)
def add_data(self, old_col, df):
if df is not None:
dat_rows = df.shape[0]
print(df.shape)
# joinDb = JoinDB(db_new=self.dB_path, db_old=self.dB_path,
# table=self.table)
# file_list = join_db.add_file_number()
for row in range(dat_rows):
dat = list(df.loc[row])
file_number = dat[0]
print(file_number)
dat_add = dat[1:]
print(dat_add)
sql.add_pk_fk_to_table(self.conn,
self.cursor,
self.table,
col_name='file_number',
pk=file_number)
sql.update_multiple(self.conn,
self.cursor,
self.table,
columns=old_col,
file_number=file_number,
data=dat_add)
sheet = "HormoneTherapy_Survival"
cols_set_old = ['Hormone_Indicated', 'Hormone_Recieved', 'Hormone_Date', 'Hormone_Type', 'Hormone_duration_years',
'Hormone_Discontinued', 'Hormone_Ovary_Surpression', 'Hormone_Therapy_Outcome', 'Hormone_followup',
'Horomone_recurrence', 'Metastasis_exam', 'Date_last_followup', 'Time_to_recurrence',
'Nature_of_recurrence', 'Distant_site', 'Patient_status_last_followup', 'update_by', 'last_update']
col_set_new = ['hormone_indicated', 'hormone_recieved', 'hormone_date', 'hormone_type', 'hormone_duration_years',
'hormone_discontinued', 'hormone_ovary_surpression', 'hormone_therapy_outcome', 'hormone_followup',
'horomone_recurrence', 'metastasis_exam', 'date_last_followup', 'time_to_recurrence', 'nature_of_recurrence',
'distant_site', 'patient_status_last_followup', 'update_by', 'last_update']
update_from_excel (file_name_excel, sheet, conn_new, cursor_new, col_set_new, cols_set_old)
conn_new.commit()
conn_new.close()
#add specific files to a new db
file_list = FilesSubtype.tnbc
research = False
table_name = 'patient_information_history'
table_old = table_name
cols_set_old = get_col_list(table_name, research)[1:]
file_number_name = 'file_number'
col_set_new = cols_set_old
for file in file_list:
sql.add_pk_fk_to_table(conn_new, cursor_new, col_name='file_number', pk=file, table=table_name)
add_old_data_to_new_table(cursor_old, conn_new, cursor_new, file_list, table_old, table_name, cols_set_old, file_number_name,
col_set_new)