Python REST APIs With Flask, Connexion, and SQLAlchemy – Half 2 – Actual Python

Seize the Conditions

Ideally, you adopted the first half of this tutorial sequence earlier than persevering with with the second half, which you’re studying proper now. Alternatively, you may as well obtain the supply code from half one by clicking the hyperlink beneath:

Should you downloaded the supply code from the hyperlink above, then make certain to observe the set up directions throughout the offered README.md file.

Earlier than you proceed with the tutorial, confirm that your folder construction appears like this:

rp_flask_api/
│
├── templates/
│   └── house.html
│
├── app.py
├── folks.py
└── swagger.yml

When you’ve received the Flask REST API folder construction in place, you’ll be able to learn on to put in the dependencies that you simply’ll want on this a part of the tutorial sequence.

Add New Dependencies

Earlier than you proceed working in your Flask venture, it’s a good suggestion to create and activate a digital setting. That means, you’re putting in any venture dependencies not system-wide however solely in your venture’s digital setting.

Choose your working system beneath and use your platform-specific command to arrange a digital setting:

PS> python -m venv venv
PS> .venvScriptsactivate
(venv) PS>

$ python -m venv venv
$ supply venv/bin/activate
(venv) $

With the instructions proven above, you create and activate a digital setting named venv through the use of Python’s built-in venv module. The parenthesized (venv) in entrance of the immediate point out that you simply’ve efficiently activated the digital setting.

Subsequent, set up flask-marshmallow with the sqlalchemy choice:

(venv) $ python -m pip set up "flask-marshmallow[sqlalchemy]==0.14.0"

Flask-Marshmallow additionally installs marshmallow, which supplies performance to serialize and deserialize Python objects as they stream out and in of your REST API, which is predicated on JSON. Marshmallow converts Python class situations to things that may be transformed to JSON.

By utilizing the sqlalchemy choice, you additionally set up packages that helps your Flask app leverage the powers of SQLAlchemy.

SQLAlchemy supplies an object-relational mannequin (ORM), which shops every Python object to a database illustration of the item’s information. That may show you how to proceed to assume in a Pythonic means and never be involved with how the item information will probably be represented in a database.

Examine Your Flask Mission

After following the steps above, you’ll be able to confirm that your Flask utility is operating with out errors. Execute the next command within the listing containing the app.py file:

Whenever you run this utility, an internet server will begin on port 8000, which is the default port utilized by Flask. Should you open a browser and navigate to http://localhost:8000, it’s best to see Hi there, World! displayed:

Excellent, your app is operating flawlessly! Now it’s time to enter the again finish and work with a correct database.

Examine Your Present Knowledge Construction

At present, you’re storing your information within the PEOPLE dictionary in folks.py. The information construction appears like this within the code:

# folks.py

# ...

PEOPLE = {
    "Fairy": {
        "fname": "Tooth",
        "lname": "Fairy",
        "timestamp": get_timestamp(),
    },
    "Ruprecht": {
        "fname": "Knecht",
        "lname": "Ruprecht",
        "timestamp": get_timestamp(),
    },
    "Bunny": {
        "fname": "Easter",
        "lname": "Bunny",
        "timestamp": get_timestamp(),
    }
}

# ...

The modifications that you simply’ll make to this system will transfer all the info to a database desk. Which means the info will probably be saved to your disk and can exist between runs of the app.py program.

Conceptualize Your Database Desk

Conceptually, you’ll be able to consider a database desk as a two-dimensional array the place the rows are information, and the columns are fields in these information.

Database tables often have an auto-incrementing integer worth because the lookup key to rows. That is known as the main key. Every report within the desk could have a main key whose worth is exclusive throughout your complete desk. Having a main key impartial of the info saved within the desk provides you the liberty to switch every other discipline within the row.

You’re going to observe a database conference of naming the desk as singular, so the desk will probably be known as individual.

Translating your PEOPLE construction above right into a database desk named individual will appear like this:

Every column within the desk has a discipline title as follows:

• id: Main key discipline for every individual
• lname: Final title of the individual
• fname: First title of the individual
• timestamp: Timestamp of the final change

With this database idea in place, it’s time to construct the database.

Construct Your Database

You’re going to make use of SQLite because the database engine to retailer the PEOPLE information. SQLite is a broadly used relational database administration system (RDBMS) that doesn’t want a SQL server to work.

In distinction to different SQL database engines, SQLite works with a single file to take care of all of the database performance. Subsequently, to make use of the database, a program simply must know the right way to learn and write to a SQLite file.

Python’s built-in sqlite3 module permits you to work together with SQLite databases with none exterior packages. This makes SQLite significantly helpful when beginning new Python tasks.

Begin a brand new Python interactive shell to create the folks.db SQLite database:

>>> import sqlite3
>>> conn = sqlite3.join("folks.db")
>>> columns = [
...     "id INTEGER PRIMARY KEY",
...     "lname VARCHAR UNIQUE",
...     "fname VARCHAR",
...     "timestamp DATETIME",
... ]
>>> create_table_cmd = f"CREATE TABLE individual ({','.be a part of(columns)})"
>>> conn.execute(create_table_cmd)
<sqlite3.Cursor object at 0x1063f4dc0>

After you import the sqlite3 module, you’ll be able to create a brand new database with .join(). In case you have a have a look at your file system after defining the conn variable, you then’ll discover that Python created the folks.db database file immediately.

With conn.execute() you’re operating the SQL command to create a individual desk with the columns id, lname, fname, and timestamp.

Notice that you simply embrace a UNIQUE constraint for lname. That’s essential since you use the final title in your REST API to determine an individual. Subsequently, your database should guarantee the individuality of lname to forestall inconsistencies in your information.

Now that your database exists, you’ll be able to add information to it:

>>> import sqlite3
>>> conn = sqlite3.join("folks.db")
>>> folks = [
...     "1, 'Fairy', 'Tooth', '2022-10-08 09:15:10'",
...     "2, 'Ruprecht', 'Knecht', '2022-10-08 09:15:13'",
...     "3, 'Bunny', 'Easter', '2022-10-08 09:15:27'",
... ]
>>> for person_data in folks:
...     insert_cmd = f"INSERT INTO individual VALUES ({person_data})"
...     conn.execute(insert_cmd)
...
<sqlite3.Cursor object at 0x104ac4dc0>
<sqlite3.Cursor object at 0x104ac4f40>
<sqlite3.Cursor object at 0x104ac4fc0>

>>> conn.commit()

When you’re related to the folks.db database, you declare a transaction to insert people_data into the individual desk. The conn.execute() command creates sqlite3.Cursor objects in reminiscence. Solely whenever you run conn.commit() do you make the transaction occur.

Work together With the Database

In contrast to programming languages like Python, SQL doesn’t outline the right way to get the info. SQL describes what information is desired and leaves the how as much as the database engine.

A SQL question that will get all the information in your individual desk would look this this:

This question tells the database engine to get all of the fields from the individual desk. Within the following Python code, you utilize SQLite to run the above question and show the info:

 1>>> import sqlite3
 2>>> conn = sqlite3.join("folks.db")
 3>>> cur = conn.cursor()
 4>>> cur.execute("SELECT * FROM individual")
 5<sqlite3.Cursor object at 0x102357a40>
 6
 7>>> folks = cur.fetchall()
 8>>> for individual in folks:
 9...     print(individual)
10...
11(1, 'Fairy', 'Tooth', '2022-10-08 09:15:10')
12(2, 'Ruprecht', 'Knecht', '2022-10-08 09:15:13')
13(3, 'Bunny', 'Easter', '2022-10-08 09:15:27')

The code above does the next:

• Line 1 imports the sqlite3 module.
• Line 2 creates a connection to the database file.
• Line 3 creates a cursor from the connection.
• Line 4 makes use of the cursor to execute a SQL question expressed as a string.
• Line 7 will get all of the information returned by the SQL question and assigns them to the folks variable.
• Strains 8 and 9 iterate over folks and print out the info of every individual.

Within the above program, the SQL assertion is a string handed on to the database to execute. On this case, that is probably not a giant drawback as a result of the SQL is a string literal utterly underneath the management of this system. Nonetheless, the use case in your REST API will probably be taking consumer enter from the online utility and utilizing it to create SQL queries. This will open your utility to assault.

Increase the part beneath to find out how:

You’ll recall from half one among this tutorial sequence that the REST API endpoint to get a single individual from the PEOPLE information seemed like this:

This implies your API is anticipating a variable, lname, within the URL endpoint path that it makes use of to discover a single individual. Modifying the Python SQLite code from above to do that would look one thing like this:

 1lname = "Fairy"
 2cur.execute(f"SELECT * FROM individual WHERE lname = '{lname}'")

The above code snippet does the next:

• Line 1 units the lname variable to 'Fairy'. This could come from the REST API URL endpoint path.
• Line 2 makes use of Python string formatting to create a SQL string and execute it.

To maintain issues easy, the above code units the lname variable to a continuing, however actually it will come from the API URL endpoint path and might be something equipped by the consumer. The SQL generated by the string formatting appears like this:

SELECT * FROM individual WHERE lname = 'Fairy'

When this SQL is executed by the database, it searches the individual desk for a report the place the final title is the same as 'Fairy'. That is what’s meant, however any program that accepts consumer enter can be open to malicious customers. This system above, the place the lname variable is ready by user-supplied enter, opens you as much as what’s known as a SQL injection assault. You may see such an assault known as Little Bobby Tables:

For instance, think about {that a} malicious consumer known as your REST API on this means:

GET /api/folks/Fairy';DROP TABLE individual;

The REST API request above units the lname variable to 'Fairy';DROP TABLE individual;', which within the code above would generate this SQL assertion:

SELECT * FROM individual WHERE lname = 'Fairy';DROP TABLE individual;

The above SQL assertion is legitimate, and when executed by the database, it’ll discover one report the place lname matches 'Fairy'. Then, it’ll discover the SQL assertion delimiter character ; and can go proper forward and drop your complete desk. This could basically wreck your utility.

You possibly can defend your program by sanitizing all information that you simply get from the customers of your utility. Sanitizing information on this context means having your program look at the user-supplied information to ensure that it doesn’t comprise something harmful to this system. This may be tough to do proper and must be finished in every single place consumer information interacts with the database.

It could be significantly better if what you bought again for individual was a Python object, the place every of the fields is an attribute of the item. That means, you ensure that the objects comprise the anticipated worth sorts and never any malicious instructions.

Whenever you work together with a database in your Python code, you could assume twice about whether or not you wish to write pure SQL instructions. As you realized above, writing SQL might not solely really feel inconvenvient, however it might probably trigger safety points. Should you don’t wish to fear an excessive amount of about database interplay, a package deal like SQLAlchemy can assist you out.

Configure Your Database

The config.py module is, because the title implies, the place your entire configuration data is created and initialized. On this file, you’re going to configure Flask, Connexion, SQLAlchemy, and Marshmallow.

Create config.py in your rp_flask_api/ venture folder:

 1# config.py
 2
 3import pathlib
 4import connexion
 5from flask_sqlalchemy import SQLAlchemy
 6from flask_marshmallow import Marshmallow
 7
 8basedir = pathlib.Path(__file__).guardian.resolve()
 9connex_app = connexion.App(__name__, specification_dir=basedir)
10
11app = connex_app.app
12app.config["SQLALCHEMY_DATABASE_URI"] = f"sqlite:///{basedir / 'folks.db'}"
13app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
14
15db = SQLAlchemy(app)
16ma = Marshmallow(app)

Right here’s what the above code is doing:

• Strains 3 to six import the built-in pathlib in addition to the third-party libraries connexion, SQLAlchemy, and Marshmallow.

• Line 8 creates the variable basedir pointing to the listing that this system is operating in.

• Line 9 makes use of the basedir variable to create the Connexion app occasion and provides it the trail to the listing that incorporates your specification file.

• Line 11 creates a variable, app, which is the Flask occasion initialized by Connexion.

• Line 12 inform SQLAlchemy to make use of SQLite because the database and a file named folks.db within the present listing because the database file.

• Line 13 turns the SQLAlchemy occasion system off. The occasion system generates occasions which are helpful in event-driven packages, but it surely provides vital overhead. Because you’re not creating an event-driven program, you flip this characteristic off.

• Line 15 initializes SQLAlchemy by passing the app configuration data to SQLAlchemy and assigning the end result to a db variable.

• Line 16 initializes Marshmallow and permits it to work with the SQLAlchemy elements connected to the app.

If you wish to be taught extra in regards to the SQLAlchemy configurations that you may implement right here, then you’ll be able to take a look at the configuration keys documentation of Flask-SQLALchemy.

Mannequin Knowledge With SQLAlchemy

SQLAlchemy is a giant venture and supplies a number of performance to work with databases utilizing Python. One of many options that it supplies is an object-relational mapper (ORM). This ORM allows you to work together with the individual database desk in a extra Pythonic means by mapping a row of fields from the database desk to a Python object.

Create a fashions.py file with a SQLAlchemy class definition for the info within the individual database desk:

 1# fashions.py
 2
 3from datetime import datetime
 4from config import db
 5
 6class Particular person(db.Mannequin):
 7    __tablename__ = "individual"
 8    id = db.Column(db.Integer, primary_key=True)
 9    lname = db.Column(db.String(32), distinctive=True)
10    fname = db.Column(db.String(32))
11    timestamp = db.Column(
12        db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow
13    )

Right here’s what the above code is doing:

• Line 3 imports the datetime object from the datetime module that comes with Python. This offers you a method to create a timestamp within the Particular person class in traces 11 to 13.
• Line 4 imports db, an occasion of SQLAlchemy that you simply outlined within the config.py module. This offers fashions.py entry to SQLAlchemy attributes and strategies.
• Line 6 defines the Particular person class. Inheriting from db.Mannequin provides Particular person the SQLAlchemy options to connect with the database and entry its tables.
• Line 7 connects the category definition to the individual database desk.
• Line 8 declares the id column containing an integer performing as the first key for the desk.
• Line 9 defines the final title discipline with a string worth. This discipline should be distinctive since you’re utilizing lname because the identifier for an individual in a REST API URL.
• Line 10 defines the primary title discipline with a string worth.
• Strains 11 to 13 outline a timestamp discipline with a datetime worth.

The default=datetime.utcnow parameter defaults the timestamp worth to the present utcnow worth when a report is created. The onupdate=datetime.utcnow parameter updates the timestamp with the present utcnow worth when the report is up to date. To be taught extra about UTC timestamps, increase the collapsible part beneath:

Utilizing SQLAlchemy permits you to assume by way of objects with habits moderately than coping with uncooked SQL. This turns into much more useful when your database tables grow to be bigger and the interactions extra advanced.

Serialize the Modeled Knowledge With Marshmallow

Working with SQLAlchemy’s modeled information inside your packages may be very handy. Nonetheless, the REST API works with JSON information, and right here you’ll be able to run into a problem with the SQLAlchemy mannequin.

As a result of SQLAlchemy returns information as Python class situations, Connexion can’t serialize these class situations to JSON-formatted information.

You’re utilizing a database as persistent information storage. With SQLAlchemy, you’ll be able to comfortably talk together with your database from inside your Python program. Nonetheless, there are two challenges that you have to clear up:

1. Your REST API works with JSON as an alternative of Python objects.
2. You will need to ensure that the info that you simply’re including to the database is legitimate.

That’s the place the Marshmallow module comes into play!

Marshmallow lets you create a PersonSchema class, which is just like the SQLAlchemy Particular person class you simply created. The PersonSchema class defines how the attributes of a category will probably be transformed into JSON-friendly codecs. Marshmallow additionally makes positive that every one attributes are current and comprise the anticipated information kind.

Right here’s the Marshmallow class definition for the info in your individual desk:

# fashions.py

from datetime import datetime
from config import db, ma

class Particular person(db.Mannequin):
    __tablename__ = "individual"
    id = db.Column(db.Integer, primary_key=True)
    lname = db.Column(db.String(32), distinctive=True)
    fname = db.Column(db.String(32))
    timestamp = db.Column(
        db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow
    )

class PersonSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        mannequin = Particular person
        load_instance = True
        sqla_session = db.session

person_schema = PersonSchema()
people_schema = PersonSchema(many=True)

You import ma from config.py to allow PersonSchema to inherit from ma.SQLAlchemyAutoSchema. To discover a SQLAlchemy mannequin and a SQLALchemy session, SQLAlchemyAutoSchema appears for after which makes use of this inside Meta class.

For PersonSchema, the mannequin is Particular person, and sqla_session is db.session. That is how Marshmallow finds attributes within the Particular person class and learns the sorts of these attributes so it is aware of the right way to serialize and deserialize them.

With load_instance, you’re capable of deserialize JSON information and cargo Particular person mannequin situations from it. Lastly, you instantiate two schemas, person_schema and people_schema, that you simply’ll use later.

Do Some Cleanup

Now it’s time to eliminate the previous PEOPLE information construction. This can ensure that any modifications you’re making to folks information are carried out on the database moderately than the out of date PEOPLE dictionary.

Open folks.py and eliminate the imports, features, and information constructions that you simply don’t want anymore, and use new imports so as to add db and information from fashions.py:

# folks.py

# Take away: from datetime import datetime
from flask import make_response, abort

from config import db
from fashions import Particular person, people_schema, person_schema

# Take away: get_timestamp():
# Take away: PEOPLE

# ...

You take away the datetime import, the get_timestamp() operate, and the PEOPLE dictionary. In alternate, you add objects from config and fashions that you simply’ll use any longer.

The second you eliminated the PEOPLE dictionary, your Python code editor might have complained in regards to the undefined PEOPLE variable in your code. Within the subsequent part, you’ll exchange all PEOPLE references with database queries and make your Python editor glad once more.

Learn From the Database

First, regulate the features in folks.py that learn information from the database with out writing something to the database. Begin with read_all():

# folks.py

# ...

def read_all():
    folks = Particular person.question.all()
    return people_schema.dump(folks)

# ...

The read_all() operate responds to the REST API URL endpoint GET /api/folks and returns all of the information within the individual database desk.

You’re utilizing people_schema which is an occasion of the Marshmallow PersonSchema class the was created with the parameter many=True. With this parameter you inform PersonSchema to count on an interable to serialize. That is essential as a result of the folks variable incorporates an inventory of database objects.

Lastly, you serialize your Python objects with .dump() and return the info of all of the folks as a response to the REST API name.

The opposite operate in folks.py that solely receives information is read_one():

# folks.py

# ...

def read_one(lname):
    individual = Particular person.question.filter(Particular person.lname == lname).one_or_none()

    if individual is not None:
        return person_schema.dump(individual)
    else:
        abort(404, f"Particular person with final title {lname} not discovered")

# ...

The read_one() operate receives an lname parameter from the REST URL path, indicating that the consumer is in search of a selected individual.

You employ lname within the question’s .filter() technique. Quite than utilizing .all(), you utilize the .one_or_none() technique to get one individual, or return None if no match is discovered.

If an individual is discovered, then individual incorporates a Particular person object and you come the serialized object. In any other case, you name abort() with an error.

Write to the Database

One other modification to folks.py is creating a brand new individual within the database. This offers you a chance to make use of the Marshmallow PersonSchema to deserialize a JSON construction despatched with the HTTP request to create a SQLAlchemy Particular person object. Right here’s a part of the up to date folks.py module displaying the handler for the REST URL endpoint POST /api/folks:

# folks.py

# ...

def create(individual):
    lname = individual.get("lname")
    existing_person = Particular person.question.filter(Particular person.lname == lname).one_or_none()

    if existing_person is None:
        new_person = person_schema.load(individual, session=db.session)
        db.session.add(new_person)
        db.session.commit()
        return person_schema.dump(new_person), 201
    else:
        abort(406, f"Particular person with final title {lname} already exists")

# ...

As a substitute of receiving solely a final title like in read_one(), create() receives a individual object. This object should comprise lname, which should not exist within the database already. The lname worth is your identifier in your individual, so you’ll be able to’t have an individual with the identical final title a number of instances in your database.

If the final title is exclusive, you then deserialize the individual object as new_person and add it db.session. When you commit new_person to the database, your database engine assigns a brand new main key worth and a UTC-based timestamp to the item. Later, you’ll see the created dataset within the API response.

Alter replace() and delete() equally to the way you adjusted the opposite features:

# folks.py

# ...

def replace(lname, individual):
    existing_person = Particular person.question.filter(Particular person.lname == lname).one_or_none()

    if existing_person:
        update_person = person_schema.load(individual, session=db.session)
        existing_person.fname = update_person.fname
        db.session.merge(existing_person)
        db.session.commit()
        return person_schema.dump(existing_person), 201
    else:
        abort(404, f"Particular person with final title {lname} not discovered")

def delete(lname):
    existing_person = Particular person.question.filter(Particular person.lname == lname).one_or_none()

    if existing_person:
        db.session.delete(existing_person)
        db.session.commit()
        return make_response(f"{lname} efficiently deleted", 200)
    else:
        abort(404, f"Particular person with final title {lname} not discovered")

With all these modifications in place, it’s time to replace your front-end code and leverage Swagger UI to check out in case your database works as anticipated.

Show Knowledge in Your Entrance Finish

Now that you simply’ve added the SQLite configuration and outlined your Particular person mannequin, your Flask venture incorporates all the data to work together with your database. Earlier than you’ll be able to show information within the entrance finish, you have to make some changes to app.py:

 1# app.py
 2
 3from flask import render_template
 4# Take away: import connexion
 5import config
 6from fashions import Particular person
 7
 8app = config.connex_app
 9app.add_api(config.basedir / "swagger.yml")
10
11@app.route("/")
12def house():
13    folks = Particular person.question.all()
14    return render_template("house.html", folks=folks)
15
16if __name__ == "__main__":
17    app.run(host="0.0.0.0", port=8000, debug=True)

You’re now working with config.py and fashions.py. So that you take away the import within the line 4 and add the imports for config in line 5 and Particular person in line 6.

The config module supplies the Connexion-flavored Flask app for you. Subsequently, you don’t create a brand new Flask app in app.py anymore, however reference config.connex_app in line 8.

In line 13 you question the Particular person mannequin to get all the info from the individual desk and cross it on to render_template() in line 14.

To indicate the folks information within the entrance finish, you have to regulate the house.html template:

<!-- templates/house.html -->

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>RP Flask REST API</title>
</head>
<physique>
    <h1>
        Hi there, Individuals!
    </h1>
    <ul>
        {% for individual in folks %}
        <li>{{ individual.fname }} {{ individual.lname }}</li>
        {% endfor %}
    </ul>
</physique>
</html>

You possibly can run your utility with this command within the listing containing the app.py file:

Whenever you run this utility, an internet server will begin on port 8000, which is the port that you simply outlined in app.py. Should you open a browser and navigate to http://localhost:8000, you’ll see the info out of your database:

Superior! Your house web page lists all three people who find themselves at the moment in your database. Lastly, you should use Swagger UI to create, replace, and delete folks and see the modifications mirrored on the house web page.

Discover Your API Documentation

With the above modifications in place, your database is now practical and persists the info even whenever you restart your utility:

You possibly can leverage your API so as to add, replace, and take away folks. With the modifications that you simply made to the entrance finish, you’re capable of see all of the people who find themselves at the moment saved in your database.

Whenever you restart your Flask app, you don’t reset the info anymore. Because you now have a database connected to your Flask venture, your information is saved.