def removeShows(self): rowsSelected = self.tableWidget.selectionModel().selectedRows() if (len(rowsSelected) > 0): connection_object = connection_pool.get_connection() if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) index = rowsSelected[0] # index consist of row() column() row = index.row() ShowName = self.tableWidget.item(row, 0).text() ExhibitName = self.tableWidget.item(row, 1).text() DateTime = self.tableWidget.item(row, 2).text() try: cmd = "DELETE FROM SHOWS WHERE Name = \'" + ShowName + "\' AND DateTime= STR_TO_DATE(\'" + DateTime + "\' , \'%m/%d/%Y %r\');" cursor = connection_object.cursor() cursor.execute(cmd) connection_object.commit() print("Delete Successfully") self.searchShows() except mysql.connector.IntegrityError as err: print("Error: {}".format(err)) if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed") else: print("No row is selected")
def ShowDisplay(self, column=1): loginIdentity = __main__.loginIdentity[0][0] query = "SELECT Name, DateTime, Location FROM SHOWS WHERE Host = \'" + loginIdentity + "\' " query += " order by " + headerDict[column] + " " + orderDict[ self.currentOrder] + ";" if (self.currentOrder == 0): self.currentOrder = 1 else: self.currentOrder = 0 connection_object = connection_pool.get_connection() cursor = connection_object.cursor() cursor.execute(query) result = cursor.fetchall() self.StaffTable.setRowCount(0) for row_number, row_data in enumerate(result): self.StaffTable.insertRow(row_number) for column_number, data in enumerate(row_data): DATETIMECOLUMN = 1 cellContent = None if (column_number == DATETIMECOLUMN): cellContent = data.strftime("%m/%d/%Y %I:%M:%S %p") if (cellContent is None): cellContent = str(data) self.StaffTable.setItem( row_number, column_number, QtWidgets.QTableWidgetItem(str(cellContent))) if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def login(self): # retrive the strings from the lineEdit object email = self.emailLineEdit.text().lstrip().rstrip() password = self.passwordLineEdit.text() # build the SQL query command cmd1 = "select * from USER where password = md5(\'" + password + "\') and email = \'" + email + "\';" # obtain the connection_object connection_object = connection_pool.get_connection() # these three lines of code is used for debugging: CHECK FOR CONNECTIONS if connection_object.is_connected(): db_Info = connection_object.get_server_info() print("Connected to MySQL database using connection pool ... MySQL Server version on ",db_Info) # get cursor cursor = connection_object.cursor() # use cursor to execute sql command cursor.execute(cmd1) # there could have multiple lines of sql command # after all the command, retrieve the queries record = cursor.fetchall() print(record) if(len(record) > 0): __main__.loginIdentity = record __main__.status = __main__.statusDef['Normal'] __main__.state = __main__.initialUIs['exitInitialUIs'] # exit Initial UIs app.exit() else: self.showEmailNotExists() # close the cursor and connection if(connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def loadData(self, column=0): connection_object = connection_pool.get_connection() username = self.username.text() print(username) query = "" if (username == ""): query = "select Username, Email from USER where UserType ='Visitor' " else: query = "select Username, Email from USER where Username = \'" + username + "\' " query += " order by " + headerDict[column] + " " + orderDict[ self.currentOrder] + ";" if (self.currentOrder == 0): self.currentOrder = 1 else: self.currentOrder = 0 cursor = connection_object.cursor() cursor.execute(query) result = cursor.fetchall() self.visitorList.setRowCount(0) for row, row_data in enumerate(result): self.visitorList.insertRow(row) for column, data in enumerate(row_data): self.visitorList.setItem(row, column, QtWidgets.QTableWidgetItem(str(data))) cursor.close() connection_object.close()
def logvisit(self): connection_object = connection_pool.get_connection() if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) Visitor = __main__.loginIdentity[0][0] DateTime = time.strftime("%m/%d/%Y %I:%M:%S %p") try: cmd = "insert into VISITEXHIBIT values(\'" + Visitor + "\',\'" + str( __main__.arg[0][1] ) + "\',STR_TO_DATE(\'" + DateTime + "\' , \'%m/%d/%Y %r\'))" cursor = connection_object.cursor() cursor.execute(cmd) connection_object.commit() print("Insert Successfully") except mysql.connector.IntegrityError as err: print("Error: {}".format(err)) if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def searchShows(self, column=2): Name = self.lineEdit.text() Location = self.comboBox_exb.currentText() DateTime = self.dateTimeEdit.dateTime().toString( "MM/dd/yyyy hh:mm:ss AP") if (Location == "All"): Location = "" if (self.checkBox.isChecked()): DateTime = "" listTuple = [('Name', Name, "str"), ("Location", Location, "str"), ("DateTime", DateTime, "datetime")] cmd1 = "SELECT Name, Location as Exhibit, DateTime from SHOWS " cmd1 = util.addWHERE(cmd1, listTuple) cmd1 += " order by " + headerDict[column] + " " + orderDict[ self.currentOrder] + ";" if (self.currentOrder == 0): self.currentOrder = 1 else: self.currentOrder = 0 print(cmd1) connection_object = connection_pool.get_connection() if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) cursor = connection_object.cursor() cursor.execute(cmd1) record = cursor.fetchall() print(record) self.tableWidget.setRowCount(0) for row_num, row_data in enumerate(record): # insert a new blank row # in other words, expand the table by inserting a new row self.tableWidget.insertRow(row_num) for column_num, data in enumerate(row_data): # IMPORTANT DATETIMECOLUMN = 2 cellContent = None if (column_num == DATETIMECOLUMN): cellContent = data.strftime("%m/%d/%Y %I:%M:%S %p") if (cellContent == None): cellContent = str(data) self.tableWidget.setItem( row_num, column_num, QtWidgets.QTableWidgetItem(cellContent)) if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def addShows(self): Name = self.lineEdit.text() DateTime = self.dateTimeEdit.dateTime().toString( "MM/dd/yyyy hh:mm:ss AP") Host = self.comboBox_staff.currentText() Location = self.comboBox_exb.currentText() if Name.lstrip().rstrip() == "" or Host.lstrip().rstrip( ) == "" or Location.lstrip().rstrip() == "": self.InformationNotComplete() return else: # obtain the connection_object connection_object = connection_pool.get_connection() # these three lines of code is used for debugging: CHECK FOR CONNECTIONS if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) # get cursor cursor = connection_object.cursor() # SELECT DateTime, Host # FROM Shows # Where DateTime = STR_TO_DATE('2018/10/10 4:00:00 PM','%m/%d/%Y %r') and Host = 'benjamin_rao' cmd_query = "SELECT DateTime, Host From SHOWS" listTuple = [("DateTime", DateTime, "datatime"), ("Host", Host, "str")] cmd_query = util.addWHERE(cmd_query, listTuple) + ";" cursor.execute(cmd_query) result = cursor.fetchall() if len(result) == 0: cmd = "INSERT INTO SHOWS VALUES (\'" + Name + "\', STR_TO_DATE(\'" + DateTime + "\',\'%m/%d/%Y %r\'), \'" + Host + "\', \'" + Location + "\');" # use cursor to execute sql command try: cursor.execute(cmd) connection_object.commit() self.Insert_successful() except mysql.connector.IntegrityError as err: self.IntegrityError() print("Error: {}", format(err)) else: self.cannotAdd() # close the cursor and connection if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def logVisit(self): rowsSelected = self.tableWidget.selectionModel().selectedRows() if(len(rowsSelected) >0): index = rowsSelected[0] # index consist of row() column() row = index.row() Visitor = __main__.loginIdentity[0][0] ShowName = self.tableWidget.item(row,0).text() ExhibitName = self.tableWidget.item(row,1).text() DateTime = self.tableWidget.item(row,2).text() showTime = datetime.strptime(DateTime, '%m/%d/%Y %I:%M:%S %p') if(showTime <= datetime.now()): print("DateTime") print(DateTime) cmd1 = "INSERT INTO VISITSHOWS VALUES (\'" + Visitor + "\' , \'" + ShowName + "\' , STR_TO_DATE(\'" + DateTime + "\' , \'%m/%d/%Y %r\') );" cmd2 = "INSERT INTO VISITEXHIBIT VALUES (\'" + Visitor + "\' , \'" + ExhibitName + "\' , STR_TO_DATE(\'" + DateTime + "\' , \'%m/%d/%Y %r\') );" print("cmd1: " + cmd1) print("cmd2: " + cmd2) # obtain the connection_object connection_object = connection_pool.get_connection() # these three lines of code is used for debugging: CHECK FOR CONNECTIONS if connection_object.is_connected(): db_Info = connection_object.get_server_info() print("Connected to MySQL database using connection pool ... MySQL Server version on ",db_Info) # get cursor cursor = connection_object.cursor() ########## This block of code is used to catch the exception in the database################ ########## To detect whether there already exist such tuples which violates ################ ########## PRIMARY KEY INTEGRITY try: # use cursor to execute sql command cursor.execute(cmd1) cursor.execute(cmd2) # commit your transaction connection_object.commit() print("Insert Successfully") except mysql.connector.IntegrityError as err: print("Error: {}".format(err)) self.showCannotLogVisitMultipleTimes() ######################################################################################### # close the cursor and connection if(connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed") else: self.showShowDateTimeHasNotStarted() print("Show\'s DateTime has passed") else: print("No row is selected")
def SearchAnimals(self, column=0): Name = self.NameLineEdit.text() Species = self.SpeciesLineEdit.text() Exhibit = self.ExhibitComboBox.currentText() Type = self.TypeComboBox.currentText() MaxAge = self.MaxSpinBox.value() MinAge = self.MinSpinBox.value() if (Exhibit == "All"): Exhibit = "" if (Type == "All"): Type = "" if (MaxAge == 0 and MinAge == 0): MaxAge = '' MinAge = '' listTuple = [("Name", Name, "str"), ("Species", Species, "str"), ("Exhibit", Exhibit, "str"), ("Type", Type, "str"), ("MinAge", MinAge, "int"), ("MaxAge", MaxAge, "int")] cmd1 = "SELECT Name, Species, Exhibit,Age, Type FROM ANIMAL" cmd1 = util.addWHERE(cmd1, listTuple) cmd1 += " order by " + headerDict[column] + " " + orderDict[ self.currentOrder] + ";" if (self.currentOrder == 0): self.currentOrder = 1 else: self.currentOrder = 0 connection_object = connection_pool.get_connection() if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) cursor = connection_object.cursor() cursor.execute(cmd1) record = cursor.fetchall() print(record) self.AnimalTable.setRowCount(0) for row_number, row_data in enumerate(record): self.AnimalTable.insertRow(row_number) for column_number, data in enumerate(row_data): self.AnimalTable.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(str(data))) if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def deleteData(self): index_list = [] for model_index in self.visitorList.selectionModel().selectedRows(): index = QtCore.QPersistentModelIndex(model_index) index_list.append(index) connection_object = connection_pool.get_connection() cursor = connection_object.cursor() for index in index_list: row = index.row() name = index.sibling(row, 0).data() email = index.sibling(row, 1).data() query = "delete from USER WHERE Username =\'" + name + "\'and Email =\'" + email + "\';" cursor.execute(query) connection_object.commit() self.visitorList.removeRow(index.row()) cursor.close() connection_object.close()
def displayText(self): print(__main__.arg[0][1]) self.lineEdit_name.setText(str(__main__.arg[0][1])) connection_object = connection_pool.get_connection() if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) #display Size cmd = " SELECT Size FROM EXHIBIT WHERE Name = \'" + str( __main__.arg[0][1]) + "\' ;" # get cursor cursor = connection_object.cursor() cursor.execute(cmd) record = cursor.fetchall() self.lineEdit_size.setText(str(record[0][0])) #display WaterFeature cmd2 = " SELECT WaterFeature FROM EXHIBIT WHERE Name = \'" + str( __main__.arg[0][1]) + "\' ;" cursor.execute(cmd2) record = cursor.fetchall() if (record[0][0] is 1): self.lineEdit_water.setText("Yes") elif (record[0][0] is 0): self.lineEdit_water.setText("No") #display number of animals cmd3 = "SELECT COUNT(*) FROM EXHIBIT as E, ANIMAL as A WHERE E.Name=A.Exhibit AND E.Name = \'" + str( __main__.arg[0][1]) + "\' GROUP BY E.Name" cursor.execute(cmd3) record = cursor.fetchall() self.lineEdit_numanimals.setText(str(record[0][0])) if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def displayAnimals(self, column=0): connection_object = connection_pool.get_connection() if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) cmd = "SELECT Name, Species FROM ANIMAL WHERE Exhibit = \'" + str( __main__.arg[0][1]) + "\' " cmd += " order by " + headerDict[column] + " " + orderDict[ self.currentOrder] if (self.currentOrder == 0): self.currentOrder = 1 else: self.currentOrder = 0 cursor = connection_object.cursor() cursor.execute(cmd) record = cursor.fetchall() self.tableWidget.setRowCount(0) for row_num, row_data in enumerate(record): # insert a new blank row # in other words, expand the table by inserting a new row self.tableWidget.insertRow(row_num) for column_num, data in enumerate(row_data): # IMPORTANT # first you must determine in which column does the DateTime attribute occur in your # query cellContent = None if (cellContent is None): cellContent = str(data) self.tableWidget.setItem( row_num, column_num, QtWidgets.QTableWidgetItem(cellContent)) # close the cursor and connection if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def loadAnimalDetails(self): # construct the query command cmd = "SELECT Name, Species, Type, Age, Exhibit FROM ANIMAL " cmd = util.addWHERE(cmd, __main__.arg) cmd += ";" # DEBUG OUTPUT print("cmd") print(cmd) # obtain the connection_object connection_object = connection_pool.get_connection() # these three lines of code is used for debugging: CHECK FOR CONNECTIONS if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) # get cursor cursor = connection_object.cursor() try: # use cursor to execute sql command cursor.execute(cmd) # there could have multiple lines of sql command # after all the command, retrieve the queries record = cursor.fetchall() # for DEBUGGING purpose print(record) self.nameLineEdit.setText(str(record[0][0])) self.speciesLineEdit.setText(str(record[0][1])) self.typeLineEdit.setText(str(record[0][2])) self.ageLineEdit.setText(str(record[0][3])) self.exhibitLineEdit.setText(str(record[0][4])) except mysql.connector.Error as err: print("Something went wrong: {}".format(err)) # close the cursor and connection if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def deleteData(self): index_list = [] for model_index in self.listOfAnimals.selectionModel().selectedRows(): index = QtCore.QPersistentModelIndex(model_index) index_list.append(index) connection_object = connection_pool.get_connection() cursor = connection_object.cursor() for index in index_list: row = index.row() name = index.sibling(row, 0).data() species = index.sibling(row, 1).data() atype = index.sibling(row, 2).data() age = index.sibling(row, 3).data() exhibit = index.sibling(row, 4).data() query = "delete from ANIMAL WHERE Name =\'" + name + "\'and Species =\'" + species + "\';" cursor.execute(query) connection_object.commit() self.listOfAnimals.removeRow(index.row()) cursor.close() connection_object.close()
def addAnimals(self): Name = self.lineEdit.text() Species = self.lineEdit_species.text() Type = self.comboBox_type.currentText() Age = self.spinBox_age.value() Exhibit = self.comboBox_exb.currentText() if Name.lstrip().rstrip() == "" or Species.lstrip().rstrip( ) == "" or Type.lstrip().rstrip() == "" or Age < 0 or Exhibit.lstrip( ).rstrip() == "": self.InformationNotComplete() return else: cmd = "INSERT INTO ANIMAL VALUES (\'" + Name + "\', \'" + Species + "\', \'" + Type + "\', " + str( Age) + ", \'" + Exhibit + "\');" # obtain the connection_object connection_object = connection_pool.get_connection() # these three lines of code is used for debugging: CHECK FOR CONNECTIONS if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) # get cursor cursor = connection_object.cursor() # use cursor to execute sql command try: cursor.execute(cmd) connection_object.commit() self.Insert_successful() except mysql.connector.IntegrityError as err: self.IntegrityError() print("Error: {}", format(err)) # close the cursor and connection if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def loadData(self, column=0): connection_object = connection_pool.get_connection() Name = self.name.text() Species = self.species.text() Exhibit = self.exhibit.currentText() Type = self.type.currentText() MaxAge = self.maxAge.value() MinAge = self.minAge.value() if (Exhibit == "All"): Exhibit = "" if (Type == "All"): Type = "" if (MaxAge == 0 and MinAge == 0): MaxAge = '' MinAge = '' listTuple = [("Name", Name, "str"), ("Species", Species, "str"), ("Exhibit", Exhibit, "str"), ("Type", Type, "str"), ("MinAge", MinAge, "int"), ("MaxAge", MaxAge, "int")] cmd1 = "SELECT Name, Species, Exhibit,Age, Type FROM ANIMAL" cmd1 = util.addWHERE(cmd1, listTuple) cmd1 += " order by " + headerDict[column] + " " + orderDict[ self.currentOrder] + ";" if (self.currentOrder == 0): self.currentOrder = 1 else: self.currentOrder = 0 cursor = connection_object.cursor() cursor.execute(cmd1) result = cursor.fetchall() self.listOfAnimals.setRowCount(0) for row, row_data in enumerate(result): self.listOfAnimals.insertRow(row) for column, data in enumerate(row_data): self.listOfAnimals.setItem( row, column, QtWidgets.QTableWidgetItem(str(data))) cursor.close() connection_object.close()
def populateStaffComboBox(self): # construct query command cmd = " SELECT * FROM STAFF;" # obtain the connection_object connection_object = connection_pool.get_connection() # these three lines of code is used for debugging: CHECK FOR CONNECTIONS if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) # get cursor cursor = connection_object.cursor() cursor.execute(cmd) result = cursor.fetchall() # close the cursor and connection if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed") for staff in result: self.comboBox_staff.addItem(staff[0])
def searchAnimal(self, column=0): Name = self.lineEdit_name.text() Species = self.lineEdit_species.text() Exhibit = self.ExhibitCombo.currentText() Type = self.TypeCombo.currentText() MaxAge = self.spinBox_max.value() MinAge = self.spinBox_min.value() if (Exhibit == "All"): Exhibit = "" if (Type == "All"): Type = "" if (MaxAge == 0 and MinAge == 0): MaxAge = '' MinAge = '' listTuple = [("Name", Name, "str"), ("Species", Species, "str"), ("Exhibit", Exhibit, "str"), ("Type", Type, "str"), ("MinAge", MinAge, "int"), ("MaxAge", MaxAge, "int")] cmd1 = "SELECT Name, Species, Exhibit, Type, Age FROM ANIMAL " cmd1 = util.addWHERE(cmd1, listTuple) cmd1 += " order by " + headerDict[column] + " " + orderDict[ self.currentOrder] + ";" if (self.currentOrder == 0): self.currentOrder = 1 else: self.currentOrder = 0 connection_object = connection_pool.get_connection() if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) cursor = connection_object.cursor() cursor.execute(cmd1) record = cursor.fetchall() self.tableWidget.setRowCount(0) for row_num, row_data in enumerate(record): # insert a new blank row # in other words, expand the table by inserting a new row self.tableWidget.insertRow(row_num) for column_num, data in enumerate(row_data): # IMPORTANT # first you must determine in which column does the DateTime attribute occur in your # query cellContent = None if (cellContent is None): cellContent = str(data) self.tableWidget.setItem( row_num, column_num, QtWidgets.QTableWidgetItem(cellContent)) if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def _register(): email = self.emailLineEdit.text().lstrip().rstrip() username = self.usernameLineEdit.text().lstrip().rstrip() password = self.passwordLineEdit.text() confirmPassword = self.confirmPasswordLineEdit.text() if (password == confirmPassword and len(password) >= 8): # build the SQL query command UserType = "" if (userType == "VISITOR"): UserType = "VUsername" elif (userType == "STAFF"): UserType = "SUsername" cmd1 = "select * from USER where Username = \'" + username + "\' or Email = \'" + email + "\';" cmd2 = "insert into USER values(\'" + username + "\' , md5(\'" + password + "\') , \'" + email + "\' , \'" + userType.lower( ) + "\' );" cmd3 = "insert into " + userType + " values(\'" + username + "\');" # additional query to ensure that the USER record has been added to the database cmd4 = "select * from USER where Password = md5(\'" + password + "\') and Email = \'" + email + "\' ;" # obtain the connection_object connection_object = connection_pool.get_connection() # these three lines of code is used for debugging: CHECK FOR CONNECTIONS if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) # get cursor cursor = connection_object.cursor() # use cursor to execute sql command cursor.execute(cmd1) # there could have multiple lines of sql command # after all the command, retrieve the queries record = cursor.fetchall() # close the cursor and connection # if the record is empty # USER DOES NOT EXIST if (len(record) == 0): if re.match( r"^[A-Za-z0-9\.\+_-]+@[A-Za-z0-9\._-]+\.[a-zA-Z]*$", email): cursor.execute(cmd2) cursor.execute(cmd3) # for any changes/ alteration that should be done # you MUST commit for the transaction to be executed connection_object.commit() cursor.execute(cmd4) record = cursor.fetchall() if (len(record) == 0): self.showUsernameNotExists() else: __main__.loginIdentity = record __main__.status = __main__.statusDef['Normal'] __main__.state = __main__.initialUIs[ 'exitInitialUIs'] # exit initial UIs print("loginIdentity") print(__main__.loginIdentity) app.exit() else: self.showEmailNotValid() else: self.showUsernameExists() # close the cursor and connection if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed") else: self.showPasswordMissMatchDialog()
def searchExhibitHistory(self, column = 2): ExhibitName = self.nameLineEdit.text().lstrip().rstrip() DateTime = self.dateTimeEdit.dateTime().toString("MM/dd/yyyy hh:mm:ss AP") NumVisitsMin = str(self.numVisitsMinSpinBox.value()) NumVisitsMax = str(self.numVisitsMaxSpinBox.value()) if(self.dateTimeCheckBox.isChecked()): DateTime = "" if(NumVisitsMin == "0" and NumVisitsMax == "0"): NumVisitsMin = "" NumVisitsMax = "" listTuple = [("ExhibitName", ExhibitName, "str"), ("DateTime", DateTime, "datetime") \ , ("NumVisitsMin", NumVisitsMin, "int"), ("NumVisitsMax", NumVisitsMax, "int")] # contruct the sql command cmdheader1 = "SELECT * from " cmdTemp1 = "(SELECT ExhibitName, DateTime from VISITEXHIBIT as v1 WHERE Visitor = \'" \ + __main__.loginIdentity[0][0] + "\') as visitExhibit1" cmdNatJoin = " Natural Join " cmdTemp2 = "(SELECT ExhibitName, count(*) as NumVisits FROM VISITEXHIBIT as v2 WHERE Visitor = \'" \ + __main__.loginIdentity[0][0] + "\' group by ExhibitName order by NumVisits) as visitExhibit2" cmdend1 = " order by " + headerDict[column] + " " + orderDict[self.currentOrder] if(self.currentOrder == 0): self.currentOrder = 1 else: self.currentOrder = 0 cmd1 = cmdheader1 + "(" + cmdTemp1 + cmdNatJoin + cmdTemp2 + ") " cmd1 = util.addWHERE(cmd1, listTuple) + cmdend1 + ";" # DEBUG OUTPUT print("listTuple") print(listTuple) print("cmd1") print(cmd1) # obtain the connection_object connection_object = connection_pool.get_connection() # these three lines of code is used for debugging: CHECK FOR CONNECTIONS if connection_object.is_connected(): db_Info = connection_object.get_server_info() print("Connected to MySQL database using connection pool ... MySQL Server version on ",db_Info) # get cursor cursor = connection_object.cursor() try: # use cursor to execute sql command cursor.execute(cmd1) # there could have multiple lines of sql command # after all the command, retrieve the queries record = cursor.fetchall() # for DEBUGGING purpose print(record) # this statement clears all the rows self.tableWidget.setRowCount(0) for row_num, row_data in enumerate(record): # insert a new blank row # in other words, expand the table by inserting a new row self.tableWidget.insertRow(row_num) for column_num, data in enumerate(row_data): # IMPORTANT # first you must determine in which column does the DateTime attribute occur in your # query DATETIMECOLUMN = 1 cellContent = None if(column_num == DATETIMECOLUMN): cellContent = data.strftime("%m/%d/%Y %I:%M:%S %p") if(cellContent is None): cellContent = str(data) self.tableWidget.setItem(row_num, column_num, QtWidgets.QTableWidgetItem(cellContent)) except mysql.connector.Error as err: print("Something went wrong: {}".format(err)) # close the cursor and connection if(connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def searchShow(self, column = 2): Name = self.lineEdit_name.text().lstrip().rstrip() Location = str(self.comboBox_exb.currentText()) DateTime = self.dateTimeEdit.dateTime().toString("MM/dd/yyyy hh:mm:ss AP") if(Location == "All"): Location = "" if(self.checkBox.isChecked()): DateTime = "" listTuple = [('Name', Name, "str"), ("Location", Location, "str"), ("DateTime", DateTime, "datetime")] cmd1 = "SELECT Name, Location as Exhibit, DateTime from SHOWS " cmd1 = util.addWHERE(cmd1, listTuple) cmd1 += " order by " + headerDict[column] + " " + orderDict[self.currentOrder] + ";" if(self.currentOrder == 0): self.currentOrder = 1 else: self.currentOrder = 0 # DEBUG OUTPUT print("listTuple") print(listTuple) print("cmd1") print(cmd1) # obtain the connection_object connection_object = connection_pool.get_connection() # these three lines of code is used for debugging: CHECK FOR CONNECTIONS if connection_object.is_connected(): db_Info = connection_object.get_server_info() print("Connected to MySQL database using connection pool ... MySQL Server version on ",db_Info) # get cursor cursor = connection_object.cursor() # use cursor to execute sql command cursor.execute(cmd1) # there could have multiple lines of sql command # after all the command, retrieve the queries record = cursor.fetchall() # for DEBUGGING purpose only print(record) # this statement clear all the rows in the table self.tableWidget.setRowCount(0) for row_num, row_data in enumerate(record): # insert a new blank row # in other words, expand the table by inserting a new row self.tableWidget.insertRow(row_num) for column_num, data in enumerate(row_data): # IMPORTANT DATETIMECOLUMN = 2 cellContent = None if(column_num == DATETIMECOLUMN): cellContent = data.strftime("%m/%d/%Y %I:%M:%S %p") if(cellContent == None): cellContent = str(data) self.tableWidget.setItem(row_num, column_num,QtWidgets.QTableWidgetItem(cellContent)) # close the cursor and connection if(connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def loadNotes(self, column=2): # construct the querycommand to retrieve logged notes regarding to the animal cmd1 = "SELECT Staff, Text, DateTime FROM NOTE " AnimalName = __main__.arg[0][1] AnimalSpecies = __main__.arg[1][1] listTuple = [("AnimalName", AnimalName, "str"), ("AnimalSpecies", AnimalSpecies, "str")] cmd1 = util.addWHERE(cmd1, listTuple) cmd1 += " order by " + headerDict[column] + " " + orderDict[ self.currentOrder] + ";" if (self.currentOrder == 0): self.currentOrder = 1 else: self.currentOrder = 0 # for DEBUGGING print("cmd1") print(cmd1) # obtain the connection_object connection_object = connection_pool.get_connection() # these three lines of code is used for debugging: CHECK FOR CONNECTIONS if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) # get cursor cursor = connection_object.cursor() try: # use cursor to execute sql command cursor.execute(cmd1) # there could have multiple lines of sql command # after all the command, retrieve the queries record = cursor.fetchall() # for DEBUGGING purpose print(record) # this statement clears all the rows self.tableWidget.setRowCount(0) for row_num, row_data in enumerate(record): # insert a new blank row # in other words, expand the table by inserting a new row self.tableWidget.insertRow(row_num) for column_num, data in enumerate(row_data): # IMPORTANT # first you must determine in which column does the DateTime attribute occur in your # query DATETIMECOLUMN = 2 cellContent = None if (column_num == DATETIMECOLUMN): cellContent = data.strftime("%m/%d/%Y %I:%M:%S %p") if (cellContent is None): cellContent = str(data) self.tableWidget.setItem( row_num, column_num, QtWidgets.QTableWidgetItem(cellContent)) except mysql.connector.Error as err: print("Something went wrong: {}".format(err)) # close the cursor and connection if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")
def logNote(self): Staff = __main__.loginIdentity[0][0] AnimalName = __main__.arg[0][1] AnimalSpecies = __main__.arg[1][1] DateTime = str(time.strftime("%m/%d/%Y %I:%M:%S %p")) Text = str(self.NoteTextEdit.toPlainText()).replace("\'", "\\'") print("Note to be Logged") print(Text) listTuple = [("Staff", Staff, "str"), ("AnimalName", AnimalName, "str"), ("AnimalSpecies", AnimalSpecies, "str") \ , ("DateTime", DateTime, "datetime"), ("Text", Text, "str")] # construct the INSERT command cmd1 = "INSERT INTO NOTE VALUES(\'" + Staff +"\' , \'" + AnimalName + "\', \'" + AnimalSpecies + "\' , STR_TO_DATE(\'" \ + DateTime + "\' , \' %m/%d/%Y %r \'), \'" + Text + "\' )" # for DEBUGGING print("cmd1") print(cmd1) # construct the querycommand to retrieve logged notes regarding to the animal cmd2 = "SELECT Staff, Text, DateTime FROM NOTE " AnimalName = __main__.arg[0][1] AnimalSpecies = __main__.arg[1][1] listTuple = [("AnimalName", AnimalName, "str"), ("AnimalSpecies", AnimalSpecies, "str")] cmd2 = util.addWHERE(cmd2, listTuple) + ";" # for DEBUGGING print("cmd2") print(cmd2) # obtain the connection_object connection_object = connection_pool.get_connection() # these three lines of code is used for debugging: CHECK FOR CONNECTIONS if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) # get cursor cursor = connection_object.cursor() ########## This block of code is used to catch the exception in the database################ ########## To detect whether there already exist such tuples which violates ################ ########## PRIMARY KEY INTEGRITY try: # use cursor to execute sql command cursor.execute(cmd1) # commit your transaction connection_object.commit() print("Insert Successfully") except mysql.connector.IntegrityError as err: print("Error: {}".format(err)) # close the cursor and connection if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed") ######################################################################################### ########## Query and update the table ############## self.loadNotes()
def searchExhibit(self, column=0): Name = self.lineEdit_name.text() MaxSize = self.spinBox_maxsize.value() MinSize = self.spinBox_minsize.value() MaxNum = self.spinBox_maxnum.value() MinNum = self.spinBox_minnum.value() WaterFeature = self.WaterCombo.currentText() if (WaterFeature == "All"): WaterFeature = "" if (WaterFeature == "Yes"): WaterFeature = "True" if (WaterFeature == "No"): WaterFeature = "False" if (MaxSize == 0 and MinSize == 0): MaxSize = '' MinSize = '' if (MaxNum == 0 and MinNum == 0): MaxNum = '' MinNum = '' cmd1 = "SELECT * FROM (SELECT E.Name as Name, WaterFeature, Size, COUNT(*) as Num FROM EXHIBIT as E, ANIMAL as A" AExhibit = "A.Exhibit" listTuple1 = [("E.Name", AExhibit, "var"), ("E.name", Name, "str"), ("WaterFeature", WaterFeature, "bool"), ("MinSize", MinSize, "int"), ("MaxSize", MaxSize, "int")] cmd1 = util.addWHERE(cmd1, listTuple1) cmd1 += " GROUP BY E.Name) as t1" listTuple2 = [("MinNum", MinNum, "int"), ("MaxNum", MaxNum, "int")] cmd1 = util.addWHERE(cmd1, listTuple2) cmd1 += " order by " + headerDict[column] + " " + orderDict[ self.currentOrder] + ";" if (self.currentOrder == 0): self.currentOrder = 1 else: self.currentOrder = 0 print(cmd1) connection_object = connection_pool.get_connection() if connection_object.is_connected(): db_Info = connection_object.get_server_info() print( "Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info) cursor = connection_object.cursor() cursor.execute(cmd1) record = cursor.fetchall() print(record) self.tableWidget.setRowCount(0) for row_num, row_data in enumerate(record): # insert a new blank row # in other words, expand the table by inserting a new row self.tableWidget.insertRow(row_num) for column_num, data in enumerate(row_data): # IMPORTANT # first you must determine in which column does the DateTime attribute occur in your # query cellContent = None if (column_num == 1): if (data == 0): cellContent = "No" else: cellContent = "Yes" if (cellContent is None): cellContent = str(data) self.tableWidget.setItem( row_num, column_num, QtWidgets.QTableWidgetItem(cellContent)) if (connection_object.is_connected()): cursor.close() connection_object.close() print("MySQL connection is closed")