Database query optimization in Flask - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding ORM Lazy Loading

In Flask with SQLAlchemy, what is the main effect of using lazy='dynamic' on a relationship?

A It disables loading of related items completely.

B It loads all related items immediately when the parent object is loaded.

C It caches related items in memory permanently after first load.

D It delays loading related items until explicitly queried, returning a query object instead of a list.

💡 Hint

Think about when related data is fetched from the database.

Using lazy='dynamic' returns a query object for related items, so you can filter or paginate before loading. This delays actual database access until you explicitly ask for data.

❓ component_behavior

intermediate

Effect of N+1 Query Problem

Given a Flask app using SQLAlchemy, what happens if you access a related attribute in a loop without eager loading?

Flask

users = User.query.all()
for user in users:
    print(user.posts)

A Only one query runs for users and posts separately, then cached.

B One query for users, then one query per user to load posts, causing many queries.

C No queries run until posts are accessed outside the loop.

D One single query joins users and posts, loading all data at once.

💡 Hint

Consider how many database queries happen inside the loop.

Without eager loading, accessing user.posts triggers a separate query per user, causing the N+1 query problem.

📝 Syntax

advanced

Correct Use of joinedload for Eager Loading

Which option correctly applies eager loading with joinedload in Flask SQLAlchemy to avoid N+1 queries?

Flask

from sqlalchemy.orm import joinedload

users = User.query.options(_____).all()

A joinedload(User.posts)

B joinedload('User.posts')

C joinedload(posts)

D joinedload('posts')

💡 Hint

Check how to reference the relationship attribute correctly.

The correct syntax uses the class attribute User.posts inside joinedload(). Passing a string or undefined variable causes errors.

🔧 Debug

advanced

Diagnosing Slow Query in Flask App

A Flask app runs slowly when listing users and their posts. The code is: users = User.query.all() for user in users: print(len(user.posts)) What is the main cause of the slowness?

A The code triggers one query for users plus one query per user to load posts (N+1 problem).

B The query uses a join that returns duplicate users causing slow processing.

C The posts attribute is not defined, causing repeated errors slowing the app.

D The database connection is closed before queries run, causing retries.

💡 Hint

Think about how many queries happen inside the loop.

Accessing user.posts inside the loop without eager loading causes a separate query per user, leading to slow performance.

❓ state_output

expert

Output of Query with Subqueryload and Filtering

Consider this Flask SQLAlchemy code: posts = Post.query.options(subqueryload(Post.comments)).filter(Post.title.like('%Flask%')).all() What is true about the loaded comments for each post?

A Comments are loaded lazily one by one when accessed, causing many queries.

B Only comments containing 'Flask' are loaded due to the filter on posts.

C All comments for each post are loaded in one additional query using subqueryload.

D No comments are loaded because subqueryload does not work with filters.

💡 Hint

Remember what subqueryload does with related collections.

subqueryload loads all related comments for the filtered posts in one separate query, improving performance by avoiding N+1 queries.

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