def delete_grocery_list(list_id: int) -> None: grocery_list: GroceryList = get_grocery_list_by_id(list_id) if grocery_list: query_db('delete from grocery_lists where id=%s', [list_id]) return else: raise GroceryListNotFoundException(list_id)
def delete_grocery_item_from_list(list_id: int, item_id: int) -> GroceryList: grocery_items_rows = query_db( 'select * from grocery_items where list_id=%s and id=%s', [list_id, item_id]) if len(grocery_items_rows) == 1: query_db('delete from grocery_items where id=%s and list_id=%s', [item_id, list_id]) return get_grocery_list_by_id(list_id)
def get_grocery_list_by_id(list_id: int) -> GroceryList: grocery_list_rows = query_db('select * from grocery_lists where id=%s', [list_id]) if len(grocery_list_rows) != 1: raise GroceryListNotFoundException(list_id) grocery_items_rows = query_db( 'select * from grocery_items where list_id=%s', [list_id]) return _grocery_list_from_row(grocery_list_rows[0], grocery_items_rows)
def get_user(id): maybe_professionals = query_db('SELECT * FROM professionals WHERE id = ?', [id], one=True) if maybe_professionals is None: return {}, status.HTTP_404_NOT_FOUND else: maybe_badges = query_db('SELECT * FROM badges WHERE professional_id = ?', [id]) maybe_professionals["badges"] = maybe_badges return maybe_professionals
def new_recipe(user_id, name, type_of_food, food_category, description, time): query_db(""" INSERT INTO recipes (user_id, type_of_food_id, food_category_id, name, description, time_required) VALUES (?, ?, ?, ?, ?, ?) """, user_id, type_of_food, food_category, name, description, time, commit=True)
def add_user_to_session(username): query_db("UPDATE users SET last_login_date = DATETIME('now', 'localtime') WHERE username = ?", username, commit=True) row = query_db_object(""" SELECT u.user_id, u.username, r.name AS role_name, u.creation_date, u.last_login_date, u.sex FROM users u INNER JOIN roles r USING(role_id) WHERE username = ? """, username, one=True) session.permanent = True session['user'] = row
def download_checkpoint(checkpoint: int): """ Downloads a zip of model .pt files. :param checkpoint: The name of the checkpoint to download. """ ckpt = db.query_db(f'SELECT * FROM ckpt WHERE id = {checkpoint}', one = True) models = db.get_models(checkpoint) model_data = io.BytesIO() with zipfile.ZipFile(model_data, mode='w') as z: for model in models: model_path = os.path.join(app.config['CHECKPOINT_FOLDER'], f'{model["id"]}.pt') z.write(model_path, os.path.basename(model_path)) model_data.seek(0) return send_file( model_data, mimetype='application/zip', as_attachment=True, attachment_filename=f'{ckpt["ckpt_name"]}.zip', cache_timeout=-1 )
def get_user(id): maybe_consumer = query_db('SELECT * FROM consumers WHERE id = ?', [id], one=True) if maybe_consumer is None: return {}, status.HTTP_404_NOT_FOUND else: return maybe_consumer
def create_user(login, password): if query_db('select count(*) from users where login=?', [login])[0][0] > 0: return None if login in app.config['FAKE_USERS']: return None directory = ''.join([random.choice(string.ascii_lowercase) for _ in range(10)]) query_db_and_commit('insert into users (login, password, directory) values (?, ?, ?)', [login, password, directory]) return User(login, password, directory)
def assign_pioneer_badge(): badge_name = "pioneer" badge_description = "One of the first 100 professionals with at least one confirmed appointment" pioneers = query_db('SELECT DISTINCT professional_id FROM appointments WHERE professional_scheduled is NOT NULL AND consumer_accepted is NOT NULL ORDER BY consumer_accepted asc', []) update_badge_table(badge_name, badge_description, pioneers) return pioneers
def assign_hero_badge(): badge_name = "hero" badge_description = "Professional who spend more than 2 hours helping people in need." heroes = query_db('SELECT professional_id, SUM(appointment_duration) as total FROM appointments GROUP BY professional_id HAVING total >= 120', []) update_badge_table(badge_name, badge_description, heroes) return heroes
def list_scheduled(pid): maybe_requests = query_db( 'SELECT appointments.id AS appointments_id, consumer_id, professional_id, consumers.username, requested, appointment_date, appointment_duration, consumer_accepted, consumer_resigned FROM appointments JOIN consumers ON (' 'consumers.id = consumer_id) WHERE professional_declined is NULL AND professional_scheduled ' 'is NOT NULL AND professional_id =?', [pid]) if not maybe_requests: return {}, status.HTTP_404_NOT_FOUND else: return maybe_requests
def list_availability_requests(pid): maybe_requests = query_db( 'SELECT appointments.id AS appointments_id, consumer_id, consumers.username, requested FROM appointments JOIN ' 'consumers ON ( consumers.id = consumer_id) WHERE professional_declined is NULL AND professional_scheduled ' 'is NULL AND professional_id =?', [pid]) if not maybe_requests: return {}, status.HTTP_404_NOT_FOUND else: return maybe_requests
def list_requested_declined(cid): maybe_requests = query_db( 'SELECT appointments.id AS appointments_id, professional_id, requested, professionals.fullname, professionals.qualifications, professionals.profession, professional_declined FROM appointments JOIN professionals ON (' 'professionals.id = professional_id) WHERE professional_declined is NOT NULL AND professional_scheduled ' 'is NULL AND consumer_id =?', [cid]) if not maybe_requests: return {}, status.HTTP_404_NOT_FOUND else: return maybe_requests
def get_bookmarks(id): maybe_bookmarks = query_db('SELECT professionals.id, fullname, qualifications, profession, specialties, languages ' 'FROM professionals JOIN professionals_bookmarks ON (professionals.id = ' 'professional_id) ' 'WHERE professionals_bookmarks.consumer_id = ?', [id]) if not maybe_bookmarks: return {}, status.HTTP_404_NOT_FOUND else: return maybe_bookmarks
def search(): # payload expected as x-www-form-urlencoded # accepts a list of words separated with whitespaces, commas etc # takes into consideration only 5 first words # two first words are most important # not-secure for demo only criteria = create_criteria_list(request.form['s']) term = create_matching_term(criteria) matching_profiles = query_db('SELECT DISTINCT professional_id, fullname, qualifications, profession ' 'FROM profile_search WHERE profile MATCH ? ORDER BY rank', [term]) return matching_profiles
def update_bookmarks(id, professional_id): maybe_bookmark = query_db('SELECT professional_id FROM professionals_bookmarks WHERE consumer_id = ? AND ' 'professional_id = ? ', [id, professional_id]) # if bookmark exists remove it, else create it if not maybe_bookmark: r = update_db('INSERT INTO professionals_bookmarks (consumer_id, professional_id) values (?, ?)', [id, professional_id]) return {}, status.HTTP_202_ACCEPTED else: update_db('DELETE FROM professionals_bookmarks WHERE consumer_id = ? AND professional_id = ?', [id, professional_id]) return {}, status.HTTP_202_ACCEPTED
def assign_hero_of_the_week(week, year): badge_name = f"hero of the week {week}" badge_description = "f10 professionals who spend most time helping people in need in week {week}/{year}." # libraries star counting weeks from 0 - this needs a bit more investigation c_week = week - 1 week_start_date = datetime.datetime.strptime(f'{year}-{c_week}-1', "%Y-%W-%w").date() week_end_date = week_start_date + datetime.timedelta(days=7) heroes_of_the_week = query_db('SELECT professional_id, appointment_date, SUM(appointment_duration) as total FROM appointments WHERE appointment_date >=date(?) and appointment_date <= date(?) GROUP BY professional_id ORDER BY total DESC LIMIT 10', [week_start_date.isoformat(), week_end_date.isoformat()]) update_badge_table(badge_name, badge_description, heroes_of_the_week) return heroes_of_the_week
def get(user_id): result = query_db('select password, directory from users where login=?', [user_id]) if len(result) != 1: return None return User(user_id, result[0][0], result[0][1])
def find_user(login): result = query_db('select password, directory from users where login=?', [login]) if len(result) == 0: return None return User(login, result[0][0], result[0][1])
def create_grocery_list(name: str) -> GroceryList: query_db('insert into grocery_lists (name) values (%s)', [name]) new_id: int = query_db('select last_insert_rowid()') return get_grocery_list_by_id(new_id)
def delete_recipe(recipe_id): query_db("DELETE FROM recipes WHERE recipe_id = ?", recipe_id, commit=True)
def add_view(recipe_id): query_db("UPDATE recipes SET views = views + 1 WHERE recipe_id = ?", recipe_id, commit=True)
def add_grocery_item_to_list(list_id: int, new_item: GroceryItem) -> GroceryList: query_db( 'insert into grocery_items (list_id, name, quantity) values (%s, %s, %s)', [list_id, new_item['name'], new_item['quantity']]) return get_grocery_list_by_id(list_id)
def recipe_checked(recipe_id): res = query_db("SELECT checked FROM recipes WHERE recipe_id = ?", recipe_id, one=True) return True if res and res[0] == 1 else False
def recipe_exists(name): res = query_db("SELECT COUNT(*) FROM recipes WHERE name = ?", name, one=True) return True if res[0] > 0 else False
def delete_user(user_id): query_db("DELETE FROM users WHERE user_id = ?", user_id, commit=True)
def accept_recipe(recipe_id): query_db("UPDATE recipes SET checked = 1 WHERE recipe_id = ?", recipe_id, commit=True)
def change_password(username, password): query_db("UPDATE users SET password = ? WHERE username = ?", generate_password_hash(password), username, commit=True)
def get_grocery_lists() -> List[GroceryList]: grocery_lists_rows = query_db('select * from grocery_lists') grocery_items_rows = query_db('select * from grocery_items') return _grocery_lists_from_rows(grocery_lists_rows, grocery_items_rows)