def signup(): dataJSON = request.get_json() username = str(dataJSON['username']) password = str(dataJSON['password']) name = str(dataJSON['name']) conn = db.connect() query1 = 'INSERT INTO Users (name, username, password) VALUES ("{}", "{}", "{}")'.format( name, username, password) conn.execute(query1) conn.close() conn = db.connect() query = "SELECT COUNT(*) AS isSuccessful, id AS userId FROM Users WHERE username = '******' AND password = '******' GROUP BY id;".format( username, password) query_results = conn.execute(query) conn.close() results = [] for row in query_results: results.append(dict(row)) result_dict = {'results': results} if (len(results) == 0): return dict({"isSuccessful": 0, "userId": -1}) return results[0]
def get_table_data(table): """ gets all of the data in 'table' """ conn = db.connect() query = 'SELECT * FROM ' + str(table) + ' LIMIT 1000;' query_results = conn.execute(query) query_results = [entry for entry in query_results] conn.close() return query_results
def update(table:str, _id:int, text:str, userId:int, rating): conn = db.connect() if table == "Reviews": query1 = 'UPDATE Reviews SET review = "{}", rating = "{}" WHERE id = {} and userID = {}'.format(text, rating, _id, userId) else: query1 = 'UPDATE Answers SET answer = "{}" WHERE id = {} and userId = {}'.format(text, _id, userId) conn.execute(query1) conn.close()
def getLocationStatistics(): conn = db.connect() query = "SELECT * FROM LocationStatistics;" query_results = conn.execute(query) results = [dict(row) for row in query_results] result_dict = {'results': results} return jsonify(result_dict)
def UserVisited_Range(num:int, locationID:int): date = datetime.now() - timedelta(int(num)) pastdate = date.strftime("%Y-%m-%d %H:%M:%S") conn = db.connect() query = "SELECT sum(hasCOVID = 1) as covid, sum(hasCOVID = 0) as notCovid FROM UserVisited WHERE time > '" + pastdate + "' AND locationID = " + locationID query_results = conn.execute(query) conn.close() return query_results
def restaurantHoursVisited(): conn = db.connect() query = "SELECT hour(time) as hourVisited, sum(hasCovid=1) as CovidCount, sum(hasCovid=0) as nonCovidCount FROM UserVisited WHERE locationID in (SELECT locationID FROM LocationOfType WHERE categoryID = 1) GROUP BY hour(time) ORDER BY hourVisited;" query_results = conn.execute(query) conn.close() results = [dict(row) for row in query_results] result_dict = {'results': results} return jsonify(result_dict)
def restaurantRatings(): conn = db.connect() query = "SELECT DISTINCT l.name, AVG(r.rating) as avg_rating FROM Reviews r JOIN Locations l ON r.locationID = l.id WHERE l.name = ANY (SELECT l.name FROM Locations l JOIN LocationOfType t on l.id = t.locationID JOIN Categories c ON t.categoryID = c.id WHERE c.name = 'Restaurant') GROUP BY l.name ORDER BY avg_rating DESC;" query_results = conn.execute(query) conn.close() results = [dict(row) for row in query_results] result_dict = {'results': results} return jsonify(result_dict)
def locationsPositive(): conn = db.connect() query = "SELECT DISTINCT L.name, count(distinct U.id) as NumberVisited FROM Users U JOIN UserVisited UV ON U.id = UV.userID JOIN Locations L ON UV.locationID = L.id WHERE U.hasCovid = 1 GROUP BY L.name;" query_results = conn.execute(query) conn.close() results = [dict(row) for row in query_results] result_dict = {'results': results} return jsonify(result_dict)
def locationCategory(): conn = db.connect() query = "SELECT Categories.name AS category_name, Locations.name AS location_name FROM LocationOfType JOIN Categories ON LocationOfType.categoryID = Categories.id JOIN Locations ON LocationOfType.locationID = Locations.id GROUP BY Categories.name, Locations.name ORDER BY Categories.name;" query_results = conn.execute(query) conn.close() results = [dict(row) for row in query_results] result_dict = {'results': results} return jsonify(result_dict)
def search(table, column, keyword): """ Searches in 'table' where 'column' is like 'keyword' """ conn = db.connect() query = 'SELECT * FROM ' + str(table) + ' WHERE ' + str(column) + " LIKE '%%" + str(keyword) + "%%' LIMIT 1000;" query_results = conn.execute(query) conn.close() return query_results
def userNotInDatabase(username, password): conn = db.connect() query = "SELECT COUNT(*) AS C FROM Users WHERE username = '******' AND password = '******';".format(username, password) query_results = conn.execute(query) conn.close() results = [dict(row) for row in query_results] result_dict = {0: results} # print(type(results[0]['C'])) return not bool(results[0]['C'])
def checkUser(id:int, username:str, password:str): conn = db.connect() query = "SELECT COUNT(*) AS C FROM Users WHERE username = '******' AND password = '******' AND id = {};".format(username, password, id) query_results = conn.execute(query) conn.close() results = [dict(row) for row in query_results] result_dict = {0: results} # print(type(results[0]['C'])) return not bool(results[0]['C'])
def getTableNames(): conn = db.connect() query = "SELECT Table_name as TableName from information_schema.tables where table_schema = 'covid_tracker';" query_results = conn.execute(query) conn.close() results = [dict(row) for row in query_results] result_dict = {'results': results} return jsonify(result_dict)
def delete(table, id: int, userId:int) -> None: """ removes from 'table' the entry that matches with 'id' """ conn = db.connect() if table == "Reviews": query = 'DELETE FROM ' + str(table) + ' WHERE id={} AND userID={};'.format(int(id), int(userId)) else: query = 'DELETE FROM ' + str(table) + ' WHERE id={} AND userId={};'.format(int(id), int(userId)) conn.execute(query) conn.close()
def insert_UserVisited(_id:int, userID:int, locationID:int, time:str, hasCOVID:int): conn = db.connect() # weird stored procedure insert fix but it works I guess query1 = '' if hasCOVID == 0: query1 = 'INSERT INTO UserVisited (id, userID, locationID, time, hasCOVID) VALUES ("{}", "{}", "{}", "{}", "{}")'.format(_id, userID, locationID, time, hasCOVID) else: query1 = 'CALL updateCovidStatus("{}", "{}", "{}", "{}", "{}")'.format(_id, userID, locationID, time, hasCOVID) conn.execute(query1) conn.close()
def getColumnsFromTable(): arg1 = str(request.args.get('table')) conn = db.connect() query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'covid_tracker' AND TABLE_NAME = '{}';".format( arg1) query_results = conn.execute(query) conn.close() results = [dict(row) for row in query_results] result_dict = {'results': results} return jsonify(result_dict)
def answer(): dataJSON = request.get_json() username = str(dataJSON['username']) password = str(dataJSON['password']) if (dbase.userNotInDatabase(username, password)): return dict({"isSuccessful": 0}) userId = str(dataJSON['userId']) questionId = str(dataJSON['questionId']) answer = str(dataJSON['answer']) conn = db.connect() query1 = 'INSERT INTO Answers (answer, questionID, userID) VALUES ("{}", "{}", "{}")'.format( answer, questionId, userId) conn.execute(query1) conn.close() return dict({"isSuccessful": 1})
def review(): dataJSON = request.get_json() username = str(dataJSON['username']) password = str(dataJSON['password']) if (dbase.userNotInDatabase(username, password)): return dict({"isSuccessful": 0}) userId = str(dataJSON['userId']) locationId = str(dataJSON['locationId']) review = str(dataJSON['review']) rating = str(dataJSON['rating']) conn = db.connect() query1 = 'INSERT INTO Reviews (rating, userID, locationID, review) VALUES ("{}", "{}", "{}", "{}")'.format( rating, userId, locationId, review) conn.execute(query1) conn.close() return dict({"isSuccessful": 1})
def addQuestion(): dataJSON = request.get_json() username = str(dataJSON['username']) password = str(dataJSON['password']) if (dbase.userNotInDatabase(username, password)): return dict({"isSuccessful": 0}) locationId = str(dataJSON['locationId']) question = str(dataJSON['question']) userId = str(dataJSON['userId']) conn = db.connect() query1 = 'INSERT INTO Questions (question, userId, locationId) VALUES ("{}", "{}", "{}")'.format( question, userId, locationId) conn.execute(query1) conn.close() return dict({"isSuccessful": 1})
def login(): dataJSON = request.get_json() arg1 = str(dataJSON['username']) arg2 = str(dataJSON['password']) conn = db.connect() query = "SELECT COUNT(*) AS isSuccessful, id AS userId FROM Users WHERE username = '******' AND password = '******' GROUP BY id".format( arg1, arg2) query_results = conn.execute(query) conn.close() results = [] for row in query_results: results.append(dict(row)) # results = [dict(row) for row in query_results] if (len(results) == 0): return dict({"isSuccessful": 0, "userId": -1}) return results[0]
def tableColumns(table): """Reads all tasks listed in the todo table Returns: A list of dictionaries """ conn = db.connect() query_results = conn.execute("SHOW COLUMNS FROM Locations;") conn.close() #todo_list = [] # print(query_results) # for result in query_results: # item = { # "id": result[0], # "task": result[1], # "status": result[2] # } # todo_list.append(item) return query_results
def visit(): dataJSON = request.get_json() username = str(dataJSON['username']) password = str(dataJSON['password']) if (dbase.userNotInDatabase(username, password)): return dict({"isSuccessful": 0}) userId = str(dataJSON['userId']) locationId = str(dataJSON['locationId']) date = str(dataJSON['date']) hasCovid = str(dataJSON['hasCovid']) if hasCovid == "Yes": hasCovid = 1 else: hasCovid = 0 conn = db.connect() query1 = 'INSERT INTO UserVisited (userID, locationID, time, hasCOVID) VALUES ("{}", "{}", "{}", "{}")'.format( userId, locationId, date, hasCovid) conn.execute(query1) conn.close() return dict({"isSuccessful": 1})
def insert_Questions(_id:int, question:str, userId:int, locationId:int): conn = db.connect() query1 = 'INSERT INTO Questions (_id, question, userId, locationId) VALUES ("{}", "{}", "{}", "{}")'.format(_id, question, userId, locationId) conn.execute(query1) conn.close()
def insert_LocationOfType(locationID:int, categoryID:int): conn = db.connect() query1 = 'INSERT INTO LocationOfType (locationID, categoryID) VALUES ("{}", "{}")'.format(locationID, categoryID) conn.execute(query1) conn.close()
def insert_Answers(_id:int, answer:str, questionID:int, userID:int): conn = db.connect() query1 = 'INSERT INTO Answers (id, answer, questionID, userID) VALUES ("{}", "{}", "{}", "{}")'.format(_id, answer, questionID, userID) conn.execute(query1) conn.close()
def insert_Reviews(_id:int, rating:int, userID:int, locationID:int, review:str): conn = db.connect() query1 = 'INSERT INTO Reviews (id, rating, userID, locationID, review) VALUES ("{}", "{}", "{}", "{}", "{}")'.format(_id, rating, userID, locationID, review) conn.execute(query1) conn.close()
def insert_User(_id:int, name:str, hasCovid:int, username:str, password:str): conn = db.connect() query1 = 'INSERT INTO Users (id, name, hasCovid, username, password) VALUES ("{}", "{}", "{}", "{}", "{}")'.format(_id, name, hasCovid, username, password) conn.execute(query1) conn.close()
def insert_Categories(_id:int, name:str): conn = db.connect() query1 = 'INSERT INTO Categories (id, name) VALUES ("{}", "{}")'.format(_id, name) conn.execute(query1) conn.close()
def insert_Locations(_id:int, name:str, longitude:int, latitude:int): conn = db.connect() query1 = 'INSERT INTO Locations (id, name, longitude, latitude) VALUES ("{}", "{}", "{}", "{}")'.format(_id, name, longitude, latitude) conn.execute(query1) conn.close()
def get_LocationId(name:int): conn = db.connect() query = "SELECT id FROM Locations WHERE name = '" + name + "'" query_results = conn.execute(query) conn.close() return query_results