Flask SQLAlchemy Setup

Introduction

When building web applications with Flask, database management is a crucial component. Flask-SQLAlchemy is a Flask extension that adds support for SQLAlchemy, a powerful SQL toolkit and Object-Relational Mapping (ORM) library for Python. This extension simplifies using SQLAlchemy with Flask by providing useful defaults and extra helpers that make it easier to accomplish common tasks.

In this tutorial, we'll learn how to:

• Install Flask-SQLAlchemy
• Configure a database connection
• Create database models
• Perform basic database operations

Prerequisites

Before we begin, make sure you have:

• Basic knowledge of Python
• Flask installed in your environment
• A basic understanding of SQL concepts

Installing Flask-SQLAlchemy

The first step is to install Flask-SQLAlchemy using pip:

bash

pip install flask-sqlalchemy

You may also need to install a database driver depending on which database you plan to use:

bash

# For SQLite (usually included with Python)
# No additional driver needed

# For PostgreSQL
pip install psycopg2-binary

# For MySQL
pip install mysqlclient

Basic Configuration

Let's start by creating a simple Flask application with SQLAlchemy:

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

# Create a Flask application
app = Flask(__name__)

# Configure the SQLAlchemy part of the app
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///myapp.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

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

# Define routes and app logic here

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

Understanding the Configuration

• SQLALCHEMY_DATABASE_URI: This configuration variable tells Flask-SQLAlchemy where to find the database. The format varies depending on the type of database you're using.
• SQLALCHEMY_TRACK_MODIFICATIONS: This is set to False to disable a feature that signals the application every time a change is about to be made in the database.

Database URI Formats

The SQLALCHEMY_DATABASE_URI uses different formats depending on your database:

python

# SQLite (file-based)
'sqlite:///myapp.db'  # Relative path
'sqlite:////absolute/path/to/myapp.db'  # Absolute path

# PostgreSQL
'postgresql://username:password@localhost/database_name'

# MySQL
'mysql://username:password@localhost/database_name'

# Microsoft SQL Server
'mssql://username:password@localhost/database_name'

Creating Database Models

Models in SQLAlchemy are Python classes that represent tables in your database. Here's how to define a basic model:

python

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

This creates a User table with three columns: id, username, and email.

Common Column Types

SQLAlchemy supports various data types for columns:

python

# Common types
db.String(80)      # Variable-length string with max length
db.Integer         # Integer
db.Float           # Floating point value
db.Boolean         # Boolean
db.DateTime        # Date and time
db.Date            # Date only
db.Text            # Unlimited length text
db.LargeBinary     # Binary data (files, etc.)

Column Options

When defining columns, you can specify various options:

python

# Common column options
db.Column(db.Integer, primary_key=True)  # Primary key
db.Column(db.String(50), unique=True)    # Unique constraint
db.Column(db.Text, nullable=False)       # Cannot be NULL
db.Column(db.Integer, default=0)         # Default value

Creating the Database

After defining your models, you need to create the database tables. With Flask-SQLAlchemy, you can use the following code:

python

# Create tables based on the models
with app.app_context():
    db.create_all()

This code needs to be run only once when setting up your application for the first time or after making changes to your models.

Using Flask Application Factory Pattern

For larger applications, it's recommended to use the application factory pattern. Here's how to set up SQLAlchemy with this pattern:

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

def create_app():
    app = Flask(__name__)
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///myapp.db'
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    
    # Initialize the db with the app
    db.init_app(app)
    
    # Create tables within app context
    with app.app_context():
        db.create_all()
    
    return app

Relationships Between Models

One of the most powerful features of an ORM like SQLAlchemy is the ability to define relationships between tables:

python

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)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    user = db.relationship('User', backref=db.backref('posts', lazy=True))
    
    def __repr__(self):
        return f'<Post {self.title}>'

In this example:

• user_id is a foreign key that references the id column in the user table
• user is a relationship attribute that allows us to access the related User object
• backref='posts' creates a posts attribute on User instances that returns all related Post objects

Practical Example: Blog Application

Let's put everything together in a simple blog application:

python

from flask import Flask, render_template, request, redirect, url_for
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
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    posts = db.relationship('Post', backref='author', lazy=True)

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)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

# Create the database and tables
with app.app_context():
    db.create_all()

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

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

@app.route('/create_post', methods=['GET', 'POST'])
def create_post():
    if request.method == 'POST':
        # For simplicity, assume user with ID 1 exists
        user = User.query.get(1)
        if not user:
            # Create a default user if none exists
            user = User(username='default', email='[email protected]')
            db.session.add(user)
            db.session.commit()
        
        title = request.form['title']
        content = request.form['content']
        post = Post(title=title, content=content, author=user)
        db.session.add(post)
        db.session.commit()
        return redirect(url_for('home'))
    return render_template('create_post.html')

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

This simplified blog application demonstrates the following concepts:

• Creating database models with relationships
• Setting up routes to interact with the database
• Adding new entries to the database

Configuration Best Practices

In a production environment, it's good practice to keep your configuration separate from your code:

python

# config.py
import os

class Config:
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'hard-to-guess-string'
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
        'sqlite:///' + os.path.join(os.path.abspath(os.path.dirname(__file__)), 'app.db')
    SQLALCHEMY_TRACK_MODIFICATIONS = False

# app.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from config import Config

app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy(app)

This approach allows you to change configuration settings without modifying your code, which is especially useful when deploying your application.

Summary

In this tutorial, we've covered:

1. Installing Flask-SQLAlchemy
2. Configuring database connections
3. Creating database models with various column types
4. Defining relationships between models
5. Creating database tables
6. Using the application factory pattern
7. Building a simple blog application
8. Best practices for configuration

Flask-SQLAlchemy provides a powerful and intuitive way to work with databases in Flask applications. By leveraging the ORM capabilities of SQLAlchemy, you can focus more on building your application logic and less on writing raw SQL queries.

Additional Resources

• Flask-SQLAlchemy Documentation
• SQLAlchemy Documentation
• Flask Mega-Tutorial by Miguel Grinberg

Exercises

1. Create a Flask application that uses SQLAlchemy to manage a to-do list.
2. Extend the blog application to include comments on posts.
3. Implement user authentication and ensure only authenticated users can create posts.
4. Add categories to blog posts and create a page to view posts by category.
5. Implement database migrations using Flask-Migrate to handle changes to your database schema.