Connection pooling in Flask - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to import the connection pool class from SQLAlchemy.

Flask

from sqlalchemy.pool import [1]

Drag options to blanks, or click blank then click option'

AConnectionPool

BQueuePool

CPoolManager

DSessionPool

Attempts:

3 left

💡 Hint

Common Mistakes

Using a non-existent class like ConnectionPool.

Confusing session management classes with pool classes.

The QueuePool is the default connection pool implementation in SQLAlchemy used with Flask.

2fill in blank

medium

Complete the code to create an engine with a connection pool size of 5.

Flask

engine = create_engine('sqlite:///app.db', pool_size=[1])

Drag options to blanks, or click blank then click option'

A5

B0

C1

D10

Attempts:

3 left

💡 Hint

Common Mistakes

Setting pool_size to 0 disables pooling.

Using a number too large or too small for the task.

Setting pool_size=5 limits the pool to 5 connections.

3fill in blank

hard

Fix the error in the code to correctly dispose the connection pool.

Flask

engine.[1]()

Drag options to blanks, or click blank then click option'

Arelease

Bclose

Cshutdown

Ddispose

Attempts:

3 left

💡 Hint

Common Mistakes

Using close() which only closes one connection.

Using shutdown() which is not a method on engine.

The dispose() method properly closes all connections in the pool.

4fill in blank

hard

Fill both blanks to configure the engine with a max overflow of 10 and a pool recycle time of 3600 seconds.

Flask

engine = create_engine('mysql://user:pass@localhost/db', pool_size=5, pool_overflow=[1], pool_recycle=[2])

Drag options to blanks, or click blank then click option'

A10

B3600

C300

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the values between pool_overflow and pool_recycle.

Using zero for pool_overflow disables overflow connections.

pool_overflow=10 allows 10 extra connections beyond pool_size.
pool_recycle=3600 recycles connections after 1 hour.

5fill in blank

hard

Fill all three blanks to create a scoped session with the engine and bind it properly.

Flask

Session = scoped_session(sessionmaker(bind=[1], autoflush=[2], autocommit=[3]))

Drag options to blanks, or click blank then click option'

Aengine

BTrue

CFalse

DNone

Attempts:

3 left

💡 Hint

Common Mistakes

Setting autocommit to True which is deprecated.

Not binding the session to the engine.

Bind the session to engine, set autoflush=False and autocommit=False for manual control.

Practice

1. What is the main benefit of using connection pooling in a Flask application?

easy

A. It reuses database connections to improve performance.

B. It automatically creates new database tables.

C. It encrypts all database queries for security.

D. It reduces the size of the Flask application code.

Solution

1. Step 1: Understand connection pooling purpose

   Connection pooling keeps database connections open and reuses them instead of opening new ones each time.

2. Step 2: Identify the benefit in Flask apps

   This reuse reduces connection overhead and speeds up database access, improving app performance.

3. Final Answer:

   It reuses database connections to improve performance. -> Option A

4. Quick Check:

   Connection pooling = reuse connections = better speed [OK]

Hint: Pooling means reusing connections to speed up queries [OK]

Common Mistakes:

• Thinking pooling creates tables automatically
• Confusing pooling with encryption
• Believing pooling reduces app code size

2. Which of the following is the correct way to set the maximum pool size in Flask-SQLAlchemy?

easy

A. app.config['SQL_POOL_LIMIT'] = 10

B. app.config['POOL_MAX_SIZE'] = 10

C. app.config['SQLALCHEMY_POOL_SIZE'] = 10

D. app.config['MAX_POOL'] = 10

Solution

1. Step 1: Recall Flask-SQLAlchemy config keys

   The correct key to set pool size is 'SQLALCHEMY_POOL_SIZE'.

2. Step 2: Match the option with correct key

   app.config['SQLALCHEMY_POOL_SIZE'] = 10 uses 'SQLALCHEMY_POOL_SIZE' correctly with value 10.

3. Final Answer:

   app.config['SQLALCHEMY_POOL_SIZE'] = 10 -> Option C

4. Quick Check:

   Pool size config = SQLALCHEMY_POOL_SIZE [OK]

Hint: Look for 'SQLALCHEMY_POOL_SIZE' to set pool size [OK]

Common Mistakes:

• Using incorrect config keys like POOL_MAX_SIZE
• Confusing pool size with max connections
• Misspelling config keys

3. Given this Flask-SQLAlchemy setup:

app.config['SQLALCHEMY_POOL_SIZE'] = 5
app.config['SQLALCHEMY_MAX_OVERFLOW'] = 10

What is the maximum number of simultaneous database connections allowed?

medium

A. 15

B. 10

C. 5

D. 20

Solution

1. Step 1: Understand pool size and max overflow

   Pool size is 5 connections kept open. Max overflow allows 10 extra temporary connections.

2. Step 2: Calculate total max connections

   Total max = pool size (5) + max overflow (10) = 15 connections.

3. Final Answer:

   15 -> Option A

4. Quick Check:

   Max connections = pool size + overflow = 15 [OK]

Hint: Add pool size and max overflow for max connections [OK]

Common Mistakes:

• Ignoring max overflow and choosing pool size only
• Adding incorrectly or mixing values
• Confusing max overflow as total limit

4. You set app.config['SQLALCHEMY_POOL_SIZE'] = 'five' in your Flask app. What will happen when you run the app?

medium

A. The app will run normally with pool size 5.

B. A TypeError or ValueError will occur due to wrong type.

C. The pool size will default to 10 silently.

D. The app will ignore the setting and use no pool.

Solution

1. Step 1: Check the type of pool size value

   Pool size must be an integer. Setting it as string 'five' is invalid.

2. Step 2: Identify error on invalid type

   Flask-SQLAlchemy or underlying SQLAlchemy will raise a TypeError or ValueError when parsing this config.

3. Final Answer:

   A TypeError or ValueError will occur due to wrong type. -> Option B

4. Quick Check:

   Wrong type config causes error, not silent fallback [OK]

Hint: Pool size must be integer, strings cause errors [OK]

Common Mistakes:

• Assuming string numbers work fine
• Expecting silent default fallback
• Ignoring error messages on startup

5. You want to optimize a Flask app for many users by adjusting connection pooling. Which combination of settings is best to handle 50 simultaneous users smoothly?

hard

A. SQLALCHEMY_POOL_SIZE=50, SQLALCHEMY_MAX_OVERFLOW=0

B. SQLALCHEMY_POOL_SIZE=20, SQLALCHEMY_MAX_OVERFLOW=10

C. SQLALCHEMY_POOL_SIZE=5, SQLALCHEMY_MAX_OVERFLOW=5

D. SQLALCHEMY_POOL_SIZE=10, SQLALCHEMY_MAX_OVERFLOW=40

Solution

1. Step 1: Calculate total connections needed

   To handle 50 users, total connections should be at least 50.

2. Step 2: Evaluate each option's total connections

   SQLALCHEMY_POOL_SIZE=10, SQLALCHEMY_MAX_OVERFLOW=40: 10 + 40 = 50 (meets requirement)
   SQLALCHEMY_POOL_SIZE=50, SQLALCHEMY_MAX_OVERFLOW=0: 50 + 0 = 50 (meets but no overflow flexibility)
   SQLALCHEMY_POOL_SIZE=5, SQLALCHEMY_MAX_OVERFLOW=5: 5 + 5 = 10 (too low)
   SQLALCHEMY_POOL_SIZE=20, SQLALCHEMY_MAX_OVERFLOW=10: 20 + 10 = 30 (too low)

3. Step 3: Choose best option for flexibility

   SQLALCHEMY_POOL_SIZE=10, SQLALCHEMY_MAX_OVERFLOW=40 allows a smaller base pool with high overflow, balancing resource use and capacity.

4. Final Answer:

   SQLALCHEMY_POOL_SIZE=10, SQLALCHEMY_MAX_OVERFLOW=40 -> Option D

5. Quick Check:

   Pool + overflow ≥ users; flexible overflow is best [OK]

Hint: Sum pool size and overflow to cover all users [OK]

Common Mistakes:

• Choosing too small total connections
• Ignoring overflow flexibility
• Setting pool size too high without overflow