Python Flask Tutorial: Full-Featured Web App Part 4

TL;DR

Install and configure Flask-SQLAlchemy by setting `SQLALCHEMY_DATABASE_URI` (SQLite for development, swap the URI later for Postgres).

Briefing Cornell Notes

Briefing

Flask-SQLAlchemy turns a Flask app’s database into Python classes—letting developers create real users and posts backed by SQLite in development, with the option to swap to Postgres in production without rewriting the core data-access code. The setup starts by installing Flask-SQLAlchemy, configuring a database URI (using an SQLite file like `site.db` during development), and creating a `DB` instance tied to the Flask app.

With the database connection in place, the next step is defining the data model. A `User` model becomes a database table with columns for `id` (an integer primary key), `username` (string, max length 20, unique, not nullable), `email` (string, max length 120, unique, not nullable), `image_file` (string, max length 20, not nullable, with a default like `default.jpg`), and `password` (string, max length 60, not nullable). A `__repr__` method is added so printed `User` objects show key fields—username, email, and image—without exposing the password.

A `Post` model is defined similarly, representing another table. It includes `id` (primary key), `title` (string, max length 100, not nullable), `date_posted` (datetime, not nullable, defaulting to `date.utcnow` in UTC for consistency), and `content` (text, not nullable). Like `User`, it also gets a `__repr__` method that prints a concise summary (title and date) rather than the full content.

The crucial relationship ties the two models together. A one-to-many association is created: one `User` can have many `Post` entries, while each `Post` belongs to a single author. In SQLAlchemy terms, the `User` model gets a `posts` relationship with a `backref='author'` and `lazy=True`, enabling convenient navigation from a post back to its author via `post.author`—even though `author` is not a physical column in the `Post` table. The `Post` model also defines a `user_id` column as a foreign key referencing `user.id`, with `nullable=False` to enforce that every post has an author.

Once models are defined, the database schema is created using `DB.create_all()`, which generates the SQLite file and tables on disk. Sample data is then inserted through the Flask app context using `DB.session.add()` and `DB.session.commit()`: two users are created, followed by two posts linked to the first user via `user_id`. Querying is demonstrated with `User.query.all()`, `User.query.first()`, `filter_by(...)`, `query.get(id)`, and relationship access like `user.posts`.

To keep the development workflow clean, the tutorial also shows how to reset everything: `DB.drop_all()` removes all tables and rows, and `DB.create_all()` recreates the schema from scratch. After the reset, querying users and posts returns empty lists, confirming a fresh database state for the next stage of the series—refactoring the app into a package and moving models into separate files.

Cornell Notes

Flask-SQLAlchemy maps database tables to Python classes so developers can define `User` and `Post` models with columns, defaults, constraints, and readable `__repr__` output. The `User` model includes unique `username` and `email`, a default `image_file` (e.g., `default.jpg`), and a hashed `password` field sized for common hashing outputs. The `Post` model stores `title`, `content`, and a UTC `date_posted` default using `date.utcnow`. A one-to-many relationship links them: `User.posts` returns a list of posts, while `Post.user_id` enforces authorship and `post.author` is available via `backref='author'`. The workflow uses `DB.create_all()` to build the schema, `DB.session.add()`/`commit()` to insert rows, and `DB.drop_all()` to reset to a clean slate.

Why configure `SQLALCHEMY_DATABASE_URI` with an SQLite file during development, and what changes when moving to Postgres later?

The URI tells SQLAlchemy where the database lives. Using SQLite with a relative path like `sqlite:///site.db` creates a local file (`site.db`) in the project directory, making setup fast for development. The key benefit is that the Python model code and queries stay the same; switching to Postgres later mainly requires changing the database URL in configuration, not rewriting the model definitions or query logic.

What constraints and defaults make the `User` model practical for a real app?

The `User` model sets `id` as an integer primary key. It enforces uniqueness and presence with `username` (string max length 20, `unique=True`, `nullable=False`) and `email` (string max length 120, `unique=True`, `nullable=False`). It also ensures every user has an image by making `image_file` `nullable=False` with a default value `default.jpg`. The `password` column is sized for hashed passwords (string max length 60) and is also `nullable=False`, preparing the model for later hashing rather than storing plaintext.

How does the `Post` model ensure consistent timestamps and required fields?

`Post` defines `title` as a string with max length 100 and `nullable=False`, so every post must have a title. `content` is a `DB.Text` column with `nullable=False`, requiring body text. For timekeeping, `date_posted` is a `DB.DateTime` column with `nullable=False` and a default of `date.utcnow` (passed as a callable, not called immediately). Using UTC keeps timestamps consistent across environments.

What exactly creates the one-to-many link between users and posts, and how does `post.author` work?

The relationship is defined in two parts. In `User`, `posts = DB.relationship('Post', backref='author', lazy=True)` declares that a user can have many posts and sets up a reverse accessor named `author`. In `Post`, `user_id = DB.Column(DB.Integer, DB.ForeignKey('user.id'), nullable=False)` stores the author’s user id and enforces that each post has an author. Because of the `backref`, SQLAlchemy provides `post.author` as a convenient way to fetch the related `User` object without adding an `author` column to the `Post` table.

What is the difference between adding objects to the session and committing them to the database?

`DB.session.add(obj)` registers pending changes in the session but does not write them to the database immediately. `DB.session.commit()` finalizes the transaction and persists the rows. In the workflow shown, users and posts are added first, then a single commit writes all changes at once.

How do `DB.drop_all()` and `DB.create_all()` support iterative development?

`DB.drop_all()` removes all tables and rows, effectively resetting the database to an empty state. `DB.create_all()` recreates the schema from the current model definitions. After dropping and recreating, queries like `User.query.all()` and `Post.query.all()` return empty lists, confirming the app is starting fresh.

Review Questions

How do `unique=True` and `nullable=False` on `User.username` and `User.email` affect database behavior when inserting duplicate or missing values?
Why is `default=date.utcnow` written without parentheses, and what would change if it were written as `default=date.utcnow()`?
In the user-post relationship, which field actually stores the link in the database, and which attribute provides the convenient reverse lookup?

Key Points

1
Install and configure Flask-SQLAlchemy by setting `SQLALCHEMY_DATABASE_URI` (SQLite for development, swap the URI later for Postgres).
2
Create a `DB` instance tied to the Flask app (`DB = SQLAlchemy(app)`) before defining models.
3
Define `User` and `Post` as SQLAlchemy model classes with explicit column types, length limits, and nullability rules.
4
Use `__repr__` methods to make debugging output readable without leaking sensitive fields like passwords.
5
Model the one-to-many relationship with `User.posts = DB.relationship(..., backref='author')` and `Post.user_id = DB.ForeignKey('user.id')`.
6
Insert data using `DB.session.add()` followed by `DB.session.commit()` to persist changes.
7
Reset the schema during development with `DB.drop_all()` and rebuild it with `DB.create_all()` to start from a clean database.

Highlights

Switching from SQLite to Postgres can be as simple as changing the database URI, while keeping the same model and query code.

The `backref='author'` setting makes `post.author` available as a convenient reverse relationship without adding an `author` column to the `Post` table.

Using `default=date.utcnow` (callable, not called immediately) ensures `date_posted` is set to the current UTC time per new post.

`DB.session.add()` queues changes, but `DB.session.commit()` is what actually writes rows to the database.

`DB.drop_all()` plus `DB.create_all()` provides a reliable “clean slate” workflow for iterative development.

Topics

Flask-SQLAlchemy Setup
SQLAlchemy Models
One-to-Many Relationships
SQLite Database URI
Database Migrations Reset

Mentioned

Corey Schafer
ORM
SQL
UTC

Python Flask Tutorial: Full-Featured Web App Part 4 - Database with Flask-SQLAlchemy