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
#create new table
path_all = 'D:/repos/pccm_db/main/DB/DB_with_real_data/PCCM_BreastCancerDB_all_data.db'
os.path.isfile(path_all)
conn_all = sqlite3.connect(path_all)
cursor_all = conn_all.cursor()
table = "Biopsy_Report_Data"
file_number = "File_number"
if sql.table_check(cursor_all, table) == 0:
cursor_all.execute('CREATE TABLE {tn}({nf})'.format(tn=table,
nf=file_number))
module_names = [
"biopsy_report_info", "tumour_biopsy_data", "lymphnode_biopsy"
]
for index in module_names:
col_name = pccm_names.names_biopsy(index)
sql.add_columns(cursor_all, table, col_name)
#read from excel with same col names and distribution as in table
file_to_read = "D:/Documents/IISER/Prashanti_docs/Breast_Cancer_FFPE_blocks_database_Biopsy_dk08062018.xlsx"
data = pd.read_excel(file_to_read, header=1, dtype='object', usecols='A:AB')
update_by = "dk from ruhi/shaheen data"
last_update = datetime.now().strftime("%Y-%b-%d %H:%M")
module_names = ["biopsy_report_info", "tumour_biopsy_data", "lymphnode_biopsy"]
col_list = ["File_number"]
for index in module_names:
col_list = col_list + pccm_names.names_biopsy(index)
columns = ", ".join(col_list)
for index in range(0, len(data)):
data_list = list(data.loc[index])
"breast_symptoms", 'other_test'
]
for index in module_names:
col_name = pccm_names.names_info(index)
add_columns(cursor, table, col_name)
print(table + ' created')
table = "biopsy_path_report_data"
if table_check(cursor, table) == 0:
cursor.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf='pk'))
module_names = [
"biopsy_report_info", "biopsy_details", 'ihc_biopsy_data',
'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')
col_list = ["File_number"] + col_list_bio + names.names_info("phys_act") + names.names_info("habits") + \
names.names_info("nut_supplements") + names.names_info("family_details") + names.names_info("med_history") + \
names.names_info("cancer_history") + names.names_info("family_cancer") + names.names_info("breast_symptoms")
sql = ('SELECT ' + ", ".join(col_list) + " FROM '" + table + "'")
df = pd.read_sql(sql, conn)
df.to_excel(writer, sheet_name=table)
writer.save()
#######################################33
table = "Biopsy_Report_Data"
module_names = ["biopsy_report_info", "tumour_biopsy_data", "lymphnode_biopsy"]
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)