Flask-SQLAlchemy - ORM Integration, Models, Queries

Introduction

Flask-SQLAlchemy provides ORM capabilities to Flask applications, allowing you to work with databases using Python objects instead of raw SQL. This tutorial covers model definition, relationships, and query operations.

Setup and Configuration

# config.py
class Config:
    SQLALCHEMY_DATABASE_URI = 'sqlite:///app.db'
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    SQLALCHEMY_ECHO = True

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

db = SQLAlchemy()

def create_app():
    app = Flask(__name__)
    app.config.from_object('config.Config')
    db.init_app(app)
    
    with app.app_context():
        db.create_all()
    
    return app

Defining Models

Models represent database tables. Each class inherits from db.Model and defines columns as db.Column instances.

# app/models.py
from app import db
from datetime import datetime

class Author(db.Model):
    __tablename__ = 'authors'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    birth_date = db.Column(db.Date)
    books = db.relationship('Book', backref='author', lazy=True)
    
    def __repr__(self):
        return f'<Author {self.name}>'

class Book(db.Model):
    __tablename__ = 'books'
    
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    published_year = db.Column(db.Integer)
    author_id = db.Column(db.Integer, db.ForeignKey('authors.id'), nullable=False)
    genre = db.Column(db.String(50))
    price = db.Column(db.Numeric(10, 2))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    def __repr__(self):
        return f'<Book {self.title}>'

Query Operations

from app import db
from app.models import Author, Book

# Create
author = Author(name='George Orwell', birth_date=1903-06-25)
db.session.add(author)
db.session.commit()

# Read - all books
all_books = Book.query.all()

# Read - filter
science_fiction = Book.query.filter_by(genre='Science Fiction').all()

# Read - with conditions
recent_books = Book.query.filter(
    Book.published_year >= 2020
).order_by(Book.published_year.desc()).all()

# Read - joins
books_with_authors = db.session.query(Book, Author).join(Author).all()

# Update
book = Book.query.first()
book.price = 19.99
db.session.commit()

# Delete
book = Book.query.first()
db.session.delete(book)
db.session.commit()

# Aggregation
from sqlalchemy import func
total_books = db.session.query(func.count(Book.id)).scalar()
avg_price = db.session.query(func.avg(Book.price)).scalar()

Relationships

# One-to-Many
class Department(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    employees = db.relationship('Employee', backref='department', lazy=True)

class Employee(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    department_id = db.Column(db.Integer, db.ForeignKey('department.id'))

# Many-to-Many
student_course = db.Table('student_course',
    db.Column('student_id', db.Integer, db.ForeignKey('student.id')),
    db.Column('course_id', db.Integer, db.ForeignKey('course.id'))
)

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    courses = db.relationship('Course', secondary=student_course, backref='students')

class Course(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))

Practice Problems

1. Create a model for a Blog with posts and comments, establishing proper relationships
2. Write a query to find all books by an author, sorted by publication year
3. Implement a pagination function that returns books in chunks of 10
4. Create a query that calculates the total price of books in each genre
5. Implement a Many-to-Many relationship between User and Role models