def add_old_data_to_new_table(self, table, file_list):
modules = table_dicts.table_module_dict(table)
columns = []
if not modules:
modules = 'no_modules'
columns = table_dicts.db_dict(table, modules)
else:
for module in modules:
print(module)
cols = table_dicts.db_dict(table, module)
columns = columns + cols
for file in file_list:
for col in range(0, len(columns)):
sql_list = [
'SELECT ', columns[col], ' FROM ', table,
" WHERE file_number = '", file, "'"
]
sql_statement = ''.join(sql_list)
data_old = self.cursor_old.execute(sql_statement)
dat = data_old.fetchone()
if not (dat is None):
dat = dat[0]
print(dat)
# for dat in data_old:
sql.update_single(self.conn_new,
self.cursor_new,
table,
column=columns[col],
file_number=file,
var=dat)
print(str(len(file_list)) + 'files added to ' + table)
def add_old_data_to_new_table(self, table, file_list):
modules = table_dicts.table_module_dict(table)
columns = []
if not modules:
modules = 'no_modules'
columns = table_dicts.db_dict(table, modules)
else:
for module in modules:
print(module)
cols = table_dicts.db_dict(table, module)
columns = columns + cols
for file in file_list:
for col in range(0, len(columns)):
sql_statement = 'SELECT ' + columns[col] + ' FROM '
+table + " WHERE file_number = '" + file
+ "'"
data_old = self.cursor_old.execute(sql_statement)
data = data_old.fetchall()[0][0]
sql.update_single(self.conn_new,
self.cursor_new,
table,
column=columns[col],
file_number=file,
var=data)
print(str(len(file_list)) + 'files added to ' + table)
def print_table(self, writer, table):
modules = table_dicts.table_module_dict(table)
if table == 'Patient_Information_History' or table == 'block_list':
if self.research:
modules = table_dicts.table_module_research(table)
columns = []
if not modules:
modules = 'no_modules'
columns = table_dicts.db_dict(table, modules)
else:
for module in modules:
cols = table_dicts.db_dict(table, module)
columns = columns + cols
if table != 'Block_list':
col_list = table_dicts.create_col_list(columns)
else:
col_list = columns
if self.user_name != 'dk':
sql = ('SELECT ' + ", ".join(col_list) + " FROM '" + table + "'")
else:
sql = ('SELECT ' + ", ".join(col_list) + " FROM '" + table +
"' WHERE file_number NOT LIKE '%delete'")
df = pd.read_sql(sql, self.conn)
number = df.shape[0]
df.to_excel(writer,
sheet_name=table,
startrow=0,
index=False,
header=True)
return number
def create_table(self):
create = CreateTable(self.cursor)
modules = td.table_module_dict(self.table)
col_names = td.db_dict(self.table, modules)
create.create_table(self.table,
file_number='file_number',
col_names=col_names)
def get_sql_data(file_number, conn, module, table):
columns = []
cols = table_dicts.db_dict(table, module)
columns = columns + cols
col_list = table_dicts.create_col_list(columns)
sql_statement = ('SELECT ' + ", ".join(col_list) + " FROM '" + str(table) + "' WHERE file_number = '" +
file_number + "'")
df = pd.read_sql(sql_statement, conn)
return df
conn_all.commit()
conn_all.close()
file_rituja = 'PCCM_BreastCancerDB_all_data_rituja.db'
folders_rituja = 'D:/Documents/IISER/Prashanti_docs/QSync/RESEARCH/Clinical_Database/PCCM Clinical Data/2018/08/07'
path_rituja = os.path.join(folders_rituja, file_rituja)
conn_rituja = sqlite3.connect(path)
cursor_rituja = conn_rituja.cursor()
tables = ["Patient_Information_History", "Follow_up_Data", "HormoneTherapy_Survival", "RadiotherapyOptions",
"General_Medical_History", "Family_Cancer_History", 'Previous_Cancer_History', 'Nutritional_Supplements',
'Physical_Activity', 'Breast_Feeding']
modules = table_dicts.table_module_dict(table)
columns = []
if modules == []:
columns = table_dicts.db_dict(table, modules)
else:
for module in modules:
cols = table_dicts.db_dict(table, module)
columns = columns + cols
col_list = table_dicts.create_col_list(columns)
files = ['499/15', '657/16', '509/15']
for table in tables:
for file in files:
sql = "SELECT * FROM '" + table + "'"
df_ = pd.read_sql(sql, conn_nutan)
df_.to_sql(index, conn_all, index=False, if_exists="append")
## change column name
sql_tumour_size = "ALTER TABLE '" + index + "' ADD COLUMN 'Tumour_size'"