Database query optimization in Flask - Mini Project: Build & Apply

Database Query Optimization with Flask

📖 Scenario: You are building a simple Flask web app that shows a list of books from a database. The database has many books, but you want to show only the books with more than 300 pages to users.

🎯 Goal: Build a Flask app that queries the database efficiently to get only books with more than 300 pages and displays their titles.

📋 What You'll Learn

Create a list of book dictionaries with title and pages

Add a variable to set the minimum page count filter

Use a list comprehension to select books with pages greater than the filter

Create a Flask route to display the filtered book titles

💡 Why This Matters

🌍 Real World

Filtering data before sending it to users helps apps run faster and use less memory.

💼 Career

Knowing how to optimize queries and filter data efficiently is important for backend web development jobs.

1

Create the book data list

Create a list called books with these exact dictionaries: {'title': 'Python Basics', 'pages': 250}, {'title': 'Flask Web Development', 'pages': 350}, {'title': 'Advanced Python', 'pages': 400}

Flask

# Create the list of books with title and pages
# Your code here

Hint

Use a list with dictionaries. Each dictionary has keys 'title' and 'pages'.

2

Set the minimum page count filter

Create a variable called min_pages and set it to 300 to filter books with more than 300 pages

Flask

books = [
    {'title': 'Python Basics', 'pages': 250},
    {'title': 'Flask Web Development', 'pages': 350},
    {'title': 'Advanced Python', 'pages': 400}
]
# Set the minimum pages filter
# Your code here

Hint

Just create a variable named min_pages and assign 300.

3

Filter books with list comprehension

Create a list called filtered_books using a list comprehension that includes only books from books where the pages value is greater than min_pages

Flask

books = [
    {'title': 'Python Basics', 'pages': 250},
    {'title': 'Flask Web Development', 'pages': 350},
    {'title': 'Advanced Python', 'pages': 400}
]
min_pages = 300
# Filter books with pages > min_pages
# Your code here

Hint

Use a list comprehension with book for book in books if book['pages'] > min_pages.

4

Create Flask route to display filtered books

Import Flask and create an app. Then add a route /books that returns a string with the titles of filtered_books joined by commas

Flask

books = [
    {'title': 'Python Basics', 'pages': 250},
    {'title': 'Flask Web Development', 'pages': 350},
    {'title': 'Advanced Python', 'pages': 400}
]
min_pages = 300
filtered_books = [book for book in books if book['pages'] > min_pages]

# Import Flask and create app
# Add route /books to show titles
# Your code here

Hint

Import Flask, create app, define route /books, and return joined titles.

Practice

1. What is the main benefit of using query filters in Flask SQLAlchemy?

easy

A. To increase the number of database connections

B. To slow down the query execution

C. To make the database schema more complex

D. To reduce the amount of data retrieved from the database

Solution

1. Step 1: Understand query filters

   Query filters limit the rows returned by a database query based on conditions.

2. Step 2: Effect on data retrieval

   By filtering, only relevant data is fetched, reducing load and improving speed.

3. Final Answer:

   To reduce the amount of data retrieved from the database -> Option D

4. Quick Check:

   Filters reduce data load = B [OK]

Hint: Filters limit data fetched, making queries faster [OK]

Common Mistakes:

• Thinking filters increase data size
• Confusing filters with database schema changes
• Assuming filters slow down queries

2. Which of the following is the correct syntax to limit query results to 10 records in Flask SQLAlchemy?

easy

A. User.limit(10).query.all()

B. User.query.all().limit(10)

C. User.query.limit(10).all()

D. User.query.filter(10).all()

Solution

1. Step 1: Identify correct method chaining

   In Flask SQLAlchemy, limit() is called on the query object before all().

2. Step 2: Check each option

   User.query.limit(10).all() correctly calls limit(10) before all(). Others misuse method order or methods.

3. Final Answer:

   User.query.limit(10).all() -> Option C

4. Quick Check:

   Limit before all() = C [OK]

Hint: Use limit() before all() to restrict records [OK]

Common Mistakes:

• Calling limit() after all()
• Using filter() instead of limit()
• Incorrect method chaining order

3. Given the code:

users = User.query.filter(User.age > 30).limit(5).all()

What does this query return?

medium

A. All users older than 30

B. First 5 users older than 30

C. 5 users regardless of age

D. Syntax error due to filter condition

Solution

1. Step 1: Analyze filter condition

   The filter selects users where age is greater than 30.

2. Step 2: Understand limit effect

   The limit(5) restricts the result to the first 5 matching users.

3. Final Answer:

   First 5 users older than 30 -> Option B

4. Quick Check:

   Filter + limit = first 5 matching users [OK]

Hint: Filter narrows data, limit caps number returned [OK]

Common Mistakes:

• Ignoring the limit and thinking all filtered users return
• Confusing filter syntax with bitwise operator
• Assuming limit applies before filter

4. What is wrong with this Flask SQLAlchemy query?

users = User.query.filter(User.name == 'Alice').all().limit(3)

medium

A. Calling limit() after all() causes an error

B. Filter condition syntax is incorrect

C. Missing parentheses in filter method

D. No error, query is correct

Solution

1. Step 1: Understand method chaining order

   In Flask SQLAlchemy, limit() must be called before all() because all() executes the query and returns a list.

2. Step 2: Identify error cause

   Calling limit() on a list (result of all()) causes an AttributeError.

3. Final Answer:

   Calling limit() after all() causes an error -> Option A

4. Quick Check:

   limit() after all() = error [OK]

Hint: Call limit() before all() to avoid errors [OK]

Common Mistakes:

• Calling limit() after all()
• Thinking all() returns a query object
• Misplacing filter parentheses

5. You want to optimize a query to fetch only the usernames of active users, limiting results to 20. Which is the best approach?

hard

A. User.query.with_entities(User.username).filter(User.active == True).limit(20).all()

B. User.query.filter(User.active == True).all()[:20]

C. User.query.all().filter(User.active == True).limit(20)

D. User.query.filter(User.active == True).limit(20).all()

Solution

1. Step 1: Select only needed fields

   Using with_entities(User.username) fetches only usernames, reducing data load.

2. Step 2: Apply filter and limit correctly

   Filter active users and limit results to 20 before executing with all().

3. Step 3: Evaluate other options

   User.query.filter(User.active == True).limit(20).all() fetches full user objects, not optimized. User.query.all().filter(User.active == True).limit(20) calls all() before filter, causing error. User.query.filter(User.active == True).all()[:20] fetches all then slices in Python, inefficient.

4. Final Answer:

   User.query.with_entities(User.username).filter(User.active == True).limit(20).all() -> Option A

5. Quick Check:

   Select fields + filter + limit = D [OK]

Hint: Use with_entities() to select fields, then filter and limit [OK]

Common Mistakes:

• Fetching full objects instead of specific fields
• Calling all() before filter or limit
• Slicing results in Python instead of limiting in query