def _create_table():
mysql_pool.execute('''
CREATE TABLE ingredients(
type VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL PRIMARY KEY,
calorie INT,
price INT,
url TEXT,
weight INT,
volume INT);
''')
def create_cook_method(cook_method):
"""
create new cook method
"""
type_str = CookMethod.CookMethodType.Name(cook_method.cook_method_type)
exist = get_cook_method(cook_method.cook_method_type)
if exist != None:
raise Exception("Cook method {} already exists".format(type_str))
mysql_pool.execute('''
INSERT INTO cook_method(type) VALUES (%s);
''', (type_str,))
def create_ingredients(ingredients):
"""
create new ingredients
"""
type_str = Ingredients.IngredientsType.Name(ingredients.ingredients_type)
exist = get_ingredients(ingredients.ingredients_type)
if exist != None:
raise Exception("Ingredients {} already exists".format(type_str))
mysql_pool.execute(
'''
INSERT INTO ingredients(type, weight, calorie) VALUES (%s, %s, %s);
''', (type_str, ingredients.kilogram, ingredients.calorie))
def get_ingredients(type):
"""
get ingredients from db
:param type: IngredientsType
:return Ingredients
"""
type_str = Ingredients.IngredientsType.Name(type)
row = mysql_pool.execute('''
SELECT * FROM ingredients WHERE type = (%s)
''', (type_str, ),
return_one=True)
if row == None:
return None
ing = Ingredients()
ing.ingredients_type = Ingredients.IngredientsType.Value(row['type'])
if row['calorie']:
ing.calorie = row['calorie']
if row['price']:
ing.price = row['price']
if row['url']:
ing.picture_urls = row['url']
if row['weight']:
ing.kilogram = row['weight']
if row['volume']:
ing.milliliter = row['volume']
return ing
def _get_prev_steps(step_id):
rows = mysql_pool.execute('''
SELECT * FROM prev_step WHERE cur_id = (%s)
''', (step_id,), return_one=False)
prev_steps = []
for row in rows:
prev_steps.append(row['prev_id'])
return prev_steps
def _create_table():
mysql_pool.execute('''
CREATE TABLE step(
id VARCHAR(100) NOT NULL PRIMARY KEY,
recipe_id VARCHAR(100) NOT NULL,
type VARCHAR(100) NOT NULL,
ingredients_type VARCHAR(100),
serving_count DOUBLE,
cook_method_type VARCHAR(100),
seconds INT,
INDEX by_recipe_id (recipe_id)
);
''')
mysql_pool.execute('''
CREATE TABLE prev_step(
cur_id VARCHAR(100) NOT NULL,
prev_id VARCHAR(100) NOT NULL,
INDEX by_cur_id(cur_id)
);
''')
def get_step(step_id):
"""
get step by unique id
:param step_id: string
:return Step
"""
row=mysql_pool.execute('''
SELECT * FROM step WHERE id = (%s)
''', (step_id,), return_one=True)
if row ==None:
return None
return _build_step(row)
def get_all_steps(recipe_id):
"""
get all steps for a recipe
:param recipe_id: string
:return [Step]
"""
rows = mysql_pool.execute('''
SELECT * FROM step WHERE recipe_id = (%s)
''', (recipe_id,), return_one=False)
steps = []
for row in rows:
steps.append(_build_step(row))
return steps
def get_cook_method(type):
"""
get cook method from db
:param type: CookMethodType
:return CookMethod
"""
type_str = CookMethod.CookMethodType.Name(type)
row=mysql_pool.execute('''
SELECT * FROM cook_method WHERE type = (%s)
''', (type_str,), return_one=True)
if row ==None:
return None
coo = CookMethod()
coo.cook_method_type = CookMethod.CookMethodType.Value(row['type'])
return coo
def create_step_method(step):
"""
create new recipe step
"""
exist = get_step(step.id)
if exist != None:
raise Exception("Step {} already exists".format(step.id))
if step.type == Step.COOK_IT:
mysql_pool.execute('''
INSERT INTO step(id, recipe_id, type, cook_method_type, seconds) VALUES (%s,%s,%s,%s,%s);
''', (step.id,step.recipe_id,CookMethod.CookMethodType.Name(step.cook_it.cook_method_type),
step.cook_it.seconds))
elif step.type == Step.GET_INGREDIENTS:
mysql_pool.execute('''
INSERT INTO step(id, recipe_id, type, ingredients_type, serving_count) VALUES (%s,%s,%s,%s,%s);
''', (step.id,step.recipe_id,Ingredients.IngredientsType.Name(step.get_ingredients.ingredients_type),
step.get_ingredients.serving_count))
for prev_step in step.previous_step_ids:
mysql_pool.execute('''
INSERT INTO prev_step(cur_id, prev_id) VALUES (%s,%s);
''', (step.id, prev_step))
def _create_table():
mysql_pool.execute('''
CREATE TABLE cook_method(
type VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL PRIMARY KEY);
''')