def create_account_closed(self, new_account_info): #Info comes in as first name, last name, user name, password print(new_account_info) temp = new_account_info.split('//') reformated_data = ([0] * 4) reformated_data[0] = temp[1] reformated_data[1] = temp[0] reformated_data[2] = temp[2] reformated_data[3] = temp[3] db = DatabaseManager(self.db_file_loc, self.protected_table_prefix) if not db.doesTableExist(self.account_info_table_name): db.create_table_list(self.account_info_table_name, account_columns, 'string') db.add_row_list(self.account_info_table_name, account_columns, reformated_data) self.show()
class DatabaseManagerTester(unittest.TestCase): def setUp(self): sqlite_file = 'test.db' self.db = DatabaseManager(sqlite_file, '__ADMIN__') filename = "Test_Files/DatabaseManagerTest_15.csv" self.ingestor = Ingestor(filename) self.ingestor.readCSV() tempHeaders = self.ingestor.getCSVHeaders() self.searchCritera = [ tempHeaders[0], tempHeaders[1], tempHeaders[2], tempHeaders[4], tempHeaders[5], tempHeaders[6] ] searchCriteraTwoD = self.ingestor.getHeaderIndex( self.searchCritera, tempHeaders) self.ingestor.searchRows(searchCriteraTwoD, self.ingestor.getRows()) self.searchCritera = self.db.remove_spaces(self.searchCritera) self.new_table = 'Test_15' def test_create_new_table(self): self.assertTrue( self.db.create_table_list(self.new_table, self.searchCritera, 'string')) def test_add_row_list(self): self.assertTrue( self.db.add_list_of_rows(self.new_table, self.searchCritera, self.ingestor.getRows())) def test_get_headers(self): expectedReturn = [ 'Street_Address', "owner's_first_name", 'last_name', 'email', 'phone_Number', 'Loan_Amount' ] self.assertEqual(self.db.get_headers(self.new_table), expectedReturn) def test_get_table(self): pass def test_get_table_names(self): tables_in_db = self.db.get_table_names() self.assertTrue(self.new_table in tables_in_db) def test_get_row_at_with_column(self): column_to_use = "72 Pearson Drive" row_from_db = self.db.get_row_at(self.new_table, column_name=self.searchCritera[0], column_value=column_to_use) expectedRetun = [ u'72 Pearson Drive', u'Bartholemy', u'Parnaby', u'*****@*****.**', u'+55 (385) 326-3642', u'$44,795.68 ' ] #The lists are the same but it doesn't think they are equal #self.assertEqual(row_from_db,expectedRetun) def test_get_row_at_with_rowid(self): rowid = 3 row_from_db = self.db.get_row_at(self.new_table, row_id=rowid) expectedRetun = [ u'72 Pearson Drive', u'Bartholemy', u'Parnaby', u'*****@*****.**', u'+55 (385) 326-3642', u'$44,795.68 ' ] #self.assertEqual(row_from_db,expectedRetun) def test_delete_row(self): rowid = 9 rowToDel = self.db.get_row_at(self.new_table, row_id=rowid) rowAfterToDel = self.db.get_row_at(self.new_table, row_id=rowid + 1) self.db.delete_row_at(self.new_table, rowid) self.assertEqual(self.db.get_row_at(self.new_table, row_id=rowid), rowAfterToDel) def test_update_row(self): rowid = 9 old_row = self.db.get_row_at(self.new_table, row_id=rowid) updated_row1 = [ "a house", "josh", "green", "*****@*****.**", "228-192-2819", "$2.17" ] self.db.update_row_at(self.new_table, primary_key=rowid, new_row=updated_row1) self.assertTrue(old_row is not updated_row1)
] searchCriteraTwoD = ingestor.getHeaderIndex(searchCritera, tempHeaders) print("\nDictionary of search critera and their indexes in the csv") print(searchCriteraTwoD) ingestor.searchRows(searchCriteraTwoD, ingestor.getRows()) print("\nPrint filtered list from unfiltered row") print(ingestor.getRowAt(0)) searchCritera = db.remove_spaces(searchCritera) new_table = 'Test_15' print('\nCreating a new table using the search critera as headers') print('If the row already exists it will throw an error and continue') db.create_table_list(new_table, searchCritera, 'string') print('\nAdding all the rows from the CSV file into new table') for person in ingestor.getRows(): db.add_row_list(new_table, searchCritera, person) print(db.get_header_index(new_table, "email")) db.add_list_of_rows(new_table, searchCritera, ingestor.getRows()) print('\nPrinting table headers') print(db.get_headers(new_table)) print('\nPrinting all table entries') print(db.get_table(new_table)) print("\nPrinting the names of non-protected tables in the database")
class Ui_Dialog(QtWidgets.QDialog): calendar_dialog_signal = QtCore.pyqtSignal() def __init__(self, db_file, protected_table_prefix): try: super().__init__() self.setupUi() self.protected_table_prefix = protected_table_prefix self.db = DatabaseManager(db_file, self.protected_table_prefix) self.db_file_loc = db_file self.event_info_table_name = protected_table_prefix + "Event_Information" except Exception as er: print('Error message:', er.args[0]) return None # creates method for the ok button that creates variables holding the date and event text typed by the user # it will also save the information into the table def handle_acceptClick(self): eventDate = self.DateLineEdit.text() eventEdit = self.textEdit.toPlainText() # print(eventDate) #print(eventEdit) event_data = ([0] * 4) event_data[0] = eventDate event_data[1] = eventEdit event_data[2] = "" event_data[3] = "" # database stuff try: if not self.db.doesTableExist(self.event_info_table_name): self.db.create_table_list(self.event_info_table_name, Event_Columns, 'string') self.db.add_row_list(self.event_info_table_name, Event_Columns, event_data) self.calendar_dialog_signal.emit() self.close() except Exception as er: print('Error message:', er.args[0]) return False # creates method for the cancel button that closes the dialog and leaves the calendar widget open def handle_rejectClick(self): self.close() # creates method for the add address button for the dialog and opens something to add an address for the event def handle_addAddressButton(self): self.close() # creates method for the add group button for the dialog and opens something to add a group for the event def handle_addGroupButton(self): self.close() # auto generated code from Qt Designer that creates all the entities for the calendar dialog and their parameters def setupUi(self): self.setObjectName("Dialog") self.resize(350, 400) self.setAutoFillBackground(True) self.DateLabel = QtWidgets.QLabel(self) self.DateLabel.setGeometry(QtCore.QRect(20, 20, 50, 25)) font = QtGui.QFont() font.setPointSize(12) font.setBold(True) font.setWeight(75) self.DateLabel.setFont(font) self.DateLabel.setObjectName("DateLabel") self.EventLabel = QtWidgets.QLabel(self) self.EventLabel.setGeometry(QtCore.QRect(20, 60, 50, 25)) font = QtGui.QFont() font.setPointSize(12) font.setBold(True) font.setWeight(75) self.EventLabel.setFont(font) self.EventLabel.setObjectName("EventLabel") self.DateLineEdit = QtWidgets.QLineEdit(self) self.DateLineEdit.setGeometry(QtCore.QRect(80, 20, 200, 25)) self.DateLineEdit.setObjectName("DateLineEdit") self.AddAddressButton = QtWidgets.QPushButton(self) self.AddAddressButton.setGeometry(QtCore.QRect(20, 305, 75, 25)) self.AddAddressButton.setObjectName("AddAddressButton") self.AddGroupButton = QtWidgets.QPushButton(self) self.AddGroupButton.setGeometry(QtCore.QRect(180, 305, 75, 25)) self.AddGroupButton.setObjectName("AddGroupButton") self.AddressLabel = QtWidgets.QLabel(self) self.AddressLabel.setGeometry(QtCore.QRect(105, 305, 75, 25)) self.AddressLabel.setObjectName("AddressLabel") self.GroupLabel = QtWidgets.QLabel(self) self.GroupLabel.setGeometry(QtCore.QRect(260, 305, 75, 25)) self.GroupLabel.setObjectName("GroupLabel") self.ExampleLabel = QtWidgets.QLabel(self) self.ExampleLabel.setGeometry(QtCore.QRect(80, 50, 115, 13)) self.ExampleLabel.setObjectName("ExampleLabel") self.CancelButton = QtWidgets.QPushButton(self) self.CancelButton.setGeometry(QtCore.QRect(260, 360, 75, 25)) self.CancelButton.setObjectName("CancelButton") self.OkButton = QtWidgets.QPushButton(self) self.OkButton.setGeometry(QtCore.QRect(180, 360, 75, 25)) self.OkButton.setObjectName("OkButton") self.textEdit = QtWidgets.QTextEdit(self) self.textEdit.setGeometry(QtCore.QRect(20, 95, 260, 200)) self.textEdit.setObjectName("textEdit") self.retranslateUi() # creates the signal for the ok and reject buttons and connects them with their corresponding functions self.OkButton.clicked.connect(self.handle_acceptClick) self.CancelButton.clicked.connect(self.handle_rejectClick) self.AddAddressButton.clicked.connect(self.handle_addAddressButton) self.AddGroupButton.clicked.connect(self.handle_addGroupButton) QtCore.QMetaObject.connectSlotsByName(self) # auto generated code from Qt Designer def retranslateUi(self): _translate = QtCore.QCoreApplication.translate self.setWindowTitle(_translate("Dialog", "Dialog")) self.DateLabel.setText(_translate("Dialog", "Date")) self.EventLabel.setText(_translate("Dialog", "Event")) self.AddAddressButton.setText(_translate("Dialog", "Add Address")) self.AddGroupButton.setText(_translate("Dialog", "Add Group")) self.AddressLabel.setText(_translate("Dialog", "None")) self.GroupLabel.setText(_translate("Dialog", "None")) self.ExampleLabel.setText(_translate("Dialog", "Example: MM-DD-YY")) self.CancelButton.setText(_translate("Dialog", "Cancel")) self.OkButton.setText(_translate("Dialog", "OK"))
class csv_importer_popup(QtWidgets.QDialog): #Signals when csv_importer_popup closes importDoneSignal = QtCore.pyqtSignal('QString') def __init__(self, window_title, db_file_loc, tables, protected_table_prefix): super().__init__() #Creating the window self.title = window_title self.setWindowTitle(self.title) self.protected_table_prefix = protected_table_prefix self.tablesInDB = tables #Database manager stuff self.db = DatabaseManager(db_file_loc, protected_table_prefix) #Create array with tables already in the database to be #put in the common files radio button box self.default_lists = [] for table in tables: tempList = [] for columnName in self.db.get_headers(table): tempList.append(columnName) self.default_lists.append(tempList) self.layout = QGridLayout() def run_popup(self, file_loc): #CSV file stuff self.ingestor = Ingestor(file_loc) self.ingestor.readCSV() self.rows = self.ingestor.getCSVHeaders() #Create buttons from the csv file headers that was just selected self.generate_checkboxes(self.rows) #Create a area that has a scroll bar scrollArea = QScrollArea() scrollArea.setWidget(self.csvHeaderGroup_box) scrollArea.horizontalScrollBar().setEnabled(False) #Create the buttons for tables that already exist in the database self.generate_radiobuttons(self.tablesInDB) #List of button groups self.buttonGroups = [self.commonFileTypesGroup, self.csvHeaderGroup] #Create label tableNameLabel = QtWidgets.QLabel("Table Name") #Create text field self.tableNameField = QtWidgets.QLineEdit() self.tableNameField.setPlaceholderText("Enter Custom Table Name") #Create buttons self.cancelButton = QPushButton('Cancel') self.importButton = QPushButton('Import') self.cancelButton.clicked.connect(self.closeWindow) self.importButton.clicked.connect(self.importCSV) #Create progress Bar self.progressBar = QtWidgets.QProgressBar() #Create the master layout which is a grid layout = QGridLayout() #Add widgets #format of addWidget(widget,row,col,row span, col span) layout.addWidget(scrollArea, 1, 1, 1, 2) layout.addWidget(tableNameLabel, 2, 1, 1, 2) layout.addWidget(self.tableNameField, 3, 1, 1, 2) layout.addWidget(self.commonFileTypesGroupBox, 4, 1, 1, 2) layout.addWidget(self.progressBar, 5, 1, 1, 2) layout.addWidget(self.cancelButton, 6, 1) layout.addWidget(self.importButton, 6, 2) self.setLayout(layout) self.resize(self.sizeHint()) def generate_checkboxes(self, button_name_list): #Generate check_boxes self.csvHeaderGroup = QButtonGroup() self.csvHeaderGroup_layout = QVBoxLayout() self.csvHeaderGroup.setExclusive(False) self.csvHeaderGroup_box = QGroupBox('Select which headers') self.csvHeaderGroup_layout.addStretch(1) for button_name in button_name_list: #Add each button to the layout from the csv file checkbox = QCheckBox(button_name) self.csvHeaderGroup.addButton(checkbox) self.csvHeaderGroup_layout.addWidget( self.csvHeaderGroup.buttons()[-1]) self.csvHeaderGroup_box.setLayout(self.csvHeaderGroup_layout) def generate_radiobuttons(self, button_name_list): #Generate Radio Buttons self.commonFileTypesGroup = QButtonGroup() self.commonFileTypesGroupLayout = QVBoxLayout() self.commonFileTypesGroupBox = QGroupBox('Select a pre-existing table') self.commonFileTypesGroupLayout.addStretch(1) count = 0 for button_name in button_name_list: #Add each button from available lists in database radioButton = QRadioButton(button_name) self.commonFileTypesGroup.addButton(radioButton, count) self.commonFileTypesGroupLayout.addWidget( self.commonFileTypesGroup.buttons()[-1]) count += 1 self.commonFileTypesGroupBox.setLayout(self.commonFileTypesGroupLayout) def import_done(self, tableName): #Returns what table was created self.importDoneSignal.emit(tableName) self.accept() def closeWindow(self): #Closes the window self.reject() def importCSV(self): self.importButton.setEnabled(False) self.cancelButton.setEnabled(False) #Check if any radio buttons were pressed by checking if they were #checked and save the number in the button group radio_button_number = -1 special_button_number = -1 count = 0 for radioButton in self.buttonGroups[0].buttons(): if radioButton.isChecked(): radio_button_number = count break count += 1 for specialButton in self.buttonGroups[1].buttons( ): # Do the same for the special buttons if specialButton.isChecked(): special_button_number = count break count += 1 if radio_button_number > -1: searchCritera = self.ingestor.getHeaderIndex( self.default_lists[radio_button_number], self.ingestor.getCSVHeaders()) buttonText = self.buttonGroups[0].buttons( )[radio_button_number].text() #Check which table corresponds with the button pressed for tableName in self.tablesInDB: if buttonText.replace(' ', '_') == tableName: #Uses the ingestor to search the un-filtered rows using #this search criteria list self.ingestor.searchRows(searchCritera, self.ingestor.getRows()) #Check if tables exists already if not self.db.doesTableExist(tableName): #If not the create it with the table name self.db.create_table_list( tableName, self.db.remove_spaces( self.default_lists[radio_button_number]), 'string') self.import_with_progress_bar( tableName, self.ingestor.getRows(), self.default_lists[radio_button_number]) self.import_done(tableName) else: try: if self.tableNameField.text( ) == '' or self.protected_table_prefix in self.tableNameField.text( ): raise Exception() else: customTableName = self.db.is_valid_string( self.tableNameField.text().replace(' ', '_')) if special_button_number > -1: # Default header option not chosen, so custom lists try: requestedHeaders = [] for item in self.buttonGroups[1].buttons(): if item.isChecked(): requestedHeaders.append(item.text()) does_exist = self.db.doesTableExist( customTableName) has_same_cols = True if not does_exist: #If not the create it with the table name self.db.create_table_list( customTableName, self.db.remove_spaces(requestedHeaders), 'string') else: #Tables exists. Does it have the same columns? if not (requestedHeaders == self.db.get_headers(customTableName)): has_same_cols = False #Find the different column names #This works by turning the lists into sets #A set is an unordered list with no duplicate elements #A set supports matrix operations so you can subtract the two sets #This returns the elements that are not shared different_cols = list( set( self.db.remove_spaces( requestedHeaders)) - set( self.db.get_headers( customTableName))) #Add the extra columns for col in different_cols: self.db.add_column( customTableName, col, 'string') if has_same_cols: #New table is identical to existing one print("same columns") searchCritera = self.ingestor.getHeaderIndex( requestedHeaders, self.ingestor.getCSVHeaders()) self.ingestor.searchRows( searchCritera, self.ingestor.getRows()) rows = self.ingestor.getRows() self.import_with_progress_bar( customTableName, self.ingestor.getRows(), requestedHeaders) else: #New table has different columns #Combine the headers in the lists print("diff columns") combinedHeaders = self.db.get_headers( customTableName) + requestedHeaders #Have to re order them to match the csv file newRequestedHeaders = [] for header in self.db.remove_spaces( self.ingestor.getCSVHeaders()): #Find the header in the csv file #The order matters because the primary key is needed to update the row if header in combinedHeaders: newRequestedHeaders.append(header) #Get the index for the header searchCritera = self.ingestor.getHeaderIndex( newRequestedHeaders, self.ingestor.getCSVHeaders()) #Filter the rows so only the requested info is there self.ingestor.searchRows( searchCritera, self.ingestor.getRows()) rows = self.ingestor.getRows() #Import them nomrally self.import_with_progress_bar( customTableName, self.ingestor.getRows(), newRequestedHeaders) self.import_done(customTableName) except Exception as er: #General error message print('Error message:', er.args[0]) return False else: raise Exception() except: ErrorBox = QtWidgets.QMessageBox() choice = ErrorBox.critical( self, 'Table Name Error', "Table name can only have letters numbers, and underscores", ErrorBox.Ok) if choice == ErrorBox.Ok: #User wants to try a new name print("Closing") ErrorBox.accept() self.importButton.setEnabled(True) self.cancelButton.setEnabled(True) def import_with_progress_bar(self, tableName, rows_to_be_added, column_headers): """ Adds the ingestor rows to the db one row at a time so the progress bar will show the progress """ #Set the max value of the progress bar to the number of rows to be add self.progressBar.setMaximum(len(rows_to_be_added)) #self.db.add_list_of_rows(tableName,self.db.remove_spaces(self.default_lists[button_number]),rows) count = 0 for row in rows_to_be_added: #For every row to be added add it to the db and increment the progress #bar value by 1 count += 1 self.db.add_row_list(tableName, self.db.remove_spaces(column_headers), row) self.progressBar.setValue(count)