def search_animal(name, species, type, min_age, max_age, exhibit, sort, order): conn, curr = connection() if name is None: name = "" if species is None: species = "" if type is None: type = "" if min_age is None: min_age = "0" if max_age is None: max_age = "1000000000" if exhibit is None: exhibit = "" if sort is None or sort == "": sort = "animal_name" if order is None or order == "": order = "ASC" query = "SELECT * FROM Animal " \ "WHERE (%s = '' OR animal_name LIKE '%%" + name + "%%')" \ " AND (%s = '' OR species LIKE '%%" + species + "%%')" \ " AND (%s = '' OR animal_type LIKE '%%" + type + "%%')" \ " AND age BETWEEN " + min_age + " AND " + max_age + \ " AND (%s = '' OR exhibit_name LIKE '%%" + exhibit + "%%')" \ " ORDER BY " + sort + " " + order curr.execute(query, (name, species, type, exhibit)) results = curr.fetchall() curr.close() conn.close() return results
def log_show_visit(visitor_username, show_name, show_time): conn, curr = connection() try: curr.execute( "INSERT INTO Visit_show(visitor_username, show_name, show_time) VALUES (%s, %s, %s);", (visitor_username, show_name, datetime.fromtimestamp( int(show_time)))) except IntegrityError as e: if e.args[0] == 1062: abort(400, message='You already logged a visit') else: raise e curr.execute( "SELECT exhibit_name FROM `Show` WHERE show_name=%s and show_time=%s ;", (show_name, datetime.fromtimestamp(int(show_time)))) results = curr.fetchall() string = str(results[0]) exhibit_name = "" + string.split("'")[3] log_exhibit_visit(visitor_username, exhibit_name, show_time) conn.commit() curr.close() conn.close() return "Successfully logged show visit!!!"
def get_user_by_username(username): conn, curr = connection() curr.execute("SELECT * FROM test.User WHERE username = %s", (username, )) results = curr.fetchall() curr.close() conn.close() return results[0]
def get_all_exhibits(): conn, curr = connection() curr.execute("SELECT * FROM Exhibit") results = curr.fetchall() curr.close() conn.close() return results
def delete_user(username): conn, curr = connection() curr.execute("DELETE FROM `User` WHERE username = %s;", (username, )) conn.commit() curr.close() conn.close() return "User was successfully deleted"
def get_all_animals(): conn, curr = connection() curr.execute("SELECT * FROM Animal") results = curr.fetchall() curr.close() conn.close() return results
def create_exhibit(exhibit_name, water_feature, size): conn, curr = connection() curr.execute( "INSERT INTO Exhibit(exhibit_name, water_feature, size) " "VALUES (%s, %s, %s);", (exhibit_name, water_feature, size)) conn.commit() curr.close() conn.close() return "Exhibit was successfully created"
def delete_show(show_name, show_time): conn, curr = connection() curr.execute("DELETE FROM `Show` WHERE show_name = %s and show_time = %s;", (show_name, datetime.fromtimestamp(int(show_time)))) conn.commit() curr.close() conn.close() return "Show was successfully deleted"
def get_users_by_type(user_type): conn, curr = connection() curr.execute("SELECT username, email FROM `User` WHERE user_type = %s", (user_type, )) results = curr.fetchall() curr.close() conn.close() return results
def get_all_staff(): conn, curr = connection() curr.execute( "SELECT username, email FROM `User` WHERE user_type = \"Staff\"") results = curr.fetchall() curr.close() conn.close() return results
def get_animal(name, species): conn, curr = connection() curr.execute("SELECT * FROM Animal WHERE animal_name=%s AND species=%s", (name, species)) results = curr.fetchall() curr.close() conn.close() return results
def get_all_shows(): conn, curr = connection() curr.execute("SELECT * FROM `Show`") results = curr.fetchall() for result in results: result['show_time'] = int(result['show_time'].timestamp()) curr.close() conn.close() return results
def log_note(staff_username, log_time, note, animal_name, animal_species): conn, curr = connection() curr.execute( "INSERT INTO Note(staff_username, log_time, note, animal_name, animal_species) VALUES (%s, %s, %s, %s, %s);", (staff_username, datetime.fromtimestamp(int( log_time, )), note, animal_name, animal_species)) conn.commit() curr.close() conn.close() return "Successfully added note"
def delete_animal(animal_name, species): conn, curr = connection() curr.execute( "DELETE FROM Animal " "WHERE animal_name = %s and species = %s;", (animal_name, species)) conn.commit() curr.close() conn.close() return "Animal was successfully deleted"
def get_show(**filters): conn, curr = connection() sql, values = _generate_filters(filters) curr.execute("SELECT * FROM test.Show {};".format(sql), values) results = curr.fetchall() for result in results: result['show_time'] = int(result['show_time'].timestamp()) curr.close() conn.close() return results
def get_exhibit_details(exhibit): conn, curr = connection() query = "SELECT Exhibit.exhibit_name, water_feature, size, COUNT(Exhibit.exhibit_name) as 'total_animal' " \ "FROM Exhibit LEFT JOIN Animal on Exhibit.exhibit_name = Animal.exhibit_name " \ "WHERE Exhibit.exhibit_name = %s" \ "GROUP BY Exhibit.exhibit_name" curr.execute(query, (exhibit, )) results = curr.fetchall() curr.close() conn.close() return results
def validate_registration(username, email): conn, curr = connection() # how to do with parameterization curr.execute("SELECT * FROM User WHERE username = %s;", (username, )) results = curr.fetchall() if len(results) > 0: return False curr.execute("SELECT * FROM User WHERE email = %s;", (email, )) results = curr.fetchall() if len(results) > 0: return False return True
def log_exhibit_visit(visitor_username, exhibit_name, visit_time): conn, curr = connection() try: curr.execute( "INSERT INTO Visit_exhibit(visitor_username, exhibit_name, visit_time) VALUES (%s, %s, %s);", (visitor_username, exhibit_name, datetime.fromtimestamp(int(visit_time)))) except IntegrityError as e: if e.args[0] == 1062: abort(400, message='You already logged a visit') else: raise e conn.commit() curr.close() conn.close() return "Successfully logged exhibit visit!!!"
def create_animal(animal_name, species, animal_type, age, exhibit_name): conn, curr = connection() try: curr.execute( "INSERT INTO Animal(animal_name, species, animal_type, age, exhibit_name) " "VALUES (%s, %s, %s, %s, %s);", (animal_name, species, animal_type, age, exhibit_name)) except IntegrityError as e: if e.args[0] == 1062: abort(400, message='This animal already exists') else: raise e conn.commit() curr.close() conn.close() return "Animal was successfully created"
def login(email, password): conn, curr = connection() curr.execute('SELECT * FROM User where email = %s ', (email, )) row = curr.fetchone() if row is None: abort(401, message="Incorrect username or password") if not argon2.verify(password, row['password']): abort(401, message="Incorrect username or password") session['logged_in'] = True session['username'] = row['username'] curr.close() conn.close() return "Successfully logged in"
def search_exhibit_history(visitor_name, exhibit_name, date, min_visits, max_visits, sort, order): conn, curr = connection() if visitor_name is None: visitor_name = "" if exhibit_name is None: exhibit_name = "" if date is None: date = "" else: date = datetime.fromtimestamp(int(date)).date() if min_visits is None: min_visits = "0" if max_visits is None: max_visits = "1000000000" if sort is None or sort == "": sort = "exhibit_name" if order is None or order == "": order = "ASC" query = "SELECT exhibit_name as exhibit, visit_time, (SELECT COUNT(exhibit_name) " \ "FROM Visit_exhibit " \ "WHERE visitor_username = %s" \ " AND exhibit_name = exhibit) as num_visits " \ "FROM Visit_exhibit " \ "WHERE visitor_username = %s" \ " AND (%s = '' OR exhibit_name LIKE '%%" + exhibit_name + "%%')" \ " AND (%s = '' OR DATE(visit_time) = %s)" \ " GROUP BY exhibit_name, visit_time" \ " HAVING num_visits >= " + min_visits + " AND num_visits <= " + max_visits+ \ " ORDER BY " + sort + " " + order curr.execute(query, (visitor_name, visitor_name, exhibit_name, date, date)) results = curr.fetchall() curr.close() conn.close() for result in results: result['visit_time'] = result['visit_time'].timestamp() return results
def get_logged_note(animal_name, animal_species, sort, order): conn, curr = connection() if sort is None or sort == "": sort = "staff_username" if order is None or order == "": order = "ASC" curr.execute( "SELECT staff_username, note, log_time FROM test.Note where animal_name=%s AND animal_species=%s ORDER BY " + sort + " " + order, (animal_name, animal_species)) results = curr.fetchall() curr.close() conn.close() for result in results: result['log_time'] = result['log_time'].timestamp() return results
def create_user(username, email, password, user_type): conn, curr = connection() if validate_registration(username, email): curr.execute( "INSERT INTO User(username, email, password, user_type) " "VALUES (%s, %s, %s, %s);", (username, email, argon2.hash(password), user_type)) conn.commit() curr.close() conn.close() session['logged_in'] = True session['username'] = username return "User was successfully created" else: abort(400, message="Duplicate user given")
def get_users(user_type, username, email, sort, order): conn, curr = connection() if username is None: username = "" if email is None: email = "" if sort is None or sort == "": sort = "username" if order is None or order == "": order = "ASC" query = "SELECT username, email FROM User WHERE user_type = %s " \ "AND (%s = '' OR username LIKE '%%" + username + "%%') " \ "AND (%s = '' OR email LIKE '%%" + email + "%%') ORDER BY " + sort + " " + order curr.execute(query, (user_type, username, email)) results = curr.fetchall() curr.close() conn.close() return results
def create_show(show_name, show_time, staff_name, exhibit_name): conn, curr = connection() query = "SELECT * FROM test.`Show` where staff_name=%s AND show_time=%s;" curr.execute(query, (staff_name, datetime.fromtimestamp(int(show_time)))) if curr.fetchall(): abort(400, message='Cannot host multiple shows at the same time') else: try: curr.execute( "INSERT INTO `Show`(show_name, show_time, staff_name, exhibit_name) " "VALUES (%s, %s, %s, %s);", (show_name, datetime.fromtimestamp( int(show_time)), staff_name, exhibit_name)) except IntegrityError as e: if e.args[0] == 1062: abort(400, message='This show already exists') else: raise e conn.commit() curr.close() conn.close() return "Show was successfully created"
def search_show_history(visitor_name, show_name, date, exhibit, sort, order): conn, curr = connection() if visitor_name is None: visitor_name = "" if show_name is None: show_name = "" if date is None: date = "" else: date = datetime.fromtimestamp(int(date)).date() if exhibit is None: exhibit = "" if sort is None or sort == "": sort = "show_name" if order is None or order == "": order = "ASC" query = "SELECT DISTINCT visitor_username, Visit_show.show_name as 'show_name', Visit_show.show_time as 'visit_time', exhibit_name " \ "FROM Visit_show INNER JOIN `Show` ON Visit_show.show_name=`Show`.show_name " \ "WHERE visitor_username = %s" \ " AND (%s = '' OR Visit_show.show_name LIKE '%%" + show_name + "%%')" \ " AND (%s = '' OR DATE(Visit_show.show_time) = %s)" \ " AND (%s = '' OR exhibit_name LIKE '%%" + exhibit + "%%')" \ " ORDER BY " + sort + " " + order curr.execute(query, (visitor_name, show_name, date, date, exhibit)) results = curr.fetchall() curr.close() conn.close() for result in results: result['visit_time'] = result['visit_time'].timestamp() return results
def search_show(show_name, date, exhibit, staff_name, sort, order): conn, curr = connection() if staff_name is None: staff_name = "" if show_name is None: show_name = "" if date is None: date = "" else: date = datetime.fromtimestamp(int(date)).date() if exhibit is None: exhibit = "" if sort is None or sort == "": sort = "show_name" if order is None or order == "": order = "ASC" query = "SELECT * FROM `Show` " \ "WHERE (%s = '' OR staff_name = %s) " \ " AND (%s = '' OR LOWER(show_name) LIKE '%%" + show_name.lower() + "%%') " \ " AND (%s = '' OR DATE(show_time) = %s) " \ " AND (%s = '' OR exhibit_name LIKE '%%" + exhibit + "%%') " \ " ORDER BY " + sort + " " + order curr.execute(query, (staff_name, staff_name, show_name, date, date, exhibit)) results = curr.fetchall() curr.close() conn.close() for result in results: result['show_time'] = result['show_time'].timestamp() return results
def search_exhibit(name, water, min_size, max_size, min_animal, max_animal, sort, order): conn, curr = connection() if name is None: name = "" if water is None: water = "" if min_size is None: min_size = "0" if max_size is None: max_size = "1000000000" if min_animal is None: min_animal = "0" if max_animal is None: max_animal = "1000000000" if sort is None or sort == "": sort = "exhibit_name" if order is None or order == "": order = "ASC" query = "SELECT Exhibit.exhibit_name, water_feature, size, COUNT(Exhibit.exhibit_name = Animal.exhibit_name) as 'total_animals' " \ "FROM Exhibit LEFT OUTER JOIN Animal on Exhibit.exhibit_name = Animal.exhibit_name " \ "WHERE (%s = '' OR Exhibit.exhibit_name LIKE '%%" + name + "%%')" \ " AND (%s ='' OR water_feature LIKE '%%" + water + "%%')" \ " AND (size BETWEEN " + min_size + " AND " + max_size + ")" \ " GROUP BY Exhibit.exhibit_name" \ " HAVING COUNT(Exhibit.exhibit_name) >= " + min_animal + " AND COUNT(Exhibit.exhibit_name) <= " + max_animal + "" \ " ORDER BY " + sort + " " + order curr.execute(query, (name, water)) results = curr.fetchall() curr.close() conn.close() return results