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 get_members(group_id):
return data_manager.execute_select('''
SELECT accounts.username, accounts.id FROM accounts
JOIN account_groups a ON accounts.id = a.account_id
JOIN groups ON a.group_id = groups.id
WHERE groups.id = %(group_id)s;''',
{'group_id': group_id})
def check_unique_username(username):
return len(
data_manager.execute_select(
'''
SELECT username FROM users
WHERE username = %(username)s;
''', {"username": username})) == 0
def add_new_card(new_card):
return data_manager.execute_select(
'''
INSERT INTO cards
VALUES (DEFAULT, %(title)s, %(board_id)s, %(status_id)s, %(order)s, %(is_active)s)
RETURNING id;
''', new_card)
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 check_username(username):
return data_manager.execute_select(
"""
SELECT user_name
FROM users
WHERE user_name = %(username)s;
""", {'username': username})
def delete_card(card_id):
return data_manager.execute_select(
'''
UPDATE cards
SET is_active = FALSE
WHERE id = %(card_id)s
RETURNING id;
''', {"card_id": card_id})
def get_board(user_id, board_id):
return data_manager.execute_select(
'''SELECT * FROM boards
WHERE user_id = %(user_id)s AND id = %(board_id)s;
''', {
'user_id': user_id,
'board_id': board_id
})
def get_data(group_id):
boards = data_manager.execute_select("""
SELECT id,title,is_active FROM boards WHERE group_id=%(group_id)s
""", {'group_id': group_id})
cards = []
for board in boards:
board_id = board['id']
temp_cards = data_manager.execute_select("""SELECT id,title,board_id,status_id,"order" FROM cards
WHERE board_id=%(board_id)s""", {'board_id': board_id})
for card in temp_cards:
cards.append(card)
statuses = data_manager.execute_select('SELECT * FROM statuses')
data = {"boards": boards,
"cards": cards,
"statuses": statuses,
"group_id": group_id}
return data
def add_new_board(new_board):
# SELECT setval('table_id_seq', 3)
return data_manager.execute_select(
'''
INSERT INTO boards
VALUES (DEFAULT, %(title)s, %(is_active)s, %(user_id)s)
RETURNING id;
''', new_board)
def search_user(search_pattern, group):
return data_manager.execute_select(
"""SELECT accounts.id, username FROM accounts
LEFT JOIN
(SELECT accounts.id AS id FROM accounts INNER JOIN account_groups ON accounts.id=account_groups.account_id
WHERE account_groups.group_id=%(group)s) AS temp
ON accounts.id=temp.id
WHERE LOWER(username) LIKE LOWER(%(pattern)s) AND temp.id IS NULL;""",
{'pattern': '%' + search_pattern + '%', 'group': group})
def edit_card(card_title, card_id):
return data_manager.execute_select(
'''
UPDATE cards
SET title = %(card_title)s
WHERE id = %(card_id)s
RETURNING id;
''', {
"card_title": card_title,
"card_id": card_id
})
def update_actors():
actors = execute_select(
"SELECT id FROM actors WHERE popularity ISNULL ORDER BY id")
for i, actor in enumerate(actors):
progress_bar(i, len(actors), prefix='Updating actors:')
try:
actor_data = tmdb.People(
actor['id']).info(append_to_response="images")
except requests.HTTPError:
print(
f"Error while updating actor with id: {actor['id']} Skipping this actor..."
)
else:
insert_actor(actor_data)
def get_user_by_name(name):
return data_manager.execute_select(
"""SELECT * FROM accounts WHERE username=%(name)s;""",
{'name': name})
def get_new_order(board_id):
return data_manager.execute_select(
'''
SELECT MAX(cards.order) FROM cards
WHERE board_id = %(board_id)s;
''', {"board_id": board_id})
def get_card_by_id(card_id):
return data_manager.execute_select(
'''
SELECT * FROM cards
WHERE id = %(card_id)s;
''', {"card_id": card_id})
def check_group_permission(account_id, group_id):
group = data_manager.execute_select("""
SELECT * FROM account_groups WHERE account_id=%(account_id)s AND group_id=%(group_id)s;
""", {'account_id': account_id, 'group_id': group_id})
return len(group) > 0
def get_board(board_id):
return data_manager.execute_select(
'''
SELECT * FROM boards
WHERE id = %(board_is)s;
''', {"board_id": board_id})
def get_cards_by_board_id(board_id):
return data_manager.execute_select(
'''
SELECT * FROM cards
WHERE board_id = %(board_id)s AND is_active = TRUE;
''', {"board_id": board_id})
def get_user_id(username):
return data_manager.execute_select(
'''
SELECT id FROM users
WHERE username = %(username)s;
''', {"username": username})
def get_boards(username):
return data_manager.execute_select(
'''
SELECT boards.* FROM boards JOIN users ON boards.user_id = users.id
WHERE users.username = %(username)s;
''', {"username": username})
def read_hashed_password(username):
return data_manager.execute_select(
'''
SELECT password FROM users
WHERE username = %(username)s;
''', {"username": username})
def get_groups(account_id):
groups = data_manager.execute_select("""SELECT groups.id as group_id, name FROM groups JOIN account_groups a on groups.id = a.group_id
WHERE a.account_id = %(account_id)s""", {'account_id': account_id})
return groups
def get_card_by_id(card_id):
return data_manager.execute_select(
"""
SELECT * FROM cards
WHERE id = %(card_id)s
""", {'card_id': card_id})
def get_tweets(day):
return data_manager.execute_select(
"""SELECT COUNT(id_str), EXTRACT('hour' FROM elonmusk.created_at)::int as hour FROM elonmusk
GROUP BY EXTRACT('day' FROM elonmusk.created_at)::int, hour
HAVING EXTRACT('day' FROM elonmusk.created_at)::int = %(day)s
ORDER BY EXTRACT('hour' FROM elonmusk.created_at)::int ASC;""", {'day': day})
def get_cards(board_id):
return data_manager.execute_select(
'''SELECT * FROM cards
WHERE board_id = %(board_id)s
ORDER BY "order";
''', {'board_id': board_id})
def get_boards(user_id):
return data_manager.execute_select(
'''SELECT * FROM boards
WHERE user_id = %(user_id)s
ORDER BY creation_time ASC;
''', {'user_id': user_id})
def get_hashed_pass(user_name):
return data_manager.execute_select(
'''SELECT password FROM users
WHERE user_name = %(user_name)s;
''', {'user_name': user_name})
def get_genre_ids(genre_list):
genres = tuple((g.title() for g in genre_list))
id_result = execute_select("SELECT id FROM genres WHERE name IN %s;",
(genres, ))
genre_ids = [result['id'] for result in id_result]
return genre_ids
def get_days():
return data_manager.execute_select(
"""SELECT EXTRACT('day' FROM elonmusk.created_at) as day FROM elonmusk
GROUP BY day;""")
