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
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)
#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"
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)
'review_biopsy'
]
for index in module_names:
col_name = pccm_names.names_biopsy(index)
add_columns(cursor, table, col_name)
print(table + ' created')
table = 'surgery_path_report_data'
if table_check(cursor, table) == 0:
cursor.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf='fk'))
module_names = [
"surgery_block_information_0", "surgery_block_information_1",
"surgery_block_information_2", "surgery_block_information_3"
]
for index in module_names:
col_name = pccm_names.names_surgery(index)
add_columns(cursor, table, col_name)
print(table + ' created')
table = "block_data"
if table_check(cursor, table) == 0:
cursor.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf='fk'))
col_name = pccm_names.names_surgery('block_data')[1:]
add_columns(cursor, table, col_name)
print(table + ' created')
table = "radiology"
if table_check(cursor, table) == 0:
module_names = ["mammography", "abvs", "sonomammo", "mri_breast"]
cursor.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf=file_number))
for index in module_names:
if table_check(cursor, table) == 0:
cursor.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf=file_number))
module_names = ["biopsy_report_info", "tumour_biopsy_data"]
for index in module_names:
col_name = pccm_names.names_biopsy_new(index)
add_columns(cursor, table, col_name)
print(table + ' created')
table = "Surgery_Block_Report_Data"
if table_check(cursor, table) == 0:
cursor.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)
add_columns(cursor, table, col_name)
print(table + ' created')
table = "block_data"
if table_check(cursor, table) == 0:
col_name = " ".join(pccm_names.names_surgery('block_data'))
cursor.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf=col_name))
print(table + ' created')
table = "Radiology"
if table_check(cursor, table) == 0:
module_names = ["mammography", "abvs", "sonomammo", "mri_breast"]
cursor.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf=file_number))
for index in module_names:
col_name = pccm_names.names_radio(index)
add_columns(cursor, table, col_name)
print(table + ' created')
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:
table = "Biopsy_Report_Data"
if table_check(cursor, table) == 0:
cursor.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf=file_number))
module_names = ["biopsy_report_info", "tumour_biopsy_data"]
for index in module_names:
col_name = pccm_names.names_biopsy_new(index)
add_columns(cursor, table, col_name)
table = "Surgery_Block_Report_Data"
if table_check(cursor, table) == 0:
cursor.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)
add_columns(cursor, table, col_name)
table = "Radiology"
if table_check(cursor, table) == 0:
module_names = ["mammography", "abvs", "sonomammo",
"mri_breast"]
cursor.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf=file_number))
for index in module_names:
col_name = pccm_names.names_radio(index)
add_columns(cursor, table, col_name)
table = "Surgery_Report"
if table_check(cursor, table) == 0:
module_names = ["surgery_information", "node_excision","post_surgery"]
cursor.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf=file_number))