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)