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!"