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()