def api_teams_id_get(id):
cursor = conn.cursor()
cursor.execute(
"""
SELECT
id,
name,
description
FROM teams
WHERE id=%s;
""", (id, ))
team = cursor.fetchone()
conn.commit()
cursor = conn.cursor()
cursor.execute(
"""
SELECT
pokemon_id,
member_level
FROM team_members
WHERE teams_id=%s;
""", (id, ))
members = cursor.fetchall()
conn.commit()
formatresults = lambda m: {"pokemon_id": m[0], "level": m[1]}
members_to_return = list(map(formatresults, members))
team_to_return = {
"id": team[0],
"name": team[1],
"description": team[2],
"members": members_to_return
}
return jsonify(team_to_return), 200
def api_pokemon_id_get(id):
cursor = conn.cursor()
cursor.execute(
"""
SELECT
id,
name,
description,
image_url,
type_1,
type_2
FROM pokemon
WHERE id = %s;
""", (id, ))
pokemon = cursor.fetchone()
conn.commit()
cursor = conn.cursor()
cursor.execute(
"""
SELECT
evolution_id,
method,
level,
evolves_to
FROM evolutions
WHERE pokemon_id=%s;
""", (id, ))
evolutions = cursor.fetchall()
conn.commit()
formatresults = lambda e: {
"id": e[0],
"method": e[1],
"level": e[2],
"to": e[3]
}
evolutions_to_return = list(map(formatresults, evolutions))
pokemon_to_return = {
"id": pokemon[0],
"name": pokemon[1],
"description": pokemon[2],
"image_url": pokemon[3],
"types": [pokemon[4], pokemon[5]],
"evolutions": evolutions_to_return
}
return jsonify(pokemon_to_return), 200
def sort_children(self):
# Load all children in order
children = loadChildren(self.id)
c = conn.cursor()
# Loop through looking for duplicates/spaces
lastsort = 0
for child in children:
newsort = lastsort + 1
if child.sort != newsort:
# Update to be correct sort
c.execute(
"""
UPDATE
`data`
SET
`sort` = %s,
`updated` = FROM_UNIXTIME(%s)
WHERE
`id` = %s
AND `ownerid` = %s
""",
(
newsort,
int(time.time()),
child.id,
OWNER_ID
)
)
conn.commit()
lastsort = newsort
c.close()
def api_teams_id_put(id):
cursor = conn.cursor()
updated_team = json.loads(request.data)
cursor.execute(
"""
UPDATE teams
SET
name = %s,
description = %s
WHERE id = %s;
""", (updated_team['name'], updated_team['description'], id))
cursor.execute(
"""
DELETE FROM ONLY team_members
WHERE teams_id = %s;
""", (id, ))
for pokemon in updated_team['members']:
cursor.execute(
"""
INSERT INTO
team_members (teams_id, pokemon_id, member_level)
VALUES
(%s, %s, %s)
""", (id, pokemon['pokemon_id'], pokemon['level']))
conn.commit()
return jsonify(updated_team), 200
def update_text(self, text):
self.text = text
c = conn.cursor()
c.execute(
"""
UPDATE
`data`
SET
`text` = %s,
`updated` = FROM_UNIXTIME(%s)
WHERE
`id` = %s
AND `ownerid` = %s
""",
(
text,
int(time.time()),
self.id,
OWNER_ID
)
)
conn.commit()
c.close()
def load(self, id):
c = conn.cursor()
c.execute(
"""
SELECT
*
FROM
`data`
WHERE
`id` = %s
AND
`ownerid` = %s
""",
(
id,
OWNER_ID
)
);
data = c.fetchone()
if not data:
raise Exception('item %s does not exist' % id)
self.set_data(data)
c.close()
def move(self, moveto):
c = conn.cursor()
c.execute(
"""
UPDATE
`data`
SET
`sort` = %s,
`updated` = FROM_UNIXTIME(%s)
WHERE
`id` = %s
AND `ownerid` = %s
""",
(
moveto,
int(time.time()),
self.id,
OWNER_ID
)
)
conn.commit()
c.close()
parent = item()
parent.id = self.parentid
parent.sort_children()
def api_teams_id_delete(id):
cursor = conn.cursor()
cursor.execute(
"""
DELETE FROM teams *
WHERE id=%s;
""", (id, ))
conn.commit()
return "ok!", 204
def loadChildren(parent):
c = conn.cursor()
c.execute(
"""
SELECT
`data`.`uid`,
`data`.`id`,
`data`.`text`,
`data`.`created`,
`data`.`updated`,
`data`.`sort`,
`children`.`count`
FROM
`data`
LEFT JOIN
(
SELECT
`data`.`parentid` AS `id`,
COUNT(`data`.`uid`) AS `count`
FROM
`data`
WHERE
`data`.`archive` = 0
AND `data`.`parentid` = %s
AND `data`.`ownerid` = %s
GROUP BY
`data`.`parentid`
) AS `children`
ON `children`.`id` = `data`.`id`
WHERE
`data`.`archive` = 0
AND `data`.`parentid` = %s
AND `data`.`ownerid` = %s
ORDER BY
`sort` ASC,
`updated` DESC
""",
(
parent,
OWNER_ID,
parent,
OWNER_ID
)
)
items = []
for data in c:
new = item()
new.set_data(data)
items.append(new)
c.close()
return items
def reparent(self, newparent):
oldparent = self.parentid
self.parentid = newparent
c = conn.cursor()
c.execute(
"""
UPDATE
`data`
SET
`parentid` = %s
WHERE
`id` = %s
AND `ownerid` = %s
""",
(
newparent,
self.id,
OWNER_ID
)
)
newsort = self.get_max_sort()
# Update the id
c.execute(
"""
UPDATE
`data`
SET
`sort` = %s
WHERE
`uid` = %s
AND `ownerid` = %s
""",
(
newsort,
self.uid,
OWNER_ID
)
)
conn.commit()
c.close()
# Resort old parent
p = item()
p.id = oldparent
p.sort_children()
# Resort new parent
p = item()
p.id = self.parentid
p.sort_children()
def api_teams_id_patch(id):
cursor = conn.cursor()
patched_team = json.loads(request.data)
cursor.execute(
"""
SELECT
id,
name,
description
FROM teams
WHERE id = %s;
""", (id, ))
teamdata = cursor.fetchone()
#update name and description
updated_team['name'] = patched_team['name'] if (
'name' in patched_team.keys()) else teamdata[1]
updated_team['description'] = patched_team['description'] if (
'description' in patched_team.keys()) else teamdata[2]
cursor.execute(
"""
UPDATE teams
SET
name = %s,
description = %s
WHERE id = %s;
""", (updated_team['name'], updated_team['description'], id))
#update team members
if 'members' in patched_team.keys():
cursor.execute(
"""
DELETE FROM ONLY team_members
WHERE teams_id = %s;
""", (id, ))
for pokemon in patched_team['members']:
cursor.execute(
"""
INSERT INTO
team_members (teams_id, pokemon_id, member_level)
VALUES
(%s, %s, %s)
""", (id, pokemon['pokemon_id'], pokemon['level']))
updated_team['members'] = patched_team['members']
else:
updated_team['members'] = cursor.execute(
"""
SELECT pokemon_id, member_level
FROM team_members
WHERE teams_id = %s;""", (id, ))
conn.commit()
return jsonify(updated_team), 200
def api_teams_get():
cursor = conn.cursor()
cursor.execute("""
SELECT
id,
name,
description
FROM teams;
""")
teams = cursor.fetchall()
formatresults = lambda t: {'id': t[0], 'name': t[1]}
teams_to_return = list(map(formatresults, teams))
return jsonify(teams_to_return), 200
def searchjson(searchterms):
searchterms = '%'+searchterms+'%'
c = conn.cursor()
c.execute(
"""
SELECT
`data`.`uid`,
`data`.`id`,
`data`.`text`,
`data`.`created`,
`data`.`updated`,
`data`.`sort`,
`data`.`parentid`
FROM
`data`
WHERE
`data`.`archive` = 0
AND `data`.`text` LIKE %s
AND `data`.`ownerid` = %s
ORDER BY
`sort` ASC,
`updated` DESC
""",
(
searchterms,
OWNER_ID
)
)
data = []
for item in c:
dc = {}
dc['id'] = item['id']
dc['text'] = item['text']
dc['children_count'] = 0
dc['parent_id'] = item['parentid']
data.append(dc)
# Return as json formatted string
return json.dumps(data)
def set_archived(self):
c = conn.cursor()
c.execute(
"""
UPDATE
`data`
SET
`archive` = 1
WHERE
`id` = %s
AND `ownerid` = %s
""",
(
self.id,
OWNER_ID
)
)
conn.commit()
c.close()
def api_teams_id_post():
cursor = conn.cursor()
new_team = json.loads(request.data)
cursor.execute(
"""
INSERT INTO
teams (name, description)
VALUES
(%s, %s)
RETURNING id
""", (new_team['name'], new_team['description']))
teams_id = cursor.fetchone()[0]
for pokemon in new_team['members']:
cursor.execute(
"""
INSERT INTO
team_members (teams_id, pokemon_id, member_level)
VALUES
(%s, %s, %s)
""", (teams_id, pokemon['pokemon_id'], pokemon['level']))
conn.commit()
return ("OK!"), 201
def api_pokemon_get():
query = request.args.get("search", "")
cursor = conn.cursor()
if len(query) > 0:
cursor.execute(
"""
SELECT
id,
name,
description,
image_url,
type_1,
type_2
FROM pokemon
WHERE name ILIKE %s;
""", ('%' + query + '%', ))
else:
cursor.execute("""
SELECT
id,
name,
description,
image_url,
type_1,
type_2
FROM pokemon;
""")
pokemon = cursor.fetchall()
conn.commit()
#Not returning 'description' column because it's never used from this call
formatresults = lambda p: {
'id': p[0],
'name': p[1],
'image_url': p[3],
'types': [p[4], p[5]]
}
pokemon_to_return = list(map(formatresults, pokemon))
return jsonify(pokemon_to_return), 200
def get_max_sort(self):
c = conn.cursor()
c.execute(
"""
SELECT
MAX(`sort`) + 1 AS `sort`
FROM
`data`
WHERE
`parentid` = %s
AND `ownerid` = %s
""",
(
self.parentid,
OWNER_ID
)
)
result = c.fetchone()
conn.commit()
c.close()
return result['sort']
def api_teams_id_patch(id):
cursor = conn.cursor()
return "Fix me!"
import api
import json
import re
import io
from data import conn
from flask import Flask, render_template, jsonify, request
from dotenv import load_dotenv
load_dotenv()
app = Flask(__name__)
app.register_blueprint(api.pokemon, url_prefix="/api")
app.register_blueprint(api.teams, url_prefix="/api")
# Get a database connection to run queries
db = conn.cursor()
# Home page route that serves index.html
@app.route('/')
def index():
return render_template('index.html')
# Detail page route that serves detail.html
# For example /1 will give you the detail page for Bulbasaur
@app.route('/pokemon/<int:id>')
def detail_id(id):
return render_template('pokemon/detail.html')
def api_pokemon_id_get(id):
cursor = conn.cursor()
return "Fix me!"
def save(self, parent):
c = conn.cursor()
c.execute(
"""
INSERT INTO
`data`
(
`text`,
`created`,
`updated`,
`parentid`,
`ownerid`,
`sort`
)
VALUES
(
%s,
FROM_UNIXTIME(%s),
FROM_UNIXTIME(%s),
%s,
%s,
0
)
""",
(
self.text,
int(time.time()),
int(time.time()),
parent,
OWNER_ID
)
)
self.uid = c.lastrowid
self.id = self.uid
self.parentid = parent
newsort = self.get_max_sort()
# Update the id
c.execute(
"""
UPDATE
`data`
SET
`id` = %s,
`sort` = %s
WHERE
`uid` = %s
AND `ownerid` = %s
""",
(
self.id,
newsort,
self.uid,
OWNER_ID
)
)
conn.commit()
c.close()
# Resort parent
p = item()
p.id = parent
p.sort_children()
def api_teams_id_get(id):
cursor = conn.cursor()
return "Fix me!"
def api_teams_id_post():
cursor = conn.cursor()
return "Fix me!"
def api_teams_id_delete(id):
cursor = conn.cursor()
return "Fix me!"
