def create_table(self, table, file_number, col_names):
print(table)
print(col_names)
if sql.table_check(self.cursor, table) == 0:
self.cursor.execute('CREATE TABLE {tn}({nf})'.format(
tn=table, nf='file_number'))
[sql.add_columns(self.cursor, table, col) for col in col_names]
print(table + ' created')
def create_table_pk(self, table, col_names, pk='pk'):
print(table)
print(col_names)
if sql.table_check(self.cursor, table) == 0:
self.cursor.execute('CREATE TABLE {tn}({nf})'.format(tn=table,
nf=pk))
[sql.add_columns(self.cursor, table, col) for col in col_names]
print(table + ' created')
else:
print('Table already exists')
#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])
data_list.append(update_by)
path = os.path.join(folder, db_name)
conn = sqlite3.connect(path)
cursor = conn.cursor()
file_number = "file_number"
table = "patient_information_history"
if table_check(cursor, table) == 0:
cursor.execute('CREATE TABLE {tn}({nf})'.format(tn=table, nf=file_number))
module_names = [
"bio_info", "phys_act", "habits", "nut_supplements", "family_details",
"med_history", "cancer_history", "family_cancer", "det_by",
"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'