def insert_actor_of_show(show_id, actor):
actor_id = actor['person']['ids']['trakt']
existing_actor = execute_select(
"SELECT id FROM actors WHERE id=%(actor_id)s", {'actor_id': actor_id})
if len(existing_actor) == 0:
execute_dml_statement(
"""
INSERT INTO actors (id, name, birthday, death, biography)
VALUES (%(id)s, %(name)s, %(birthday)s, %(death)s, %(biography)s);
""", {
"id": actor_id,
"name": actor['person']['name'],
"birthday": actor['person']['birthday'],
"death": actor['person']['death'],
"biography": actor['person']['biography']
})
execute_dml_statement(
"""
INSERT INTO show_characters (show_id, actor_id, character_name)
VALUES (%(show_id)s, %(actor_id)s, %(character_name)s)
""", {
'show_id': show_id,
'actor_id': actor_id,
'character_name': actor['character']
})
def insert_genres_of_show(genre_ids, show_entity):
for genre_id in genre_ids:
execute_dml_statement(
"""
INSERT INTO show_genres (show_id, genre_id)
VALUES (%(show_id)s, %(genre_id)s); """, {
'show_id': show_entity['id'],
'genre_id': genre_id
})
def insert_shows(limit=20, max_show_count=1000):
limit = max(1, min(limit, TRAKT_MAX_LIMIT))
max_show_count = max(1, min(max_show_count, TRAKT_MAX_SHOW_COUNT))
inserted_ids = []
result_page = 1
total_counter = 0
while total_counter < max_show_count:
url = f"{TRAKT_API_URL}/shows/popular?limit={limit}&page={result_page}&extended=full"
result_page += 1
result_set = requests.get(url, headers=headers)
if result_set == '[]':
break
for show_raw in result_set.json():
# If max_show_count is not dividable by the limit, we have to jump out before processing all received shows
if total_counter >= max_show_count:
break
show = get_show_entity(show_raw)
# Some shorts have no id, some has no date, etc... Skip these
if show['id'] is None or show['year'] is None:
continue
statement = """
INSERT INTO shows (id, title, year, overview, runtime, trailer, homepage, rating)
VALUES (%(id)s, %(title)s, %(year)s, %(overview)s, %(runtime)s, %(trailer)s, %(homepage)s, %(rating)s);"""
try:
execute_dml_statement(statement, show)
inserted_ids.append(show['id'])
if len(show['genres']) > 0:
genre_ids = get_genre_ids(show['genres'])
insert_genres_of_show(genre_ids, show)
except DataError:
print('Error while inserting ' + show['title'] +
'. Skipping this show...')
# except IntegrityError:
# print('Show (' + show['title'] + ') already exists...')
insert_seasons_of_show(show['id'])
insert_cast_of_show(show['id'])
progress_bar(total_counter + 1,
max_show_count,
prefix='Inserting shows:',
suffix=show['title'])
total_counter += 1
clear_progress_bar('Inserted ' + str(len(inserted_ids)) + ' shows')
return inserted_ids
def insert_new_user(username, hashpassword):
try:
data_manager.execute_dml_statement(
"""
INSERT INTO users(username, password)
VALUES (%(username)s, %(hashpassword)s );
""", {
'username': username,
'hashpassword': hashpassword
})
return True
except:
return False
def insert_genres():
url = f'{TRAKT_API_URL}/genres/movies'
genre_request = requests.get(url, headers=headers)
counter = 0
trakt_expected_genre_count = len(genre_request.json())
for i, genre in enumerate(genre_request.json()):
statement = "INSERT INTO genres (name) VALUES (%(name)s);"
execute_dml_statement(statement, {'name': genre['name']})
progress_bar(i, trakt_expected_genre_count, prefix='Inserting genres:')
counter = i
clear_progress_bar('Inserted ' + str(counter) + ' genres')
def insert_seasons_of_show(show_id):
url = f'{TRAKT_API_URL}/shows/{str(show_id)}/seasons?extended=full,episodes'
season_request = requests.get(url, headers=headers)
for season_raw in season_request.json():
statement = """
INSERT INTO seasons ( id, season_number, title, overview, show_id)
VALUES (%(id)s, %(season_number)s, %(title)s, %(overview)s, %(show_id)s);
"""
season = get_season_entity(season_raw, show_id)
execute_dml_statement(statement, season)
insert_episodes_of_season(season)
def insert_comment_query(show_id, user_id, message):
return data_manager.execute_dml_statement(
'INSERT INTO comments (show_id,user_id,message) '
'VALUES (%(show_id)s,%(user_id)s,%(message)s)', {
"show_id": show_id,
"user_id": user_id,
"message": message
})
def change_actor(actor_id, new_name):
return data_manager.execute_dml_statement(
"""
UPDATE actors
SET name = %(new_name)s
WHERE actors.id = %(actor_id)s
""", {"actor_id": actor_id, "new_name": new_name}
)
def add_season(show_id, season_id, season_number, season_title,
season_overview):
try:
data_manager.execute_dml_statement(
"""
INSERT INTO seasons(id, season_number, title, overview, show_id)
VALUES (%(season_id)s, %(season_number)s, %(season_title)s, %(season_overview)s, %(show_id)s)
""", {
'show_id': show_id,
'season_id': season_id,
'season_number': season_number,
'season_title': season_title,
'season_overview': season_overview
})
return True
except:
return False
def update_episode(episode_id, title, overview):
return data_manager.execute_dml_statement(
"""
UPDATE episodes
SET title = %(title)s, overview = %(overview)s
WHERE id = %(id)s
""", {"title": title, "overview": overview, "id": episode_id}
)
def change_task_column(task_id, new_col):
return data_manager.execute_dml_statement(
"UPDATE tasks "
" SET columnid = %(new_column)s "
" WHERE taskid=%(task_id)s;", {
'task_id': task_id,
'new_column': new_col
})
def insert_episodes_of_season(season):
for episode in season['episodes']:
stmt = """
INSERT INTO episodes (
id,
title,
episode_number,
overview,
season_id)
VALUES (
%(id)s,
%(title)s,
%(episode_number)s,
%(overview)s,
%(season_id)s
);
"""
execute_dml_statement(stmt, get_episode_entity(season['id'], episode))
def add_episode(season_id, episode_number, title, overview):
return data_manager.execute_dml_statement(
"""
INSERT INTO episodes
(title, episode_number, overview, season_id)
VALUES
(%(title)s, %(episode_number)s, %(overview)s, %(season_id)s)
""", {"title": title, "overview": overview, "episode_number": episode_number, "season_id": season_id}
)
def delete_episode(show_id, season_nr, episode_nr):
try:
data_manager.execute_dml_statement(
"""
DELETE
FROM episodes
USING seasons
WHERE seasons.show_id = %(show_id)s
AND seasons.season_number = %(season_nr)s
AND episodes.episode_number = %(episode_nr)s
AND seasons.id = episodes.season_id
""", {
'show_id': show_id,
'season_nr': season_nr,
'episode_nr': episode_nr
})
return True
except:
return False
def update_actor_name(actor_id, new_name):
return data_manager.execute_dml_statement(
"""
UPDATE actors
SET name = %(new_name)s
WHERE actors.id = %(actor_id)s
""", {
'actor_id': actor_id,
'new_name': new_name
})
def register_user(username, text_password, submission_time):
if username_exists(username):
return False
return data_manager.execute_dml_statement(
'INSERT INTO users (username,password,submission_time) '
'VALUES (%(username)s,%(password)s,%(submission_time)s)', {
"username": username,
"password": encrypt_password(text_password),
"submission_time": submission_time
})
def add_season(season_number, title, overview, show_id):
return data_manager.execute_dml_statement(
"""
INSERT INTO seasons
(season_number, title, overview, show_id)
VALUES
(%(season_number)s, %(title)s, %(overview)s, %(show_id)s)
""",
{"season_number": season_number, "title": title, "overview": overview, "show_id": show_id}
)
def update_episode(show_id, season_nr, episode_nr, new_title):
try:
data_manager.execute_dml_statement(
"""
UPDATE episodes
SET title = %(new_title)s
FROM seasons
WHERE seasons.show_id = %(show_id)s
AND seasons.season_number = %(season_nr)s
AND episodes.episode_number = %(episode_nr)s
AND seasons.id = episodes.season_id
""", {
'show_id': show_id,
'season_nr': season_nr,
'episode_nr': episode_nr,
'new_title': new_title
})
return True
except:
return False
def add_episode(show_id, season_nr, episode_nr, episode_title):
try:
season_id_dict = data_manager.execute_select(
"""
select seasons.id
from seasons
where seasons.show_id = %(show_id)s
and seasons.season_number = %(season_nr)s
""", {
'show_id': show_id,
'season_nr': season_nr
}, False)
season_id = int(season_id_dict['id'])
print(season_id, season_nr)
max_taken_episode_id_dict = data_manager.execute_select("""
SELECT MAX(id)
FROM episodes
""",
fetchall=False)
max_taken_episode_id = int(max_taken_episode_id_dict['max'])
episode_id = max_taken_episode_id + 1
print(max_taken_episode_id, episode_nr)
data_manager.execute_dml_statement(
"""
INSERT INTO episodes (id, title, episode_number, season_id)
VALUES ( %(episode_id)s, %(episode_title)s, %(episode_nr)s, %(season_id)s )
""", {
'show_id': show_id,
'season_id': season_id,
'episode_nr': episode_nr,
'episode_title': episode_title,
'episode_id': episode_id
})
return True
except:
return False
def check_user(username):
return data_manager.execute_dml_statement(
'SELECT id, password '
'FROM users '
'WHERE username '
'ILIKE %(username)s;', {"username": username})
def add_comment_to_database(data):
return data_manager.execute_dml_statement(""" INSERT INTO comments (username, show_id, subject, comment) VALUES( %(data_username)s, %(data_show_id)s, %(data_subject)s, %(data_comment)s)
""", {'data_username': data['username'], 'data_show_id': data['show_id'], 'data_subject': data['subject'], 'data_comment': data['comment']})
def username_exists(username):
return data_manager.execute_dml_statement(
'SELECT * FROM users WHERE username = %(username)s;',
{'username': username})
def get_show_by_id(id):
return data_manager.execute_dml_statement(
'SELECT * '
'FROM shows '
'WHERE id = %(id)s;', {'id': id})
def login(username):
return data_manager.execute_dml_statement("""SELECT hashed_password FROM users
WHERE username = %(username)s;""",
{'username': username})
def user_id_by_username(username):
return data_manager.execute_dml_statement("""SELECT id FROM users
WHERE username = %(username)s;""",
{'username': username})
def add_user(user_data):
return data_manager.execute_dml_statement(
"INSERT INTO users (email, username, password)"
"VALUES %(email)s, %(username)s, %(password)s);", user_data)
def add_column(column):
return data_manager.execute_dml_statement(
"INSERT INTO columns (title, boardID) "
"VALUES(%(title)s, %(board_id)s);", column)
def add_board(board_data):
return data_manager.execute_dml_statement(
"INSERT INTO boards (title, userID) "
"VALUES(%(title)s, %(user_id)s);", board_data)
def add_user(username, hashed_password):
query = """
INSERT INTO users (username, password)
VALUES (%s, %s);
"""
data_manager.execute_dml_statement(query, (username, hashed_password, ))
def add_new_user(username, password):
return data_manager.execute_dml_statement(
f'''INSERT INTO users (username, password)
VALUES ('{username}', '{password}')
'''
)
