Skip to content
W3S101

Flask - SQLAlchemy

Flask – Integrating with SQLAlchemy

Section titled “Flask – Integrating with SQLAlchemy”

While Flask doesn’t mandate a specific database or Object-Relational Mapper (ORM), SQLAlchemy is the de facto standard for working with relational databases (like PostgreSQL, MySQL, SQLite) in the Python ecosystem. Flask-SQLAlchemy is a Flask extension that seamlessly integrates SQLAlchemy, providing helpful defaults and utilities for managing database connections, sessions, and models within your Flask application.

What is an ORM?

Section titled “What is an ORM?”

Object-Relational Mapping (ORM) is a technique that lets you interact with your database, like querying data or saving records, using an object-oriented paradigm within your preferred programming language (Python, in this case). Instead of writing raw SQL queries (e.g., SELECT * FROM users WHERE city = 'New York'), you work with Python objects and methods (e.g., User.query.filter_by(city='New York').all()). The ORM handles the translation between your Python code and the underlying SQL database operations.

Benefits of using Flask-SQLAlchemy:

  • Abstraction: Hides differences between various SQL databases.
  • Productivity: Reduces boilerplate code for common database operations (CRUD - Create, Read, Update, Delete).
  • Maintainability: Maps database tables to Python classes (Models), making the data structure clear and easier to manage.
  • Integration: Manages database sessions and connections within the Flask request lifecycle automatically.

Setting up Flask-SQLAlchemy

Section titled “Setting up Flask-SQLAlchemy”

Step 1: Install the extension.

pip install Flask-SQLAlchemy

You will also need a database driver for the specific database you intend to use (e.g., psycopg2-binary for PostgreSQL, mysqlclient or PyMySQL for MySQL). SQLite is built into Python, so no extra driver is needed for it.

Step 2: Configure the database URI.

In your Flask application setup, configure the SQLALCHEMY_DATABASE_URI. This string tells SQLAlchemy where your database is located.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)


# Configuration Examples:
# SQLite (relative path)
# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mydatabase.db'
# SQLite (absolute path)
# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////path/to/mydatabase.db'
# PostgreSQL
# app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:password@host:port/dbname'
# MySQL
# app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://user:password@host:port/dbname'


# Using SQLite for this example
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///students.sqlite3'
# Optional: Disable SQLAlchemy event system if not needed, improves performance
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# Required for flashing messages later in the example
app.config['SECRET_KEY'] = 'your-secret-key'

Step 3: Initialize the extension.

Create an instance of the SQLAlchemy class, passing your Flask application object.

db = SQLAlchemy(app)

This db object becomes your main access point for SQLAlchemy features, including defining models and managing sessions.

Step 4: Define Models.

Models are Python classes that inherit from db.Model. Each class represents a table in your database, and class attributes (defined using db.Column) represent columns in that table.

class Student(db.Model):  # Use singular, capitalized names for models
    # Define table name explicitly (optional, defaults based on class name)
    # __tablename__ = 'students_table'


    # Define columns
    id = db.Column(db.Integer, primary_key=True) # Primary key column
    name = db.Column(db.String(100), nullable=False) # String, cannot be null
    city = db.Column(db.String(50))
    address = db.Column(db.String(200))
    pin_code = db.Column(db.String(10)) # Renamed 'pin' to 'pin_code' for clarity


    # Optional: Define an __init__ method for easy object creation
    def __init__(self, name, city, address, pin_code):
        self.name = name
        self.city = city
        self.address = address
        self.pin_code = pin_code


    # Optional: Define a __repr__ method for helpful debugging output
    def __repr__(self):
        return f'<Student {self.name}>'

Step 5: Create Database Tables.

Before you can interact with the database, the tables defined by your models need to be created. For simple cases or initial setup, you can use db.create_all().

# This should typically be run once, perhaps in a setup script or
# conditionally when the app starts, within the application context.
with app.app_context():
    db.create_all()

Important Note on Migrations: db.create_all() only creates tables that don’t exist. It does not handle updates to existing table structures (e.g., adding a new column). For managing database schema changes over time in production applications, use a database migration tool like Alembic, typically integrated via the Flask-Migrate extension. (pip install Flask-Migrate)

Performing CRUD Operations

Section titled “Performing CRUD Operations”

Flask-SQLAlchemy manages database sessions for you. You interact with the database through the db.session object.

  • Create (Add): Create an instance of your model class and add it to the session using db.session.add(). Then, commit the session with db.session.commit() to save changes to the database.
  • Read (Query): Use the query attribute on your model class (e.g., Student.query). Common query methods include all() (get all records), get(id) (get by primary key), filter_by(...) (filter by keyword arguments), filter(...) (filter using SQL-like expressions), first() (get the first result), count() (get the number of results).
  • Update: Query the object you want to update, modify its attributes, and then db.session.commit().
  • Delete: Query the object you want to delete, pass it to db.session.delete(), and then db.session.commit().

Example Application: Student Records

Section titled “Example Application: Student Records”

Let’s build a small application to list and add student records.

View function to show all students (/) and render show_all.html:

from flask import render_template


@app.route('/')
def show_all():
    students = Student.query.all() # Fetch all student records
    return render_template('show_all.html', students=students)

Template templates/show_all.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Student List</title>
</head>
<body>
    <h3>
        <a href="{{ url_for('show_all') }}">Student Records - Flask-SQLAlchemy</a>
    </h3>
    <hr/>


    {# Display flashed messages #}
    {% with messages = get_flashed_messages() %}
      {% if messages %}
        <ul class=flashes>
        {% for message in messages %}
          <li>{{ message }}</li>
        {% endfor %}
        </ul>
      {% endif %}
    {% endwith %}


    <h3>
        Students
        (<a href="{{ url_for('add_student') }}">Add New Student</a>)
    </h3>
    <table border="1">
        <thead>
            <tr>
                <th>Name</th>
                <th>City</th>
                <th>Address</th>
                <th>PIN Code</th>
            </tr>
        </thead>
        <tbody>
            {% for student in students %}
            <tr>
                <td>{{ student.name }}</td>
                <td>{{ student.city }}</td>
                <td>{{ student.address }}</td>
                <td>{{ student.pin_code }}</td>
            </tr>
            {% else %}
            <tr>
                <td colspan="4">No students found.</td>
            </tr>
            {% endfor %}
        </tbody>
    </table>
</body>
</html>

View function to handle adding new students (/new), rendering new_student.html (GET) and processing form data (POST):

from flask import request, flash, redirect, url_for


@app.route('/new', methods=['GET', 'POST'])
def add_student():
    if request.method == 'POST':
        # Basic validation (Consider using Flask-WTF for robust validation)
        if not request.form.get('name') or not request.form.get('city') or                not request.form.get('address') or not request.form.get('pin_code'):
            flash('Please enter all the fields', 'error')
        else:
            # Create a new Student object from form data
            new_student = Student(
                name=request.form['name'],
                city=request.form['city'],
                address=request.form['address'],
                pin_code=request.form['pin_code']
            )


            try:
                # Add the new student to the session and commit
                db.session.add(new_student)
                db.session.commit()
                flash('Record was successfully added', 'success')
                return redirect(url_for('show_all')) # Redirect back to the list
            except Exception as e:
                db.session.rollback() # Rollback in case of error
                flash(f'Error adding record: {e}', 'error')


    # If GET request or validation failed, render the form template
    return render_template('new_student.html')

Template templates/new_student.html:

<!DOCTYPE html>
<html>
<head>
    <title>Add Student</title>
</head>
<body>
    <h3>Add New Student</h3>
    <hr/>
    {% with messages = get_flashed_messages(with_categories=true) %}
      {% if messages %}
        <ul>
        {% for category, message in messages %}
          <li class="{{ category }}">{{ message }}</li>
        {% endfor %}
        </ul>
      {% endif %}
    {% endwith %}


    <form action="{{ url_for('add_student') }}" method="post">
        <div>
            <label for="name">Name</label><br/>
            <input type="text" name="name" placeholder="Full Name" required/>
        </div>
        <div>
            <label for="city">City</label><br/>
            <input type="text" name="city" placeholder="City" required/>
        </div>
        <div>
            <label for="address">Address</label><br/>
            <textarea name="address" placeholder="Street Address" required></textarea>
        </div>
        <div>
            <label for="pin_code">PIN Code</label><br/>
            <input type="text" name="pin_code" placeholder="PIN Code" required/>
        </div>
        <div>
            <input type="submit" value="Submit"/>
        </div>
    </form>
    <br>
    <a href="{{ url_for('show_all') }}">Back to List</a>
</body>
</html>

Complete Application Code (app.py):

# --- Start of app.py ---
from flask import Flask, request, flash, url_for, redirect, render_template
from flask_sqlalchemy import SQLAlchemy


# --- App Initialization and Configuration ---
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///students.sqlite3'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] = "very-random-secret-string"


db = SQLAlchemy(app)


# --- Model Definition ---
class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    city = db.Column(db.String(50))
    address = db.Column(db.String(200))
    pin_code = db.Column(db.String(10))


    def __init__(self, name, city, address, pin_code):
        self.name = name
        self.city = city
        self.address = address
        self.pin_code = pin_code


    def __repr__(self):
        return f'<Student {self.name}>'


# --- Routes and View Functions ---
@app.route('/')
def show_all():
    students = Student.query.order_by(Student.name).all() # Order by name
    return render_template('show_all.html', students=students)


@app.route('/new', methods=['GET', 'POST'])
def add_student():
    if request.method == 'POST':
        if not request.form.get('name') or not request.form.get('city') or \           not request.form.get('address') or not request.form.get('pin_code'):
            flash('Please enter all the fields', 'error')
        else:
            new_student = Student(
                name=request.form['name'],
                city=request.form['city'],
                address=request.form['address'],
                pin_code=request.form['pin_code']
            )
            try:
                db.session.add(new_student)
                db.session.commit()
                flash('Record was successfully added', 'success')
                return redirect(url_for('show_all'))
            except Exception as e:
                db.session.rollback()
                flash(f'Error adding record: {e}', 'error')


    return render_template('new_student.html')


# --- Utility function to create tables (run once) ---
def create_tables():
     with app.app_context():
        print("Creating database tables...")
        db.create_all()
        print("Tables created.")


# --- Run Application ---
if __name__ == '__main__':
    # Optional: Create tables if the script is run directly
    # Be cautious with this in production; use migrations instead.
    # create_tables()
    app.run(debug=True)

To run this:

  • Save the Python code as app.py.
  • Create a templates folder and save show_all.html and new_student.html inside it.
  • Run the application: python app.py.
  • Open your browser to http://localhost:5000/.
  • The first time you run (or if the students.sqlite3 file doesn’t exist), you might need to create the tables. You can uncomment the create_tables() call within the if __name__ == '__main__': block temporarily, run the script once, then comment it out again. A better approach is using the Flask shell: flask shell, then inside the shell: from app import db, db.create_all(), exit().
  • You should see the (initially empty) student list. Click ‘Add New Student’, fill the form, and submit. The new student should appear on the list.

This example covers the basics. SQLAlchemy offers much more power, including relationships between models (one-to-many, many-to-many), more complex querying, and transaction management. Refer to the Flask-SQLAlchemy and SQLAlchemy documentation for advanced usage.