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")
