def __init__(self, fk, file_number, block_id, block_no, user_name): self.pk = fk self.file_number = file_number self.block_id = block_id self.block_no = block_no self.block_desc_df_cols = names.names_surgery('block_data') self.block_type_list = names.names_surgery('block_type_list') self.user_name = user_name
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
def block_description_for_db(block_desc_df): block_type_list = names.names_surgery('block_type_list') block_data_all = [] for type in block_type_list: query_type = 'block_type == ' + '"' + type + '"' id_desc = block_desc_df.query(query_type) if list(id_desc.shape)[0] == 0: block_data_type = ['Not Removed'] block_data_all.append(block_data_type) else: block_data_type = [] pk = list(id_desc['block_reference']) desc = list(id_desc['block_description']) for index in range(list(id_desc.shape)[0]): data = pk[index] + ": " + desc[index] block_data_type.append(data) block_data_type = ["; ".join(block_data_type)] block_data_all.append(block_data_type) return block_data_all
col_list = ["File_number"] for index in module_names: col_list = col_list + names.names_biopsy(index) sql = ('SELECT ' + ", ".join(col_list) + " FROM '" + table + "'") df = pd.read_sql(sql, conn) df.to_excel(writer, sheet_name=table, index=False) #writer.save() table = "Surgery_Block_Report_Data" 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 + names.names_surgery(index) sql = ('SELECT ' + ", ".join(col_list) + " FROM '" + table + "'") df = pd.read_sql(sql, conn) df.to_excel(writer, sheet_name=table, index=False) writer.save() table = "Radiology" sql = "SELECT File_number FROM '" + table + "'" df = pd.read_sql(sql, conn) df_list = list(df['File_number']) module_names = [ "mammography", "tomosynthesis", "abvs", "sonomammo", "mri_breast" ] col_list = ["File_number"] for index in module_names:
def makedb(self): db_create = CreateTable(self.cursor) file_number = "file_number" table = "patient_information_history" module_list = [ "bio_info", "phys_act", "habits", "nut_supplements", "family_details", "med_history", "cancer_history", 'family_cancer', 'det_by', "breast_symptoms", 'other_test' ] col_names = [pccm_names.names_info(module) for module in module_list] col_list = ask.flatten_nested_list(col_names) db_create.create_table(table, file_number=file_number, col_names=col_list) table = "biopsy_path_report" module_list = [ "biopsy_report_info", "biopsy_details", 'ihc_biopsy_data', 'review_biopsy' ] col_names = [pccm_names.names_biopsy(module) for module in module_list] col_list = ask.flatten_nested_list(col_names) db_create.create_table_pk(table, col_list) table = 'surgery_path_report_data' module_list = [ "surgery_block_information_0", "surgery_block_information_1", "surgery_block_information_2", "surgery_block_information_3" ] col_names = [ pccm_names.names_surgery(module) for module in module_list ] col_list = ask.flatten_nested_list(col_names) db_create.create_table_pk(table, col_list, pk='fk') table = "block_data" col_names = pccm_names.names_surgery('block_data')[1:] db_create.create_table_pk(table, col_names, pk='fk') table = "mammography" col_names = pccm_names.names_radio(table) db_create.create_table_pk(table, col_names) table = 'abvs' col_names = pccm_names.names_radio(table) db_create.create_table_pk(table, col_names) table = 'ultrasound' col_names = pccm_names.names_radio(table) db_create.create_table_pk(table, col_names) table = 'mri' col_names = pccm_names.names_radio(table) db_create.create_table_pk(table, col_names) table = 'pet_reports' module_list = [ 'pet_report_identifier', 'pet_report_findings', 'pet_breast_cancer' ] col_names = [pccm_names.names_pet(module) for module in module_list] col_list = ask.flatten_nested_list(col_names) db_create.create_table_pk(table, col_list) table = "surgery_report" module_list = ["surgery_information", "node_excision", "post_surgery"] col_names = [ pccm_names.names_surgery_information(module) for module in module_list ] col_list = ask.flatten_nested_list(col_names) db_create.create_table(table, file_number='file_number', col_names=col_list) table = "general_medical_history" col_names = ['condition', 'diagnosis_date', 'treatment'] # col_list = ask.flatten_nested_list(col_names) db_create.create_table(table, file_number='file_number', col_names=col_names) table = "family_cancer_history" col_names = [ 'type_cancer', 'relation_to_patient', 'type_relation', 'age_at_detection_yrs' ] # col_list = ask.flatten_nested_list(col_names) db_create.create_table(table, file_number='file_number', col_names=col_names) table = "previous_cancer_history" col_names = [ 'type_cancer', 'year_diagnosis', 'surgery', 'type_surgery', 'duration_surgery', 'radiation', 'type_radiation', 'duration_radiation', 'chemotherapy', 'type_chemotherapy', 'duration_chemotherapy', 'hormone', 'type_hormone', 'duration_hormone', 'alternative', 'type_alternative', 'duration_alternative', 'homeremedy', 'type_homeremedy', 'duration_homeremedy' ] col_list = ask.flatten_nested_list(col_names) db_create.create_table(table, file_number='file_number', col_names=col_list) table = "nutritional_supplements" col_names = [ 'type_nutritional_supplements', 'quantity_nutritional_supplements_per_day', 'duration_nutritional_supplements' ] col_list = ask.flatten_nested_list(col_names) db_create.create_table(table, file_number='file_number', col_names=col_list) table = "physical_activity" col_names = ['type_activity', 'frequency_activity'] col_list = ask.flatten_nested_list(col_names) db_create.create_table(table, file_number='file_number', col_names=col_list) table = "breast_feeding" col_names = [ 'child_number', 'feeding_duration', 'breast_usage_feeding' ] col_list = ask.flatten_nested_list(col_names) db_create.create_table(table, file_number='file_number', col_names=col_list) table = "nact_tox_table" col_names = pccm_names.names_nact(table) db_create.create_table(table, file_number='file_number', col_names=col_names) table = "nact_drug_table" col_names = pccm_names.names_nact(table) db_create.create_table(table, file_number='file_number', col_names=col_names) table = "neo_adjuvant_therapy" module_list = ["neo_adjuvant_therapy", "clip_information"] col_names = [pccm_names.names_nact(module) for module in module_list] col_list = ask.flatten_nested_list(col_names) db_create.create_table(table, file_number='file_number', col_names=col_list) table = "adjuvant_chemotherapy" col_names = pccm_names.names_chemotherapy(table) db_create.create_table(table, file_number='file_number', col_names=col_names) table = "chemo_tox_table" col_names = pccm_names.names_chemotherapy(table) db_create.create_table(table, file_number='file_number', col_names=col_names) table = "chemo_drug_table" col_names = pccm_names.names_chemotherapy(table) db_create.create_table(table, file_number='file_number', col_names=col_names) table = "radiotherapy" col_names = pccm_names.names_radiation() db_create.create_table(table, file_number='file_number', col_names=col_names) table = "follow_up_data" col_names = pccm_names.name_follow_up() db_create.create_table(table, file_number='file_number', col_names=col_names) table = "hormonetherapy_survival" module_list = ["hormone", "metastasis"] col_names = [ pccm_names.names_longterm(module) for module in module_list ] col_list = ask.flatten_nested_list(col_names) db_create.create_table(table, file_number='file_number', col_names=col_list) table = 'block_list' col_names = pccm_names.block_list('all') db_create.create_table_pk(table, col_names=col_names, pk='pk') table = "clinical_exam" module_list = ['clinical_exam_initial', 'nipple_cytology'] col_names = [ pccm_names.name_clinical(module) for module in module_list ] col_list = ask.flatten_nested_list(col_names) db_create.create_table(table, file_number=file_number, col_names=col_list) self.conn.commit() print(self.path + " file created") self.conn.close()
data_list = list(data.loc[index]) data_list.append(update_by) data_list.append(sql.last_update()) sql.insert(conn_all, cursor_all, table, columns, data_list) #for surgery_report table = "Surgery_Block_Report_Data" if sql.table_check(cursor_all, table) == 0: cursor_all.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf=file_number)) module_names = [ "surgery_block_information_1", "surgery_block_information_2", "surgery_block_information_3", "path_stage" ] for index in module_names: col_name = pccm_names.names_surgery(index) sql.add_columns(cursor_all, table, col_name) #add data file_to_read = "D:/Documents/IISER/Prashanti_docs/Breast_Cancer_FFPE_blocks_database_Surgery_dk08062018.xlsx" data = pd.read_excel(file_to_read, header=1, dtype='object', usecols='A:BB') update_by = "dk from ruhi/shaheen data" 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)