Create a RESTfull API using Python and Flask (Part 3)

Summary From Part 2

In part 2, we implemented a small working API using list of Python dictionaries.

Let’s Go

Before we modify our code, first download the example database from this location and copy the file to your root folder using your graphical user interface (GUI) and unzip it. The final version of our API will query this database when returning results to users.

import flaskfrom flask import request, jsonifyimport sqlite3app = flask.Flask(__name__)app.config["DEBUG"] = Truedef dict_factory(cursor, row):d = {}for idx, col in enumerate(cursor.description):d[col[0]] = row[idx]return d@app.route('/', methods=['GET'])def home():return '''<h1>Distant Reading Archive</h1><p>A prototype API for distant reading of Music Albums and Playlists.</p>'''#Get all media types@app.route('/api/v1/resources/media/all', methods=['GET'])def api_media_all():conn = sqlite3.connect('chinook.db')conn.row_factory = dict_factorycur = conn.cursor()all_media = cur.execute('SELECT * FROM media_types;').fetchall()return jsonify(all_media)#Get all genres types@app.route('/api/v1/resources/genres/all', methods=['GET'])def api_genres_all():conn = sqlite3.connect('chinook.db')conn.row_factory = dict_factorycur = conn.cursor()genres = cur.execute('SELECT * FROM genres;').fetchall()return jsonify(genres)#Get all playlists types@app.route('/api/v1/resources/playlists/all', methods=['GET'])def api_playlists_all():conn = sqlite3.connect('chinook.db')conn.row_factory = dict_factorycur = conn.cursor()playlists = cur.execute('SELECT * FROM playlists;').fetchall()return jsonify(playlists)#Get all playlist_track types@app.route('/api/v1/resources/playlist_track/all', methods=['GET'])def api_playlist_track_all():conn = sqlite3.connect('chinook.db')conn.row_factory = dict_factorycur = conn.cursor()playlist_track = cur.execute('SELECT * FROM playlist_track;').fetchall()return jsonify(playlist_track)#Get all tracks types@app.route('/api/v1/resources/tracks/all', methods=['GET'])def api_tracks_all():conn = sqlite3.connect('chinook.db')conn.row_factory = dict_factorycur = conn.cursor()tracks = cur.execute('SELECT * FROM tracks;').fetchall()return jsonify(tracks)#Get all artists types@app.route('/api/v1/resources/artists/all', methods=['GET'])def api_artists_all():conn = sqlite3.connect('chinook.db')conn.row_factory = dict_factorycur = conn.cursor()artists = cur.execute('SELECT * FROM artists;').fetchall()return jsonify(artists)#Get all albums types@app.route('/api/v1/resources/albums/all', methods=['GET'])def api_albums_all():conn = sqlite3.connect('chinook.db')conn.row_factory = dict_factorycur = conn.cursor()albums = cur.execute('SELECT * FROM albums;').fetchall()return jsonify(albums)@app.errorhandler(404)def page_not_found(e):return "<h1>404</h1><p>The resource could not be found.</p>", 404#Get all tracks by filtering@app.route('/api/v1/resources/tracks', methods=['GET'])def api_filter():query_parameters = request.argstrackid = query_parameters.get('TrackId')name = query_parameters.get('Name')albumid = query_parameters.get('AlbumId')composer = query_parameters.get('Composer')query = "SELECT * FROM tracks WHERE"to_filter = []if trackid:query += ' trackid=? AND'to_filter.append(trackid)if name:query += ' name=? AND'to_filter.append(name)if albumid:query += ' albumid=? AND'to_filter.append(albumid)if composer:query += ' composer=? AND'to_filter.append(composer)if not (trackid or name or albumid or composer):return page_not_found(404)query = query[:-4] + ';'conn = sqlite3.connect('chinook.db')conn.row_factory = dict_factorycur = conn.cursor()results = cur.execute(query, to_filter).fetchall()return jsonify(results)app.run()
python app.py
?Composer=Angus+Young,+Malcolm+Young,+Brian+Johnson
Angus+Young,+Malcolm+Young,+Brian+Johnson
http://127.0.0.1:5000/api/v1/resources/tracks?Composer=Angus+Young,+Malcolm+Young,+Brian+Johnson&TrackId=1

Examining Our API

The database we’re working with has many tables (Ex: media_types, genres, playlists, tracks, artists albums….). Each table consists of different columns(Fields) and rows(Table Data).

#Get all media types@app.route('/api/v1/resources/media/all', methods=['GET'])def api_media_all():conn = sqlite3.connect('chinook.db')conn.row_factory = dict_factorycur = conn.cursor()all_media = cur.execute('SELECT * FROM media_types;').fetchall()return jsonify(all_media)
@app.errorhandler(404)
def page_not_found(e):
return "<h1>404</h1><p>The resource could not be found.</p>", 404
query_parameters = request.args
trackid = query_parameters.get('TrackId')
name = query_parameters.get('Name')
albumid = query_parameters.get('AlbumId')
composer = query_parameters.get('Composer')
SELECT <columns> FROM <table> WHERE <column=match> AND <column=match>;
query = "SELECT * FROM tracks WHERE"
to_filter = []
if trackid:
query += ' trackid=? AND'
to_filter.append(trackid)
if name:
query += ' name=? AND'
to_filter.append(name)
if albumid:
query += ' albumid=? AND'
to_filter.append(albumid)
if composer:
query += ' composer=? AND'
to_filter.append(composer)
if not (trackid or name or albumid or composer):
return page_not_found(404)
query = query[:-4] + ';'
conn = sqlite3.connect('chinook.db')
conn.row_factory = dict_factory
cur = conn.cursor()
results = cur.execute(query, to_filter).fetchall()
return jsonify(results)
Note : In this filter section, I have used only Tracks table to filter data.You can implement your own API with other tables too.

Software Engineer at Pearson Lanka || Former Associate Software Engineer at hSenid Software International