def check_uniqeid_exist(uniqeid): uniqeid = uniqeid.upper() if FileOperator.csv_or_db() == "db": server_name, user_name, user_password, database_name = FileOperator.app_config_reader( ) db = sql.connect(host=server_name, user=user_name, password=user_password, charset='utf8', use_unicode=True, autocommit=True) cursor = db.cursor() query_in_users_exist = "SELECT unique_identifier FROM BloodDonationStorage.Donor WHERE unique_identifier = '" + uniqeid + "';" cursor.execute(query_in_users_exist) allusers = cursor.fetchall() if allusers: print("Already in the database.") return False else: return True else: file = open("Data/donors.csv", "r", encoding="utf-8") reader = csv.reader(file) for line in reader: if len(line) != 0 and uniqeid == line[6].upper(): print("Already in the database.") file.close() return False file.close() return True
def listing_database_db(which_file): server_name, user_name, user_password, database_name = FileOperator.app_config_reader() if 'donors' in which_file: table_name = '.Donor' else: table_name = '.Event' sql_command, result, header = [], [], [] sql_command.append("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE " + \ "`TABLE_SCHEMA`='" + database_name + "' AND `TABLE_NAME`='" + table_name[1:] + "';") sql_command.append("SELECT * FROM " + database_name + table_name) dbcon = mysql.connector.connect(user=user_name, password=user_password, host=server_name, database=database_name) cursor = dbcon.cursor() for i, one_command in enumerate(sql_command): cursor.execute(one_command) for cursor_message in cursor: if i == 0: header.append(cursor_message[0]) else: result.append(cursor_message) if len(result) != 0: if len(result) == 1: print("There is only one result:") elif len(result) > 1: print("There are " + str(len(result)) + " results:") print("-" * 52) for i in range(len(result)): ListingDataBase.printer(i + 1, header, result[i]) else: print("There is no data corresponding to this query...") dbcon.close() getch()
def search_in_file_db(which_file, search_term): num_of_find = 1 server_name, user_name, user_password, database_name = FileOperator.app_config_reader() sql_search_term = " LIKE '%" + search_term + "%' OR " if 'donors' in which_file: table_name = '.Donor' else: table_name = '.Event' sql_command, result, header = [], [], [] sql_command.append("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE " + \ "`TABLE_SCHEMA`='" + database_name + "' AND `TABLE_NAME`='" + table_name[1:] + "';") sql_command.append("SELECT * FROM " + database_name + table_name + " WHERE ") dbcon = mysql.connector.connect(user=user_name, password=user_password, host=server_name, database=database_name) cursor = dbcon.cursor() cursor.execute(sql_command[0]) for cursor_message in cursor: header.append(cursor_message[0]) for one_message in cursor_message: sql_command[1] += one_message + sql_search_term sql_command[1] = sql_command[1][0:len(sql_command[1]) - 4] cursor.execute(sql_command[1]) for cursor_message in cursor: if Search.search_and_print(search_term, header, cursor_message, num_of_find): num_of_find += 1 if num_of_find == 1: print("There is no data corresponding to this query...") dbcon.close()
def search_in_ids_db(id): server_name, user_name, user_password, database_name = FileOperator.app_config_reader() original, changed, sql_command, line, header, table_name = [], [], [], [], [], "" if id.isdigit(): table_name, id_name = ".Event", "id" elif (id[:6].isdigit() and id[6:8].isalpha() and len(id) == 8) or (id[:6].isalpha() and id[6:8].isdigit() and len(id) == 8): table_name, id_name = ".Donor", "unique_identifier" else: print("The input is not correct.") time.sleep(1.5) return sql_command.append("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE " + \ "`TABLE_SCHEMA`='" + database_name + "' AND `TABLE_NAME`='" + table_name[1:] + "';") sql_command.append("SELECT * FROM " + database_name + table_name + " WHERE " + id_name + " = '" + id + "';") dbcon = mysql.connector.connect(user=user_name, password=user_password, host=server_name, database=database_name) cursor = dbcon.cursor() cursor.execute(sql_command[0]) for cursor_message in cursor: header.append(cursor_message[0]) cursor.execute(sql_command[1]) for cursor_message in cursor: for one_message in cursor_message: line.append(str(one_message)) if id_name == "id": original.append(EventObject(line[0],line[1],line[2],line[3],line[4],line[5],line[6],line[7],line[8],line[9])) changed.append(EventObject(line[0],line[1],line[2],line[3],line[4],line[5],line[6],line[7],line[8],line[9])) line = [] else: original.append(DonorObject(line[0],line[1],line[2],line[3],str(line[4]).replace("None", "Never"),line[5],line[6],line[7],line[8],line[9],line[10],line[11],line[12])) changed.append(DonorObject(line[0],line[1],line[2],line[3],str(line[4]).replace("None", "Never"),line[5],line[6],line[7],line[8],line[9],line[10],line[11],line[12])) line = [] if id_name == 'id' and original: ChangeClass.change_process_event(original, changed, [id, header]) elif original: ChangeClass.change_process_donor(original, changed, [id, header]) else: print("Not included in the database.") time.sleep(1.5)