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)