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 update_from_excel (file_name_excel, sheet, conn_new, cursor_new, col_names_new, col_names_old):
df_old = pd.read_excel(file_name_excel, sheet_name=sheet, index_col=0)
df_old = df_old.astype(str)
file_number_old = list(df_old.index)
table_name = Tables.sheet_name[sheet]
print(table_name)
sql_statement = 'SELECT file_number from ' + table_name
file_number = cursor_new.execute(sql_statement)
file_number_sql = file_number.fetchall()
file_list_sql = []
for file in file_number_sql:
file_var = file[0]
file_list_sql.append(file_var)
file_to_add = list(set(file_number_old) - set(file_list_sql))
if len(file_to_add)>0:
for file in file_to_add:
print(file)
sql.add_pk_fk_to_table(conn_new, cursor_new, col_name='file_number', pk=file, table=table_name)
data = df_old.loc[file]
for col in range(0, len(col_names_old)):
sql.update_single(conn_new, cursor_new, table_name, col_names_new[col], file,
data.loc[col_names_old[col]])
print(file, ' added to ', table_name, col_names_new[col])
else:
print('No new files to add')
def add_old_data_to_new_table(cursor_old, conn_new, cursor_new, file_list, table_old, table, cols_set_old, file_number_name, cols_set_new):
file_added = []
for file in file_list:
for col in range(0, len(cols_set_old)):
sql_statement = 'SELECT ' + cols_set_old[col] + ' FROM ' + table_old + " WHERE " + file_number_name + \
" = '" + file + "'"
data_old = cursor_old.execute(sql_statement)
if data_old != []:
data = data_old.fetchall()[0][0]
sql.update_single(conn_new, cursor_new, table, column=cols_set_new[col], file_number=file, var=data)
print(file, 'added to new database')
file_added = file_added + [file]
print (str(len(file_added)) + 'files added to ' + table)
def review_data(self):
sql = ('SELECT ' + ", ".join(self.columns) + ' FROM ' + self.table + " WHERE File_number = '" +
self.file_number + "'")
data = self.cursor.execute(sql)
data_list = data.fetchall()
data_list = list(data_list[0])
col_number = len(self.columns)
if data_list == [None]*len(data_list):
print("This section of the database has not been entered")
enter = ask.ask_y_n("Do you want to enter now")
return enter
if None in set(data_list):
print("Some entries are missing from the database: ")
for index in range(0, col_number):
print(self.columns[index] + " : " + str(self.data[index]))
enter = ask.ask_option("Do you want to proceed?", ["Edit all", "Add new data only"])
if enter == "Edit all":
return True
else:
edit_few(self.conn, self.cursor, self.table, self.columns, self.file_number, self.data)
else:
print("Entries present in database are as follows : ")
for index in range(0, col_number):
print(self.columns[index] + " : " + str(self.data[index]))
enter = ask.ask_option("Do you want to", ["Edit all", "Edit some entries", "Edit None"])
if enter == "Edit some entries":
for index in range(0, col_number):
print(self.columns[index] + " : " + str(self.data[index]))
edit = ask.ask_y_n("Edit")
if edit:
data = input("Data for " + self.columns[index] + ": ")
update_single(self.conn, self.cursor, self.table, self.columns, self.file_number, data)
return False
elif enter == "Edit all":
return True
else:
return False