Skip to main content
Flask Basics Tutorial
CHAPTER 09 Beginner

Flask SQLAlchemy ORM Basics

Updated: May 14, 2026
35 min read

# CHAPTER 9

Flask SQLAlchemy ORM Basics

1. Introduction

In the previous chapter, we established a connection to the database. Now, we must define the structure of our data. Using SQLAlchemy, we don't write SQL CREATE TABLE commands. Instead, we define our database schemas using native Python Classes. In this chapter, we will build a User model, use the Python Interactive Shell to generate the physical database file, and execute basic queries to add and retrieve data.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Define database tables using Python Classes (Models).
  • Map Python attributes to SQL Columns (Integer, String).
  • Use the Python REPL (Interactive Shell) to execute code live.
  • Use db.createall() to generate the SQLite database file.
  • Add records using db.session.add().

3. Beginner-Friendly Explanation

Imagine you are the architect of a new library. You don't start by nailing wood together. You draw a Blueprint. You say, "Every Book must have an ID Number, a Title (text), and a Page Count (number)." In SQLAlchemy, a Model is that Blueprint. You write a Python class that describes exactly what a Book should look like. Once the blueprint is finished, you hand it to the construction crew (db.createall()), and they instantly build the physical wooden shelves (the SQLite Database File) to match your exact specifications.

4. Step 1: Defining a Model

Let's expand our app.py file to include a blueprint for a User.
python
12345678910111213141516171819202122232425 
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import os

basedir = os.path.abspath(os.path.dirname(__file__))
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'app.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# --- NEW CODE: Defining the Database Blueprint ---
class User(db.Model):
    # Primary Key: A unique ID number for every user
    id = db.Column(db.Integer, primary_key=True)
    
    # String Column: Max 80 characters, must be unique, cannot be empty
    username = db.Column(db.String(80), unique=True, nullable=False)
    
    # String Column: Max 120 characters
    email = db.Column(db.String(120), unique=True, nullable=False)

    # A magic method to make the output readable in the terminal
    def __repr__(self):
        return f"<User {self.username}>"

*(Notice how the Python types db.Integer and db.String directly map to SQL data types).*

5. Step 2: Generating the Database

We wrote the blueprint, but the physical app.db file does not exist yet. To build it, we will use the Python Interactive Shell (REPL). This allows us to run Python commands live inside our application's context.

Open your terminal, ensure (venv) is active, and start the Python shell:

bash
1 
python

Your prompt will change to >>>. Type the following commands exactly:

python
123 
>>> from app import app, db
>>> app.app_context().push() # Tells Flask we are working inside the app
>>> db.create_all() # Creates the physical database file!

*Look at your project folder. You will magically see a new file called app.db appear! The tables have been created.*

6. Step 3: Creating Data (Insert)

Stay in the Python shell. Let's add users to the database.
python
123456789101112 
>>> from app import User

# 1. Instantiate the Python object
>>> user1 = User(username=&#039;alice', email='alice@example.com')
>>> user2 = User(username=&#039;bob', email='bob@example.com')

# 2. Stage the objects in the Database Session
>>> db.session.add(user1)
>>> db.session.add(user2)

# 3. Commit the transaction to save it permanently!
>>> db.session.commit()

*(If you forget .commit(), the data is erased the moment you close the terminal).*

7. Step 4: Reading Data (Select)

Now let's ask SQLAlchemy to retrieve the data we just saved.
python
1234567891011121314 
# Get a list of ALL users in the table
>>> all_users = User.query.all()
>>> print(all_users)
[<User alice>, <User bob>]

# Find a specific user by their Primary Key ID
>>> specific_user = User.query.get(1)
>>> print(specific_user.email)
&#039;alice@example.com'

# Filter users based on a condition
>>> filtered_user = User.query.filter_by(username=&#039;bob').first()
>>> print(filtered_user)
<User bob>

*Type exit() and press Enter to leave the Python shell.*

8. Backend Workflow: The App Context

In Step 5, we ran app.appcontext().push(). Why? Because Flask is designed to handle multiple applications running simultaneously, the db object doesn't automatically know *which* application it is currently working with. The App Context acts as a spotlight, telling SQLAlchemy, "Focus your attention on this specific Flask app and its configuration right now." You will encounter App Context errors frequently if you try to run database commands outside of a routing function without pushing the context first.

9. Best Practices

  • Never Modify Live Tables with createall(): The db.createall() command is great for creating a fresh database from scratch. However, if you add a new column to your Python Model next week, running createall() again will NOT update the existing table. To safely alter existing tables, professional developers use a tool called Flask-Migrate (Alembic), which we will introduce in later advanced builds.

10. Common Mistakes

  • Forgetting db.session.commit(): The most common beginner frustration. They run db.session.add(user1), query the database, see the user, close the terminal, and the next day the user is gone. add() only places the data in temporary staging (RAM). It is not written to the hard drive until you call commit().

11. Exercises

  1. 1. What is the fundamental difference between User.query.all() and User.query.first()? What data type does each return (A list vs a single object)?

12. Coding Challenges

  • Challenge: Open the Python shell. Push the app context. Create a new User object with your own name and email. Add it to the session and commit it. Then, write a query using .filterby() to retrieve your specific user from the database and print its email.

13. MCQs with Answers

Question 1

In SQLAlchemy, what is the purpose of the db.session.commit() command?

Question 2

When defining a database Model, which argument ensures that no two users can register with the exact same email address?

14. Interview Questions

  • Q: Explain the purpose of the "Database Session" (db.session) in SQLAlchemy. Why does SQLAlchemy utilize a staging area rather than executing SQL inserts instantly upon object instantiation?
  • Q: What is the Flask "Application Context" (app.app_context()), and why is it required when executing database operations via the interactive terminal shell?

15. FAQs

Q: Can I view the app.db file visually like an Excel spreadsheet? A: Yes! You can download free GUI tools like DB Browser for SQLite or DBeaver. You simply open the app.db file in the program, and it will display all your tables and rows in a beautiful, clickable interface.

16. Summary

In Chapter 9, we mapped our Python code directly to SQL architecture. We defined a User blueprint using SQLAlchemy Model classes and configured column constraints like unique and nullable. Utilizing the Python Interactive Shell and the Flask App Context, we generated the physical SQLite database file. Finally, we executed basic CRUD operations, successfully staging and committing records to the database and querying them back out using Python syntax.

17. Next Chapter Recommendation

Terminal commands are great for developers, but users need graphical interfaces. We must wire our database to our web pages. Proceed to Chapter 10: Building CRUD Applications in Flask.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter Working with Databases in Flask
Next Chapter Building CRUD Applications in Flask

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6

🎥 Related Videos 1