Flask SQLAlchemy [In-Depth Tutorial]

Introduction

Flask is a minimalist web framework in Python that provides the essential functionalities needed to build web applications. It follows the WSGI (Web Server Gateway Interface) toolkit and is characterized by its simplicity, flexibility, and fine-grained control over configurations. Built on top of Werkzeug and Jinja2, Flask enables rapid development and clean, maintainable architecture for both small-scale projects and large enterprise solutions.

SQLAlchemy is a highly acclaimed Object-Relational Mapping (ORM) library for Python. It abstracts the underlying database system, allowing developers to interact with databases using Pythonic constructs instead of writing SQL queries. SQLAlchemy supports multiple database backends and provides rich query generation capabilities. It comes with a powerful and customizable API that accommodates both simple use-cases and complex database schemas.

Combining Flask with SQLAlchemy, often referred to as "Flask SQLAlchemy," forms a potent combination for web application development. This integrated approach offers streamlined database operations, type safety, code reusability, simplified session management, and scalability.

 

Setting up Environment

Setting up the environment for Flask SQLAlchemy is like preparing your kitchen before you start cooking. You need the right tools and ingredients. Here's what you'll need:

Required Packages and Installation

First things first, let's make sure your Ubuntu package list is up to date. Open your terminal and run:

sudo apt update

Flask and SQLAlchemy are Python libraries, so you need Python installed. Most Ubuntu systems come with Python pre-installed. You can check if Python is already installed by running:

$ python3 --version
Python 3.10.12

If it's not installed, or you want to update it, run:

sudo apt install python3

Pip is a package installer for Python. It's like an app store for Python libraries. To install pip, run:

sudo apt install python3-pip

 

Create a Virtual Environment (Optional but Recommended)

A virtual environment helps you manage Python packages for different projects separately. Think of it as creating a separate basket for each project's ingredients.

To install the virtual environment package, run:

sudo apt install python3-venv

Create a new folder where you want your project to live and navigate into it:

mkdir my_flask_project
cd my_flask_project

Now create the virtual environment:

python3 -m venv myenv

Activate the environment:

source myenv/bin/activate

 

Install Flask and Flask-SQLAlchemy

Now that you're in your project folder and the virtual environment is activated, install Flask and SQLAlchemy using pip:

pip3 install Flask-SQLAlchemy

After installation, you can verify if flask_sqlalchemy is successfully installed by running:

pip3 freeze

This will list all the installed packages, and you should see Flask-SQLAlchemy in the list.

 

Initialization

Initializing your project involves some fundamental steps that prepare you to start building. It's like turning on your stove and getting your cooking utensils ready before you start cooking.

 

Importing Modules

First, you'll need to "import" (or bring into your project) the tools you installed earlier—Flask and SQLAlchemy. Think of this as taking the spices and vegetables out of the cupboard and placing them on the kitchen counter, so they're ready to use.

In a Python file (often named app.py), you'll write:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

This line tells Python: "Hey, we're going to use Flask and SQLAlchemy in this project, so make them available to us."

 

Flask-SQLAlchemy Initialization

Now that Flask and SQLAlchemy are imported, the next step is to initialize or "start" them.

Initialize Flask: Creating a Flask "app" is like setting up your cooking station. Everything you build will be centered around this.

app = Flask(__name__)

The app variable now holds your Flask application.

Initialize SQLAlchemy: SQLAlchemy needs to know it's going to work with this Flask app. So, we introduce them to each other.

db = SQLAlchemy(app)

Here, db is a variable that you'll use to interact with your database through SQLAlchemy.

Here's how your app.py should look so far:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy(app)

 

Configuration

Database URI Configuration

Before you can start interacting with a database, you have to tell Flask and SQLAlchemy where to find it. This is similar to entering an address into your GPS before you can start driving. In technical terms, this address is called a Database URI (Uniform Resource Identifier).

Here's how you specify which database you want to use:

SQLite: A simple, file-based database. Good for development and testing.

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'

This tells Flask SQLAlchemy to use an SQLite database file named site.db in your project directory.

PostgreSQL: A more robust, production-ready database.

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://username:password@localhost/db_name'

Here, replace username, password, localhost, and db_name with your PostgreSQL username, password, the localhost, and the database name, respectively.

Place this line of code in your app.py file, right after you initialize the db variable.

 

Additional Configuration Options

Aside from telling your app where the database is, you might want to set some additional configurations.

Track Modifications: SQLAlchemy will ping you with a warning if you don't explicitly set whether or not to track modifications. While this feature can be useful, it consumes memory and is turned off by default to save resources.

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

Commit on Teardown: This automatically commits database changes at the end of each web request. In most cases, you won't need to enable this, but it's good to know it exists.

app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True

Note: This is generally not recommended unless you're very sure of your use case, as it might lead to unexpected behavior.

Here’s how your app.py file would look after adding the database URI and additional configurations:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'  # or your PostgreSQL URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False  # optional

db = SQLAlchemy(app)

 

Example:

Let us create and configure our app.py to connect to SQLite database.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'  # SQLite database named 'site.db'
db = SQLAlchemy(app)

 

Create a User Model

Below your database initialization in app.py, define a User class to represent a user in the database.

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)

Open the terminal, navigate to your project folder, and start the Flask shell.

flask shell

Then run the following commands to create the database.

$ flask shell
Python 3.10.12 (main, Jun 11 2023, 05:26:28) [GCC 11.4.0] on linux
App: app
Instance: /home/deepak/my_flask_project/instance
>>> from app import db
>>> db.create_all()

Check your project directory for a site.db file. This confirms that the database was created.

$ ls -l instance/
total 12
-rw-r--r-- 1 deepak deepak 12288 Aug 27 11:25 site.db

While still in the Flask shell, run the following commands to add a user.

>>> new_user = User(username='deepak')
>>> db.session.add(new_user)
>>> db.session.commit()

Run the following command to check if the user was added to the database.

>>> user = User.query.filter_by(username='deepak').first()
>>> print(user.username)
deepak

 

Database Models

Defining Models

In the context of Flask SQLAlchemy, a model is a Python class that represents an entity in your application. This could be anything—users, blog posts, products, etc. Think of a model like a blueprint for a house. It defines the structure but is not the house itself.

To define a model, create a Python class that inherits from db.Model:

class User(db.Model):
    pass

 

Fields and Data Types

Just as a house blueprint specifies where the kitchen, bedroom, and bathrooms will be, a model specifies the "fields" that each entity will have. For instance, a User might have an id, username, and email.

Each field is represented by a class variable and has an associated "data type" to indicate what kind of data it will hold:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

Here, db.Integer and db.String are data types, and primary_key=True indicates that the id is the unique identifier for each user.

 

Relationships Between Models (One-to-One, One-to-Many, Many-to-Many)

Life isn't lived in isolation, and neither are models. They often have relationships with one another.

One-to-One: Let's say each User has one Profile.

class Profile(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), unique=True)
    user = db.relationship('User')

Here, db.ForeignKey establishes a connection between Profile and User.

 

One-to-Many: One user can have multiple Posts.

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    user = db.relationship('User')

In this case, db.ForeignKey connects each Post to a User, but it's not unique, allowing for multiple posts per user.

 

Many-to-Many: Suppose a User can belong to multiple Groups, and a Group can have multiple Users.

To manage this, you'd usually have an intermediate "association" table:

memberships = db.Table('memberships',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('group_id', db.Integer, db.ForeignKey('group.id'))
)

class Group(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    users = db.relationship('User', secondary=memberships)

db.relationship and secondary establish the many-to-many link between User and Group.

 

Session Management

Role of Sessions in SQLAlchemy

In SQLAlchemy, a session is like a shopping cart on an e-commerce website. You can add items (data), modify them, or remove them from the cart. Nothing is final until you "check out," or in technical terms, "commit" the session.

Sessions allow you to stage changes to the database. This means you can prepare a bunch of changes and either apply them all at once or discard them if something goes wrong.

 

Committing Data

"Committing" is like hitting the "Save" button in a word processor. It takes all the staged changes and applies them to the database. In SQLAlchemy, you commit changes by calling the commit method on your session object.

Here's an example. Let's say you've created a new User object:

new_user = User(username='john', email='john@example.com')

To add this new user to the database:

db.session.add(new_user)

But remember, the above code is like putting an item in your shopping cart—it's not yours until you check out.

To "check out," or save this new user to the database, you commit the session:

db.session.commit()

 

Rollbacks

Imagine you're painting a picture and you make a mistake—you'd want to undo it, right? That's what "rollback" is for in SQLAlchemy. If you've staged some changes that you want to discard, you can "rollback" the session to its state before those changes were staged.

For example, if you realize you made an error in adding a new user:

db.session.rollback()

This will undo all the changes that were staged but not yet committed, essentially giving you a clean slate.

 

Example:

We will update our app.py to include a new add_user function which takes username as a parameter and adds it to the database

def add_user(username):
    existing_user = User.query.filter_by(username=username).first()
    if existing_user is None:
        new_user = User(username=username)
        db.session.add(new_user)
        db.session.commit()
        print(f"User {username} added.")
    else:
        print("Username already exists.")

To show how rollback works we will intentionally create an error by trying to add duplicate username and that should trigger a session rollback:

def add_user_with_error(username):
    new_user = User(username=username)
    db.session.add(new_user)
    try:
        db.session.commit()
        print(f"User {username} added.")
    except Exception as e:
        db.session.rollback()
        print(f"An error occurred: {e}. Rollback initiated.")

Verify these functions:

$ flask shell
Python 3.10.12 (main, Jun 11 2023, 05:26:28) [GCC 11.4.0] on linux
App: app
Instance: /home/deepak/my_flask_project/instance
>>> from app import add_user, add_user_with_error
>>> add_user('John')
User John added.
>>> User.query.filter_by(username='John').first()
<User 3>
>>> add_user('John')
Username already exists.
>>> add_user_with_error('John')
An error occurred: (sqlite3.IntegrityError) UNIQUE constraint failed: user.username
[SQL: INSERT INTO user (username) VALUES (?)]
[parameters: ('John',)]
(Background on this error at: https://sqlalche.me/e/20/gkpj). Rollback initiated.

As you can see, a rollback was initiated when error was encountered.

 

Querying Data

CRUD Operations

CRUD stands for Create, Read, Update, and Delete—these are the basic operations you'll perform on your data.

Create: You've already seen how to add a new user with db.session.add().

Read: To read data, you'll often use the query attribute. For example, to get all users:

all_users = User.query.all()

Update: To update a record, you fetch it first, modify the attributes, and then commit the changes.

user = User.query.get(1)
user.username = 'new_username'
db.session.commit()

Delete: To delete a record, you fetch it first, then delete it, and commit the change.

user = User.query.get(1)
db.session.delete(user)
db.session.commit()

 

Filtering and Sorting

Just as you can filter and sort your email by date, sender, or subject, you can filter and sort database queries in SQLAlchemy.

Filtering: To get all users with a specific username, you might do:

users = User.query.filter_by(username='john').all()

Sorting: To sort users by their username:

users = User.query.order_by(User.username).all()

You can also combine these:

users = User.query.filter_by(email='john@example.com').order_by(User.username).all()

 

Aggregation Functions

Aggregation functions let you perform calculations over a range of rows in a database table. These are operations like counting, summing, or finding the average.

Count: To count the number of users:

user_count = User.query.count()

Average, Min, Max: SQLAlchemy supports more advanced aggregations through functions like func.avg, func.min, and func.max.

from sqlalchemy import func

avg_age = db.session.query(func.avg(User.age)).scalar()

This would calculate the average age of all users.

 

Example:

We already have written add_user function to CREATE a user. Next we will update app.py with this function to UPDATE a user:

def update_username(old_username, new_username):
    user = User.query.filter_by(username=old_username).first()
    if user:
        user.username = new_username
        db.session.commit()
        print(f"Username updated to {new_username}.")
    else:
        print("User not found.")

Similarly to DELETE a user:

def delete_user(username):
    user = User.query.filter_by(username=username).first()
    if user:
        db.session.delete(user)
        db.session.commit()
        print(f"User {username} deleted.")
    else:
        print("User not found.")

Now let's use these functions:

(myenv) deepak@deepak-VirtualBox:~/my_flask_project$ flask shell
Python 3.10.12 (main, Jun 11 2023, 05:26:28) [GCC 11.4.0] on linux
App: app
Instance: /home/deepak/my_flask_project/instance
>>> from app import add_user, update_username, delete_user
>>> add_user('user2')
User user2 added.
>>> add_user('user3')
User user3 added.
>>> add_user('user4')
User user4 added.
>>> add_user('user5')
User user5 added.
>>> users = User.query.all()
>>> print(users)
[<User 1>, <User 2>, <User 3>, <User 4>, <User 5>, <User 6>, <User 7>]
>>> for user in users:
...   print(user.username)
... 
deepak
user1
John
user2
user3
user4
user5

>>> update_username('user1', 'user100')
Username updated to user100.
>>> for user in users:
...   print(user.username)
... 
deepak
user100
John
user2
user3
user4
user5

>>> delete_user('user100')
User user100 deleted.

>>> sorted_users = User.query.order_by(User.username).all()
>>> for user in sorted_users:
...   print(user.username)
... 
John
deepak
user2
user3
user4
user5

>>> filtered_users = User.query.filter(User.username.like('u%')).all()
>>> for user in filtered_users:
...   print(user.username)
... 
user2
user3
user4
user5

 

Joins and Relationships

Querying Across Tables

Sometimes, the data you need isn't stored in a single table. For example, if you have a User table and a Post table, you might want to retrieve all posts from a specific user. This is where joins come into play.

SQLAlchemy allows you to perform joins using the join function, making it easier to query across multiple tables.

For instance, to find all posts by a specific user, you can do something like:

from sqlalchemy.orm import joinedload

user_posts = db.session.query(User).join(Post).filter(User.id == Post.user_id).all()

 

Working with Foreign Keys

Foreign keys are the glue that holds these tables together. When you define a model, you may add a field that is a ForeignKey. This tells SQLAlchemy that there's a link between this field and a field in another table.

For example, in the Post model, you could have a user_id field like this:

user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

 

Lazy vs Eager Loading

When querying data, SQLAlchemy gives you options for how to handle related objects.

1. Lazy Loading: This is the default. When you query a User, SQLAlchemy won't load the related Post objects until you specifically access them. This is resource-efficient, but it could be slow if you end up needing to access the related objects frequently.
2. Eager Loading: With eager loading, SQLAlchemy will load related objects at the same time as the parent object. This can speed up your application but consumes more resources initially.

For example, if you know you'll need both User and their related Post objects, you could use eager loading like this:

from sqlalchemy.orm import joinedload

users = User.query.options(joinedload('posts')).all()

This will load all User objects and their related Post objects in a single query, potentially improving performance.

 

Form Handling

Using Flask-WTF with SQLAlchemy Models

Flask-WTF is a Flask extension that integrates the WTForms library with Flask. With Flask-WTF, it becomes relatively straightforward to handle form submissions, validations, and the rendering of form fields.

To make this work with SQLAlchemy, you can design your form classes to mirror your SQLAlchemy models. For example, if you have a User model with a username and email, you can create a UserForm class like this:

from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField
from wtforms.validators import DataRequired

class UserForm(FlaskForm):
    username = StringField('Username', validators=[DataRequired()])
    email = StringField('Email', validators=[DataRequired()])
    submit = SubmitField('Submit')

Here, DataRequired is a simple validator that ensures the field is not submitted empty.

 

Validation with SQLAlchemy

Validations can happen at two levels: form-level and database-level.

1. Form-Level: This is what you saw above with WTForms validators like DataRequired. These validators ensure that the data coming from the form meets certain criteria before it even gets processed by your backend.
2. Database-Level: SQLAlchemy also lets you enforce some constraints at the database level. For example, if you've set the email field in your User model to be unique, SQLAlchemy will throw an error if you try to add a duplicate email to the database.

email = db.Column(db.String(120), unique=True, nullable=False)

You often want to catch these errors in your Flask routes and provide appropriate feedback to the user. Here's a simplified example:

from flask import flash, redirect

@app.route('/add_user', methods=['POST'])
def add_user():
    form = UserForm()
    if form.validate_on_submit():
        new_user = User(username=form.username.data, email=form.email.data)
        try:
            db.session.add(new_user)
            db.session.commit()
            flash('User added successfully!')
        except:
            db.session.rollback()
            flash('Error: Could not add user.')
    return redirect('/')

 

Migrations

Alembic for Migration Management

Alembic is a database migration tool that works beautifully with SQLAlchemy. It allows you to alter your database schema in a structured and organized manner, without needing to recreate the database from scratch.

Here are some key terms and steps to get started:

Initialize Alembic: First, you initialize Alembic configuration in your project directory. This creates a folder, usually named alembic, containing configuration files.

alembic init alembic

Generate Migration Script: Whenever you make changes to your SQLAlchemy models, you'll need to generate a migration script. This script contains the changes to be made to the database schema.

alembic revision --autogenerate -m "Added new field to User model"

Run Migrations: After generating the migration script, the next step is to apply these changes to the actual database.

alembic upgrade head

Downgrade: If you need to undo a migration, Alembic allows you to downgrade to a previous version.

alembic downgrade -1

History and Status: Alembic allows you to view the history of your migrations as well as the current status.

alembic history
alembic current

 

Security

Preventing SQL Injection

SQL Injection is a security vulnerability that allows an attacker to interfere with the SQL queries your application makes. Fortunately, SQLAlchemy uses parameterized queries, which makes it nearly impossible for an SQL injection to occur, provided you use SQLAlchemy's API properly.

For instance, when fetching data based on user input, avoid composing SQL queries using string concatenation or formatting. Instead, use the query API provided by SQLAlchemy:

# Avoid this
dangerous_query = "SELECT * FROM users WHERE username='" + user_input + "'"

# Do this
safe_query = User.query.filter_by(username=user_input).first()

By sticking with SQLAlchemy's query API, you help ensure that any data passed into the queries is properly escaped and safe to use.

 

Secure Configuration

Apart from SQL-related vulnerabilities, you also need to take care of the security of your application configuration. Here are some guidelines:

Environment Variables: Sensitive information like your database URI should not be hardcoded in your application. Instead, use environment variables to securely manage this information.

import os
DATABASE_URI = os.environ.get('DATABASE_URI')

Least Privilege: When setting up your database user, grant them only the permissions they need. They shouldn’t need permission to drop tables in a production application, for example.

Secure Sessions: Flask’s session management is secure when configured properly. Always use secure cookies in production by setting the SESSION_COOKIE_SECURE option to True.

app.config['SESSION_COOKIE_SECURE'] = True

Flask Configuration: Flask allows you to control various security features through its configuration system. Take advantage of this to disable features you don’t need or to enable additional security measures.

app.config['WTF_CSRF_ENABLED'] = True  # Enables CSRF protection

Monitoring and Logging: Implement comprehensive logging and monitoring to detect any unauthorized access or unusual behavior in real-time.

 

Extending Flask-SQLAlchemy

Custom Query Classes

Flask-SQLAlchemy allows you to define custom query classes to add additional methods or override existing ones. This is incredibly useful for encapsulating commonly used queries or custom database operations. You can set your custom query class when defining your model.

Here's a simple example:

from flask_sqlalchemy import BaseQuery

class UserQuery(BaseQuery):
    def find_by_username(self, username):
        return self.filter_by(username=username).first()

class User(db.Model):
    query_class = UserQuery
    # ... existing fields ...

# Usage
user = User.query.find_by_username("john_doe")

By doing this, you've made it easier to execute specific queries without repeating yourself.

 

Mixins and Utility Functions

You can use Mixins to add common attributes or methods to multiple models. This avoids duplicating code and makes it easier to maintain. For instance, you might want all your models to have creation and modification timestamps.

from datetime import datetime

class TimestampMixin:
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class User(TimestampMixin, db.Model):
    # ... existing fields ...

Utility functions can serve a similar purpose but are typically standalone functions that you can import and use throughout your project. For example, you might have a utility function that handles complex filtering criteria.

def complex_filter(query, conditions):
    for field, value in conditions.items():
        query = query.filter(getattr(MyModel, field) == value)
    return query

# Usage
filtered_query = complex_filter(User.query, {'field1': 'value1', 'field2': 'value2'})

 

Debugging and Error Handling

Common Pitfalls

1. N+1 Query Problem: As mentioned before, this is a common issue where separate queries are made for each related object, leading to performance degradation. Eager loading can help resolve this issue.
2. Transaction Errors: If you're seeing issues like "Session’s transaction has been rolled back due to a previous exception", it usually means that an error occurred within a database transaction. Identifying the root cause can often be tricky.
3. Misconfigured Database URI: Ensure that your database URI is correct, and that the database server is reachable. A misconfigured URI can lead to a variety of errors.
4. Schema Mismatch: Make sure that your SQLAlchemy models are in sync with your actual database schema, especially after migrations.
5. Insecure Dynamic Queries: Ensure that you're not creating SQL queries through string concatenation with user-supplied data, as this opens up risks for SQL injection.

 

Debugging Tools

Flask Debug Mode: Enabling Flask's built-in debug mode provides you with a stack trace and interactive debugger right in the browser when an error occurs.

app.run(debug=True)

SQLAlchemy Echo: To see the actual SQL queries being executed, you can enable SQLAlchemy’s echo feature. This is particularly helpful for debugging SQL errors or optimizing queries.

app.config['SQLALCHEMY_ECHO'] = True

Logging: Use Python’s built-in logging framework to log warnings and errors. This can help in tracking down issues that are sporadic or hard to reproduce.

import logging

logging.basicConfig(filename='error.log', level=logging.DEBUG)

Interactive Debuggers: Tools like pdb or ipdb can be incredibly useful for step-by-step debugging.

import pdb; pdb.set_trace()

Automated Testing: Creating unit tests and integration tests can help you catch errors before they make it into your live application. Frameworks like pytest can assist in this.

Third-Party Tools: There are several excellent third-party tools designed for debugging Flask apps, like Flask-DebugToolbar, which provides an interactive debugging panel in your browser.

 

Deployment Considerations

Database Connection Pooling

Database Connection Pooling refers to the pre-allocation of database connections so that they can be reused when future requests to the database are required. SQLAlchemy comes with a built-in connection pooling library, allowing you to efficiently manage database connections.

Example:

When configuring your Flask application, you can specify options for SQLAlchemy’s connection pool:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# Configure SQLAlchemy
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://user:password@localhost/mydb'
app.config['SQLALCHEMY_POOL_SIZE'] = 20  # Number of connections to keep open
app.config['SQLALCHEMY_MAX_OVERFLOW'] = 5  # Allow up to 5 additional connections

db = SQLAlchemy(app)

 

Transaction Management

Transactions ensure that a series of operations are completed successfully before the changes are committed to the database. If any operation fails, the transaction can be rolled back, maintaining data integrity. SQLAlchemy provides built-in support for transaction management.

Example:

You can use the db.session to manage transactions:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from models import User  # Assume User model is defined in a separate 'models.py'

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://user:password@localhost/mydb'
db = SQLAlchemy(app)

# Transaction Example
try:
    new_user1 = User(username='user1', email='user1@email.com')
    new_user2 = User(username='user2', email='user2@email.com')
    
    db.session.add(new_user1)
    db.session.add(new_user2)
    db.session.commit()  # Commit the transaction
    
except:
    db.session.rollback()  # Roll back the transaction in case of error
    raise

finally:
    db.session.close()  # Close the session

In this example, either both new_user1 and new_user2 are added to the database, or neither are, ensuring data integrity.

 

Summary

In this article we used Flask SQLAlchemy ORM to communicate with a sqlite3 database directly from python. We created python objects and Flask SQLAlchemy converted them to SQL tables. Firstly, we created a table for football teams called Team, the table contains two columns one column for the SQL id and the other is for the team's name. Secondly, we created a table fro football players called Player , the player's table consists of 5 columns, the player's SQL id, the player's name, the player's jersey number, the SQL id that the player belongs to and a final fifth  relationship column that allows a team to access the players that belong to it.

 

Sources

Flask-SQLAlchemy