def get_table_list(self) -> list:
tables_to_print = []
for table in db_tables():
check = table_check(self.cursor_old, table)
if check:
tables_to_print.append(table)
return tables_to_print
def weekly_data(conn, cursor, table):
file_number_table = pd.DataFrame(columns=('File_number', table))
if table_check(cursor, table) == 1:
sql = ("SELECT File_number, update_by, last_update FROM '" + table + "'")
df_all = pd.read_sql(sql, conn)
df_all[table]= True
df_dup = df_all.drop_duplicates(keep= 'first')
base = datetime.datetime.today()
date_list = [base - datetime.timedelta(days=x) for x in range(0, 8)]
dates_toget = []
for index in date_list:
dates = index.strftime("%Y-%b-%d")
dates_toget.append(dates)
df_update = pd.DataFrame(columns = ['File_number', 'update_by', 'last_update'])
index = 0
for i in dates_toget:
sql = ("SELECT File_number, update_by, last_update FROM '" + table + "' WHERE last_update LIKE '"+i+"%'")
df_index = pd.read_sql(sql, conn)
if len(df_index) > 0:
for j in range(0, len(df_index)):
df_update.loc[index]= list(df_index.loc[j])
index = index +1
updates = [len(df_all), len(df_update)]
file_number_table = df_dup.loc[:,['File_number', table]]
else:
updates = "does not exist in the database so far"
return updates, file_number_table
# add capability to send list of files added, with yes/no in each table, should be for all files added.
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')
def output_data(self):
cursor = self.conn.cursor()
tables_to_print = []
summary_df = pd.DataFrame(columns=["table_name", "number_entries"])
output_name = self.output_name
if self.research:
output_name = 'Research_' + self.output_name
output_path = os.path.join(self.output_folder, output_name)
for table in print_db_tables():
check = table_check(cursor, table)
if check:
tables_to_print.append(table)
if not tables_to_print:
print(
"Selected Database has no tables. Please re-start and edit database file"
)
return
else:
writer = pd.ExcelWriter(output_path, engine='xlsxwriter')
print('This database contains the following tables:')
i = 1
for table in tables_to_print:
print(str(i) + ". " + table)
i = i + 1
to_print = ask_option(
"Do you want to print all tables or only select tables?",
["All tables", "Select tables"])
if to_print == "All tables":
index = 0
for table in tables_to_print:
self.print_table(writer, table)
number = self.print_table(writer, table)
summary_df.loc[index] = [table, number]
index = index + 1
elif to_print == "Select tables":
for table in tables_to_print:
to_print = ask_y_n("Do you want to print " + table)
if to_print:
self.print_table(writer, table)
number = self.print_table(writer, table)
summary_df.loc[table] = [table, number]
self.print_summary(summary_df)
print("Data file " + output_name + " has been created at " +
self.output_folder + '\n')
writer.save()
import pandas as pd
import sqlite3
import os
import modules.pccm_names as pccm_names
import sql.add_update_sql as sql
from datetime import datetime
#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"]
import sqlite3
from sql.add_update_sql import add_columns, table_check
from datetime import date
import os
import helper_function.pccm_names as pccm_names
import pandas as pd
folder = "d:/repos/pccm_db/main/DB/DB_with_real_data"
db_name = 'PCCM_BreastCancerDB_all_data.db'
path = os.path.join(folder, db_name)
conn_all = sqlite3.connect(path)
cursor_all = conn_all.cursor()
file_number = "file_number"
table = "patient_information_history"
if table_check(cursor_all, table) == 0:
cursor_all.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"
]
for index in module_names:
col_name = pccm_names.names_info(index)
add_columns(cursor_all, table, col_name)
table = "radiotherapy"
if table_check(cursor_all, table) == 0:
column = ", ".join(pccm_names.names_radiation())
cols_file = "file_number, " + column
cursor_all.execute('CREATE TABLE {tn}({nf})'.format(tn=table,
nf=cols_file))
from modules.pccm_names import db_tables
from sql.add_update_sql import table_check
from modules.ask_y_n_statement import ask_option, ask_y_n
from add_edit.output_excel import print_table
import os
import sqlite3
import pandas as pd
file_all = 'PCCM_BreastCancerDB_all_data_cleaned_dk.db'
folders = 'D:/Documents/IISER/Prashanti_docs/QSync/RESEARCH/Clinical_Database/PCCM Clinical Data/2018/08/07'
path = os.path.join(folders, file_all)
conn = sqlite3.connect(path)
cursor = conn.cursor()
tables_to_print = []
for table in db_tables():
check = table_check(cursor, table)
if check:
tables_to_print.append(table)
table = tables_to_print
df_del = [
'test', 'test1', 'test 434/16', 'test 335/17', 'test 351/17',
'test 346/16', 'test 312/17', 'test 259/17'
]
for index in table:
for j in df_del:
sql = "DELETE FROM '" + index + "' WHERE File_number = '" + j + "'"
cursor.execute(sql)
print('This database contains the following tables:')
i = 1
from sql.add_update_sql import add_columns, table_check
from datetime import datetime
import os
import modules.pccm_names as pccm_names
folder = "d:/repos/pccm_db/main/DB"
db_name = 'PCCM_BreastCancerDB_FFPE_check_2019-05-11.db'
db_name = "PCCM_BreastCancerDB_" + datetime.now().strftime("%Y_%m_%d") + '.db'
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 = [