def load_from_db_by_id(cls, recipe_id):
with ConnectionFromPool() as cursor:
cursor.execute("SELECT * from recipes WHERE recipe_id=%s", (recipe_id,))
recipe_data = cursor.fetchone()
print(recipe_data)
return cls(recipe_id=recipe_data[0], title=recipe_data[1], type=recipe_data[2], image_url=recipe_data[3], beer_url=recipe_data[4],
batch=recipe_data[5], original_gravity=recipe_data[6], final_gravity=recipe_data[7],
abv=recipe_data[8], ibu=recipe_data[9], directions=recipe_data[10])
def find_all(cls):
with ConnectionFromPool() as cursor:
cursor.execute("SELECT recipes.recipe_id, recipes.title, recipes.type, recipes.image_url, recipes.beer_url, "
"recipes.batch, recipes.original_gravity, recipes.final_gravity, recipes.abv, recipes.ibu, "
"recipes.directions, ingredients.ingredient "
"FROM recipes, ingredients "
"WHERE recipes.recipe_id = ingredients.recipe_id")
columns = [desc[0] for desc in cursor.description]
rows = [dict(zip(columns, row)) for row in cursor.fetchall()]
result = Recipe.jsonify_data(rows)
return json.dumps(result)
def find_generic(cls, query, params):
q = ("SELECT recipes.recipe_id, recipes.title, recipes.type, recipes.image_url, recipes.beer_url, "
"recipes.batch, recipes.original_gravity, recipes.final_gravity, recipes.abv, recipes.ibu, "
"recipes.directions, ingredients.ingredient "
"FROM recipes JOIN ingredients ON recipes.recipe_id=ingredients.recipe_id ")
q += query
with ConnectionFromPool() as cursor:
cursor.execute(q, params)
columns = [desc[0] for desc in cursor.description]
rows = [dict(zip(columns, row)) for row in cursor.fetchall()]
result = Recipe.jsonify_data(rows)
return result
def find_name(cls, name):
name = name.replace("+", " ")
name = '%' + name + '%'
with ConnectionFromPool() as cursor:
cursor.execute("SELECT recipes.recipe_id, recipes.title, recipes.type, recipes.image_url, recipes.beer_url, "
"recipes.batch, recipes.original_gravity, recipes.final_gravity, recipes.abv, recipes.ibu, "
"recipes.directions, ingredients.ingredient "
"FROM recipes, ingredients "
"WHERE recipes.recipe_id = ingredients.recipe_id "
"AND LOWER(recipes.title) LIKE LOWER(%s)", (name,))
columns = [desc[0] for desc in cursor.description]
rows = [dict(zip(columns, row)) for row in cursor.fetchall()]
result = Recipe.jsonify_data(rows)
return result
def general_query(cls, q):
col = q[0].replace('+', ' ')
col = 'recipes.' + col
search = q[1].replace("+", ' ')
search = '%' + search + '%'
query = ("SELECT recipes.recipe_id, recipes.title, recipes.type, recipes.image_url, recipes.beer_url, "
"recipes.batch, recipes.original_gravity, recipes.final_gravity, recipes.abv, recipes.ibu, "
"recipes.directions, ingredients.ingredient "
"FROM recipes, ingredients "
"WHERE recipes.recipe_id = ingredients.recipe_id ")
query = query + "AND LOWER(" + col
query = query + ") LIKE LOWER("
query = query + "'" + search + "')"
with ConnectionFromPool() as cursor:
cursor.execute(query)
columns = [desc[0] for desc in cursor.description]
rows = [dict(zip(columns, row)) for row in cursor.fetchall()]
result = Recipe.jsonify_data(rows)
return json.dumps(result)
def find_random(cls, num_results):
max_resuts = 20
if num_results > max_resuts:
num_results = max_resuts
rand_recipes = []
for x in range(num_results):
rand_recipes.append(random.randint(1, 370))
rand_tuple = tuple(rand_recipes)
with ConnectionFromPool() as cursor:
cursor.execute("SELECT recipes.recipe_id, recipes.title, recipes.type, recipes.image_url, recipes.beer_url, "
"recipes.batch, recipes.original_gravity, recipes.final_gravity, recipes.abv, recipes.ibu, "
"recipes.directions, ingredients.ingredient "
"FROM recipes, ingredients "
"WHERE recipes.recipe_id = ingredients.recipe_id "
"AND recipes.recipe_id IN %s" % (rand_tuple,))
columns = [desc[0] for desc in cursor.description]
rows = [dict(zip(columns, row)) for row in cursor.fetchall()]
result = Recipe.jsonify_data(rows)
return result
def load_from_db_by_id(cls, recipe_id):
with ConnectionFromPool() as cursor:
cursor.execute("SELECT * from ingredients WHERE recipe_id=%s", (recipe_id,))
recipe_data = cursor.fetchone()
return cls(ingredient_id=recipe_data[1], recipe_id=recipe_data[2], ingredient=recipe_data[3])