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