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