Why ORM simplifies database access in Flask - See It in Action

Why ORM simplifies database access

📖 Scenario: You are building a simple web app to manage a list of books. Instead of writing raw SQL queries, you want to use an ORM to make database access easier and more readable.

🎯 Goal: Create a Flask app that uses SQLAlchemy ORM to define a Book model, configure the database, add some books, and query them using ORM methods.

📋 What You'll Learn

Define a Book model class with id, title, and author fields

Configure the Flask app to use SQLite database with SQLAlchemy

Add two book entries to the database using ORM

Query all books from the database using ORM and store in a variable

💡 Why This Matters

🌍 Real World

ORMs let developers work with databases using familiar programming language objects instead of writing complex SQL queries. This speeds up development and reduces errors.

💼 Career

Many web development jobs require knowledge of ORMs like SQLAlchemy to efficiently manage database access in Python web apps.

1

Set up Flask app and SQLAlchemy

Create a Flask app instance called app and configure it to use SQLite database at sqlite:///books.db. Then create a SQLAlchemy object called db linked to app.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

# Your code here

Hint

Use Flask(__name__) to create the app and set app.config['SQLALCHEMY_DATABASE_URI'] to the SQLite URL.

2

Define the Book model

Define a class called Book that inherits from db.Model. Add three columns: id as an integer primary key, title as a string of max length 100, and author as a string of max length 50.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///books.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

# Define Book model here

Hint

Use db.Column with appropriate types and primary_key=True for id.

3

Add books to the database

Create two Book objects with titles 'The Great Gatsby' by 'F. Scott Fitzgerald' and '1984' by 'George Orwell'. Add them to the database session and commit the session.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///books.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    author = db.Column(db.String(50))

# Create two Book objects and add to session
# Commit the session

Hint

Create Book instances with the given titles and authors, then add and commit them using db.session.

4

Query all books using ORM

Use Book.query.all() to get all book records from the database and store them in a variable called all_books.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///books.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    author = db.Column(db.String(50))

book1 = Book(title='The Great Gatsby', author='F. Scott Fitzgerald')
book2 = Book(title='1984', author='George Orwell')
db.session.add(book1)
db.session.add(book2)
db.session.commit()

# Query all books and store in all_books

Hint

Use Book.query.all() to get all records and assign to all_books.

Practice

1. Why does using an ORM in Flask make working with databases easier?

easy

A. Because it only works with one type of database

B. Because it requires writing more SQL code manually

C. Because it lets you use Python objects instead of writing SQL queries directly

D. Because it removes the need to understand databases at all

Solution

1. Step 1: Understand ORM purpose

   ORM stands for Object-Relational Mapping, which connects Python classes to database tables.

2. Step 2: Identify how ORM simplifies work

   It lets you use Python objects to create, read, update, and delete data without writing SQL queries manually.

3. Final Answer:

   Because it lets you use Python objects instead of writing SQL queries directly -> Option C

4. Quick Check:

   ORM uses Python objects, not raw SQL [OK]

Hint: Think: ORM means Python objects replace SQL queries [OK]

Common Mistakes:

• Thinking ORM requires more SQL code
• Believing ORM only supports one database
• Assuming ORM removes all database knowledge needs

2. Which of the following is the correct way to define a model class using Flask's ORM (SQLAlchemy)?

easy

A. class User: id = Column(Integer, primary_key=True)

B. def User(): id = Column(Integer, primary_key=True)

C. User = db.Model(id = Column(Integer, primary_key=True))

D. class User(db.Model): id = db.Column(db.Integer, primary_key=True)

Solution

1. Step 1: Recall Flask SQLAlchemy model syntax

   Models inherit from db.Model and define columns with db.Column and types like db.Integer.

2. Step 2: Check each option

   class User(db.Model): id = db.Column(db.Integer, primary_key=True) correctly defines a class inheriting db.Model with a column using db.Column and db.Integer.

3. Final Answer:

   class User(db.Model): id = db.Column(db.Integer, primary_key=True) -> Option D

4. Quick Check:

   Model class inherits db.Model and uses db.Column [OK]

Hint: Model classes inherit db.Model and use db.Column for fields [OK]

Common Mistakes:

• Not inheriting from db.Model
• Using Column without db prefix
• Defining model as a function instead of class

3. Given this Flask ORM code:

user = User(name='Alice')
db.session.add(user)
db.session.commit()
print(user.id)

What will print(user.id) output after commit?

medium

A. The new unique ID assigned by the database

B. None

C. An error because id is not set

D. The string 'Alice'

Solution

1. Step 1: Understand ORM session commit behavior

   When you add and commit a new object, the database assigns a unique ID (primary key) automatically.

2. Step 2: Check what user.id holds after commit

   After commit, ORM updates the object with the assigned ID, so user.id contains that new value.

3. Final Answer:

   The new unique ID assigned by the database -> Option A

4. Quick Check:

   Commit assigns and updates object ID [OK]

Hint: After commit, ORM sets the object's ID from the database [OK]

Common Mistakes:

• Expecting id to be None before commit
• Thinking id is the name value
• Assuming commit does not update object fields

4. What is wrong with this Flask ORM code snippet?

user = User(name='Bob')
db.session.add(user)
print(user.id)

medium

A. It will print None because commit is missing

B. It will raise a syntax error

C. It will print the name instead of id

D. It will print a random number

Solution

1. Step 1: Identify missing commit

   The code adds the user to the session but does not commit, so the database does not assign an ID yet.

2. Step 2: Understand user.id value before commit

   Before commit, user.id is None because the database has not generated the primary key.

3. Final Answer:

   It will print None because commit is missing -> Option A

4. Quick Check:

   Commit needed to assign ID [OK]

Hint: Remember to commit to get database-generated IDs [OK]

Common Mistakes:

• Forgetting to call commit() after add()
• Expecting id to be set immediately after add()
• Confusing id with other attributes

5. You want to update a user's email in Flask ORM. Which approach best shows how ORM simplifies this compared to raw SQL?

user = User.query.filter_by(id=5).first()
user.email = '[e-mail]'
db.session.commit()

Why is this simpler than writing SQL?

hard

A. Because you must write raw SQL inside the Python code

B. Because you update the Python object directly without writing an UPDATE SQL statement

C. Because ORM automatically commits changes without calling commit()

D. Because you still need to write the full SQL UPDATE query manually

Solution

1. Step 1: Understand ORM object update

   You fetch the user as a Python object, change its attribute, and commit the session.

2. Step 2: Compare with raw SQL

   Raw SQL requires writing an UPDATE statement manually, but ORM lets you work with objects and attributes directly.

3. Final Answer:

   Because you update the Python object directly without writing an UPDATE SQL statement -> Option B

4. Quick Check:

   ORM updates objects, no manual SQL needed [OK]

Hint: ORM lets you change objects, not write SQL queries [OK]

Common Mistakes:

• Thinking ORM requires manual SQL for updates
• Assuming commit() is automatic
• Believing raw SQL is simpler than ORM