Flask-SQLAlchemy Extension

Why Use an ORM?

Before diving into Flask-SQLAlchemy, it's worth understanding why ORMs (Object-Relational Mappers) are so widely used in web development:

• Work with Python objects: Instead of writing SQL queries, you work with familiar Python classes and objects
• Database agnostic: Your code isn't tied to a specific database engine, making it easier to switch databases if needed
• SQL injection protection: ORMs handle parameter sanitization, reducing the risk of SQL injection attacks
• Reduced boilerplate code: Common database operations are simplified, requiring less code
• Data validation and type conversion: ORMs can validate data and convert between Python and database types

However, ORMs also have some trade-offs:

• Learning curve: You need to learn the ORM's API in addition to understanding databases
• Performance overhead: ORMs add a layer of abstraction that can impact performance for complex queries
• Less control: Some advanced database features might be harder to access through an ORM

For most web applications, the benefits of using an ORM far outweigh these drawbacks, especially during development when flexibility and productivity are key concerns.

Setting Up Flask-SQLAlchemy

Let's get started by installing Flask-SQLAlchemy and configuring it in our Flask application:

# Install Flask-SQLAlchemy
pip install Flask-SQLAlchemy

# Also install a database driver (for example, SQLite is built-in to Python)
# For PostgreSQL:
pip install psycopg2-binary
# For MySQL:
pip install mysqlclient

Now, let's set up a basic Flask application with SQLAlchemy:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import os

# Create Flask application
app = Flask(__name__)

# Configure the database URI
# SQLite (file-based database, great for development)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'

# PostgreSQL
# app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://username:password@localhost/dbname'

# MySQL
# app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:password@localhost/dbname'

# Disable a feature that signals the application every time a change is about to be made in the database
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# Create the SQLAlchemy database instance
db = SQLAlchemy(app)

# The rest of your Flask application goes here...

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

Let's break down this setup:

• We import the required modules and create a Flask application
• We configure the database URI, which tells SQLAlchemy which database engine to use and how to connect to it
• We disable the SQLALCHEMY_TRACK_MODIFICATIONS option, which can cause overhead and isn't needed for most applications
• We create a db object, which represents our database connection

Database URI Formats: The format of the database URI varies depending on the database engine:

Flask Application Factory Pattern

The above approach works well for simple applications, but larger projects often use the application factory pattern. This pattern allows you to create multiple instances of your application with different configurations, which is especially useful for testing.

# app/__init__.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

# Create the database instance without binding it to an app yet
db = SQLAlchemy()

def create_app(config_class=None):
    app = Flask(__name__)
    
    # Configure the database
    if config_class:
        app.config.from_object(config_class)
    else:
        # Default configuration
        app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'
        app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    
    # Initialize the database with this application
    db.init_app(app)
    
    # Register blueprints and other extensions here...
    
    return app

# config.py
class Config:
    SQLALCHEMY_DATABASE_URI = 'sqlite:///site.db'
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    
class TestConfig(Config):
    SQLALCHEMY_DATABASE_URI = 'sqlite:///test.db'
    TESTING = True

# main.py or run.py
from app import create_app

app = create_app()

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

With this approach, the db object is created without being bound to a specific application instance. Later, when an application is created, we bind the database to it using db.init_app(app).

Environment-based Configuration

In real-world applications, you'll typically want different database configurations for development, testing, and production environments. A common approach is to use environment variables:

# config.py
import os

class Config:
    # Use environment variables with fallback values
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or 'sqlite:///site.db'
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'dev-key-for-development-only'
    
class DevelopmentConfig(Config):
    DEBUG = True
    
class TestingConfig(Config):
    TESTING = True
    SQLALCHEMY_DATABASE_URI = 'sqlite:///test.db'
    
class ProductionConfig(Config):
    # Production might use different settings
    DEBUG = False
    TESTING = False
    
# Get the configuration based on the environment
config = {
    'development': DevelopmentConfig,
    'testing': TestingConfig,
    'production': ProductionConfig,
    'default': DevelopmentConfig
}

def get_config():
    env = os.environ.get('FLASK_ENV', 'default')
    return config[env]

Then, in your application factory:

from config import get_config

def create_app():
    app = Flask(__name__)
    app.config.from_object(get_config())
    
    db.init_app(app)
    # ...
    return app

This approach allows you to switch between configurations by simply setting the FLASK_ENV environment variable.

Creating the Database

After setting up Flask-SQLAlchemy, we need to create the database. For SQLite, this means creating the database file. For other databases, you might need to create the database first using the database's administrative tools.

Flask-SQLAlchemy provides a way to create all tables defined in your models:

# In your application context or in a Python shell
from app import create_app, db

app = create_app()

# Create an application context
with app.app_context():
    # Create all tables
    db.create_all()

For the application factory pattern, we need to use an application context because the db instance is not bound to an application outside of a context.

For a simple application without the factory pattern, you might do:

# At the end of your app.py file
if __name__ == '__main__':
    db.create_all()  # Create tables before running the app
    app.run(debug=True)

Note that db.create_all() only creates tables that don't exist yet. It doesn't update tables if your models change. For that, you'd need a migration tool like Flask-Migrate (which we'll cover in a future lecture).

SQLAlchemy Engine, Session, and Metadata

Before we start defining models, it's helpful to understand the key components of SQLAlchemy that Flask-SQLAlchemy builds upon:

• Engine: The core of SQLAlchemy that manages connections to the database. It's responsible for translating Python function calls into SQL commands that the database understands.
• Session: Represents a "workspace" for your objects, keeping track of changes and managing transactions. When you're ready to persist your changes, you commit the session.
• Metadata: Contains definitions of tables, columns, and constraints. It's used to generate SQL schema statements.

Flask-SQLAlchemy abstracts away much of this complexity, but understanding these concepts can help when you need to do more advanced operations.

Common Database Engines

Let's briefly look at some common database engines you might use with Flask-SQLAlchemy:

SQLite

SQLite is a lightweight disk-based database that doesn't require a separate server process. It's included in Python and is great for development and small applications.

• Pros: Zero configuration, self-contained, reliable
• Cons: Limited concurrency, not suitable for high-write applications
• Use case: Development, testing, small applications, embedded systems

PostgreSQL

PostgreSQL is a powerful, open-source object-relational database system with a strong reputation for reliability, feature robustness, and performance.

• Pros: Advanced features, strong compliance with SQL standards, excellent for complex queries
• Cons: More complex to set up, might be overkill for simple applications
• Use case: Production applications, complex data models, when data integrity is critical

MySQL/MariaDB

MySQL is one of the most popular database systems, known for its performance and reliability. MariaDB is a community-developed fork of MySQL.

• Pros: Fast, widely supported, familiar to many developers
• Cons: Some limitations in SQL compliance, especially in older versions
• Use case: Web applications, content management systems, when performance is a priority

Creating Your First Model

In SQLAlchemy, models are Python classes that represent database tables. Each instance of the class represents a row in the table. Let's create a simple model for a blog post:

from datetime import datetime

# This assumes db is already defined
class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    
    def __repr__(self):
        return f"Post('{self.title}', '{self.date_posted}')"

Let's break down this model:

• db.Model is the base class for all SQLAlchemy models in Flask-SQLAlchemy
• Each db.Column defines a column in the database table
• The first argument to db.Column is the column type (Integer, String, Text, DateTime, etc.)
• Additional arguments specify constraints and defaults:
  • primary_key=True marks the column as the primary key
  • nullable=False means the column cannot be NULL
  • default=datetime.utcnow provides a default value (note we pass the function, not the result)
  • For String, the number in parentheses is the maximum length
• The __repr__ method defines how the object is represented when printed

By default, the table name is the lowercase version of the class name. You can customize this:

class Post(db.Model):
    __tablename__ = 'blog_posts'  # Custom table name
    # ... rest of the model ...

Column Types in SQLAlchemy

SQLAlchemy provides a variety of column types to match different kinds of data:

Each database engine might map these types differently to its native types. SQLAlchemy handles these differences for you, which is part of the database-agnostic advantage of using an ORM.

A Complete Example

Let's put everything together in a complete, albeit simple, Flask application with SQLAlchemy:

from flask import Flask, render_template, request, redirect, url_for, flash
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///blog.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] = 'your_secret_key'

db = SQLAlchemy(app)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    
    def __repr__(self):
        return f"Post('{self.title}', '{self.date_posted}')"

@app.route('/')
def home():
    posts = Post.query.order_by(Post.date_posted.desc()).all()
    return render_template('home.html', posts=posts)

@app.route('/post/new', methods=['GET', 'POST'])
def new_post():
    if request.method == 'POST':
        post = Post(
            title=request.form['title'],
            content=request.form['content']
        )
        db.session.add(post)
        db.session.commit()
        flash('Your post has been created!', 'success')
        return redirect(url_for('home'))
    return render_template('create_post.html')

@app.route('/post/')
def post(post_id):
    post = Post.query.get_or_404(post_id)
    return render_template('post.html', post=post)

@app.route('/post//update', methods=['GET', 'POST'])
def update_post(post_id):
    post = Post.query.get_or_404(post_id)
    if request.method == 'POST':
        post.title = request.form['title']
        post.content = request.form['content']
        db.session.commit()
        flash('Your post has been updated!', 'success')
        return redirect(url_for('post', post_id=post.id))
    return render_template('update_post.html', post=post)

@app.route('/post//delete', methods=['POST'])
def delete_post(post_id):
    post = Post.query.get_or_404(post_id)
    db.session.delete(post)
    db.session.commit()
    flash('Your post has been deleted!', 'success')
    return redirect(url_for('home'))

if __name__ == '__main__':
    db.create_all()  # Create tables
    app.run(debug=True)

This example showcases:

• Setting up Flask-SQLAlchemy
• Defining a model with various column types
• Creating basic CRUD operations (Create, Read, Update, Delete)
• Using SQLAlchemy queries to retrieve data
• Working with the database session to persist changes

In a real application, you'd also need templates (home.html, create_post.html, etc.) to render the views, but this gives you a good starting point for the backend logic.

Real-world Analogies

To better understand SQLAlchemy and database operations, let's consider some real-world analogies:

• Database as a filing cabinet: The database is like a filing cabinet, tables are like drawers, rows are like individual folders, and columns are like sections within each folder.
• Model class as a form template: The model class is like a form template that defines what information needs to be collected. Each instance of the model is like a filled-out form.
• Session as a shopping cart: The database session is like a shopping cart where you collect items (changes) until you're ready to check out (commit).
• Migration as a building renovation: Database migrations are like renovating a building while people are still using it - you need to carefully plan changes to avoid disrupting existing operations.

Practical Activity: Creating a Simple Database App

Let's apply what we've learned by creating a simple task manager application:

1. Set up a Flask application with Flask-SQLAlchemy
2. Create a Task model with fields for title, description, due date, and completion status
3. Implement routes for:
   • Viewing all tasks
   • Adding a new task
   • Marking a task as complete
   • Deleting a task
4. Create simple templates to render the views

This activity will help reinforce the concepts we've covered and give you a practical starting point for working with Flask-SQLAlchemy.

# Task model
class Task(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    due_date = db.Column(db.Date, nullable=True)
    completed = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    def __repr__(self):
        return f"Task('{self.title}', due: '{self.due_date}', completed: {self.completed})"

Implement the following routes and complete the application with templates and styling.

@app.route('/tasks')
def list_tasks():
    # Get all tasks, with incomplete tasks first, then sorted by due date
    tasks = Task.query.order_by(Task.completed, Task.due_date).all()
    return render_template('tasks.html', tasks=tasks)

@app.route('/tasks/add', methods=['GET', 'POST'])
def add_task():
    # Implement this route to add a new task
    pass

@app.route('/tasks//complete', methods=['POST'])
def complete_task(task_id):
    # Implement this route to mark a task as complete
    pass
    
@app.route('/tasks//delete', methods=['POST'])
def delete_task(task_id):
    # Implement this route to delete a task
    pass

Key Takeaways

• Flask-SQLAlchemy integrates SQLAlchemy with Flask, providing a powerful ORM for database operations
• ORMs like SQLAlchemy allow you to work with databases using Python objects rather than raw SQL
• Setting up Flask-SQLAlchemy involves configuring a database URI and creating models
• Models are Python classes that represent database tables, with attributes that map to columns
• The application factory pattern provides flexibility, especially for larger applications
• Flask-SQLAlchemy supports various database engines, each with its own strengths and use cases

Further Learning Resources

• Flask-SQLAlchemy Documentation
• SQLAlchemy Documentation
• Miguel Grinberg's Flask Mega-Tutorial: Databases
• Flask-SQLAlchemy Tutorial: Database Models and Relationships