def get_pokemon_id(pok_name):
with connection.cursor() as cursor:
query = f"SELECT id FROM Pokemon WHERE name = '{pok_name}'"
cursor.execute(query)
result = cursor.fetchone()
connection.commit()
try:
return result.get("id")
except AttributeError:
return None
def get_heaviest_pokemon():
query = """SELECT * FROM Pokemon WHERE weight_ = (SELECT MAX(weight_) FROM Pokemon);"""
try:
with connection.cursor() as cursor:
cursor.execute(query)
pokemon = cursor.fetchone()
if not pokemon:
return json.dumps({"Error": "pokemon does not exists"}), 404
return json.dumps(pokemon), 200
except Exception as ex:
return {"Error": str(ex)}, 500
def get_type_id(type_):
try:
with connection.cursor() as cursor:
query = f"SELECT id FROM Types WHERE name = '{type_}';"
cursor.execute(query)
result = cursor.fetchone()
connection.commit()
return result["id"]
except Exception:
return -1
def get_trainer_id(trainer):
try:
with connection.cursor() as cursor:
# name, town = trainer.split(' ')
# models = f"SELECT id FROM trainer WHERE name = '{name}' and town = '{town}';"
query = f"SELECT id FROM Trainer WHERE name = '{trainer}'"
cursor.execute(query)
result = cursor.fetchone()
return result["id"]
except Exception:
return -1
def new_query(query):
try:
with connection.cursor() as cursor:
cursor.execute(query)
result = []
item = cursor.fetchone()
while item:
result.append(item["name"])
item = cursor.fetchone()
return result
except Exception as ex:
return "Error"
def update_owned_by(new_pokemon, trainer, old_pokemon):
query = f"""UPDATE Owned_by
SET pokemon_id = {new_pokemon["id"]}
WHERE trainer_id = {trainer["id"]} AND pokemon_id = {old_pokemon["id"]};"""
try:
with connection.cursor() as cursor:
cursor.execute(query)
connection.commit()
return json.dumps({"succeed": f"{trainer['name']} pokemon evolved to {new_pokemon['name']}"}), 200
except IntegrityError:
delete_from_trainer(trainer['name'], old_pokemon['name'])
return json.dumps({"error": f"{trainer['name']} owned the evolve {new_pokemon['name']}"}), 500
def find_pokemon_by_type(type_):
query = f"""SELECT Pokemon.name \
FROM Pokemon JOIN Of_type on Pokemon.id = Of_type.pokemon_id \
WHERE Of_type.type_id = {pokemon_type.get_type_id(type_)} ;"""
try:
with connection.cursor() as cursor:
cursor.execute(query)
ans = []
result = cursor.fetchone()
while result:
ans.append(result["name"])
result = cursor.fetchone()
return json.dumps({"ok": ans}), 200
except IntegrityError as ex:
return json.dumps({"error": "not found"}), 404
except Exception as ex:
return json.dumps({"Error": str(ex)}), 500
def delete_from_trainer(trainer, pokemon_):
t_id = get_trainer_id(trainer)
pok_id = pokemon.get_pokemon_id(pokemon_)
if t_id == -1:
return json.dumps({"error": "Not Found trainer"}), 404
if pok_id is None:
return json.dumps({"error": "Not Found pokemon name"}), 404
query = f"""DELETE FROM Owned_by \
WHERE pokemon_id = {pok_id} AND trainer_id = {t_id};"""
try:
with connection.cursor() as cursor:
cursor.execute(query)
connection.commit()
return json.dumps({"deleted": f"{pokemon_} of {trainer}"}), 200
except IntegrityError:
return json.dumps({"internal error": "DB Error"}), 500
return json.dumps({"ok": "deleted trainer"}), 200
def insert_to_table(name_table, values, fields=""):
with connection.cursor() as cursor:
query = f"INSERT into {name_table}{fields} values ({values});"
cursor.execute(query)
connection.commit()