In this Python programming tutorial you will learn how to create a REST API using Flask, SQLite 3 (for data), and JSON for data communication.
In the end you will have an API that you can consume from any client that could be an Android application, a browser with JavaScript or even another server language.
I will show you how to create an API that communicates using JSON and saves the data in SQLite 3. The API will be created with Flask, and we will use the Python programming language to handle all the data.
API description
We are going to use the 4 most used HTTP verbs: GET, POST, PUT and DELETE, which will be related to the CRUD of the database.
What we are going to manage will be a database of games, which have a name, price and rate (or rating). We will also have several operations that we are going to expose through the API created with Flask:
- Get all the games
- Create a new game
- Update a game
- Delete a game
- Get a game by ID
First we are going to create the CRUD that is related to the database using Python, and later we are going to expose all these functions with Flask in the API, encoding as JSON.
SQLite3 database
The structure of the database is as seen below. It is a single table, although from this tutorial we can create more tables, relationships, and so on.
Then we see the connection file to the database:
In this SQLite3 connection file with Python we see that the database will be called games.db. In addition, we have two functions: one of them is to obtain the database, and the other (create_tables) is to create the tables within the database only if they do not exist.
Take a good look at these functions, as we are going to import them from other files. Now that we have the database defined, let’s see the CRUD of games with the SQLite3 database.
Game controller: database connection
Before exposing the database in the API, we are going to create a game controller that will take care of all the operations to save, update, delete and get the game data.
All these functions are inside a file called game_controller.py and it looks like this:
In the file we see several functions. The insert_game function receives the game data and inserts it into the database (INSERT); all this using prepared statements to avoid SQL injections in this API that we are creating with Python and Flask.
We also see other methods such as update_game that performs the UPDATE operation to update a game, delete_game that deletes a game (DELETE) from its id, get_by_id that returns a game from its id (using the SELECT operation).
Finally we look at the get_games function that returns all existing games.
Note that all functions use the database and a cursor to perform all operations.
Now that we have the CRUD of the operations with the database, it is time to expose everything in the API with Flask.
Creating the API with Flask and Python
The first thing we do in the API is create the Flask app and import the games controller. We also import a function from the database because we need to create the tables when starting the application:
Now we define the routes with the GET, PUT, POST and DELETE http verbs:
Each path exposes a game controller function that we saw earlier, which in turn interacts with the SQLite3 database. It is important to highlight a few things. For example, when updating and inserting a game we read the JSON of the request with get_json and we access the dictionary.
In the case of deleting or obtaining by ID we read the variable id from the path as <variable> and receiving it in the method.
Also note that this API with Python communicates through JSON, so all responses are made according to what the jsonify function returns.
Finally we create the Flask app to start the server and listen to requests:
Optional: add CORS
If you are going to consume this API from a domain other than where the API is listening, you need to enable CORS. Just add the following code snippet in the API (in the repository you will find the code already added, which you can remove if you want):
Putting it all together
The full code of this API created with Flask and SQLite3 is like this:
If you want to see the code for the other files and the full repository, visit my GitHub. There you can download and test all the open source code.
Remember that to start the server and the API you must execute:
python main.py
Or failing that:
python3 main.py
Testing the API
After cloning and running the repository you can run the tests using Postman or any language of your choice. Later I will bring examples of consumption with JavaScript Frameworks or with pure JavaScript.
You can also test the API online, at the following link (check the defined routes in case a 404 or 405 error appears): https://apirestflaskpythonsqlite3.parzibyte.repl.co/
For example, to see all the games, it’s: https://apirestflaskpythonsqlite3.parzibyte.repl.co/games
Meanwhile I leave you a capture testing the API:
If you like Python, I invite you to read more content on that topic on my blog. Or learn more about Flask.
