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;""")