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_seasons(show_id):
url = 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(season)
def insert_shows(limit=20, max_show_count=1000):
if limit < 1:
limit = 1
if limit > TRAKT_MAX_LIMIT:
limit = TRAKT_MAX_LIMIT
if max_show_count > TRAKT_MAX_SHOW_COUNT:
max_show_count = TRAKT_MAX_SHOW_COUNT
inserted_ids = []
result_page = 1
total_counter = 0
while total_counter < max_show_count:
url = "{api_url}/shows/popular?limit={limit}&page={page}&extended=full".format(
api_url=TRAKT_API_URL, limit=limit, page=result_page)
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_show_genres(genre_ids, show)
except DataError:
print('Error while inserting ' + show['title'] +
'. Skipping this show...')
# except IntegrityError:
# print('Show (' + show['title'] + ') already exists...')
insert_seasons(show['id'])
insert_cast(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_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_genres():
genres = tmdb.Genres().tv_list()["genres"]
genres += tmdb.Genres().movie_list()["genres"]
genres = {x['id']: x for x in genres}.values()
i = 0
for i, genre in enumerate(genres):
statement = "INSERT INTO genres (id, name) VALUES (%(id)s, %(name)s) ON CONFLICT DO NOTHING;"
execute_dml_statement(statement, genre)
progress_bar(i, len(genres), prefix='Inserting genres:')
clear_progress_bar('Inserted ' + str(i) + ' genres')
def insert_genres():
url = TRAKT_API_URL + '/genres/movies'
genre_request = requests.get(url, headers=headers)
counter = 0
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_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 save_data(group_id, cards, boards):
current_boards = data_manager.execute_select("""SELECT id FROM boards""")
current_cards = data_manager.execute_select("""SELECT id FROM cards""")
for board in boards:
if is_in_current_data(current_boards, board['id']):
data_manager.execute_dml_statement("""UPDATE boards SET title=%(title)s,is_active=%(is_active)s
WHERE id=%(board_id)s AND group_id=%(group_id)s""",
{'title': board['title'], 'is_active': board['is_active'],
'board_id': board['id'], 'group_id': group_id})
else:
data_manager.execute_dml_statement(
"""INSERT INTO boards (title, is_active, group_id) VALUES (%(title)s,%(is_active)s,%(group_id)s)""",
{'title': board['title'], 'is_active': board['is_active'],
'group_id': group_id})
for card in cards:
if card['board_id'] is None:
remove_card(card['id'])
else:
if is_in_current_data(current_cards, card['id']):
data_manager.execute_dml_statement("""UPDATE cards SET title=%(title)s,board_id=%(board_id)s,status_id=%(status_id)s,"order"=%(order)s
WHERE id=%(card_id)s
""", {'title': card['title'], 'board_id': card['board_id'],
'status_id': card['status_id'], 'order': card['order'],
'card_id': card['id']})
else:
data_manager.execute_dml_statement(
"""INSERT INTO cards (title, board_id) VALUES (%(title)s,%(board_id)s)""",
{'title': card['title'], 'board_id': card['board_id']})
def update_card(updated_card):
return data_manager.execute_dml_statement(
'''
UPDATE cards
SET title = %(title)s, board_id = %(board_id)s, status_id = %(status_id)s, "order" = %(order)s
WHERE id = %(id)s;
''', updated_card)
def insert_shows(max_show_count=1000):
max_show_count = max(1, min(max_show_count, TMDB_MAX_SHOW_COUNT))
result_page = 1
total_counter = 0
while total_counter < max_show_count:
result_set = tmdb.TV().popular(page=result_page)['results']
result_page += 1
for show in result_set:
progress_bar(total_counter,
max_show_count,
prefix='Inserting shows:',
suffix=show['name'])
if total_counter < START_WITH:
total_counter += 1
continue
# Some shorts have no id, some has no date, etc... Skip these
if show['id'] is None or show['first_air_date'] is None:
print('Some data is missing for ' + show['title'] +
'. Skipping this show...')
statement = """
INSERT INTO shows (id, title, air_date, overview, rating)
VALUES (%(id)s, %(name)s, %(first_air_date)s, %(overview)s, %(vote_average)s)
ON CONFLICT DO NOTHING;
"""
try:
execute_dml_statement(statement, show)
except DataError:
print('Error while inserting ' + show['name'] +
'. Skipping this show...')
else:
insert_genres_of_show(show['genre_ids'], show)
insert_seasons_of_show(show['id'])
total_counter += 1
# 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
clear_progress_bar('Inserted ' + str(total_counter) + ' shows')
def insert_actors_of_type(cast_type, type_id, cast):
for actor in cast:
insert_actor(actor)
query = sql.SQL("""
INSERT INTO {0} ({1}, actor_id, character_name)
VALUES (%(type_id)s, %(actor_id)s, %(character_name)s)
ON CONFLICT DO NOTHING;
""").format(
sql.Identifier(ACTOR_TYPE[cast_type]['table']),
sql.Identifier(ACTOR_TYPE[cast_type]['id']),
)
execute_dml_statement(
query, {
'type_id': type_id,
'actor_id': actor['id'],
'character_name': actor['character']
})
def add_new_user(username, password):
return data_manager.execute_dml_statement(
'''
INSERT INTO users
VALUES (DEFAULT, %(username)s, %(password)s)
''', {
"username": username,
"password": password
})
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 insert_seasons_of_show(show_id):
show_details = tmdb.TV(show_id).info(append_to_response="credits")
show_credits = show_details.get('credits', {})
insert_actors_of_type('show', show_details['id'],
show_credits.get('cast', []))
for i, season_raw in enumerate(show_details['seasons']):
statement = """
INSERT INTO seasons (id, season_number, air_date, episode_count, poster_path, title, overview, show_id)
VALUES (%(id)s, %(season_number)s, %(air_date)s, %(episode_count)s,
%(poster_path)s, %(name)s, %(overview)s, %(show_id)s)
ON CONFLICT DO NOTHING;
"""
season = get_season_entity(season_raw, show_id)
execute_dml_statement(statement, season)
insert_episodes_of_season(show_id, season['season_number'])
progress_bar(i + 1,
len(show_details['seasons']),
prefix='Inserting seasons:',
suffix=f"{show_details['name']} - {season['name']}")
def create_board(board_title, user_id):
return data_manager.execute_dml_statement(
"""
INSERT INTO boards (title, is_active, user_id, creation_time, modified_time)
VALUES (%(board_title)s, FALSE, %(user_id)s, now(), now())
RETURNING id
""", {
'board_title': board_title,
'user_id': user_id
})
def save_card_title(card_id, title):
return data_manager.execute_dml_statement(
"""
UPDATE cards SET title = %(title)s
WHERE id = %(card_id)s
RETURNING id;
""", {
'card_id': card_id,
'title': title
})
def save_board_status(boardId, is_active):
return data_manager.execute_dml_statement(
"""
UPDATE boards
SET is_active = %(is_active)s
WHERE boards.id = %(boardId)s
RETURNING id;
""", {
'is_active': is_active,
'boardId': boardId
})
def create_user(username, password):
return data_manager.execute_dml_statement(
"""
INSERT INTO users
(user_name,password)
VALUES(%(username)s,%(password)s)
RETURNING id;
""", {
'username': username,
'password': password
})
def edit_card(card_id, status_id, order):
return data_manager.execute_dml_statement(
"""
UPDATE cards
SET status_id = %(status_id)s,
"order" = %(order)s
WHERE id = %(card_id)s;
""", {
'card_id': card_id,
'status_id': status_id,
'order': order
})
def insert_actor(actor):
execute_dml_statement(
"""
INSERT INTO actors
(id, name, gender, profile_path, birthday, death, biography, popularity, place_of_birth, homepage)
VALUES (%(id)s, %(name)s, %(gender)s, %(profile_path)s, %(birthday)s, %(death)s,
%(biography)s, %(popularity)s, %(place_of_birth)s, %(homepage)s)
ON CONFLICT(id) DO UPDATE SET
(name, gender, profile_path, birthday, death, biography, popularity, place_of_birth, homepage)
= (%(name)s, %(gender)s, %(profile_path)s, %(birthday)s, %(death)s,
%(biography)s, %(popularity)s, %(place_of_birth)s, %(homepage)s);
""", {
"id": actor.get('id', None),
"name": actor.get('name', None),
"gender": actor.get('gender', None),
"profile_path": actor.get('profile_path', None),
"birthday": actor.get('birthday', None),
"death": actor.get('deathday', None),
"biography": actor.get('biography', None),
"popularity": actor.get('popularity', None),
"place_of_birth": actor.get('place_of_birth', None),
"homepage": actor.get('homepage', None),
})
def insert_episodes_of_season(show_id, season_number):
season_details = tmdb.TV_Seasons(
show_id, season_number).info(append_to_response="credits")
episodes = season_details['episodes']
season_credits = season_details.get('credits', {})
insert_actors_of_type('season', season_details['id'],
season_credits.get('cast', []))
for i, episode in enumerate(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)
ON CONFLICT DO NOTHING;
"""
execute_dml_statement(
stmt, get_episode_entity(season_details["id"], episode))
# episode_credits = tmdb.TV_Episodes(show_id, season_number, episode['episode_number']).credits()
# insert_actors_of_type('episode', episode['id'], episode_credits.get('cast', []))
progress_bar(i + 1,
len(episodes),
prefix='Inserting episodes:',
suffix=f"{season_details['name']} - {episode['name']}")
def create_new_card(title, board_id, user_id):
next_order = get_new_order(board_id)[0]
if next_order is None:
next_order = 1
else:
next_order += 1
return data_manager.execute_dml_statement(
"""
INSERT INTO cards (title, board_id, status_id, "order", user_id)
VALUES (%(title)s, %(board_id)s, 1, %(next_order)s, %(user_id)s)
RETURNING id,"order"
""", {
'title': title,
'board_id': board_id,
'next_order': next_order,
'user_id': user_id
})
def remove_group(group_id):
data_manager.execute_dml_statement("""
DELETE
FROM groups
WHERE groups.id=%(group_id)s;
""", {'group_id': group_id})
def add_user_account(name, password):
response = data_manager.execute_dml_statement(
"""INSERT INTO accounts (username, password) VALUES (%(name)s, %(pass)s)""",
{'name': name, 'pass': password}
)
return response
def get_userid_by_name(username):
return data_manager.execute_dml_statement(
"""
SELECT id FROM users
WHERE user_name = %(user_name)s;
""", {'user_name': username})
def add_user_to_group(account_id, group_id):
data_manager.execute_dml_statement("""
INSERT INTO account_groups (account_id, group_id)
VALUES (%(account_id)s, %(group_id)s)
""", {'account_id': account_id, 'group_id': group_id})
def execute_sql_file(filename):
with open(filename, encoding="utf8") as file:
execute_dml_statement(file.read())
def get_new_order(board_id):
return data_manager.execute_dml_statement(
"""
SELECT MAX("order") FROM cards
WHERE board_id = %(board_id)s AND status_id = 1;
""", {'board_id': board_id})
def add_group(account_id, title):
group_id = data_manager.execute_dml_statement("""INSERT INTO groups (name) VALUES (%(title)s) RETURNING id""",
{'title': title})
data_manager.execute_dml_statement(
"""INSERT INTO account_groups (account_id, group_id) VALUES (%(account_id)s,%(group_id)s)""",
{'account_id': account_id, 'group_id': group_id})
def execute_sql_file(filename):
with open(filename) as file:
execute_dml_statement(file.read())
