def update_show(self,
my_db: MyDBConnection,
pict: str = None,
season_next_episode_num: int = None,
next_episode_num: int = None,
date_next_episode: datetime = None,
season_list: List[Season] = None,
number_of_episodes: int = None,
number_of_seasons: int = None):
if pict is not None:
self.__set_pict(pict)
if season_next_episode_num is not None:
self.__set_season_next_episode_num(season_next_episode_num)
if next_episode_num is not None:
self.__set_next_episode_num(next_episode_num)
if date_next_episode is not None:
self.__set_date_next_episode(date_next_episode)
self.__set_last_maj(datetime.now().replace(microsecond=0))
if season_list is not None:
self.__set_season_list(season_list)
if number_of_episodes is not None:
self.__set_number_of_episodes(number_of_episodes)
if number_of_seasons is not None:
self.__set_number_of_seasons(number_of_seasons)
my_db.exec_one(
"UPDATE SHOW SET pict=(?), last_maj=(?), season_next_episode_num=(?), next_episode_date=(?), "
"next_episode_num=(?) WHERE api_id=(?)",
(self.pict, datetime_to_str(
self.last_maj), self.season_next_episode_num,
datetime_to_str(
self.date_next_episode), self.next_episode_num, self.api_id))
def create_preference_in_bdd(self, my_db: MyDBConnection):
my_db.exec_one(
"""
INSERT INTO preference (id_user, id_show, seen_flag) VALUES
((?), (?), (?))""",
(self.id_user, self.id_show, self.seen_flag))
new_pref = Preference.retrieve_preference_from_bdd(
my_db, self.id_user, self.id_show)
self.__set_id(new_pref.id)
def create_show_in_bdd(self, my_db: MyDBConnection):
my_db.exec_one(
"""
INSERT INTO show (pict, last_maj, title, season_next_episode_num, next_episode_date, next_episode_num, api_id)
VALUES ((?), (?), (?), (?), (?), (?), (?))""",
(self.pict, self.last_maj, self.title,
self.season_next_episode_num, self.date_next_episode,
self.next_episode_num, self.api_id))
new_show = Show.retrieve_show_from_bdd(self.api_id, my_db)
self.__set_db_id(new_show.db_id)
def create_user_in_bdd(self, my_db: MyDBConnection):
new_user = self.retrieve_user_from_credentials(self.login, self.pwd,
my_db)
if new_user is None:
my_db.exec_one(
"""
INSERT INTO user (surname, firstname, login, pwd, poster) VALUES
((?), (?), (?), (?), (?))""", (self.firstname, self.surname,
self.login, self.pwd, self.poster))
new_user = User.retrieve_user_from_credentials(
self.login, self.pwd, my_db)
self.__set_id(new_user.id)
def get(self, user_id):
my_db = MyDBConnection(CONFIG["db_path"])
try:
user = UserController.get_one_from_id(int(user_id), my_db)
except ErrorUserDoesNotExist:
return ErrorUserDoesNotExist.flask_desc_code()
return user.to_json()
def retrieve_user_from_id(cls, user_id: int, my_db: MyDBConnection):
user_res = my_db.exec_one("SELECT * from `user` WHERE id = (?)",
(user_id))
if not user_res:
return None
firstname, surname, login, pwd, poster, user_id = user_res[0]
return User(firstname, surname, login, pwd, poster, new_id=user_id)
def put(self, user_id: int, show_id: int):
my_db = MyDBConnection(CONFIG["db_path"])
user_id = int(user_id)
show_id = int(show_id)
# retrieve the user from the id
user = UserController.get_one_from_id(user_id, my_db)
if user is None:
return "Error, this user does not exist"
posted_data = request.get_json()
new_seen_flag = posted_data["new_seen_flag"]
show = ShowController.get_or_create_from_db_w_api_id(my_db, show_id)
if show is None:
return "Error, the API does not know this Show.", 500
preference = PreferenceController.get_one_w_api_id(my_db, user, show)
try:
PreferenceController.update_preference_seen_flag(
my_db, preference, new_seen_flag)
except ValueError as err:
return str(err), 500
return "ok"
def get(self, api_id):
my_db = MyDBConnection(CONFIG["db_path"])
api_id = int(api_id)
try:
show = ShowController.get_one_all_info(api_id, my_db)
except ErrorShowDoesNotExist:
return ErrorShowDoesNotExist.flask_desc_code()
return show.to_json()
def get_preference_from_show(cls, show: Show, my_db: MyDBConnection):
if show is None:
raise TypeError(
"The show from which we want to get preferences is not valid")
else:
list_preferences = my_db.exec_one(
"SELECT * from `preference` WHERE id_show = (?)", (show.db_id))
return list_preferences
def retrieve_user_from_credentials(cls, login: str, pwd: str,
my_db: MyDBConnection):
user_res = my_db.exec_one(
"SELECT * from `user` WHERE login = (?) AND pwd = (?)",
(login, pwd))
if not user_res:
return None
firstname, surname, login, pwd, poster, user_id = user_res[0]
return User(firstname, surname, login, pwd, poster, new_id=user_id)
def retrieve_show_from_bdd(cls, api_id: int, my_db: MyDBConnection):
show_res = my_db.exec_one("SELECT * from `show` WHERE api_id = (?)",
(api_id))
if not show_res:
return None
pict, last_maj, title, season_next_episode_num, next_episode_date, next_episode_num, api_id, db_id = show_res[
0]
return Show(title, pict, api_id, season_next_episode_num,
next_episode_num, str_to_datetime(next_episode_date),
str_to_datetime(last_maj), db_id)
def get(self, user_id: int):
my_db = MyDBConnection(CONFIG["db_path"])
# retrieve the user from the id
user = UserController.get_one_from_id(user_id, my_db)
if not user:
return None
# retrieve preferences
prefs = UserController.get_user_show_preferences(user, my_db)
return [pref.to_json() for pref in prefs]
def retrieve_preference_from_bdd(cls, my_db: MyDBConnection, id_user: int,
id_show: int):
preference_res = my_db.exec_one(
"SELECT * from `preference` WHERE id_user = (?) AND id_show = (?)",
(id_user, id_show))
if not preference_res:
return None
id_user, id_show, seen_flag, new_id = preference_res[0]
return Preference(id_user=id_user,
id_show=id_show,
seen_flag=seen_flag,
new_id=new_id)
def post(self):
my_db = MyDBConnection(CONFIG["db_path"])
posted_data = request.get_json()
try:
new_user = UserController.add_user(posted_data["firstname"],
posted_data["surname"],
posted_data["login"],
posted_data["pwd"],
posted_data["poster"], my_db)
except ErrorUserAlreadyExist:
return ErrorUserAlreadyExist.flask_desc_code()
return AuthUser.create_user_token(new_user).decode('ascii')
def post(self):
my_db = MyDBConnection(CONFIG["db_path"])
posted_data = request.get_json()
user = UserController.get_one_from_cred(posted_data["login"],
posted_data["pwd"], my_db)
if user is not None:
# we use the entire user to create the jwt
encoded_jwt = AuthUser.create_user_token(user)
# by default the jwt is binary so we convert it to ascii
return encoded_jwt.decode('ascii')
else:
return "Wrong credentials", 401
def get_preference_from_user(user: User, my_db: MyDBConnection):
if user is None:
raise TypeError(
"The user from which we want to get preferences is not valid")
else:
list_preferences_res = my_db.exec_one(
"SELECT * from `preference` WHERE id_user = (?)", (user.id))
list_preferences = []
for preference in list_preferences_res:
id_user, id_show, seen_flag, _ = preference
list_preferences.append(Preference(id_user, id_show,
seen_flag))
return list_preferences
def __init__(self):
"""
This class Manage the notifications updates. It's running in background, checking regulary if some notifications
have to be updated.
"""
Thread.__init__(self)
self.name = random.random()
self.notif_step = 3600 # check for update every hours
self.max_request_per_minute = 3
self.absolute_max_request_per_second = 100
self.min_time_between_maj = 10 # in s
self.__sleep_between_request = max(
60 / self.max_request_per_minute,
1 / self.absolute_max_request_per_second)
self.my_db = MyDBConnection(CONFIG["db_path"])
def update_user_in_bdd(self,
my_db: MyDBConnection,
firstname: str = None,
surname: str = None,
login: str = None,
pwd: str = None,
poster: str = None,
list_preferences: List[Show] = None):
if firstname is not None:
self.__set_firstname(firstname)
if surname is not None:
self.__set_surname(surname)
if login is not None:
self.__set_login(login)
if pwd is not None:
self.__set_pwd(pwd)
if poster is not None:
self.__set_poster(poster)
if list_preferences is not None:
self.__set_list_preferences(list_preferences)
my_db.exec_one(
"UPDATE USER SET firstname=(?), surname=(?), login=(?), pwd=(?), poster=(?) WHERE id=(?)",
(self.firstname, self.surname, self.login, self.pwd, self.poster,
self.id))
def get_all_with_a_pref(my_db: MyDBConnection):
show_list = []
list_shows_in_bdd = my_db.exec_one("""
SELECT DISTINCT pict, last_maj, title, season_next_episode_num, next_episode_date, next_episode_num, api_id, show.id
FROM `show` JOIN preference
WHERE preference.id_show = show.api_id""")
if not list_shows_in_bdd:
return None
else:
for show_res in list_shows_in_bdd:
pict, last_maj, title, season_next_episode_num, next_episode_date, next_episode_num, api_id, db_id = show_res
show_list.append(
Show(title, pict, api_id, season_next_episode_num,
next_episode_num, str_to_datetime(next_episode_date),
str_to_datetime(last_maj), db_id))
return show_list
def post(self, user_id: int):
my_db = MyDBConnection("db/gotCrawler.db")
# retrieve the user from the id
user = UserController.get_one_from_id(user_id, my_db)
if user is None:
return "Error, this user does not exist"
posted_data = request.get_json()
show_id = posted_data["show_id"]
# get the show from the id
show = ShowController.get_or_create_from_db_w_api_id(my_db, show_id)
if show is None:
return "Error, the API does not know this Show.", 500
PreferenceController.add_preference(my_db, user, show, 0)
return "ok"
def get_show_preferences_from_user(cls, user: User, my_db: MyDBConnection):
show_pref_db_res = my_db.exec_one("""
SELECT $cols FROM show
JOIN preference ON preference.id_show = show.api_id
WHERE preference.id_user = (?)""",
args=(user.id),
selected_rows=cls.__db_rows,
rows_as_objects=True)
show_preferences = []
# we force the order in a way that the DB result has the perfect shape
# for creating the object (with selected_rows).
for row in show_pref_db_res:
pop_args = [
row[row_name] for row_name in ShowPreferences.__db_rows
]
show_preferences.append(cls(*pop_args))
return show_preferences
def delete(self, user_id: int, show_id: int):
my_db = MyDBConnection(CONFIG["db_path"])
user_id = int(user_id)
show_id = int(show_id)
# retrieve the user from the id
user = UserController.get_one_from_id(user_id, my_db)
if not user:
return None
# get the show from the id
show = ShowController.get_or_create_from_db_w_api_id(my_db, show_id)
if not show:
return None
try:
PreferenceController.delete_preference(
my_db,
PreferenceController.get_one_w_api_id(my_db, user, show))
except ValueError as err:
return str(err), 500
return "ok"
from src.db.MyDBConnection import MyDBConnection
import os
DB_FILENAME = 'db/gotCrawler.db'
if os.path.isfile(DB_FILENAME) and input(
"Woops, a database already exist, remove it ? (y/n) : ") == 'y':
os.remove(DB_FILENAME)
with open('db/create_and_seed.sql') as sql_file:
my_db_connection = MyDBConnection(DB_FILENAME)
queries = sql_file.read().split(';')
my_db_connection.exec_mul(queries)
def update_preference_seen_flag_in_bdd(self, my_db: MyDBConnection,
seen_flag: int):
if seen_flag is None:
raise ValueError("seen_flag cannot be None")
my_db.exec_one("UPDATE preference SET seen_flag=(?) WHERE id=(?)",
(seen_flag, self.id))
def delete_user_in_bdd(self, my_db: MyDBConnection):
my_db.exec_one("DELETE from `user` WHERE `user`.id = (?)", (self.id))
del self
def delete_preference_in_bdd(self, my_db: MyDBConnection):
my_db.exec_one("DELETE from `preference` WHERE `preference`.id = (?)",
(self.id))
del self