Ejemplo n.º 1
0
 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
Ejemplo n.º 2
0
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.
Ejemplo n.º 3
0
 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')
Ejemplo n.º 4
0
 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')
Ejemplo n.º 5
0
 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()
Ejemplo n.º 6
0
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"]
Ejemplo n.º 7
0
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))
Ejemplo n.º 8
0
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
Ejemplo n.º 9
0
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 = [