def get_block_pk(self,
user_name,
col_filter_value,
col_select='block_id',
col_filter='block_type'):
#retrieves a list of block_ids that correspond to a particular col_filter (block_type, file_number etc)
new_block = NewBlock(self.conn, self.cursor, user_name)
block_columns = ['file_number'] + names.block_list('all')
block_list = sql.extract_multiple_value_select_column(
self.conn, block_columns, self.table_name, self.file_number,
col_select, col_filter, col_filter_value)
block_id = ask.ask_list(
str(col_filter_value) +
' block id information is to be entered for: ',
block_list + ['not available', 'Other'])
block_list = ask.flatten_nested_list(block_list)
if block_id not in set(block_list):
new_block.add_new_pk(self.file_number, block_type=col_filter_value)
pk, number_of_blocks = self.get_block_information(
block_id, block_data=['pk', 'number_of_blocks'])
else:
sql_statement = ("SELECT pk FROM block_list WHERE (block_id = '" +
block_id + "')")
self.cursor.execute(sql_statement)
pk_ = self.cursor.fetchall()
pk = pk_[0][0]
number_of_blocks = sql.get_value(
col_name='number_of_blocks',
table='block_list',
pk_name='pk',
pk=pk,
cursor=self.cursor,
error_statement="Enter number of blocks: ")
return pk, str(block_id), number_of_blocks
def __init__(self, conn, cursor, user_name):
self.user_name = user_name
self.table_name = 'block_list'
self.conn = conn
self.cursor = cursor
self.columns = names.block_list('all')
self.block_data = pd.DataFrame(columns=['pk'] + self.columns)
def get_block_id(self, col_filter_value):
block_columns = ['file_number'] + names.block_list('all')
block_list = sql.extract_multiple_value_select_column(
self.conn,
block_columns,
table=self.table_name,
file_number=self.file_number,
col_select='block_id',
col_filter='block_type',
col_filter_value=col_filter_value)
block_id = ask.ask_list(
str(col_filter_value) +
' block id information is to be entered for: ',
block_list + ['not available', 'Other'])
block_list = ask.flatten_nested_list(block_list)
if block_id not in set(block_list):
number_of_blocks = 'not available'
else:
number_of_blocks = sql.get_value(col_name='number_of_blocks',
table='block_list',
pk_name='file_number',
pk=self.file_number,
cursor=self.cursor,
error_statement="Enter number of "
"blocks: ")
return str(block_id), str(number_of_blocks)
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 update_patient(self, file_number, block_id):
block_df = self.block_data
check = False
while not check:
print('update_patient')
pk = uuid.uuid4().hex
patient_name = self.get_value_and_check_value(
col_name='patient_name',
file_number=file_number,
input_statement='Please enter patient name: ',
integer=False)
block_sr_number = self.get_value_and_check_value(
col_name='block_sr_number',
file_number=file_number,
input_statement='Please enter block serial number: ',
integer=True)
if block_sr_number != 'block_not_available':
block_location_check = sql.get_value_no_error(
'block_location', self.table_name, file_number,
'file_number', self.cursor)
block_location = block_location_check
print('block_location: ' + str(block_location))
block_location_check = ask.ask_y_n('Is this correct?')
while not block_location_check:
block_location = block_location_format()
block_location_check = self.check_block_value_not_exist(
value_name='block_location',
value=block_location,
table=self.table_name)
blocks_received_at_pccm = sql.check_db_value(
col_name='blocks_received_at_pccm',
table=self.table_name,
file_number=file_number,
cursor=self.cursor,
error_statement='Please enter date blocks recieved at PCCM'
' (dd.mm.yyyy): ')
block_type = ask.ask_list('Block type', ['biopsy', 'surgery'])
if block_id == 'block_id':
block_id = self.check_block_value_in_db(
input_statement='Please enter block id: ',
value_name='block_id',
integer=False)
number_of_blocks = ask.check_number_input(
'Please enter number of blocks recieved at PCCM: ',
'Number of blocks has to be a whole number or NA')
block_series = input(
"Series of blocks recieved (Please separate series by ';'): "
)
current_block_location = ask.ask_list(
'Current location of block', names.block_list('location'))
consent_discussed, consent = self.get_consent(file_number)
else:
block_type = ask.ask_list('Block type', ['biopsy', 'surgery'])
if block_id == 'block_id':
block_id = self.check_block_value_in_db(
input_statement='Please enter block id: ',
value_name='block_id',
integer=False)
block_location, blocks_received_at_pccm, number_of_blocks, block_series, current_block_location, \
consent_discussed, consent = (block_sr_number, )*7
data_list = [
pk, file_number, patient_name, block_sr_number, block_location,
block_type, block_id, current_block_location,
blocks_received_at_pccm, number_of_blocks, block_series,
consent_discussed, consent, self.user_name,
sql.last_update()
]
block_df.loc[pk] = data_list
check, block_df = sql.review_df_row(block_df)
print("error check in update_patient")
sql.print_df(block_df)
return block_df
def add_update_patient(self):
block_df = self.block_data
file_number = 'test'
check_file = False
while not check_file:
print('add_update_patient')
file_number = input("Enter File Number: ")
print("File Number: " + file_number)
check_file = ask.ask_y_n("Is this file number correct")
check = False
while not check:
if sql.check_file_number_exist(self.cursor, file_number,
self.table_name):
print('add_update_patient_not_checck')
pk = uuid.uuid4().hex
patient_name = self.get_value_and_check_value(
col_name='patient_name',
file_number=file_number,
input_statement='Please enter patient name: ',
integer=False)
block_sr_number = self.get_value_and_check_value(
col_name='block_sr_number',
file_number=file_number,
input_statement='Please enter block serial number: ',
integer=True)
# block_location = 'block_location'
block_location_check = sql.get_value_no_error(
col_name='block_location',
table=self.table_name,
pk=file_number,
pk_name='file_number',
cursor=self.cursor)
if not block_location_check:
print(
'Block location already exists for another file_number'
)
while not block_location_check:
block_location = block_location_format()
block_location_check = sql.check_value_not_exist(
self.cursor,
value_name='block_location',
value=block_location,
table=self.table_name)
else:
block_location = block_location_check
blocks_received_at_pccm = sql.get_value_no_error(
col_name='blocks_received_at_pccm',
table=self.table_name,
pk=file_number,
pk_name='file_number',
cursor=self.cursor)
if not blocks_received_at_pccm:
blocks_received_at_pccm = ask.check_date_or_today(
'Please enter date blocks recieved at PCCM (or '
'today): ')
consent_discussed, consent = self.get_consent(file_number)
else:
pk = uuid.uuid4().hex
patient_name = input('Please enter patient name: ')
block_sr_number = self.check_block_value_in_db(
input_statement='Please enter block serial number: ',
value_name='block_sr_number',
integer=True)
block_pccm = ask.ask_y_n(
'Have these blocks been recieved at PCCM?')
block_location, blocks_received_at_pccm = [
'NA',
] * 2
if block_pccm:
block_location_check = False
while not block_location_check:
block_location = block_location_format()
block_location_check = sql.check_value_not_exist(
self.cursor,
value_name='block_location',
value=block_location,
table=self.table_name)
blocks_received_at_pccm = ask.check_date_or_today(
'Please enter date blocks recieved at PCCM (or '
'today): ')
consent_discussed, consent = self.get_consent(file_number)
block_type = ask.ask_list('Block type', ['biopsy', 'surgery'])
block_id = self.check_block_value_in_db(
input_statement='Please enter block id: ',
value_name='block_id',
integer=False)
number_of_blocks = ask.check_number_input(
'Please enter number of blocks recieved at PCCM: ',
'Number of blocks has to be a whole number or NA')
block_series = input(
"Series of blocks recieved (Please separate series by ';'): ")
current_block_location = ask.ask_list('Current location of block',
names.block_list('location'))
data_list = [
pk, file_number, patient_name, block_sr_number, block_location,
block_type, block_id, current_block_location,
blocks_received_at_pccm, number_of_blocks, block_series,
str(consent_discussed), consent, self.user_name,
sql.last_update()
]
# error check
print("error check in loop 1")
sql.print_df(block_df)
block_df.loc[pk] = data_list
print("error check in loop 2")
sql.print_df(block_df)
check, block_df = sql.review_df_row(block_df)
# error check
print("error check out of loop_to_db")
sql.print_df(block_df)
return block_df
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()
sheet_name='Follow_up_Data')
col_list = ["File_number"]
col_list = col_list + pccm_names.name_follow_up()
columns = ", ".join(col_list)
for index in range(0, len(data)):
data_list = list(data.loc[index])
sql.insert(conn_all, cursor_all, table, columns, tuple(data_list))
#add mock block list table
import pandas as pd
import sql.add_update_sql as sql
table = 'block_list'
file_to_read = "D:/OneDrive/iiser_data/Prashanti_docs/Database_files/Block_data_biopsy_surgery/" \
"2019_03_06_mock_block_list.xlsx"
data = pd.read_excel(file_to_read,
header=0,
dtype='object',
usecols='A:K',
sheet_name='Sheet2')
data[['last_update']] = sql.last_update()
col_list = ["file_number"]
col_list = col_list + pccm_names.block_list()
columns = ", ".join(col_list)
#convert dates to objects
data[['blocks_received_at_pccm']] = sql.last_update()
data[['update_by']] = 'dk'
for index in range(0, len(data)):
data_list = list(data.loc[index])
sql.insert(conn, cursor, table, columns, tuple(data_list))