Python REST APIs With Flask, Connexion, and SQLAlchemy – Part 2 – Real Python

Seize the Conditions

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

If you happen to downloaded the supply code from the hyperlink above, then be sure to observe the set up directions throughout the supplied README.md file.

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

rp_flask_api/
│
├── templates/
│   └── residence.html
│
├── app.py
├── individuals.py
└── swagger.yml

When you’ve acquired 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 collection.

Add New Dependencies

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

Choose your working system under 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 by utilizing 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 gives performance to serialize and deserialize Python objects as they movement out and in of your REST API, which is predicated on JSON. Marshmallow converts Python class instances 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 gives an object-relational model (ORM), which shops every Python object to a database illustration of the item’s information. That may assist you proceed to assume in a Pythonic approach and never be involved with how the item information can 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. If you happen to open a browser and navigate to http://localhost:8000, it’s best to see Howdy, 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 the moment, you’re storing your information within the PEOPLE dictionary in individuals.py. The information construction seems to be like this within the code:

# individuals.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 information to a database desk. Which means the information can 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 data, and the columns are fields in these data.

Database tables often have an auto-incrementing integer worth because the lookup key to rows. That is known as the main key. Every document within the desk may have a main key whose worth is exclusive throughout your complete desk. Having a main key unbiased of the information saved within the desk provides you the liberty to switch another subject within the row.

You’re going to observe a database conference of naming the desk as singular, so the desk can be known as particular person.

Translating your PEOPLE construction above right into a database desk named particular person will appear to be this:

Every column within the desk has a subject identify as follows:

• id: Major key subject for every particular person
• lname: Final identify of the particular person
• fname: First identify of the particular person
• 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 other SQL database engines, SQLite works with a single file to keep up all of the database performance. Subsequently, to make use of the database, a program simply must know the best 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 individuals.db SQLite database:

>>> import sqlite3
>>> conn = sqlite3.join("individuals.db")
>>> columns = [
...     "id INTEGER PRIMARY KEY",
...     "lname VARCHAR UNIQUE",
...     "fname VARCHAR",
...     "timestamp DATETIME",
... ]
>>> create_table_cmd = f"CREATE TABLE particular person (','.be 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(). When you’ve got a have a look at your file system after defining the conn variable, then you definately’ll discover that Python created the individuals.db database file immediately.

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

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

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

>>> import sqlite3
>>> conn = sqlite3.join("individuals.db")
>>> individuals = [
...     "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 individuals:
...     insert_cmd = f"INSERT INTO particular person 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 individuals.db database, you declare a transaction to insert people_data into the particular person desk. The conn.execute() command creates sqlite3.Cursor objects in reminiscence. Solely if 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 best way to get the information. 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 particular person desk would look this this:

This question tells the database engine to get all of the fields from the particular person desk. Within the following Python code, you employ SQLite to run the above question and show the information:

 1>>> import sqlite3
 2>>> conn = sqlite3.join("individuals.db")
 3>>> cur = conn.cursor()
 4>>> cur.execute("SELECT * FROM particular person")
 5<sqlite3.Cursor object at 0x102357a40>
 6
 7>>> individuals = cur.fetchall()
 8>>> for particular person in individuals:
 9...     print(particular person)
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 data returned by the SQL question and assigns them to the individuals variable.
• Traces 8 and 9 iterate over individuals and print out the information of every particular person.

Within the above program, the SQL assertion is a string handed on to the database to execute. On this case, that might not be a giant drawback as a result of the SQL is a string literal utterly beneath the management of this system. Nevertheless, the use case in your REST API can be taking person enter from the net utility and utilizing it to create SQL queries. This may open your utility to assault.

Increase the part under to find out how:

You’ll recall from half one in every of this tutorial collection that the REST API endpoint to get a single person 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 particular person. Modifying the Python SQLite code from above to do that would look one thing like this:

 1lname = "Fairy"
 2cur.execute(f"SELECT * FROM particular person WHERE lname = 'lname'")

The above code snippet does the next:

• Line 1 units the lname variable to 'Fairy'. This may 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 provided by the person. The SQL generated by the string formatting seems to be like this:

SELECT * FROM particular person WHERE lname = 'Fairy'

When this SQL is executed by the database, it searches the particular person desk for a document the place the final identify is the same as 'Fairy'. That is what’s supposed, however any program that accepts person 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 attack. You would possibly see such an assault known as Little Bobby Tables:

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

GET /api/individuals/Fairy';DROP TABLE particular person;

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

SELECT * FROM particular person WHERE lname = 'Fairy';DROP TABLE particular person;

The above SQL assertion is legitimate, and when executed by the database, it’ll discover one document 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 may basically wreck your utility.

You’ll be able to shield 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 study 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 person information interacts with the database.

It could be a lot better if what you bought again for particular person was a Python object, the place every of the fields is an attribute of the item. That approach, 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 need to write pure SQL instructions. As you discovered above, writing SQL could not solely really feel inconvenvient, however it could trigger safety points. If you happen to don’t need to fear an excessive amount of about database interplay, a bundle like SQLAlchemy may help you out.

Configure Your Database

The config.py module is, because the identify implies, the place your entire configuration info 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/ undertaking 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 / 'individuals.db'"
13app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
14
15db = SQLAlchemy(app)
16ma = Marshmallow(app)

Right here’s what the above code is doing:

• Traces 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 individuals.db within the present listing because the database file.

• Line 13 turns the SQLAlchemy event system off. The occasion system generates occasions which might be 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 info to SQLAlchemy and assigning the end result to a db variable.

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

If you wish to study extra in regards to the SQLAlchemy configurations you could 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 undertaking and gives a variety of performance to work with databases utilizing Python. One of many options that it gives is an object-relational mapper (ORM). This ORM allows you to work together with the particular person database desk in a extra Pythonic approach 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 information within the particular person database desk:

 1# fashions.py
 2
 3from datetime import datetime
 4from config import db
 5
 6class Individual(db.Mannequin):
 7    __tablename__ = "particular person"
 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 provides you a approach to create a timestamp within the Individual class in traces 11 to 13.
• Line 4 imports db, an occasion of SQLAlchemy that you simply outlined within the config.py module. This provides fashions.py entry to SQLAlchemy attributes and strategies.
• Line 6 defines the Individual class. Inheriting from db.Mannequin provides Individual the SQLAlchemy options to hook up with the database and entry its tables.
• Line 7 connects the category definition to the particular person database desk.
• Line 8 declares the id column containing an integer appearing as the first key for the desk.
• Line 9 defines the final identify subject with a string worth. This subject have to be distinctive since you’re utilizing lname because the identifier for an individual in a REST API URL.
• Line 10 defines the primary identify subject with a string worth.
• Traces 11 to 13 outline a timestamp subject with a datetime worth.

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

Utilizing SQLAlchemy permits you to assume by way of objects with conduct somewhat than coping with uncooked SQL. This turns into much more useful when your database tables develop into 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. Nevertheless, 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 cases, Connexion can’t serialize these class cases to JSON-formatted information.

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

1. Your REST API works with JSON as an alternative of Python objects.
2. You will need to ensure that the information 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 Individual class you simply created. The PersonSchema class defines how the attributes of a category can 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 information in your particular person desk:

# fashions.py

from datetime import datetime
from config import db, ma

class Individual(db.Mannequin):
    __tablename__ = "particular person"
    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 = Individual
        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 seems to be for after which makes use of this inner Meta class.

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

With load_instance, you’re capable of deserialize JSON information and cargo Individual mannequin cases 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 do away with the previous PEOPLE information construction. It will ensure that any modifications you’re making to individuals information are carried out on the database somewhat than the out of date PEOPLE dictionary.

Open individuals.py and do away with 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:

# individuals.py

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

from config import db
from fashions import Individual, people_schema, person_schema

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

# ...

You take away the datetime import, the get_timestamp() perform, and the PEOPLE dictionary. In trade, 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 could have complained in regards to the undefined PEOPLE variable in your code. Within the subsequent part, you’ll change all PEOPLE references with database queries and make your Python editor pleased once more.

Learn From the Database

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

# individuals.py

# ...

def read_all():
    individuals = Individual.question.all()
    return people_schema.dump(individuals)

# ...

The read_all() perform responds to the REST API URL endpoint GET /api/individuals and returns all of the data within the particular person 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 individuals variable incorporates a listing of database gadgets.

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

The opposite perform in individuals.py that solely receives information is read_one():

# individuals.py

# ...

def read_one(lname):
    particular person = Individual.question.filter(Individual.lname == lname).one_or_none()

    if particular person is not None:
        return person_schema.dump(particular person)
    else:
        abort(404, f"Individual with final identify lname not discovered")

# ...

The read_one() perform receives an lname parameter from the REST URL path, indicating that the person is in search of a particular particular person.

You employ lname within the question’s .filter() methodology. Slightly than utilizing .all(), you employ the .one_or_none() methodology to get one particular person, or return None if no match is discovered.

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

Write to the Database

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

# individuals.py

# ...

def create(particular person):
    lname = particular person.get("lname")
    existing_person = Individual.question.filter(Individual.lname == lname).one_or_none()

    if existing_person is None:
        new_person = person_schema.load(particular person, session=db.session)
        db.session.add(new_person)
        db.session.commit()
        return person_schema.dump(new_person), 201
    else:
        abort(406, f"Individual with final identify lname already exists")

# ...

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

If the final identify is exclusive, then you definately deserialize the particular person 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:

# individuals.py

# ...

def replace(lname, particular person):
    existing_person = Individual.question.filter(Individual.lname == lname).one_or_none()

    if existing_person:
        update_person = person_schema.load(particular person, 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"Individual with final identify lname not discovered")

def delete(lname):
    existing_person = Individual.question.filter(Individual.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"Individual with final identify 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 Individual mannequin, your Flask undertaking incorporates all the knowledge to work along with your database. Earlier than you’ll be able to show information within the entrance finish, you want 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 Individual
 7
 8app = config.connex_app
 9app.add_api(config.basedir / "swagger.yml")
10
11@app.route("/")
12def residence():
13    individuals = Individual.question.all()
14    return render_template("residence.html", individuals=individuals)
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 Individual in line 6.

The config module gives 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 Individual mannequin to get all the information from the particular person desk and cross it on to render_template() in line 14.

To point out the individuals information within the entrance finish, you want to modify the residence.html template:

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

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>RP Flask REST API</title>
</head>
<physique>
    <h1>
        Howdy, Folks!
    </h1>
    <ul>
        % for particular person in individuals %
        <li> particular person.fname   particular person.lname </li>
        % endfor %
    </ul>
</physique>
</html>

You’ll be able to 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. If you happen to open a browser and navigate to http://localhost:8000, you’ll see the information out of your database:

Superior! Your own home web page lists all three people who find themselves presently in your database. Lastly, you should utilize Swagger UI to create, replace, and delete individuals and see the modifications mirrored on the house web page.

Discover Your API Documentation

With the above modifications in place, your database is now useful and persists the information even if you restart your utility:

You’ll be able to leverage your API so as to add, replace, and take away individuals. With the modifications that you simply made to the entrance finish, you’re capable of see all of the people who find themselves presently saved in your database.

Whenever you restart your Flask app, you don’t reset the information anymore. Because you now have a database hooked up to your Flask undertaking, your information is saved.