def update_recipe_into_db(data):
db.execute(
"UPDATE recipes SET name=%s, pre_time=%s, difficulty=%s, vegetarian=%s, created_at=%s WHERE id=%s",
(data['name'], data['pre_time'], data['difficulty'],
data['vegetarian'], datetime.datetime.now(), data['id']))
db_connection.commit()
status_code = 200
return RECIPE_UPDATED, status_code
def delete_partido(data):
try:
db.execute("DELETE FROM partido where id=%s" % data['id'])
db_connection.commit()
status_code = 200
return PARTIDO_DELETED, status_code
except Exception as e:
db_connection.rollback()
return {'errors': e.args}, 400
def create_recipe_into_db(data):
db.execute(
"INSERT INTO recipes (name, pre_time, difficulty, vegetarian, created_at) VALUES (%s, %s, %s, %s, %s)",
(data['name'], data['pre_time'], data['difficulty'],
data['vegetarian'], datetime.datetime.now()))
db_connection.commit()
# db_connection.close()
# db.close()
status_code = 201
return RECIPE_CREATED, status_code
def delete_recipe_from_db(data):
recipes = fetch_all_recipes()
for recipe in recipes:
if recipe['id'] == int(data['id']):
db.execute("DELETE FROM recipes where id=%s" % data['id'])
db_connection.commit()
status_code = 200
return RECIPE_DELETED, status_code
status_code = 400
return DATA_NOT_FOUND, status_code
def recipe_rating_into_db(data):
recipes = fetch_all_recipes()
for recipe in recipes:
if recipe['id'] == int(data['id']):
db.execute(
"INSERT INTO recipe_rating (recipe_id, rated) VALUES (%s, %s)",
(data['id'], data['rated']))
db_connection.commit()
status_code = 201
return RECIPE_RATING, status_code
status_code = 400
return DATA_NOT_FOUND, status_code
def add_partido(data):
try:
db.execute(
"INSERT INTO partido (nome, sigla, fundo, numero, created_at) VALUES (%s, %s, %s, %s, %s)",
(data['nome'], data['sigla'], data['fundo'], data['numero'],
datetime.datetime.now()))
db_connection.commit()
status_code = 201
return PARTIDO_CREATED, status_code
except Exception as e:
db_connection.rollback()
return {'errors': e.args}, 400
def update_partido(data):
try:
db.execute(
"UPDATE partido SET nome=%s, sigla=%s, fundo=%s, numero=%s WHERE id=%s",
(data['nome'], data['sigla'], data['fundo'], data['numero'],
data['id']))
db_connection.commit()
status_code = 200
return PARTIDO_UPDATED, status_code
except Exception as e:
db_connection.rollback()
return {'errors': e.args}, 400
def get_all_partidos():
db.execute("SELECT id, nome, sigla, fundo, numero, created_at "
"FROM partido ORDER BY id ASC")
partidos = []
for item in db.fetchall():
data = dict()
data['id'] = item[0]
data['nome'] = item[1]
data['sigla'] = item[2]
if item[3]:
data['fundo'] = float(item[3])
data['numero'] = item[4]
data['created_at'] = item[5].strftime('%Y-%m-%dT%H:%M:%S')
partidos.append(data)
return partidos
def get_partido_by_id(partido_id):
db.execute("SELECT id, nome, sigla, fundo, numero, created_at "
f"FROM partido where id = {partido_id}")
partidos = []
for item in db.fetchall():
data = dict()
data['id'] = item[0]
data['nome'] = item[1]
data['sigla'] = item[2]
if item[3]:
data['fundo'] = float(item[3])
data['numero'] = item[4]
data['created_at'] = item[5].strftime('%Y-%m-%dT%H:%M:%S')
partidos.append(data)
return partidos[0] if len(partidos) > 0 else None
def fetch_all_recipes():
# db.execute(
# "SELECT recipes.id, name, pre_time, difficulty, vegetarian, created_at, ROUND(AVG(rated),2) FROM recipes INNER JOIN recipe_rating ON recipe_id=id GROUP BY recipes.id")
db.execute(
"SELECT recipes.id, name, pre_time, difficulty, vegetarian, created_at, ROUND(AVG(rated),2) "
"FROM recipes LEFT JOIN recipe_rating ON recipe_id=id GROUP BY recipes.id ORDER BY id ASC"
)
recipes = []
for item in db.fetchall():
data = dict()
data['id'] = item[0]
data['name'] = item[1]
data['pre_time'] = item[2]
data['difficulty'] = item[3]
data['vegetarian'] = item[4]
data['created_at'] = item[5].strftime('%Y-%m-%dT%H:%M:%S')
data['average_rating'] = str(item[6])
recipes.append(data)
# serializer = json.dumps(db.fetchall(), indent=4, sort_keys=True, default=str)
return recipes
from db.database_connection import db_connection, db
recipe_table = "CREATE TABLE recipes (id serial PRIMARY KEY , name VARCHAR(100), " \
"pre_time INTEGER CHECK ( pre_time > 0), difficulty INTEGER check ( difficulty > 0)," \
" vegetarian BOOLEAN, created_at TIMESTAMP )"
# db.execute("CREATE TABLE vubon (id serial PRIMARY KEY, num integer, data varchar);")
recipe_rating = "CREATE TABLE recipe_rating (recipe_id INTEGER REFERENCES recipes, rated INTEGER CHECK( rated > 0))"
db.execute("select exists(select * from information_schema.tables where table_name=%s)", ('recipes',))
# checking if table already exists then pass or create that table
if db.fetchone()[0]:
pass
else:
db.execute(recipe_table)
db.execute("select exists(select * from information_schema.tables where table_name=%s)", ('recipe_rating',))
if db.fetchone()[0]:
pass
else:
db.execute(recipe_rating)
db_connection.commit()
# db_connection.close()
# db.close()
from db.database_connection import db_connection, db
partido_table = "CREATE TABLE partido (id serial primary key , nome VARCHAR(150) not null, " \
" sigla varchar(10) not null, fundo decimal(10, 2) null," \
" numero integer not null, created_at TIMESTAMP )"
resp = db.execute(
"select exists(select * from information_schema.tables where table_name=%s)",
('partido', ))
# checando se a tabela já foi criada
if db.fetchone()[0]:
print('Tabela partido já criada.')
else:
db.execute(partido_table)
db_connection.commit()
# db_connection.close()
# db.close()
import datetime
from db.database_connection import db_connection, db
db.execute(
"INSERT INTO recipes (name, pre_time, difficulty, vegetarian, created_at) VALUES (%s, %s, %s, %s, %s)",
('Hello', 10, 3, True, datetime.datetime.now()))
db.execute(
"INSERT INTO recipes (name, pre_time, difficulty, vegetarian, created_at) VALUES (%s, %s, %s, %s, %s)",
('Recipe Two', 8, 3, True, datetime.datetime.now()))
db_connection.commit()
db_connection.close()
db.close()
