Building and Documenting Python REST APIs With Flask and Connexion – Part 2

In Part 1 of this series, you used Flask and Connexion to create a REST API providing CRUD operations to a simple in-memory structure called PEOPLE. That worked to demonstrate how the Connexion module helps you build a nice REST API along with interactive documentation.

As some noted in the comments for Part 1, the PEOPLE structure is re-initialized every time the application is restarted. In this article, you’ll learn how to store the PEOPLE structure, and the actions the API provides, to a database using SQLAlchemy and Marshmallow.

SQLAlchemy provides an Object Relational Model (ORM), which stores Python objects to a database representation of the object’s data. That can help you continue to think in a Pythonic way and not be concerned with how the object data will be represented in a database.

Marshmallow provides functionality to serialize and deserialize Python objects as they flow out of and into our JSON-based REST API. Marshmallow converts Python class instances to objects that can be converted to JSON.

You can find the Python code for this article here.

Who This Article Is For

If you enjoyed Part 1 of this series, this article expands your tool belt even further. You’ll be using SQLAlchemy to access a database in a more Pythonic way than straight SQL. You’ll also use Marshmallow to serialize and deserialize the data managed by the REST API. To do this, you’ll be making use of basic Object Oriented Programming features available in Python.

You’ll also be using SQLAlchemy to create a database as well as interact with it. This is necessary to get the REST API up and running with the PEOPLE data used in Part 1.

The web application presented in Part 1 will have its HTML and JavaScript files modified in minor ways in order to support the changes as well. You can review the final version of the code from Part 1 here.

Additional Dependencies

Before you get started building this new functionality, you’ll need to update the virtualenv you created in order to run the Part 1 code, or create a new one for this project. The easiest way to do that after you have activated your virtualenv is to run this command:

$ pip install Flask-SQLAlchemy flask-marshmallow marshmallow-sqlalchemy marshmallow

This adds more functionality to your virtualenv:

1. Flask-SQLAlchemy adds SQLAlchemy, along with some tie-ins to Flask, allowing programs to access databases.

2. flask-marshmallow adds the Flask parts of Marshmallow, which lets programs convert Python objects to and from serializable structures.

3. marshmallow-sqlalchemy adds some Marshmallow hooks into SQLAlchemy to allow programs to serialize and deserialize Python objects generated by SQLAlchemy.

4. marshmallow adds the bulk of the Marshmallow functionality.

People Data

As mentioned above, the PEOPLE data structure in the previous article is an in-memory Python dictionary. In that dictionary, you used the person’s last name as the lookup key. The data structure looked like this in the code:

# Data to serve with our API
PEOPLE = {
    "Farrell": {
        "fname": "Doug",
        "lname": "Farrell",
        "timestamp": get_timestamp()
    },
    "Brockman": {
        "fname": "Kent",
        "lname": "Brockman",
        "timestamp": get_timestamp()
    },
    "Easter": {
        "fname": "Bunny",
        "lname": "Easter",
        "timestamp": get_timestamp()
    }
}

The modifications you’ll make to the program will move all the data to a database table. This means the data will be saved to your disk and will exist between runs of the server.py program.

Because the last name was the dictionary key, the code restricted changing a person’s last name: only the first name could be changed. In addition, moving to a database will allow you to change the last name as it will no longer be used as the lookup key for a person.

Conceptually, a database table can be thought of as a two-dimensional array where the rows are records, and the columns are fields in those records.

Database tables usually have an auto-incrementing integer value as the lookup key to rows. This is called the primary key. Each record in the table will have a primary key whose value is unique across the entire table. Having a primary key independent of the data stored in the table frees you to modify any other field in the row.

You’re going to follow a database convention of naming the table as singular, so the table will be called person. Translating our PEOPLE structure above into a database table named person gives you this:

Each column in the table has a field name as follows:

• person_id: primary key field for each person
• lname: last name of the person
• fname: first name of the person
• timestamp: timestamp associated with insert/update actions

Database Interaction

You’re going to use SQLite as the database engine to store the PEOPLE data. SQLite is the mostly widely distributed database in the world, and it comes with Python for free. It’s fast, performs all its work using files, and is suitable for a great many projects. It’s a complete RDBMS (Relational Database Management System) that includes SQL, the language of many database systems.

For the moment, imagine the person table already exists in a SQLite database. If you’ve had any experience with RDBMS, you’re probably aware of SQL, the Structured Query Language most RDBMSes use to interact with the database.

Unlike programming languages like Python, SQL doesn’t define how to get the data: it describes what data is desired, leaving the how up to the database engine.

A SQL query getting all of the data in our person table, sorted by last name, would look this this:

SELECT * FROM person ORDER BY 'lname';

This query tells the database engine to get all the fields from the person table and sort them in the default, ascending order using the lname field.

If you were to run this query against a SQLite database containing the person table, the results would be a set of records containing all the rows in the table, with each row containing the data from all the fields making up a row. Below is an example using the SQLite command line tool running the above query against the person database table:

sqlite> SELECT * FROM person ORDER BY lname;
2|Brockman|Kent|2018-08-08 21:16:01.888444
3|Easter|Bunny|2018-08-08 21:16:01.889060
1|Farrell|Doug|2018-08-08 21:16:01.886834

The output above is a list of all the rows in the person database table with pipe characters (‘|’) separating the fields in the row, which is done for display purposes by SQLite.

Python is completely capable of interfacing with many database engines and executing the SQL query above. The results would most likely be a list of tuples. The outer list contains all the records in the person table. Each individual inner tuple would contain all the data representing each field defined for a table row.

Getting data this way isn’t very Pythonic. The list of records is okay, but each individual record is just a tuple of data. It’s up to the program to know the index of each field in order to retrieve a particular field. The following Python code uses SQLite to demonstrate how to run the above query and display the data:

 1 import sqlite3
 2 
 3 conn = sqlite3.connect('people.db')
 4 cur = conn.cursor()
 5 cur.execute('SELECT * FROM person ORDER BY lname')
 6 people = cur.fetchall()
 7 for person in people:
 8     print(f'{person[2]} {person[1]}')

The program above does the following:

• Line 1 imports the sqlite3 module.

• Line 3 creates a connection to the database file.

• Line 4 creates a cursor from the connection.

• Line 5 uses the cursor to execute a SQL query expressed as a string.

• Line 6 gets all the records returned by the SQL query and assigns them to the people variable.

• Line 7 & 8 iterate over the people list variable and print out the first and last name of each person.

The people variable from Line 6 above would look like this in Python:

people = [
    (2, 'Brockman', 'Kent', '2018-08-08 21:16:01.888444'), 
    (3, 'Easter', 'Bunny', '2018-08-08 21:16:01.889060'), 
    (1, 'Farrell', 'Doug', '2018-08-08 21:16:01.886834')
]

The output of the program above looks like this:

Kent Brockman
Bunny Easter
Doug Farrell

In the above program, you have to know that a person’s first name is at index 2, and a person’s last name is at index 1. Worse, the internal structure of person must also be known whenever you pass the iteration variable person as a parameter to a function or method.

It would be much better if what you got back for person was a Python object, where each of the fields is an attribute of the object. This is one of the things SQLAlchemy does.

Little Bobby Tables

In the above program, the SQL statement is a simple string passed directly to the database to execute. In this case, that’s not a problem because the SQL is a string literal completely under the control of the program. However, the use case for your REST API will take user input from the web application and use it to create SQL queries. This can open your application to attack.

You’ll recall from Part 1 that the REST API to get a single person from the PEOPLE data looked like this:

GET /api/people/{lname}

This means your API is expecting a variable, lname, in the URL endpoint path, which it uses to find a single person. Modifying the Python SQLite code from above to do this would look something like this:

 1 lname = 'Farrell'
 2 cur.execute('SELECT * FROM person WHERE lname = '{}''.format(lname))

The above code snippet does the following:

• Line 1 sets the lname variable to 'Farrell'. This would come from the REST API URL endpoint path.

• Line 2 uses Python string formatting to create a SQL string and execute it.

To keep things simple, the above code sets the lname variable to a constant, but really it would come from the API URL endpoint path and could be anything supplied by the user. The SQL generated by the string formatting looks like this:

SELECT * FROM person WHERE lname = 'Farrell'

When this SQL is executed by the database, it searches the person table for a record where the last name is equal to 'Farrell'. This is what’s intended, but any program that accepts user input is also open to malicious users. In the program above, where the lname variable is set by user-supplied input, this opens your program to what’s called a SQL Injection Attack. This is what’s affectionately known as Little Bobby Tables:

For example, imagine a malicious user called your REST API in this way:

GET /api/people/Farrell');DROP TABLE person;

The REST API request above sets the lname variable to 'Farrell');DROP TABLE person;', which in the code above would generate this SQL statement:

SELECT * FROM person WHERE lname = 'Farrell');DROP TABLE person;

The above SQL statement is valid, and when executed by the database it will find one record where lname matches 'Farrell'. Then, it will find the SQL statement delimiter character ; and will go right ahead and drop the entire table. This would essentially wreck your application.

You can protect your program by sanitizing all data you get from users of your application. Sanitizing data in this context means having your program examine the user-supplied data and making sure it doesn’t contain anything dangerous to the program. This can be tricky to do right and would have to be done everywhere user data interacts with the database.

There’s another way that’s much easier: use SQLAlchemy. It will sanitize user data for you before creating SQL statements. It’s another big advantage and reason to use SQLAlchemy when working with databases.

Modeling Data With SQLAlchemy

SQLAlchemy is a big project and provides a lot of functionality to work with databases using Python. One of the things it provides is an ORM, or Object Relational Mapper, and this is what you’re going to use to create and work with the person database table. This allows you to map a row of fields from the database table to a Python object.

Object Oriented Programming allows you to connect data together with behavior, the functions that operate on that data. By creating SQLAlchemy classes, you’re able to connect the fields from the database table rows to behavior, allowing you to interact with the data. Here’s the SQLAlchemy class definition for the data in the person database table:

class Person(db.Model):
    __tablename__ = 'person'
    person_id = db.Column(db.Integer, 
                          primary_key=True)
    lname = db.Column(db.String)
    fname = db.Column(db.String)
    timestamp = db.Column(db.DateTime, 
                          default=datetime.utcnow, 
                          onupdate=datetime.utcnow)

The class Person inherits from db.Model, which you’ll get to when you start building the program code. For now, it means you’re inheriting from a base class called Model, providing attributes and functionality common to all classes derived from it.

The rest of the definitions are class-level attributes defined as follows:

• __tablename__ = 'person' connects the class definition to the person database table.

• person_id = db.Column(db.Integer, primary_key=True) creates a database column containing an integer acting as the primary key for the table. This also tells the database that person_id will be an autoincrementing Integer value.

• lname = db.Column(db.String) creates the last name field, a database column containing a string value.

• fname = db.Column(db.String) creates the first name field, a database column containing a string value.

• timestamp = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) creates a timestamp field, a database column containing a date/time value. The default=datetime.utcnow parameter defaults the timestamp value to the current utcnow value when a record is created. The onupdate=datetime.utcnow parameter updates the timestamp with the current utcnow value when the record is updated.

Where are you heading with this Person class definition? The end goal is to be able to run a query using SQLAlchemy and get back a list of instances of the Person class. As an example, let’s look at the previous SQL statement:

SELECT * FROM people ORDER BY lname;

Show the same small example program from above, but now using SQLAlchemy:

 1 from models import Person
 2 
 3 people = Person.query.order_by(Person.lname).all()
 4 for person in people:
 5     print(f'{person.fname} {person.lname}')

Ignoring line 1 for the moment, what you want is all the person records sorted in ascending order by the lname field. What you get back from the SQLAlchemy statements Person.query.order_by(Person.lname).all() is a list of Person objects for all records in the person database table in that order. In the above program, the people variable contains the list of Person objects.

The program iterates over the people variable, taking each person in turn and printing out the first and last name of the person from the database. Notice the program doesn’t have to use indexes to get the fname or lname values: it uses the attributes defined on the Person object.

Using SQLAlchemy allows you to think in terms of objects with behavior rather than raw SQL. This becomes even more beneficial when your database tables become larger and the interactions more complex.

Serializing/Deserializing Modeled Data

Working with SQLAlchemy modeled data inside your programs is very convenient. It is especially convenient in programs that manipulate the data, perhaps making calculations or using it to create presentations on screen. Your application is a REST API essentially providing CRUD operations on the data, and as such it doesn’t perform much data manipulation.

The REST API works with JSON data, and here you can run into an issue with the SQLAlchemy model. Because the data returned by SQLAlchemy are Python class instances, Connexion can’t serialize these class instances to JSON formatted data. Remember from Part 1 that Connexion is the tool you used to design and configure the REST API using a YAML file, and connect Python methods to it.

In this context, serializing means converting Python objects, which can contain other Python objects and complex data types, into simpler data structures that can be parsed into JSON datatypes, which are listed here:

• string: a string type
• number: numbers supported by Python (integers, floats, longs)
• object: a JSON object, which is roughly equivalent to a Python dictionary
• array: roughly equivalent to a Python List
• boolean: represented in JSON as true or false, but in Python as True or False
• null: essentially a None in Python

As an example, your Person class contains a timestamp, which is a Python DateTime. There is no date/time definition in JSON, so the timestamp has to be converted to a string in order to exist in a JSON structure.

Your Person class is simple enough so getting the data attributes from it and creating a dictionary manually to return from our REST URL endpoints wouldn’t be very hard. In a more complex application with many larger SQLAlchemy models, this wouldn’t be the case. A better solution is to use a module called Marshmallow to do the work for you.

Marshmallow helps you to create a PersonSchema class, which is like the SQLAlchemy Person class we created. Here however, instead of mapping database tables and field names to the class and its attributes, the PersonSchema class defines how the attributes of a class will be converted into JSON-friendly formats. Here’s the Marshmallow class definition for the data in our person table:

class PersonSchema(ma.ModelSchema):
    class Meta:
        model = Person
        sqla_session = db.session

The class PersonSchema inherits from ma.ModelSchema, which you’ll get to when you start building the program code. For now, this means PersonSchema is inheriting from a Marshmallow base class called ModelSchema, providing attributes and functionality common to all classes derived from it.

The rest of the definition is as follows:

• class Meta defines a class named Meta within your class. The ModelSchema class that the PersonSchema class inherits from looks for this internal Meta class and uses it to find the SQLAlchemy model Person and the db.session. This is how Marshmallow finds attributes in the Person class and the type of those attributes so it knows how to serialize/deserialize them.

• model tells the class what SQLAlchemy model to use to serialize/deserialize data to and from.

• db.session tells the class what database session to use to introspect and determine attribute data types.

Where are you heading with this class definition? You want to be able to serialize an instance of a Person class into JSON data, and to deserialize JSON data and create a Person class instances from it.

Create the Initialized Database

SQLAlchemy handles many of the interactions specific to particular databases and lets you focus on the data models as well as how to use them.

Now that you’re actually going to create a database, as mentioned before, you’ll use SQLite. You’re doing this for a couple of reasons. It comes with Python and doesn’t have to be installed as a separate module. It saves all of the database information in a single file and is therefore easy to set up and use.

Installing a separate database server like MySQL or PostgreSQL would work fine but would require installing those systems and getting them up and running, which is beyond the scope of this article.

Because SQLAlchemy handles the database, in many ways it really doesn’t matter what the underlying database is.

You’re going to create a new utility program called build_database.py to create and initialize the SQLite people.db database file containing your person database table. Along the way, you’ll create two Python modules, config.py and models.py, which will be used by build_database.py and the modified server.py from Part 1.

Here’s where you can find the source code for the modules you’re about to create, which are introduced here:

• config.py gets the necessary modules imported into the program and configured. This includes Flask, Connexion, SQLAlchemy, and Marshmallow. Because it will be used by both build_database.py and server.py, some parts of the configuation will only apply to the server.py application.

• models.py is the module where you’ll create the Person SQLAlchemy and PersonSchema Marshmallow class definitions described above. This module is dependent on config.py for some of the objects created and configured there.

Config Module

The config.py module, as the name implies, is where all of the configuration information is created and initialized. We’re going to use this module for both our build_database.py program file and the soon to be updated server.py file from the Part 1 article. This means we’re going to configure Flask, Connexion, SQLAlchemy, and Marshmallow here.

Even though the build_database.py program doesn’t make use of Flask, Connexion, or Marshmallow, it does use SQLAlchemy to create our connection to the SQLite database. Here is the code for the config.py module:

 1 import os
 2 import connexion
 3 from flask_sqlalchemy import SQLAlchemy
 4 from flask_marshmallow import Marshmallow
 5 
 6 basedir = os.path.abspath(os.path.dirname(__file__))
 7 
 8 # Create the Connexion application instance
 9 connex_app = connexion.App(__name__, specification_dir=basedir)
10 
11 # Get the underlying Flask app instance
12 app = connex_app.app
13 
14 # Configure the SQLAlchemy part of the app instance
15 app.config['SQLALCHEMY_ECHO'] = True
16 app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////' + os.path.join(basedir, 'people.db')
17 app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
18 
19 # Create the SQLAlchemy db instance
20 db = SQLAlchemy(app)
21 
22 # Initialize Marshmallow
23 ma = Marshmallow(app)

Here’s what the above code is doing:

• Lines 2 – 4 import Connexion as you did in the server.py program from Part 1. It also imports SQLAlchemy from the flask_sqlalchemy module. This gives your program database access. Lastly, it imports Marshmallow from the flask_marshamllow module.

• Line 6 creates the variable basedir pointing to the directory the program is running in.

• Line 9 uses the basedir variable to create the Connexion app instance and give it the path to the swagger.yml file.

• Line 12 creates a variable app, which is the Flask instance initialized by Connexion.

• Lines 15 uses the app variable to configure values used by SQLAlchemy. First it sets SQLALCHEMY_ECHO to True. This causes SQLAlchemy to echo SQL statements it executes to the console. This is very useful to debug problems when building database programs. Set this to False for production environments.

• Line 16 sets SQLALCHEMY_DATABASE_URI to sqlite:////' + os.path.join(basedir, 'people.db'). This tells SQLAlchemy to use SQLite as the database, and a file named people.db in the current directory as the database file. Different database engines, like MySQL and PostgreSQL, will have different SQLALCHEMY_DATABASE_URI strings to configure them.

• Line 17 sets SQLALCHEMY_TRACK_MODIFICATIONS to False, turning off the SQLAlchemy event system, which is on by default. The event system generates events useful in event-driven programs but adds significant overhead. Since you’re not creating an event-driven program, turn this feature off.

• Line 19 creates the db variable by calling SQLAlchemy(app). This initializes SQLAlchemy by passing the app configuration information just set. The db variable is what’s imported into the build_database.py program to give it access to SQLAlchemy and the database. It will serve the same purpose in the server.py program and people.py module.

• Line 23 creates the ma variable by calling Marshmallow(app). This initializes Marshmallow and allows it to introspect the SQLAlchemy components attached to the app. This is why Marshmallow is initialized after SQLAlchemy.

Models Module

The models.py module is created to provide the Person and PersonSchema classes exactly as described in the sections above about modeling and serializing the data. Here is the code for that module:

 1 from datetime import datetime
 2 from config import db, ma
 3 
 4 class Person(db.Model):
 5     __tablename__ = 'person'
 6     person_id = db.Column(db.Integer, primary_key=True)
 7     lname = db.Column(db.String(32), index=True)
 8     fname = db.Column(db.String(32))
 9     timestamp = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
10 
11 class PersonSchema(ma.ModelSchema):
12     class Meta:
13         model = Person
14         sqla_session = db.session    

Here’s what the above code is doing:

• Line 1 imports the datatime object from the datetime module that comes with Python. This gives you a way to create a timestamp in the Person class.

• Line 2 imports the db and ma instance variables defined in the config.py module. This gives the module access to SQLAlchemy attributes and methods attached to the db variable, and the Marshmallow attributes and methods attached to the ma variable.

• Lines 4 – 9 define the Person class as discussed in the data modeling section above, but now you know where the db.Model that the class inherits from originates. This gives the Person class SQLAlchemy features, like a connection to the database and access to its tables.

• Lines 11 – 14 define the PersonSchema class as was discussed in the data serialzation section above. This class inherits from ma.ModelSchema and gives the PersonSchema class Marshmallow features, like introspecting the Person class to help serialize/deserialize instances of that class.

Creating the Database

You’ve seen how database tables can be mapped to SQLAlchemy classes. Now use what you’ve learned to create the database and populate it with data. You’re going to build a small utility program to create and build the database with the People data. Here’s the build_database.py program:

 1 import os
 2 from config import db
 3 from models import Person
 4 
 5 # Data to initialize database with
 6 PEOPLE = [
 7     {'fname': 'Doug', 'lname': 'Farrell'},
 8     {'fname': 'Kent', 'lname': 'Brockman'},
 9     {'fname': 'Bunny','lname': 'Easter'}
10 ]
11 
12 # Delete database file if it exists currently
13 if os.path.exists('people.db'):
14     os.remove('people.db')
15 
16 # Create the database
17 db.create_all()
18 
19 # Iterate over the PEOPLE structure and populate the database
20 for person in PEOPLE:
21     p = Person(lname=person['lname'], fname=person['fname'])
22     db.session.add(p)
23 
24 db.session.commit()

Here’s what the above code is doing:

• Line 2 imports the db instance from the config.py module.

• Line 3 imports the Person class definition from the models.py module.

• Lines 6 – 10 create the PEOPLE data structure, which is a list of dictionaries containing your data. The structure has been condensed to save presentation space.

• Lines 13 & 14 perform some simple housekeeping to delete the people.db file, if it exists. This file is where the SQLite database is maintained. If you ever have to re-initialize the database to get a clean start, this makes sure you’re starting from scratch when you build the database.

• Line 17 creates the database with the db.create_all() call. This creates the database by using the db instance imported from the config module. The db instance is our connection to the database.

• Lines 20 – 22 iterate over the PEOPLE list and use the dictionaries within to instantiate a Person class. After it is instantiated, you call the db.session.add(p) function. This uses the database connection instance db to access the session object. The session is what manages the database actions, which are recorded in the session. In this case, you are executing the add(p) method to add the new Person instance to the session object.

• Line 24 calls db.session.commit() to actually save all the person objects created to the database.

In SQLAlchemy, the session is an important object. It acts as the conduit between the database and the SQLAclchemy Python objects created in a program. The session helps maintain the consistency between data in the program and the same data as it exists in the database. It saves all database actions and will update the underlying database accordingly by both explicit and implicit actions taken by the program.

Now you’re ready to run the build_database.py program to create and initialize the new database. You do so with the following command, with your Python virtual environment active:

python build_database.py

When the program runs, it will print SQLAlchemy log messages to the console. These are the result of setting SQLALCHEMY_ECHO to True in the config.py file. Much of what’s being logged by SQLAlchemy is the SQL commands it’s generating to create and build the people.db SQLite database file. Here’s an example of what’s printed out when the program is run:

2018-09-11 22:20:29,951 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-09-11 22:20:29,951 INFO sqlalchemy.engine.base.Engine ()
2018-09-11 22:20:29,952 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-09-11 22:20:29,952 INFO sqlalchemy.engine.base.Engine ()
2018-09-11 22:20:29,956 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("person")
2018-09-11 22:20:29,956 INFO sqlalchemy.engine.base.Engine ()
2018-09-11 22:20:29,959 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE person (
    person_id INTEGER NOT NULL, 
    lname VARCHAR, 
    fname VARCHAR, 
    timestamp DATETIME, 
    PRIMARY KEY (person_id)
)
2018-09-11 22:20:29,959 INFO sqlalchemy.engine.base.Engine ()
2018-09-11 22:20:29,975 INFO sqlalchemy.engine.base.Engine COMMIT
2018-09-11 22:20:29,980 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-09-11 22:20:29,983 INFO sqlalchemy.engine.base.Engine INSERT INTO person (lname, fname, timestamp) VALUES (?, ?, ?)
2018-09-11 22:20:29,983 INFO sqlalchemy.engine.base.Engine ('Farrell', 'Doug', '2018-09-12 02:20:29.983143')
2018-09-11 22:20:29,984 INFO sqlalchemy.engine.base.Engine INSERT INTO person (lname, fname, timestamp) VALUES (?, ?, ?)
2018-09-11 22:20:29,985 INFO sqlalchemy.engine.base.Engine ('Brockman', 'Kent', '2018-09-12 02:20:29.984821')
2018-09-11 22:20:29,985 INFO sqlalchemy.engine.base.Engine INSERT INTO person (lname, fname, timestamp) VALUES (?, ?, ?)
2018-09-11 22:20:29,985 INFO sqlalchemy.engine.base.Engine ('Easter', 'Bunny', '2018-09-12 02:20:29.985462')
2018-09-11 22:20:29,986 INFO sqlalchemy.engine.base.Engine COMMIT

Using the Database

Once the database has been created, you can modify the existing code from Part 1 to make use of it. All of the modifications necessary are due to creating the person_id primary key value in our database as the unique identifier rather than the lname value.

Update the REST API

None of the changes are very dramatic, and you’ll start by re-defining the REST API. The list below shows the API definition from Part 1 but is updated to use the person_id variable in the URL path:

Where the URL definitions required an lname value, they now require the person_id (primary key) for the person record in the people table. This allows you to remove the code in the previous app that artificially restricted users from editing a person’s last name.

In order for you to implement these changes, the swagger.yml file from Part 1 will have to be edited. For the most part, any lname parameter value will be changed to person_id, and person_id will be added to the POST and PUT responses. You can check out the updated swagger.yml file.

Update the REST API Handlers

With the swagger.yml file updated to support the use of the person_id identifier, you’ll also need to update the handlers in the people.py file to support these changes. In the same way that the swagger.yml file was updated, you need to change the people.py file to use the person_id value rather than lname.

Here’s part of the updated person.py module showing the handler for the REST URL endpoint GET /api/people:

 1 from flask import (
 2     make_response,
 3     abort,
 4 )
 5 from config import db
 6 from models import (
 7     Person,
 8     PersonSchema,
 9 )
10 
11 def read_all():
12     """
13     This function responds to a request for /api/people
14     with the complete lists of people
15 
16     :return:        json string of list of people
17     """
18     # Create the list of people from our data
19     people = Person.query 
20         .order_by(Person.lname) 
21         .all()
22 
23     # Serialize the data for the response
24     person_schema = PersonSchema(many=True)
25     return person_schema.dump(people).data

Here’s what the above code is doing:

• Lines 1 – 9 import some Flask modules to create the REST API responses, as well as importing the db instance from the config.py module. In addition, it imports the SQLAlchemy Person and Marshmallow PersonSchema classes to access the person database table and serialize the results.

• Line 11 starts the definition of read_all() that responds to the REST API URL endpoint GET /api/people and returns all the records in the person database table sorted in ascending order by last name.

• Lines 19 – 22 tell SQLAlchemy to query the person database table for all the records, sort them in ascending order (the default sorting order), and return a list of Person Python objects as the variable people.

• Line 24 is where the Marshmallow PersonSchema class definition becomes valuable. You create an instance of the PersonSchema, passing it the parameter many=True. This tells PersonSchema to expect an interable to serialize, which is what the people variable is.

• Line 25 uses the PersonSchema instance variable (person_schema), calling its dump() method with the people list. The result is an object having a data attribute, an object containing a people list that can be converted to JSON. This is returned and converted by Connexion to JSON as the response to the REST API call.

TypeError: Object of type Person is not JSON serializable

Here’s another part of the person.py module that makes a request for a single person from the person database. Here, read_one(person_id) function receives a person_id from the REST URL path, indicating the user is looking for a specific person. Here’s part of the updated person.py module showing the handler for the REST URL endpoint GET /api/people/{person_id}:

 1 def read_one(person_id):
 2     """
 3     This function responds to a request for /api/people/{person_id}
 4     with one matching person from people
 5 
 6     :param person_id:   ID of person to find
 7     :return:            person matching ID
 8     """
 9     # Get the person requested
10     person = Person.query 
11         .filter(Person.person_id == person_id) 
12         .one_or_none()
13 
14     # Did we find a person?
15     if person is not None:
16 
17         # Serialize the data for the response
18         person_schema = PersonSchema()
19         return person_schema.dump(person).data
20 
21     # Otherwise, nope, didn't find that person
22     else:
23         abort(404, 'Person not found for Id: {person_id}'.format(person_id=person_id))

Here’s what the above code is doing:

• Lines 10 – 12 use the person_id parameter in a SQLAlchemy query using the filter method of the query object to search for a person with a person_id attribute matching the passed-in person_id. Rather than using the all() query method, use the one_or_none() method to get one person, or return None if no match is found.

• Line 15 determines whether a person was found or not.

• Line 17 shows that, if person was not None (a matching person was found), then serializing the data is a little different. You don’t pass the many=True parameter to the creation of the PersonSchema() instance. Instead, you pass many=False because only a single object is passed in to serialize.

• Line 18 is where the dump method of person_schema is called, and the data attribute of the resulting object is returned.

• Line 23 shows that, if person was None (a matching person wasn’t found), then the Flask abort() method is called to return an error.

Another modification to person.py is creating a new person in the database. This gives you an opportunity to use the Marshmallow PersonSchema to deserialize a JSON structure sent with the HTTP request to create a SQLAlchemy Person object. Here’s part of the updated person.py module showing the handler for the REST URL endpoint POST /api/people:

 1 def create(person):
 2     """
 3     This function creates a new person in the people structure
 4     based on the passed-in person data
 5 
 6     :param person:  person to create in people structure
 7     :return:        201 on success, 406 on person exists
 8     """
 9     fname = person.get('fname')
10     lname = person.get('lname')
11 
12     existing_person = Person.query 
13         .filter(Person.fname == fname) 
14         .filter(Person.lname == lname) 
15         .one_or_none()
16 
17     # Can we insert this person?
18     if existing_person is None:
19 
20         # Create a person instance using the schema and the passed-in person
21         schema = PersonSchema()
22         new_person = schema.load(person, session=db.session).data
23 
24         # Add the person to the database
25         db.session.add(new_person)
26         db.session.commit()
27 
28         # Serialize and return the newly created person in the response
29         return schema.dump(new_person).data, 201
30 
31     # Otherwise, nope, person exists already
32     else:
33         abort(409, f'Person {fname} {lname} exists already')

Here’s what the above code is doing:

• Line 9 & 10 set the fname and lname variables based on the Person data structure sent as the POST body of the HTTP request.

• Lines 12 – 15 use the SQLAlchemy Person class to query the database for the existence of a person with the same fname and lname as the passed-in person.

• Line 18 addresses whether existing_person is None. (existing_person was not found.)

• Line 21 creates a PersonSchema() instance called schema.

• Line 22 uses the schema variable to load the data contained in the person parameter variable and create a new SQLAlchemy Person instance variable called new_person.

• Line 25 adds the new_person instance to the db.session.

• Line 26 commits the new_person instance to the database, which also assigns it a new primary key value (based on the auto-incrementing integer) and a UTC-based timestamp.

• Line 33 shows that, if existing_person is not None (a matching person was found), then the Flask abort() method is called to return an error.

Update the Swagger UI

With the above changes in place, your REST API is now functional. The changes you’ve made are also reflected in an updated swagger UI interface and can be interacted with in the same manner. Below is a screenshot of the updated swagger UI opened to the GET /people/{person_id} section. This section of the UI gets a single person from the database and looks like this:

As shown in the above screenshot, the path parameter lname has been replaced by person_id, which is the primary key for a person in the REST API. The changes to the UI are a combined result of changing the swagger.yml file and the code changes made to support that.

Update the Web Application

The REST API is running, and CRUD operations are being persisted to the database. So that it is possible to view the demonstration web application, the JavaScript code has to be updated.

The updates are again related to using person_id instead of lname as the primary key for person data. In addition, the person_id is attached to the rows of the display table as HTML data attributes named data-person-id, so the value can be retrieved and used by the JavaScript code.

This article focused on the database and making your REST API use it, which is why there’s just a link to the updated JavaScript source and not much discussion of what it does.

Example Code

All of the example code for this article is available here. There’s one version of the code containing all the files, including the build_database.py utility program and the server.py modified example program from Part 1.

Conclusion

Congratulations, you’ve covered a lot of new material in this article and added useful tools to your arsenal!

You’ve learned how to save Python objects to a database using SQLAlchemy. You’ve also learned how to use Marshmallow to serialize and deserialize SQLAlchemy objects and use them with a JSON REST API. The things you’ve learned have certainly been a step up in complexity from the simple REST API of Part 1, but that step has given you two very powerful tools to use when creating more complex applications.

SQLAlchemy and Marshmallow are amazing tools in their own right. Using them together gives you a great leg up to create your own web applications backed by a database.

In Part 3 of this series, you’ll focus on the R part of RDBMS: relationships, which provide even more power when you are using a database.

[ Improve Your Python With 🐍 Python Tricks 💌 – Get a short & sweet Python Trick delivered to your inbox every couple of days. >> Click here to learn more and see examples ]