def clean_auction(auction_id, new_quantity): session.query(Auction).filter(Auction.auctionId == auction_id).update({ Auction.dirty: 0, Auction.quantity: new_quantity }) session.commit()
def prefill_users(clean=True, amount=1000): if clean: session.query(User).delete() for counter in range(amount): user = User(email=faker.email(), password=generate_password(), name=faker.name()) session.add(user) user = User(email="admin", password="******", name="Administrator") session.add(user) session.commit()
def prefill_movies(clean=True): if clean: session.query(Movie).delete() data = pd.read_csv('../data/movies_metadata.csv') titles = data["original_title"] for title in titles: movie = Movie(name=title) session.add(movie) session.commit()
def check_availibility(self, id, date, nb_people): q1 = session.query(RestaurantsDbTable).filter_by(id=id).first() if q1 is None: return -1 q2 = session.query(func.sum(ReservationDbTable.nb_people).label('people'))\ .filter(ReservationDbTable.id_restaurant==id, ReservationDbTable.date==date).first() reserved = q2[0] if reserved is None: reserved = 0 if reserved + nb_people > q1.seats: return -2 return 0
def prefill_ratings(clean=True, max_amount=30): if clean: session.query(Rating).delete() movies = session.query(Movie).all() users = session.query(User).all() for user in users: sampled_movies = random.sample(movies, random.randint(0, max_amount)) for movie in sampled_movies: rating = Rating(user_id=user.id, movie_id=movie.id, mark=random.randint(1, 5)) session.add(rating) session.commit()
def delete(self, data): customer = session.query(UsersDbTable).filter_by(token=data['token']).first() if customer is not None: session.delete(customer) session.commit() else: abort(409)
def work_on_user(self, user_id): user_ratings = session.query(Rating).filter_by(user_id=user_id).all() ratings_number = len(user_ratings) user_recommendations = session.query(Recommendation).filter_by( user_id=user_id).all() recommendations_number = len(user_recommendations) if ((recommendations_number == 0 and ratings_number > Constants.MIN_RATINGS_TO_RECOMMEND) or ratings_number % (Constants.MIN_RATINGS_TO_RECOMMEND / 2) == 0): # Basically if user did >20 and has no recommendations or did 30, 40, 50 and so on ratings - make # new recommendations self.initialise_rater() session.query(Recommendation).filter_by(user_id=user_id).delete() session.commit() self.generate_recommendations_for_user(user_id)
def addPlayerClassIfNotPresent(class_name, class_id): if session.query(PlayerClass).filter( PlayerClass.playerClassId == class_id).first() is not None: return player_class_obj = PlayerClass(class_id, class_name) session.add(player_class_obj) session.commit()
def like(): try: check_logged_in() movie = session.query(Movie).filter_by(id=request.form["movie_id"]).first() return go_to_page("like", {"movie": movie}) except AuthorisationException: return redirect(url_for('login'))
def addAzeriteTraitsForClass(item_id, traits_json, player_class_id): for trait in traits_json: power_id = trait["spell"]["id"] if session.query(AzeriteTrait).filter( AzeriteTrait.power_id == power_id).first() is not None: return tier = trait["tier"] name = trait["spell"]["name"] azerite_obj = AzeriteTrait(power_id, tier, name, player_class_id) session.add(azerite_obj) session.commit() if "allowed_specializations" in trait: for spec in trait["allowed_specializations"]: player_class_spec_id = spec["id"] player_class_spec_name = spec["name"] addPlayerClassSpecializationIfNotPresent( player_class_id, player_class_spec_id, player_class_spec_name) relation = AzeriteClassSpecialization(player_class_spec_id, power_id) session.add(relation) azerite_item_obj = AzeriteItem(item_id, power_id) session.add(azerite_item_obj) session.commit()
def delete(self, id): if id.isdigit() == False: return build_error_response(+3, "Bad ID.") q = session.query(RestaurantsDbTable).filter_by(id = id).first() if q is not None: q2 = session.query(ReservationDbTable).filter_by(id_restaurant=id).all() q3 = session.query(DishDbTable).filter_by(id_restaurant=id).all() for i in q2: session.delete(i) for i in q3: session.delete(i) session.delete(q) session.commit() else: return build_error_response(-2, "Restaurant does not exist.") return build_error_response(0, 'All done.')
def addMedia(media_url, media_id): if session.query(Media).filter( Media.mediaId == media_id).first() is not None: return media_obj = Media(media_id, media_url) session.add(media_obj) session.commit()
def addItemToDb(item_json_data): item_id = item_json_data["id"] if session.query(Item).filter(Item.itemId == item_id).first() is not None: return item_name = item_json_data["name"] item_media_id = item_json_data["media"]["id"] item_media_url = item_json_data["media"]["key"]["href"] addMedia(item_media_url, item_media_id) item_class = item_json_data["item_class"]["id"] item_subclass = item_json_data["item_subclass"]["id"] item_inventory_type = item_json_data["inventory_type"]["name"] item_purchase_price = item_json_data["purchase_price"] if ( "purchase_price" in item_json_data) else 0 stat_dict = {} if "stats" in item_json_data["preview_item"].keys(): try: stat_dict = organizeStats(item_json_data["preview_item"]["stats"]) except: print(item_id, item_json_data) item = Item(item_id, item_class, item_subclass, item_name, item_media_id, item_inventory_type, item_purchase_price, **stat_dict) session.add(item) if "azerite_class_powers" in item_json_data: addAzeriteTraits(item_json_data["azerite_class_powers"], item_id) session.commit()
def recommendations(): try: check_logged_in() recs = session.query(Movie, Recommendation).filter(Recommendation.user_id == get_current_user().id ).filter(Movie.id == Recommendation.movie_id).all() return go_to_page("recommendations", {"recommendations": recs}) except AuthorisationException: return redirect(url_for('login'))
def addItemById(item_id): if session.query(Item).filter(Item.itemId == item_id).first() is not None: return item_id data = get_item_data(item_id) if data is not None: addItemToDb(data) return item_id return None
def likes_history(): try: check_logged_in() recs = session.query(Movie, Rating).filter(Rating.user_id == get_current_user().id ).filter(Movie.id == Rating.movie_id).all() return go_to_page("likes_history", {"likes": recs}) except AuthorisationException: return redirect(url_for('login'))
def addPlayerClassSpecializationIfNotPresent(class_id, spec_id, spec_name): if session.query(PlayerClassSpecialization).filter( PlayerClassSpecialization.playerClassSpecId == spec_id).first() is not None: return spec_class_obj = PlayerClassSpecialization(spec_id, class_id, spec_name) session.add(spec_class_obj) session.commit()
def results(): try: check_logged_in() movies = [] if "search_string" in request.form: movies = session.query(Movie).filter(Movie.name.contains(request.form["search_string"])).all() return go_to_page("results", {"movies": movies}) except AuthorisationException: return redirect(url_for('login'))
def check_logged_in(): try: email = request.cookies.get('email') password = request.cookies.get("password") user = session.query(User).filter_by(email=email, password=password).first() if user is None: raise AuthorisationException("No such user") except TypeError: raise AuthorisationException("No cookies")
def trim_watched_recommendations(user_id, recommendations): user_ratings = session.query(Rating).filter_by(user_id=user_id).all() liked_movie_ids = [user_rating.movie_id for user_rating in user_ratings] deletion_candidates = [] for movie_id in recommendations: if movie_id in liked_movie_ids: deletion_candidates.append(movie_id) for deletion_candidate in deletion_candidates: del recommendations[deletion_candidate] return recommendations
def cancel(self, id): if id.isdigit() == False: return build_error_response(+3, "Bad ID.") q = session.query(ReservationDbTable).filter_by(id=id).first() if q is not None: session.delete(q) session.commit() else: return build_error_response(-2, "Reservation does not exist.") return build_error_response(0, 'All done.')
def get_auction_data(items, amount_return): data = {} for item in items: itemObj = get_item_by_name(item) auctions = session.query(Auction.unitPrice, Auction.buyout, func.sum(Auction.quantity)) \ .group_by(Auction.unitPrice, Auction.buyout) \ .filter(Auction.itemId == itemObj.itemId, or_(Auction.buyout != None, Auction.unitPrice != None)) \ .order_by(Auction.unitPrice.asc()).limit(amount_return).all() data[item] = auctions return data
def get_manager(self, data): customer = session.query(UsersDbTable).filter_by(token=data['token']).first() if customer is not None: json = jsonify({'first_name': customer.first_name, 'last_name': customer.last_name, 'email_adress': customer.email_adress }) else: abort(409) return json
def update(self, id, data): allowed_changes = ['id_restaurant', 'date', 'nb_people'] if not data: return build_error_response(-1, "Not enough parameters.") q = session.query(ReservationDbTable).filter_by(id=id).first() if q is not None: for key in allowed_changes: if key in data: q.set_value_by_name(key, data[key]) session.commit() return build_error_response(0, 'All done.')
def check_token(self, token): customer = session.query(UsersDbTable).filter_by(email=data['email']).first() if customer is not None: if sha256_crypt.verify(data['password'], customer.password) is False: abort(401) else: return ( {'name': customer.name, 'surname': customer.surname, 'email': customer.email, 'pseudo': customer.pseudo, 'type': customer.type, 'token': customer.token}) else: abort(401)
def getAllAzeriteTraits(character_obj): overall = session.query(AzeriteTrait, PlayerClass).filter( AzeriteTrait.playerClassId == PlayerClass.playerClassId and character_obj.class_id == PlayerClass.playerClassId).all() allowed = [] for item in overall: spec_allowed = False specificSpecs = session.query(AzeriteClassSpecialization).filter( AzeriteClassSpecialization.power_id == item[0].power_id).all() if len(specificSpecs) > 0: for spec in specificSpecs: if spec.playerClassSpecId == character_obj.spec_id: spec_allowed = True else: spec_allowed = True if spec_allowed: allowed.append(item[0]) # returns list of AzeriteTrait for allowed traits for specified character return allowed
def update(self, id, data): allowed_changes = ['name', 'description', 'address', 'seats', 'longitude', 'latitude'] if not data: return build_error_response(-1, "Not enough parameters.") q = session.query(RestaurantsDbTable).filter_by(id = id).first() if q is not None: for key in allowed_changes: if key in data: q.set_value_by_name(key, data[key]) session.commit() else: return build_error_response(-2, 'Restaurant does not exist.') return build_error_response(0, 'All done.')
def get(self, id): if id.isdigit() == False: return build_error_response(+3, "Bad ID.") q = session.query(DishDbTable).filter_by(id=id).first() if q is not None: retObject = { 'error': build_error_object(0, 'All done.'), 'id': id, 'name': q.name, 'description': q.description, 'id_restaurant': q.id_restaurant, 'price': q.price} return jsonify(retObject) else: return build_error_response(-2, "Dish does not exist.")
def prefill_ratings_from_csv(clean=True): if clean: session.query(Rating).delete() data = pd.read_csv('../data/ratings_small.csv') user_ids = data["userId"] movie_ids = data["movieId"] ratings = data["rating"] ratings_sql = [] for index in range(len(user_ids)): ratings_sql.append(Rating(user_id=user_ids[index].item(), movie_id=movie_ids[index].item(), mark=ratings[index].item())) counter = 0 for rating_sql in ratings_sql: counter += 1 if counter % 1000 == 0: print("{}/{}".format(counter, len(ratings_sql))) try: session.add(rating_sql) if counter % 100 == 0: session.commit() except IntegrityError: session.rollback() print("Skipping, violates constraints")
def search(): try: check_logged_in() user = get_current_user() data = {"person_name": user.name} print(request.form) if "rank" in request.form and "movie_id" in request.form: # Redirected here after adding like movie = session.query(Movie).filter_by(id=request.form["movie_id"]).first() session.add(Rating(user_id=user.id, movie_id=movie.id, mark=request.form["rank"])) session.commit() RecEng().work_on_user(user.id) data["message"] = "Your like {} for movie '{}' was added".format(request.form["rank"], movie.name) return go_to_page("search", data) except AuthorisationException: return redirect(url_for('login'))
def register(self, data): customer = session.query(UsersDbTable).filter_by(email=data['email']).first() if customer is None: bits = random.getrandbits(126) token = hex(bits) customer = UsersDbTable(name=data['name'], surname=data['surname'], email=data['email'], password=sha256_crypt.encrypt(data['password']), pseudo=data['pseudo'], type=data['type'], token=token) session.add(customer) session.commit() return customer.token else: abort(409)
def update(self, data): customer = session.query(UsersDbTable).filter_by(email=data['email']).first() if customer is None: abort(400) else: customer.name = customer.name if (data['name'] == '' or data['name'] == None) else data['name'] customer.surname = customer.surname if (data['surname'] == '' or data['surname'] == None) else data['surname'] customer.email = customer.email if (data['email'] == '' or data['email'] == None) else data['email'] customer.password = customer.password if (data['password'] == '' or data['password'] == None) else sha256_crypt.encrypt(data['password']) customer.pseudo = customer.pseudo if (data['pseudo'] == '' or data['pseudo'] == None) else data['pseudo'] customer.type = customer.type if (data['type'] == '' or data['type'] == None) else data['type'] session.add(customer) session.commit() return ( {'name': customer.name, 'surname': customer.surname, 'email': customer.email, 'pseudo': customer.pseudo, 'type': customer.type, 'token': customer.token, 'id': customer.id})
def get(self, id): if id.isdigit() == False: return build_error_response(+3, "Bad ID.") q = session.query(RestaurantsDbTable).filter_by(id=id).first() if q is not None: retObject = { 'error' : build_error_object(0, 'All done.'), 'id' : id, 'name' : q.name, 'description' : q.description, 'address' : q.address, 'owner_id' : q.owner_id, 'seats' : q.seats, 'longitude' : q.longitude, 'latitude' : q.latitude} return jsonify(retObject) else: return build_error_response(-2, "Restaurant does not exist.")
def list(self, id): if id.isdigit() == False: return build_error_response(+3, "Bad ID.") q = session.query(DishDbTable).filter_by(id_restaurant=id).all() if q is None: return build_error_response(-4, "Unknown error from database.") l = list() for item in q: o = { 'id': item.id, 'name': item.name, 'description': item.description, 'id_restaurant': item.id_restaurant, 'price': item.price } l.append(o) retObject = { 'error': build_error_object(0, 'All done'), 'list': l } return jsonify(retObject)
def list_by_user(self, id): if id.isdigit() == False: return build_error_response(+3, "Bad ID.") q = session.query(ReservationDbTable).filter_by(id_user=id).all() if q is None: return build_error_response(-4, "Unknown error from database.") l = list() for item in q: o = { 'id': item.id, 'id_user': item.id_user, 'id_restaurant': item.id_restaurant, 'dishes': item.dishes, 'date': item.date, 'nb_people': item.nb_people } l.append(o) retObject = { 'error': build_error_object(0, 'All done'), 'list': l } return jsonify(retObject)
def list(self): q = session.query(RestaurantsDbTable).all() if q is None: return build_error_response(-4, "Unknown error from database.") l = list() for item in q: o = { 'id': item.id, 'name': item.name, 'description': item.description, 'address': item.address, 'owner_id': item.owner_id, 'seats': item.seats, 'longitude': item.longitude, 'latitude': item.latitude } l.append(o) retObject = { 'error' : build_error_object(0, 'All done'), 'list' : l } return jsonify(retObject)
def search_by_owner(self, id): if id.isdigit() == False: return build_error_response(+3, "Bad ID.") q = session.query(RestaurantsDbTable).filter_by(owner_id = id).all() if q is None: return build_error_response(-4, "Unknown error from database.") l = list() for item in q: o = { 'id': item.id, 'name': item.name, 'description': item.description, 'address': item.address, 'owner_id': item.owner_id, 'seats': item.seats, 'longitude': item.longitude, 'latitude': item.latitude } l.append(o) retObject = { 'error': build_error_object(0, 'All done'), 'list': l } return jsonify(retObject)
def get_current_user(): return session.query(User).filter_by(email=request.cookies.get('email'), password=request.cookies.get("password")).first()
def check_restaurant(self, id): q = session.query(RestaurantsDbTable).filter_by(id=id).first() if q is None: return False return True
def set_auctions_dirty(): session.query(Auction).filter(Auction.dirty == 0).update( {Auction.dirty: 1}) session.commit()
def search_user_by_name(self, name): customer = session.query(UsersDbTable).filter_by(name=name).all() if customer is not None: return customer else: abort(401)
def remove_dirty_auctions(): session.query(Auction).filter(Auction.dirty == 1).update( {Auction.dateRemoved: datetime.datetime.now()}) session.commit()
def item_exists_by_name(item_name): return session.query(Item).filter( func.lower(Item.name) == item_name.lower()).first() is not None
def get_most_recently_added_date(): return session.query(Auction.dateInserted).order_by( Auction.dateInserted.desc()).first()
from Database import Rating, session from Rater import Rater # put already made ratings into table except 10 results for some user ratings = session.query(Rating).all() real_results = ratings[:10] data = ratings[10:] rater = Rater(data) total_error = 0 for real_result in real_results: real_mark = real_result.mark predicted_ratings = rater.get_ratings(real_result.user_id) predicted_mark = predicted_ratings[real_result.movie_id] print("diff: {}\t real: {}\tpredicted:{}".format( abs(real_mark - predicted_mark), real_mark, predicted_mark)) total_error += abs(real_mark - predicted_mark) print(total_error / 10) # get estimates for those results # compare them # diff: 0.25050359362432895 real: 2.5 predicted:2.249496406375671 # diff: 0.2309004004413966 real: 3 predicted:2.7690995995586034 # diff: 0.3817095466514031 real: 3 predicted:2.618290453348597 # diff: 0.39263823590422087 real: 2 predicted:2.392638235904221 # diff: 0.7008890654176327 real: 4 predicted:3.2991109345823673 # diff: 0.9507700997812201 real: 2 predicted:2.95077009978122 # diff: 0.9878910080325669 real: 2 predicted:2.987891008032567 # diff: 1.066646152501515 real: 2 predicted:3.066646152501515 # diff: 1.159839242405503 real: 3.5 predicted:2.340160757594497 # diff: 0.8414740114032355 real: 2 predicted:2.8414740114032355
def clean_recommendations(): session.query(Recommendation).delete() session.commit()
def hello_world(): items = session.query(User).all(); return items[0].username
def get_item_by_name(item_name): return session.query(Item).filter( func.lower(Item.name) == item_name.lower()).first()