Efficient management of database interactions is one of the most important tasks when building Python web applications. SQLAlchemy — a comprehensive SQL toolkit and Object-Relational Mapping (ORM) library — is widely used in the Python ecosystem for this purpose.

However, integrating SQLAlchemy with Flask can be a complex process. So developers created Flask-SQLAlchemy to provide a seamless way to use SQLAlchemy in Flask applications, making database management a straightforward process.

In this article, we introduce SQLAlchemy and Flask-SQLAlchemy, highlighting their key features. We'll also create a basic Flask app to use Flask-SQLAlchemy for database management.

What is SQLAlchemy?

Let's start by describing what SQLAlchemy is and what its strengths are.

SQLAlchemy is an open source SQL toolkit and ORM library for Python, created to let objects be objects, and tables be tables. It provides developers with tools to work with databases such as Oracle, DB2, MySQL, PostgreSQL, and SQLite at both high and low levels of abstraction.

Its two primary components are the core and ORM:

• SQLAlchemy Core: This provides a full suite of well-organized database interaction tools. In particular, it's a low-level SQL toolkit that offers an abstraction layer allowing developers to work directly with relational databases by using Python. It contains the so-called "SQL Expression Language", a toolkit on its own that provides a system for constructing SQL expressions.
• SQLAlchemy ORM: The ORM layer builds on top of the Core and provides a higher-level, more abstracted interface for interacting with databases. In practice, it maps Python classes to database tables and instances of these classes to rows in those tables, enabling developers to work with database records as if they were Python objects.

Here's a high-level schema of these two components:

So, basically, if you like writing SQL directly and want fine-grained control over database interactions, then Core is for you. With the ORM, you do not need to write SQL clauses: it allows you to define database models as Python classes and interact with them by using object-oriented programming concepts like inheritance and relationships.

Now let's look at both in more detail.

Using SQLAlchemy ORM

One of the primary features of SQLAlchemy ORM is its ability to map database tables to Python classes seamlessly, allowing developers to manipulate database records using object-oriented paradigms. This approach simplifies database interactions by enabling you to work directly with Python objects instead of writing SQL queries, making code more maintainable and readable.

The ORM also provides a rich set of tools for managing relationships between entities, handling complex queries, and automatically synchronizing the state of in-memory objects with the database. Additionally, SQLAlchemy ORM supports lazy loading and eager loading, offering efficient data retrieval mechanisms tailored to specific use cases. These features, combined with the ability to declaratively define schemas, make the ORM a powerful tool for developers seeking to integrate databases into their Python applications with minimal boilerplate.

Here's how to create a table, insert a record, and query data with the ORM:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker
 
# Define the database engine
engine = create_engine('sqlite:///:memory:', echo=True)
 
# Define the base class for ORM models
Base = declarative_base()
 
# Define the ORM model
class User(Base):
    __tablename__ = 'users'
 
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
 
# Create the table in the database
Base.metadata.create_all(engine)
 
# Create a session to interact with the database
Session = sessionmaker(bind=engine)
session = Session()
 
# Insert a new user
new_user = User(name="John Doe", age=30)
session.add(new_user)
session.commit()
 
# Query the database
user = session.query(User).filter_by(name="John Doe").first()
print(f"User: {user.name}, Age: {user.age}")

Now onto SQLAlchemy Core.

Using SQLAlchemy Core

SQLAlchemy Core, on the other hand, offers a lower-level approach that provides direct control over database operations, providing features like schema definition, a powerful SQL expression language, and the engine for database connectivity.

The schema/types system in SQLAlchemy Core allows developers to define the structure of their database tables explicitly, including data types, constraints, and indexes, using Python constructs. This ensures a consistent and clear definition of the database schema that can be programmatically manipulated.

The SQL expression language is the central feature of SQLAlchemy Core, enabling developers to build SQL queries dynamically and expressively using Python. This language allows for the construction of complex queries and data manipulations while maintaining full control over the generated SQL, making it ideal for developers who need fine-grained control over their database interactions.

Finally, the engine in SQLAlchemy Core is responsible for establishing and managing connections to the database, executing SQL statements, and handling transactions. It provides a robust and flexible foundation for working with various database backends, offering both synchronous and asynchronous interaction modes.

Here's how to create a table, insert a record, and query data using the Core:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, insert, select
 
# Define the database engine
engine = create_engine('sqlite:///:memory:', echo=True)
 
# Define the metadata object for schema management
metadata = MetaData()
 
# Define the table using SQLAlchemy Core
users_table = Table(
    'users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('age', Integer)
)
 
# Create the table in the database
metadata.create_all(engine)
 
# Insert a new user using Core
with engine.connect() as connection:
    insert_stmt = users_table.insert().values(name="John Doe", age=30)
    connection.execute(insert_stmt)
 
# Query the database using Core
with engine.connect() as connection:
    select_stmt = select(users_table).where(users_table.c.name == "John Doe")
    result = connection.execute(select_stmt)
    user = result.fetchone()
    print(f"User: {user.name}, Age: {user.age}")

Now, let's define what Flask-SQLAlchemy is and why you should use it.

What is Flask-SQLAlchemy in Python?

Flask-SQLAlchemy is a Flask extension that integrates SQLAlchemy with Flask, simplifying the process of database management in Flask applications. It combines the power and flexibility of SQLAlchemy with the simplicity and ease of use that Flask developers expect.

By providing a layer of abstraction over SQLAlchemy, in fact, Flask-SQLAlchemy makes it easier to set up, configure, and manage databases in Flask-based projects.

Key Features of Flask-SQLAlchemy for Python

Flask-SQLAlchemy comes with several key features that make it an excellent choice for database management in Flask applications, including:

• Easy configuration: Flask-SQLAlchemy simplifies the configuration of SQLAlchemy with Flask. You can configure your database connection and other settings through Flask’s configuration system, making it easy to manage different environments (for example: development, testing, and production environments).
• Automatic tables creation: If you are connecting to a database that already has tables, Flask-SQLAlchemy can automatically create tables based on your defined models when the application starts. This reduces the need for manual table creation and synchronization.
• Integration with Flask’s application context: Flask-SQLAlchemy leverages Flask's app context, ensuring that your database session is properly managed across requests. This simplifies transaction management and ensures that your database operations are handled correctly in a web environment.
• Simplified querying: With Flask-SQLAlchemy, querying the database becomes straightforward, as it extends SQLAlchemy’s query interface to work seamlessly within Flask. This allows you to use familiar Flask patterns when interacting with your database.

How Flask-SQLAlchemy Simplifies Database Management

Flask-SQLAlchemy reduces the complexity of setting up and using SQLAlchemy with Flask by:

• Streamlining configuration and setup: With Flask-SQLAlchemy, you don’t need to manually initialize and configure SQLAlchemy. The extension takes care of this by automatically binding SQLAlchemy to your Flask application and using Flask’s configuration to manage database settings.
• Providing simplified session management: In a web application, managing database sessions can be tricky. Flask-SQLAlchemy automatically manages the database session for each request, ensuring that sessions are properly opened and closed, reducing the risk of transaction errors and memory leaks.
• Enhancing development speed: By abstracting away much of the boilerplate code needed to set up and interact with a database, Flask-SQLAlchemy allows developers to focus more on building features rather than on database management. The extension’s tight integration with Flask’s development ecosystem also means that it plays well with other Flask extensions and tools.

Setting Up Flask-SQLAlchemy

So, now that we've presented Flask-SQLAlchemy, let's provide a practical example of how to use it in a Flask application.

You can find all the code stored in this public repo.

Prerequisites

To replicate this example, your system must satisfy the following prerequisites:

• Ubuntu (any recent version) for Windows Subsystem for Linux (WSL): The steps in this tutorial are tailored for Ubuntu in WSL, but they are adaptable to other Linux distributions, especially not under WLS, or for different Operating Systems (but you'll need to adapt the code).
• Python 3.6+ and pip: Flask-SQLAlchemy requires Python 3.6 or higher. We’ll also install everything we need with pip.
• MySQL: The steps in this guide are tested with MySQL installed (on Ubuntu for WSL).
• Basic knowledge of Python and Flask: This tutorial requires familiarity with virtual environments, Flask application structure, and basic terminal commands.

Requirements

For Flask to do its job, we first need to create a new database in MySQL.

If you are unfamiliar with it, you can follow this guide.

Step 1: Launch MySQL

Launch MySQL by typing:

$ sudo service mysql start

NOTE: This is particular for Ubuntu on WSL. On Ubuntu (not under WSL) the command changes ($ sudo systemctl mysql start).

Step 2: Access MySQL

Access MySQL by typing:

$ mysql -u root -p

NOTE: Here you'll be asked to insert the password you set when you installed MySQL.

Step 3: Create the Database

Everything is now set up to create a database. You can create it like so:

CREATE DATABASE flask_db;

NOTE: You will have to insert this database name in the Flask app later.

Then, create a user and set its password:

CREATE USER 'flaskuser'@'localhost' IDENTIFIED BY 'your_password';

NOTE: You will have to insert this user and password in the Flask app later.

Finally, grant privileges to the user:

GRANT ALL PRIVILEGES ON flask_db.* TO 'flaskuser'@'localhost';
 
FLUSH PRIVILEGES;

Installation and Configuration

Now, let's install all the necessary libraries for using Flask and create a web app example using FlaskSQL-Alchemy.

Step 1: Create the Project Directory

First, create the main folder that will host the project via the terminal:

$ mkdir flask_sqlalchemy_example
$ cd flask_sqlalchemy_example

Step 2: Create and Activate a Virtual Environment

Before installing the needed libraries, we suggest creating a virtual environment:

$ python3 -m venv venv
$ source venv/bin/activate

Step 3: Install Flask, Flask-SQLAlchemy, and Other Libraries

You can now install everything that's needed for this tutorial:

$ pip install Flask Flask-SQLAlchemy PyMySQL cryptography Flask-WTF

Step 4: Create the Structure of the Project

Finally, to recreate the demo project, create the following project structure:

flask_sqlalchemy_example/
│
├── app/
│   ├── __init__.py
│   ├── models.py
│   ├── routes.py
│   ├── forms.py
│   ├── templates/
│       └── index.html
│
├── venv/
│   └── ... (virtual environment files)
│
├── config.py
└── run.py

Create the Flask App

Now everything is set up to create the Flask app.

The application we're creating leverages Flask-SQLAlchemy to build a lightweight web application that handles user data management. In particular, we'll create a web app where users can compile a form and input their username and email address.

Once they fill out the form and submit it, the application validates the data. If the input is valid, the app adds a new user record to the MySQL database.

Step 1: Create the config.py File

First, create the config.py file to hold the application configuration, including the database URI:

import os
 
basedir = os.path.abspath(os.path.dirname(__file__))
 
class Config:
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'a_very_secret_key'
    SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://flaskuser:your_password@localhost/flask_db'
    SQLALCHEMY_TRACK_MODIFICATIONS = False

NOTE: Here you have to use the user, password, and database name set when you created the database in MySQL.

Step 2: Create the __init__.py File

Create the __init__.py file inside the app/ directory to initialize the Flask app and SQLAlchemy:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from config import Config
 
db = SQLAlchemy()
 
def create_app():
    app = Flask(__name__)
    app.config.from_object(Config)
 
    db.init_app(app)
 
    with app.app_context():
        from . import routes, models
        db.create_all()
        routes.register_routes(app)
 
    return app

Step 3: Create a Model

Now, let's define a model representing a user in app/models.py:

from app import db
 
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
 
    def __repr__(self):
        return f'<User {self.username}>'

This is a direct representation of a database table within SQLAlchemy’s ORM, allowing for efficient querying and manipulation of user records.

Step 4: Define a Basic Route

In app/routes.py, create a route to display a list of users:

from flask import render_template, redirect, url_for, flash
from app import db
from app.models import User
from app.forms import UserForm
 
def register_routes(app):
    @app.route('/', methods=['GET', 'POST'])
    def index():
        form = UserForm()
        if form.validate_on_submit():
            username = form.username.data
            email = form.email.data
            user = User(username=username, email=email)
            db.session.add(user)
            db.session.commit()
            flash('User added successfully!', 'success')
            return redirect(url_for('index'))
 
        users = User.query.all()
        return render_template('index.html', form=form, users=users)

Step 5: Create a form

Create a form for users in the file app/forms.py:

from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField
from wtforms.validators import DataRequired, Email, Length
 
class UserForm(FlaskForm):
    username = StringField('Username', validators=[DataRequired(), Length(min=1, max=64)])
    email = StringField('Email', validators=[DataRequired(), Email(), Length(max=120)])
    submit = SubmitField('Add User')

Step 6: Create a Template for the Web Application

Create a template for users to interact with the web app. Add their usernames and emails with Jinja like so:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>User Management</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
    <div class="container mt-4">
        <h1 class="mb-4">User Management</h1>
 
        <!-- Flash messages -->
        {% with messages = get_flashed_messages(with_categories=true) %}
            {% if messages %}
                <div class="alert alert-{{ messages[0][0] }} alert-dismissible fade show" role="alert">
                    {{ messages[0][1] }}
                    <button type="button" class="close" data-dismiss="alert" aria-label="Close">
                        <span aria-hidden="true">&times;</span>
                    </button>
                </div>
            {% endif %}
        {% endwith %}
 
        <!-- User Form -->
        <div class="card mb-4">
            <div class="card-body">
                <form method="POST">
                    {{ form.hidden_tag() }}
                    <div class="form-group">
                        {{ form.username.label(class="form-label") }}
                        {{ form.username(class="form-control") }}
                    </div>
                    <div class="form-group">
                        {{ form.email.label(class="form-label") }}
                        {{ form.email(class="form-control") }}
                    </div>
                    <div class="form-group">
                        {{ form.submit(class="btn btn-primary") }}
                    </div>
                </form>
            </div>
        </div>
 
        <!-- User List -->
        <h2 class="mb-3">List of Users</h2>
        <table class="table table-striped">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Username</th>
                    <th>Email</th>
                </tr>
            </thead>
            <tbody>
                {% for user in users %}
                <tr>
                    <td>{{ user.id }}</td>
                    <td>{{ user.username }}</td>
                    <td>{{ user.email }}</td>
                </tr>
                {% endfor %}
            </tbody>
        </table>
    </div>
 
    <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.3/dist/umd/popper.min.js"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
</body>
</html>

Step 7: Run the Application

Finally, create the run.py file at the root of the project to run the application:

from app import create_app
 
app = create_app()
 
if __name__ == '__main__':
    app.run(debug=True)

To run the app, type the following via CLI:

$ python3 run.py

And here's the expected result:

Wrapping Up

In this article, we've presented SQLAlchemy as a powerful tool to manage databases using Python.

Specifically, using Flask-SQLAlchemy makes it easier to manage databases as this extension leverages SQLAlchemy specifically for Flask apps.

The example we've run through shows how easy it is to use this library.

Happy coding!

P.S. If you'd like to read Python posts as soon as they get off the press, subscribe to our Python Wizardry newsletter and never miss a single post!