def backup():
directory = filedialog.askdirectory()
start_time = time.time()
#commit updates to csv files
tableDataCourse = evaluateExpression.setData('COURSE', [], True)
tableDataCourse.to_csv(directory + '/COURSE.csv', index=False)
tableDataCourseOffering = evaluateExpression.setData(
'COURSEOFFERING', [], True)
tableDataCourseOffering.to_csv(directory + '/COURSEOFFERING.csv',
index=False)
tableDataStudCourse = evaluateExpression.setData('STUDCOURSE', [], True)
tableDataStudCourse.to_csv(directory + '/STUDCOURSE.csv', index=False)
tableDataStudent = evaluateExpression.setData('STUDENT', [], True)
tableDataStudent.to_csv(directory + '/STUDENT.csv', index=False)
tableDataStudentHistory = evaluateExpression.setData(
'STUDENTHISTORY', [], True)
tableDataStudentHistory.to_csv(directory + '/STUDENTHISTORY.csv',
index=False)
print("\n--- execution time in seconds : %s ---" %
(time.time() - start_time))
def askopenfile():
file = filedialog.askopenfile(mode='r')
start_time = time.time()
data = pd.read_csv(file)
tableData = evaluateExpression.setData(optionSelected.get(), [], True)
tableData = tableData.append([data], ignore_index=True)
evaluateExpression.setData(optionSelected.get(), tableData, False)
if outputQuery.index("end") != 0:
outputQuery.configure(state='normal')
outputQuery.delete(1.0, 'end')
outputQuery.configure(state='disabled')
print("--- execution time in seconds : %s ---" %
(time.time() - start_time))
def insertQuery(table,columns, values):
tableData = evaluateExpression.setData(table,[],True)
insertDataTypeChecker(table,columns,values)
data = {}
counter = 0
if len(columns) != 0:
for cols in columns:
data[cols] = values[counter]
counter = counter + 1
else:
cols = getColumns(table)
counter = 0
for value in values:
data[cols[counter]] = value
counter = counter + 1
tableData = tableData.append([data],ignore_index=True)
evaluateExpression.setData(table,tableData,False)
def quit():
#commit updates to csv files
tableDataCourse = evaluateExpression.setData('COURSE',[],True)
tableDataCourse.to_csv('./data/COURSE.csv', index=False)
tableDataCourseOffering = evaluateExpression.setData('COURSEOFFERING',[],True)
tableDataCourseOffering.to_csv('./data/COURSEOFFERING.csv', index=False)
tableDataStudCourse = evaluateExpression.setData('STUDCOURSE',[],True)
tableDataStudCourse.to_csv('./data/STUDCOURSE.csv', index=False)
tableDataStudent = evaluateExpression.setData('STUDENT',[],True)
tableDataStudent.to_csv('./data/STUDENT.csv', index=False)
tableDataStudentHistory = evaluateExpression.setData('STUDENTHISTORY',[],True)
tableDataStudentHistory.to_csv('./data/STUDENTHISTORY.csv', index=False)
root.quit()
def displayTable(optionSelected):
start_time = time.time()
tableData = evaluateExpression.setData(optionSelected,[],True)
if outputQuery.index("end") != 0:
outputQuery.configure(state='normal')
outputQuery.delete(1.0, 'end')
outputQuery.configure(state='disabled')
print(tableData.replace(np.nan, '', regex=True))
print("\n--- execution time in seconds : %s ---" % (time.time() - start_time))
display_results_window(tableData)
def selectQuery(tableName,columns,whereClause,orderby):
tableData = evaluateExpression.setData(tableName,[],True)
table = {}
table['STUDENT'] = ['StudNo', 'StudentName','Birthday','Degree','Major','UnitsEarned']
table['STUDENTHISTORY'] =['StudNo','Description','Action','DateFiled','DateResolved']
table['COURSE'] =['CNo','CTitle', 'CDesc','NoOfUnits','HasLab','SemOffered']
table['COURSEOFFERING'] = ['Semester','AcadYear','CNo','Section','Time','MaxStud']
table['STUDCOURSE'] = ['StudNo','CNo','CDesc','Semester','AcadYear']
cols = table[tableName]
if columns[0] =='*':
columns = cols
if whereClause == 'null' and orderby == 'null':
tableData = tableData[columns].replace(np.nan, '', regex=True)
print(tableData.head(tableData.shape[0]))
print('\nNumber of rows returned: ' + str(tableData.shape[0]))
print('Number of columns returned: ' + str(tableData.shape[1]))
#only order by specified in the query
elif whereClause == 'null' and orderby != 'null':
if orderby[1] == 'asc':
tableData = tableData.sort_values([orderby[0]], ascending=True)
else:
tableData = tableData.sort_values([orderby[0]], ascending=False)
tableData = tableData[columns].replace(np.nan, '', regex=True)
print(tableData)
#only where clause is specified
elif whereClause != 'null' and orderby == 'null' :
if len(whereClause) == 4 :
newStr = whereClause[3].replace("'",'')
newStr = newStr.replace('"','')
if whereClause[2] == '=':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData[tableData[whereClause[1]].astype(float) == int(newStr)]#.head()
print(tableData[columns].replace(np.nan, '', regex=True))
elif dataTypeChecker(whereClause[1]) == 'varchar':
if len(newStr) > 50 :
print('values must be less than 50 characters')
else:
tableData= tableData[tableData[whereClause[1]] == newStr]#.head()
print(tableData[columns].replace(np.nan, '', regex=True))
elif whereClause[2] == '<':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData[tableData[whereClause[1]].astype(float) < int(newStr)]#.head()
print(tableData[columns].replace(np.nan, '', regex=True))
else:
print('Cannot perform lesser than operation on non-numeric column')
elif whereClause[2] == '>':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData[tableData[whereClause[1]].astype(float) > int(newStr)]#.head()
print(tableData[columns].replace(np.nan, '', regex=True))
else:
print('Cannot perform greater than operation on non-numeric column')
elif len(whereClause) == 5 :
newStr = whereClause[4].replace("'",'')
newStr = newStr.replace('"','')
if whereClause[2] == '<':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData[tableData[whereClause[1]].astype(float) <= int(newStr)]#.head()
print(tableData[columns].replace(np.nan, '', regex=True))
else:
print('Cannot perform greater than operation on non-numeric column')
elif whereClause[2] == '>':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData[tableData[whereClause[1]].astype(float) >= int(newStr)]#.head()
print(tableData[columns].replace(np.nan, '', regex=True))
else:
print('Cannot perform greater than operation on non-numeric column')
elif whereClause[2] == '!':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData[tableData[whereClause[1]].astype(float) != int(newStr)]#.head()
print(tableData[columns].replace(np.nan, '', regex=True))
elif dataTypeChecker(whereClause[1]) == 'varchar':
if len(newStr) > 50 :
print('values must be less than 50 characters')
else:
tableData= tableData[tableData[whereClause[1]] != newStr]#.head()
print(tableData[columns].replace(np.nan, '', regex=True))
#both where and order by are specified
elif whereClause != 'null' and orderby != 'null' :
if len(whereClause) == 4 :
newStr = whereClause[3].replace("'",'')
newStr = newStr.replace('"','')
newString = ''
for char in range(0, len(newStr)-1):
newString = newString + newStr[char]
newStr = newString
if whereClause[2] == '=':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData[tableData[whereClause[1]].astype(float) < int(newStr)]#.head()
if orderby[1] == 'asc':
tableData = tableData.sort_values([orderby[0]], ascending=True)
else:
tableData = tableData.sort_values([orderby[0]], ascending=False)
print(tableData[columns].replace(np.nan, '', regex=True))
elif dataTypeChecker(whereClause[1]) == 'varchar':
if len(newStr) > 50 :
print('values must be less than 50 characters')
else:
tableData= tableData[tableData[whereClause[1]] == newStr ]#.head()
if orderby[1] == 'asc':
tableData = tableData.sort_values([orderby[0]], ascending=True)
else:
tableData = tableData.sort_values([orderby[0]], ascending=False)
print(tableData[columns].replace(np.nan, '', regex=True))
elif whereClause[2] == '<':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData[tableData[whereClause[1]].astype(float) < int(newStr)].head()
if orderby[1] == 'asc':
tableData = tableData.sort_values([orderby[0]], ascending=True)
else:
tableData = tableData.sort_values([orderby[0]], ascending=False)
print(tableData[columns].replace(np.nan, '', regex=True))
else:
print('Cannot perform lesser than operation on non-numeric column')
elif whereClause[2] == '>':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData[tableData[whereClause[1]].astype(float) > int(newStr)]#.head()
if orderby[1] == 'asc':
tableData = tableData.sort_values([orderby[0]], ascending=True)
else:
tableData = tableData.sort_values([orderby[0]], ascending=False)
print(tableData[columns].replace(np.nan, '', regex=True))
else:
print('Cannot perform greater than operation on non-numeric column')
elif len(whereClause) == 5 :
newStr = whereClause[4].replace("'",'')
newStr = newStr.replace('"','')
if whereClause[2] == '<':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData[tableData[whereClause[1]].astype(float) <= int(newStr)]#.head()
if orderby[1] == 'asc':
tableData = tableData.sort_values([orderby[0]], ascending=True)
else:
tableData = tableData.sort_values([orderby[0]], ascending=False)
print(tableData[columns].replace(np.nan, '', regex=True))
else:
print('Cannot perform greater than operation on non-numeric column')
elif whereClause[2] == '>':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData[tableData[whereClause[1]].astype(float) >= int(newStr)]#.head()
if orderby[1] == 'asc':
tableData = tableData.sort_values([orderby[0]], ascending=True)
else:
tableData = tableData.sort_values([orderby[0]], ascending=False)
print(tableData[columns].replace(np.nan, '', regex=True))
else:
print('Cannot perform greater than operation on non-numeric column')
elif whereClause[2] == '!':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData[tableData[whereClause[1]].astype(float) != int(newStr)]#.head()
if orderby[1] == 'asc':
tableData = tableData.sort_values([orderby[0]], ascending=True)
else:
tableData = tableData.sort_values([orderby[0]], ascending=False)
print(tableData[columns].replace(np.nan, '', regex=True))
elif dataTypeChecker(whereClause[1]) == 'varchar':
if len(newStr) > 50 :
print('values must be less than 50 characters')
else:
tableData= tableData[tableData[whereClause[1]] != newStr]#.head()
if orderby[1] == 'asc':
tableData = tableData.sort_values([orderby[0]], ascending=True)
else:
tableData = tableData.sort_values([orderby[0]], ascending=False)
print(tableData[columns].replace(np.nan, '', regex=True))
return tableData
def deleteQuery(tableName, whereClause):
tableData = evaluateExpression.setData(tableName,[], True)
if len(whereClause) == 4 :
newStr = whereClause[3].replace("'",'')
newStr = newStr.replace('"','')
if whereClause[2] == '=':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData.loc[tableData[whereClause[1]].astype(float) != int(newStr)]#.head()
else:
tableData= tableData.loc[tableData[whereClause[1]] != newStr]#.head()
elif whereClause[2] == '<':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData.loc[tableData[whereClause[1]].astype(float) >= int(newStr)]#.head()
else:
print('Cannot perform lesser than operation on non-numeric column')
elif whereClause[2] == '>':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData.loc[tableData[whereClause[1]].astype(float) <= int(newStr)]#.head()
else:
print('Cannot perform greater than operation on non-numeric column')
evaluateExpression.setData(tableName,tableData,False)
elif len(whereClause) == 5 :
newStr = whereClause[4].replace("'",'')
newStr = newStr.replace('"','')
if whereClause[2] == '<':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData.loc[tableData[whereClause[1]].astype(float) > int(newStr)]#.head()
else:
print('Cannot perform greater than operation on non-numeric column')
elif whereClause[2] == '>':
if dataTypeChecker(whereClause[1]) == 'int':
tableData= tableData.loc[tableData[whereClause[1]].astype(float) < int(newStr)]#.head()
else:
print('Cannot perform greater than operation on non-numeric column')
elif whereClause[2] == '!':
if dataTypeChecker(whereClause[1]) == 'int':
tableData = tableData[tableData[whereClause[1]].astype(float) == int(newStr)]#.head()
else:
tableData = tableData[tableData[whereClause[1]] == newStr]#.head()
evaluateExpression.setData(tableName,tableData,False)
