In our first article, we looked at some of the principles involved in building REST APIs, as well as a first example of their implementation in Python using Flask.
In this example, data was integrated into the code in the form of a list of dictionaries.
We also have an article on how to program and document an API with python, flask, swagger and connection.
In the following example, we’ll look at how to connect an API to an external relational database and allow items to be filtered by various conditions:
1. Relational databases
Relational databases store and retrieve data in the form of tables.
Tables are similar to spreadsheets: they have rows and columns, with the columns indicating what the data corresponds to, such as a title or a date. The rows represent individual data items, which may correspond to users, transactions or any other type of entity.
2. SQLite
The database engine used in the suite is SQLite, a very lightweight database engine available under Python by default.
The standard extension for SQLite files is .db.
The database used is the Chinook database (available via the following link), comprising 11 tables. In the following, we’ll be focusing on the employees table: this contains data on employees of the Chinook company, such as their ID, surname, first name, etc.
3. Connecting an API to the Chinook database
We start by copying the chinook.db database into our api directory.
Our API will query this database to return the desired results to users.
The corresponding code is as follows:
We save it in the api directory under the name api4.py.
To run it, launch a command line window from the api directory and enter the following commands:
$ export FLASK_APP = api4.py
$ export FLASK_ENV = development
$ flask run
(to stop execution, press Ctrl-C).
Among other messages, you get
* Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
Once the program has run, you can submit requests like :
http://127.0.0.1:5000/api/v1/resources/employees/all
http://127.0.0.1:5000/api/v1/resources/employees?LastName=Edwards
http://127.0.0.1:5000/api/v1/resources/employees?LastName=Edwards&City=Calgary
http://127.0.0.1:5000/api/v1/resources/employees?City=Calgary
The employees table in the Chinook database has 8 records, one for each Chinook employee.
The data includes, among other things, the employee’s ID, surname, first name, city of residence, date of birth and date of recruitment.
Our API allows filtering by three fields: EmployeeId, LastName and City.
The new API responds to user requests by extracting information from the database using SQL queries.
It also enables filtering by more than one field.
4. Understanding the new API
The employees table in the Chinook database is made up of eight columns: EmployeeId, LastName, FirstName, Title, ReportsTo, BirthDate, HireDate and Address.
Each row represents a Chinook employee.
Rather than specifying the data in the code, our api_all function extracts it from the Chinook database:
We start by connecting to the database using the sqlite3 library.
An object representing the database connection is bound to the conn variable.
The instruction conn.row_factory = dict_factory tells the object corresponding to the connection to use the dict_factory function, which returns results as dictionaries rather than lists – which converts better to JSON format.
We then create a cursor object (cur = conn.cursor( )), which scans the database to extract the data.
Finally, we execute a SQL query using the cur.execute method to extract all available data ( * ) from the employees table in our database.
At the end of our function, the retrieved data is converted to JSON format: jsonify(all_employees).
The other function returning data, api_filter, uses the same approach to extract specific data from the database.
The purpose of the page_not_found function is to create an error page displayed to the user if he specifies a route that is not supported by the API:
In HTML results, code 200 means “OK” (data transferred), while code 404 means “not found” (no resources available at the specified address).
The page_not_found function returns 404 if something goes wrong.
The api_filter function filters according to three fields: EmployeeId, LastName and City.
It begins by identifying all the query parameters supplied in the URL, using the instruction :
query_parameters = request.args
It then retrieves the parameter values and binds them to variables:
employeeid = query_parameters.get(‘EmployeeId’)
lastname = query_parameters.get(‘LastName’)
city = query_parameters.get(‘City’)
The following code portion is used to build an SQL query which is used to extract the required information from the database.
The simplest SQL queries are of the form :
SELECT <columns> FROM <table> WHERE <column=match> AND <column=match>;
To obtain the data you’re looking for, you need to build both an SQL query of the type described above and a list with the specified filters (values).
We begin by defining the query and the list of filters:
Then, if EmployeeId, LastName and City have been passed as query parameters, they are added to the query and to the filter list:
If the user has not specified any of these query parameters, the 404 error page is returned:
To perfect our SQL query, we delete the last AND and complete the query with the semicolon required by SQL :
Next, we connect to the database, then execute the SQL query built using our list of filters:
Finally, we return the results in JSON format to the user:
return jsonify(results)
5. Using the new API
Our new API enables more sophisticated user queries.
What’s more, as soon as new data is added to the database, it becomes immediately available to projects built using the API.
So, in many cases, it makes sense to start by creating an API-like interface for project data before building a data-driven visualization, application or website.
In the next article, we’ll expand on the techniques presented earlier to see how to program a professional REST API in Python using Flask, OpenAPI (formerly Swagger) and Connexion.
6. References
- Creating Web APIs with Python and Flask, Patrick Smyth : https://programminghistorian.org/en/lessons/creating-apis-with-python-and-flask.
- Flask RESTful documentation : https://flask-restful.readthedocs.io/en/latest/index.html.
- Flask Web Development : Developing Web Applications with Python (2ème édition). M. Grinberg. O’Reilly 2018.
- Architectural Styles and the Design of Network-Based Software Architectures. T. Fielding. Thèse, Université de Californie, 2000.
