Python REST APIs With Flask, Connexion, and SQLAlchemy

Suman Das
11 min readJul 9, 2021
Rest API with Flask, Connexion, and SQLAlchemy

Each and every web application needs to make HTTP calls to the server to procure data to populate the dynamic parts of the application.

The aim of this tutorial is to work with Flask extensions that help us create a production environment ready Python application and to integrate with Swagger UI without a hitch. We will learn to build Rest APIs using Python 3 and Flask extensions such as Connexion, Flask-Marshmallow, and Flask-SQLAlchemy and share the API using Swagger UI.


The OpenAPI Specification (OAS) defines a standard, language-agnostic interface to RESTful APIs which allows both humans and computers to discover and understand the capabilities of the service without access to source code, documentation, or through network traffic inspection. When properly defined, a consumer can understand and interact with the remote service with a minimal amount of implementation logic.


Swagger is a set of open-source tools built around the OpenAPI Specification that can help to design, build, document, and consume REST APIs. The major Swagger tools include:

  • Swagger Editor — browser-based editor where you can write OpenAPI specs
  • Swagger UI — renders OpenAPI specs as interactive API documentation
  • Swagger Codegen — generates server stubs and client libraries from an OpenAPI spec


Connexion is a framework that automagically handles HTTP requests based on OpenAPI Specification (formerly known as Swagger Spec) of our API described in YAML format. Connexion allows us to write an OpenAPI specification, then maps the endpoints to our Python functions; this makes it unique, as many tools generate the specification based on our Python code. We can describe the REST API in as much detail as we want; then Connexion guarantees that it will work as we specified.

Creating the Item REST API

We are going to create a REST API providing access to a collection of items with CRUD access to an individual item within that collection. Here’s the API design for the item collection:

API Design for Item Resource

Following are the steps required to create the sample Flask-based API for an Item management application:

  1. Setup and Installation
  2. Integrate Flask-Marshmallow
  3. Integrate Flask-SQLAlchemy
  4. Create Models
  5. Create Schemas
  6. OpenAPI Configuration File
  7. Handler for Item Endpoint
  8. Application Entry Point
  9. Swagger UI
  10. Conclusion


We require Python 3 with Pipenv and Git installed. Pipenv is a package and a virtual environment manager which uses PIP under the hood. It provides more advanced features like version locking and dependency isolation between projects.

1. Setup and Installation

Once the prerequisites are in place we can begin creating the application.

a) Create a Sample Item Management Flask Application
To begin with our application, create a folder called python-sample-flask-application in any directory on the disk for our project.

$ cd /path/to/my/workspace/
$ mkdir python-sample-flask-application
$ cd python-sample-flask-application

Navigate to the project folder.

b) Activate Virtual Environment

Once we are inside the project folder, execute the following commands to activate the VirtualEnv.

pipenv shell --python 3.7

The virtual environment will now be activated, which will provide the required project isolation and version locking.

c) Install Dependencies

Next, install all the required dependencies using Pipenv as shown.

pipenv install flask==1.1.2
pipenv install flask-marshmallow==0.14.0
pipenv install flask-sqlalchemy==2.5.0
pipenv install marshmallow-sqlalchemy==0.24.1
pipenv install "connexion[swagger-ui]"

After we execute the above commands, the required dependencies will be installed.

We can see now two files, which have been created inside our project folder, namely, Pipfile and Pipfile.lock.

  • Pipfile contains all the names of the dependencies we just installed.
  • Pipfile.lock is intended to specify, based on the dependencies present in Pipfile, which specific version of those should be used, avoiding the risks of automatically upgrading dependencies that depend upon each other and breaking your project dependency tree.

Note: Here, we have installed all the dependencies with specific versions, which worked on my machine while writing this tutorial. If we don’t specify any version then the latest version of that dependency will be installed, which might not be compatible with other dependencies.

Now, we are all set to write some code for our application.

2. Integrate Flask-Marshmallow

To integrate Flask-Marshmallow with our application, create a file with the following content.

from flask_marshmallow import Marshmallow
ma = Marshmallow()

Here, we have imported Marshmallow from flask_marshmallow. Later on, we will use this Marshmallow instance ma to integrate with the flask application using the command ma.init_app(app).

3. Integrate Flask-SQLAlchemy

For Flask-SqlAlchemy integration, create a file called having the following content.

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

Here, we have imported SQLAlchemy from flask_sqlalchemy. We will also use this SQLAlchemy instance db to integrate with the flask application using the command db.init_app(app).

4. Create Database Models

Next, we will create database models for our data storage and organization. For our application, we need to create one database model Item and its repository ItemRepo. We will be using db, the instance of the SQLAlchemy from Flask-SQLAlchemy, which we created earlier (Step 3) to create our models.

The db instance contains all the functions and helpers from both sqlalchemyand sqlalchemy.orm. It provides a class called Model that is a declarative base, which can be used to declare our models.

Create the models package and add two files named and We can add all the database entities in and it’s corresponding repository in

The file should contain the following content:

The above code in does the following:

  • We started off by creating the Item Model class in line 3.
  • In line 4, we declared the table name items where this model will be mapped to.
  • From line 6 to 8, we defined the table columns along with their data types.
  • From line 11to 15, we added some helper methods to print the object at runtime.


The file should contain the following content:

The above code in does the following:

  • We started off by creating the ItemRepo class in line 6.
  • From line 8 to 25, we defined some helper methods, which we can use to perform CRUD operations on Item entity.

5. Create Schemas

Create the Marshmallow Schemas from our models defined earlier using SQLAlchemyAutoSchema. We will be using ma, the instance of the Marshmallow from Flask-Marshmallow, which we created earlier (Step 2) to create our schemas.

Create the schemas package and the file named

We need to create the schema for the Item Model class, which we defined earlier (Step 4). Once we have defined the Schema, we can use it to dump and load the ORM objects.

6. OpenAPI Configuration File

Now, let us define the OpenAPI specification for our Item Resource. API specifications can be written in YAML or JSON format. Although, YAML and JSON have similar capabilities, YAML is a superset of JSON and tends to be more readable than JSON.

Here, we will use the YAML format to write the specification.

The above swagger.yml is a YAML file containing all of the information necessary to perform input parameter validation, output response data validation, URL endpoint definition, and Swagger UI.

The specification defines several endpoints for our API. Here, we have defined one endpoint for each of the CRUD (GET, POST, PUT and DELETE) operations.

The above code in swagger.yml does the following:

  • We started off defining the version of the OpenAPI Specification in line 1. The OpenAPI version defines the overall structure of an API definition – what we can document and how we can document it.
  • Line 2 to 5 contains the info section of the API specification: title, description (optional), and version .
    title is the API name.
    description is extended information about the API.
    version is an arbitrary string that specifies the version of the API.
  • Line 6 to 8 contains the servers section, which specifies the API server and base URL. All API paths are relative to this server URL.
  • Line 9 to 106 contains the paths section where we defined individual endpoints (paths) of our API, and the HTTP methods (operations) supported by these endpoints.
    An operation definition includes parameters, request body (if any), possible response status codes (such as 200 OK or 404 Not Found) and response contents. For more information, see Paths and Operations. Operations can have parameters passed via URL path (/item/{id}), query string (/item?name=chair), headers (X-CustomHeader: Value) or cookies (Cookie: debug=0). We can define the parameter data types, format, whether they are required or optional, and other details. For each operation, we can define possible status codes, such as 200 OK or 404 Not Found, and the response body schema. Schemas can be defined inline or referenced via $ref.

Note: The operation_id will be the Python function name in our code that will respond to the API call and tags: defines a grouping for the UI interface.

  • Line 108 to 119 contains global components/schemas section, which lets us define common data structures used in our API. They can be referenced via $ref whenever a schema is required – in parameters, request bodies, and response bodies.

7. Handler for Item Endpoint

In the swagger.yml file, we configured Connexion with the operationId value to call the various functions within item module. This means module must exist and contain all the functions used in swagger.yml. Following is the module that we need to create:

The above code in does the following:

It contains all the functions with arguments specified in swagger.yml file. For example, def get(id) function has one argument ID. The corresponding configuration used in swagger.yml is as shown below:

operationId: item.get
- Item
summary: Return an Item with given ID
description: Return an Item with given ID
- name: id
in: path
description: Item ID
required: true
type: integer
format: int64
"200": # status code
description: Return an Item with given ID
$ref: "#/components/schemas/Item"

The above def get(id) function is called when an HTTP request to GET /item/{id} is received by the server. The function returns the JSON string representation of Item object as specified in the configuration file.

All the functions in use ItemRepo created in step 4.2 to perform all database related operations on Item Data Model. It also uses ItemSchema created in Step 5 to load or dump the Item schema.

8. Application Entry Point

Now, let us create our application entry point. In the root directory of the project, create a file named with the following content:

The above code within does the following:

8.1 Add Connexion to the server

There are two parts to adding a REST API URL endpoint to your application with Connexion. We will need to add Connexion to the server and create a configuration file it will use. We have already added the configuration file swagger.yml to our application. Let us now add the Connexion to the server.

  • The import connexion statement at line 1adds the module to our program.
  • Then we created an application instance using Connexion at line 6 rather than using Flask. This internally, creates a Flask application, but it now has additional functionality added to it. It includes a parameter specification_dir. This informs Connexion what directory to look in for its configuration file, in our case it’s root directory.
  • Right after this, at line 8 we tell the app instance to read the file configuration file swagger.yml from the specification directory and configure the system to provide the Connexion functionality.

8.2 SQLAlchemy

  • From line 12to 14 we have added some configuration related to SQLAlchemy. Here, we are using SQLite DB. These configurations are required to link SQLite DB with SQLAlchemy. data.db is the name of the DB File.

8.3 Database

  • From line 17to 19 we have used the db instance (from file to create the DB file along with the tables before the user accesses the server.

8.4 SQLAlchemy and Marshmallow

  • In line 23and line 24integrate SQLAlchemy and Marshmallow.

8.5 Start the Application

  • Finally, in line 25we configure the application to run at port=5000.

Our application is ready now. We can start the application by executing the below command:


We are done with all the coding part and it’s testing time. Once the application is started successfully and we navigate to http://localhost:5000/ui/, the system will bring up a page that looks something like this:


9. Swagger UI

We can now test our application to ensure that everything is working fine. We can open the URL http://localhost:5000/ui/ in our browser.

Swagger interface and shows the list of URL endpoints supported by our application. This is built automatically by Connexion when it parses the swagger.yml file.

If we click on the /item endpoint in the Swagger UI, the interface will expand to show a great deal more information about our API and should look something like this:

Get all the Items

This displays the structure of the expected response, the content-type of that response, and the description text we entered about the endpoint in the swagger.yml file.

We can even try the endpoint out by clicking the Try It Out! button . That will further expand the interface to look something like this:

Get All Response

Using this Swagger UI the API users can explore and experiment with the API without having to write any code to do so. Building an API this way is very useful. Not only is the Swagger UI useful as a way to experiment with the API and read the provided documentation, but it’s also dynamic. Any time we update the configuration file, the Swagger UI changes as well. This UI allows everyone to see all of the documentation we have included in the swagger.yml file and interact with all of the URL endpoints making up the CRUD functionality of the item interface.

Swagger UI also helps the developers with API testing, either in case of any issues reported or while adding new API’s to the existing application.

If you would like to refer to the full code, do check:

10. Conclusion

In this tutorial, we saw how easy it is to create a comprehensive REST API using Connexion and Flask. With the help of Connexion module and some additional configuration work, a useful documentation and interactive system can be put in place, which will make the API consumers life easy and enjoyable.

References & Useful Readings