Welcome to Part 2 of the Flask Series.
If you have been following along, you'd have a simple API in Flask which returns your favorite games. If you haven't, simply clone this GitHub repository and install flask and dotenv packages.
What we did the last time, was displaying a list of our favorite games from a list in a separate games file, which isn't really the ideal thing to do. In this thread, let's connect our application to a PostgreSQL database.
Prerequisites
Before we begin with Python Code, I'd like to take you through Supabase.
What is Supabase?
Supabase is an Open Source Backend as a Service. It provides a Postgres Database on the fly, it has a very easy-to-use UI and it provides its PostgREST API and client-libraries on various languages (thanks to the Community support) for easy database operations.
Installing pgadmin and setting up a database in that and then connecting to it, is a PAIN. Let's just do it the easy way. Click here, login with GitHub and create a new Supabase project.
Supabase Setup
Choose your default organization (already created for you), enter a name and a secure password for your project. Then select the region which is closest to you for less delay.
And here we are. In less than 5 minutes, we have our Postgres database set up. Now, click on Table Editor and then Click on Create New Table.
Create a new 'games' table. You can add columns/attributes to this table using the Add Column button. Finally, save this.
Great! Now our table is ready. All we need to do here now, is to add some rows to this table. To do this, simply click on Insert Row.
You just have to enter the name of the game and the no. of hours you've played it. I'll just add 4-5 of these.
Here's what the table looks like after a few entries and now you know I game a lot XD
Setting up environment variables
Add environment variables to your .flaskenv file
You can find these in Supabase settings => API
Using the supabase_py module
The python supabase-py module (and many other libraries for different languages) was created (and is still in development) by the awesome Supabase Community. You can find the docs for this module here.
Install this module in your virtual env using pip -
python -m pip install supabase_py
Let's go ahead and connect our app to Supabase! Create a new file store.py and import supabase. Then, create a supabase Client variable which we can use for our database operations.
import os
from supabase import create_client, Client
url: str = os.environ.get("SUPABASE_URL")
key: str = os.environ.get("SUPABASE_ANON_KEY")
supabase: Client = create_client(url, key)
Fetching Games Data from the Database
Next up, in the same file, simply create a new function which returns data of all games from our database.
# Function to Fetch All Games
def find_all_games():
data = supabase.table("games").select("*").execute()
# Equivalent for SQL Query "SELECT * FROM games;"
return data['data']
games = find_all_games()
You can go ahead and print this on the console to see what it looks like -
Returning data from the API
In app.py, import the games variable from the store instead of our hardcoded games file (you can actually go ahead and delete that, we aren't using it anymore).
#from games import games ## Delete this line
from store import games
That's pretty much it. Now run the app using -
flask run
If you now go to the route, you'll see a list of games show up, exactly what we added in our database!
/games
Our API has dynamic data coming from a database!
Function to Add a New game to the Database
In store.py, let's create a new function to add a new game to the database. We just take in title and hours as the parameters and insert it into the database
# Function to add a new game
def add_game_to_DB(title, hours) -> dict:
game = {
"title": title,
"hours": hours
}
data = supabase.table("games").insert(game).execute()
# Equivalent to the SQL Insert
return data['data']
A new route for Adding a New Game
Now here's where things get a little interesting. To send data over the API, we need to send a POST Request to the API. To do that, we need to create a new endpoint which accepts POST requests and inserts this data into the database.
Before we create a new route, let's just import request and Response to deal with the request data that we pass in. We'll also import the new function we just created.
from flask import request, Response
from store import add_game_to_DB
We will now create a route which takes a POST request, grabs the data from the request body and adds the data to the database.
@app.route('/games/add', methods=['POST'])
def add_game():
data = request.get_json()
try:
title = data['title']
hours = data['hours']
if title and hours:
data = add_game_to_DB(title, int(hours))
return jsonify(data), 201
except:
return Response('''{"message": "Bad Request"}''', status=400, mimetype='application/json')
You can identify a few changes here. We add a methods parameter to the route method, specifying that the route only responds to POST requests.
We are also doing some data validation here, hence the try-except blocks. So if any of the values for hours or title are missing, we return a 400-Bad Request in the response.
If everything is good, we call the add_game_to_DB function and send it the game details. We return the data we get as our response.
Testing the add_game route
To put this to test, open up your REST Client. I'm using Postman in this case.
Create a New Request and add the game details like so -
Click on Send and you should see a response like this -
We can go back to Supabase and check if the data was inserted correctly.
There you go, we now have a fully functional API where we get dynamic data from our database and also add to it. I hope you had a good time working with Flask
I also hope you enjoyed working with Supabase. Trust me there's a lot more you can explore :)
That's it for this thread folks, next up, we will deploy our application to Heroku. I'm sure that'll be exciting! See you then, thanks for reading!