class ImportFile: running = '' # *************************************************** def __init__(self): self.keypress = '' self.filename = '' self.import_table = '' self.database = '' self.file_columns = [] # *************************************************** # *********************************** def scan_dir(self): from os import listdir from os.path import isfile, join from common.utilities import menu_builder file_system_errors = False menu_config = { 'config': { 'title': 'Select File', 'subtitle': '' }, 'menu_entries': [] } try: all_files = [ f for f in listdir('process_files/') if isfile(join('process_files/', f)) ] except: file_system_errors = True if file_system_errors is True: menu_config['config'][ 'subtitle'] = 'missing the folder process_files in this directory' if file_system_errors is False: counter = 0 for item in all_files: menu_config['menu_entries'].append({ 'display': str(counter) + " = " + item, 'key': counter, 'run_function': '' }) counter += 1 menu_config['menu_entries'].append({ 'display': "c = cancel", 'key': 'c', 'run_function': self.menu }) self.keypress = menu_builder(menu_config) if self.keypress == 'c': #nothing has to be done here, it will return to the loop in main pass else: if file_system_errors is False: bad_keypress = False #case str to int. if they typed in a letter, then dont go forward try: index = int(self.keypress) except: bad_keypress = True self.scan_dir() if bad_keypress is False: if index >= len(all_files): self.scan_dir() else: self.filename = all_files[index] self.prep() # *********************************** # *********************************** def prep(self): from common.utilities import menu_builder from classes.database import Database # pylint: disable=no-name-in-module menu_config = { 'config': { 'title': 'Import File', 'subtitle': '' }, 'menu_entries': [] } self.keypress = menu_builder(menu_config) self.database = Database() if self.database.error != '': print() print(self.database.error) print('Press enter to go back to the main menu') input() ImportFile.running = False else: while True: proceed = self.check_table() if proceed is True: break else: print("Table doesn't exist. Try again") if self.check_columns() is False: print("The above columns don't exist") print('Press enter to go back to the main menu') input() ImportFile.running = False self.database.close() else: self.import_file() # *********************************** # *********************************** def import_file(self): cursor = self.database.db_conn.cursor(dictionary=True) columns = ",".join(self.file_columns) sql = "LOAD DATA LOCAL INFILE 'process_files/" + self.filename + "' INTO TABLE " + self.import_table + " FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n' IGNORE 1 LINES (" + columns + ")" cursor.execute(sql) input('done. press return to go back to the main menu') ImportFile.running = False self.database.close() # *********************************** # *********************************** def check_table(self): self.import_table = input('What is the import table name: ') if self.import_table not in self.database.tables: return False else: return True # *********************************** # *********************************** def check_columns(self): cursor = self.database.db_conn.cursor(dictionary=True) first_line = '' table_columns = [] proceed = True with open('process_files/' + self.filename) as f: first_line = f.readline() self.file_columns = first_line.split("\t") if len(self.file_columns) < 2: proceed = False print('Check file format') for i in range(len(self.file_columns)): self.file_columns[i] = self.file_columns[i].replace("\n", '') sql = "describe " + self.import_table cursor.execute(sql) results = cursor.fetchall() for row in results: if row['Field'] != 'id': table_columns.append(row['Field']) for item in self.file_columns: if item not in table_columns: print(item) proceed = False #sql = "select * from temp_table where firstname = %s" #cursor.execute(insert_query, (string1, string2), multi=True) #the trailing comma is needed for single #cursor.execute(sql, (test,)) #results = cursor.fetchall() return proceed # *********************************** # *********************************** def to_main_menu(self): #set running to false, the while loop in main will exit and return back to the main loop in main ImportFile.running = False # *********************************** # *********************************** def menu(self): from common.utilities import menu_builder menu_config = { 'config': { 'title': 'File Importer', 'subtitle': '' }, 'menu_entries': [{ 'display': 's = scan process_files', 'key': 's', 'run_function': self.scan_dir }, { 'display': 'm = main menu', 'key': 'm', 'run_function': self.to_main_menu }] } self.keypress = menu_builder(menu_config) for item in menu_config['menu_entries']: if item['key'] == self.keypress: item['run_function']() break # *********************************** # *********************************** def main(self): #import ../../databaseconfig as dbinfo #connect(cfg.mysql["host"], cfg.mysql["user"], cfg.mysql["password"]) ImportFile.running = True while ImportFile.running: self.menu()