Flask-SQLAlchemy — Designing Database Models

In this chapter, you will learn how to define data models with Flask-SQLAlchemy, create SQLite database tables, and insert test data.

Why use an ORM?

Writing SQL statements directly to operate on a database is tedious and error-prone.

ORM (Object-Relational Mapping)It lets you define table structures with Python classes and manipulate data with Python methods.

Flask-SQLAlchemy is the Flask integration of SQLAlchemy, wrapping common operations and simplifying configuration.

Installation and Configuration

(venv) $ pip install flask-sqlalchemy

Example

# File path: app.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# SQLite database file path (located in the project root directory)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///blog.db'
# Disable modification tracking (saves memory, will be disabled by default in future versions)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# Create a db instance and bind it to app
db = SQLAlchemy(app)

sqlite:///blog.dbThe three slashes in it indicate a relative path. An SQLite database is just a file, requiring no database software to be installed, making it ideal for development and testing.

Defining Models: Post and Category

Example

# File path: models.py
from datetime import datetime
from app import db

class Category(db.Model):
"""Article category"""
__tablename__ = 'categories'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(50), unique=True, nullable=False)
slug = db.Column(db.String(50), unique=True, nullable=False)
# backref: On the Category object, you can use .posts to retrieve all its articles in reverse.
posts = db.relationship('Post', backref='category', lazy='dynamic')

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

class Post(db.Model):
"""Blog Post"""
__tablename__ = 'posts'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
title = db.Column(db.String(200), nullable=False)
slug = db.Column(db.String(200), unique=True, nullable=False)
summary = db.Column(db.Text, default='')
content = db.Column(db.Text, nullable=False)
# Foreign key: db.ForeignKey('table_name.field_name')
category_id = db.Column(db.Integer, db.ForeignKey('categories.id'), nullable=False)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

def __repr__(self):
return f'<Post {self.title}>'

Common field types

Field Type	Database Type	Common Parameters
db.Integer	INTEGER	primary_key, autoincrement
db.String(N)	VARCHAR(N)	unique, nullable, default
db.Text	TEXT	default
db.DateTime	DATETIME	default, onupdate
db.Boolean	BOOLEAN	default
db.ForeignKey('table.field')	FOREIGN KEY	—

Create database table

Unlike Django's migrate, Flask requires manually initializing database tables.

(venv) $ flask shell                        # 进入 Flask 交互式 Shell

Example

# Enter in the flask shell
from app import db
db.create_all() # Create database tables for all models
exit()

After execution, the following will be generated in the project root directoryblog.dbFile.

db.create_all()Create the table only if it does not exist. If you modify the model structure (such as adding new fields), it will not automatically update the table. In production, you should use Flask-Migrate (Chapter 5) to manage structure changes.

Use flask shell to insert test data.

Example

# Enter in the flask shell
from app import db
from models import Category, Post

# 1. Create category
py_cat = Category(name='Python', slug='python')
css_cat = Category(name='CSS', slug='css')
flask_cat = Category(name='Flask', slug='flask')
db.session.add_all([py_cat, css_cat, flask_cat])
db.session.commit()

# 2. Create article
post1 = Post(
title='Flask Complete Guide for Beginners',
slug='flask-beginner-guide',
summary=Learn Flask from scratch, covering core concepts such as routing, templates, ORM.,
content=<h2>Why Learn Flask?</h2><p>Flask is the most flexible web microframework in Python...</p>,
category=flask_cat
)
post2 = Post(
title=Python Coroutines Explained,
slug='python-coroutine',
summary=Understand asyncio, coroutines, and event loops in one article.,
content=<h2>What Is a Coroutine?</h2><p>A coroutine is a lighter-weight concurrency solution than threads...</p>,
category=py_cat
)
post3 = Post(
title=Practical CSS Grid Layout,
slug='css-grid-layout',
summary=Easily implement complex responsive layouts with CSS Grid.,
content=<h2>Getting Started with Grid</h2><p>Grid is a two-dimensional layout system...</p>,
category=css_cat
)
db.session.add_all([post1, post2, post3])
db.session.commit()

# 3. Verify Queries
print(Post.query.all())
print(Post.query.filter(Post.category_id == 1).all())

Flask-SQLAlchemy Basic Queries

Methods	Equivalent SQL	Return Value
Post.query.all()	SELECT * FROM posts	List
Post.query.get(1)	WHERE id = 1	A single object or None
Post.query.get_or_404(1)	WHERE id = 1	A single object or automatically returns 404
Post.query.filter_by(category_id=1).all()	WHERE category_id=1	List
Post.query.filter(Post.title.ilike('%flask%')).all()	WHERE title LIKE '%flask%'	List
Post.query.order_by(Post.created_at.desc()).all()	ORDER BY created_at DESC	List

Chapter summary

In this chapter, you mastered the complete Flask-SQLAlchemy workflow: installing and configuring SQLALCHEMY_DATABASE_URI, defining models with db.Model (field types/foreign keys/relationship), initializing tables with db.create_all(), and inserting and querying data in the flask shell.

At this point, the data structures for posts and categories have been established in the SQLite database.

other extensions