Connection pooling in Flask - Step-by-Step Execution

Concept Flow - Connection pooling

App starts

↓

Create pool of DB connections

↓

Request comes in

↓

Get connection from pool

↓

Use connection for query

↓

Return connection to pool

↓

Next request uses pooled connection

↓

App stops, close all connections

This flow shows how a Flask app creates a pool of database connections to reuse them efficiently for incoming requests.

Execution Sample

Flask

from flask import Flask
from sqlalchemy import create_engine

app = Flask(__name__)
engine = create_engine('sqlite:///mydb.sqlite', pool_size=2)

@app.route('/')
def index():
    with engine.connect() as conn:
        result = conn.execute('SELECT 1')
        return str(result.scalar())

This Flask app uses SQLAlchemy to create a connection pool of size 2 and reuses connections for each request.

Execution Table

Step	Action	Pool State (connections in use / available)	Result
1	App starts and creates pool with 2 connections	0 / 2	Pool ready
2	First request arrives	0 / 2	Get connection from pool
3	Assign connection #1 to request	1 / 1	Connection #1 in use
4	Execute query using connection #1	1 / 1	Query result: 1
5	Return connection #1 to pool	0 / 2	Connection #1 available
6	Second request arrives	0 / 2	Get connection from pool
7	Assign connection #2 to request	1 / 1	Connection #2 in use
8	Execute query using connection #2	1 / 1	Query result: 1
9	Return connection #2 to pool	0 / 2	Connection #2 available
10	Third request arrives	0 / 2	Get connection from pool
11	Assign connection #1 to request (reused)	1 / 1	Connection #1 in use
12	Execute query using connection #1	1 / 1	Query result: 1
13	Return connection #1 to pool	0 / 2	Connection #1 available
14	App stops, close all connections	0 / 0	Pool closed

💡 App stops or no more requests; all connections closed

Variable Tracker

Variable	Start	After Step 3	After Step 5	After Step 7	After Step 9	After Step 11	After Step 13	Final
connections_in_use	0	1	0	1	0	1	0	0
connections_available	2	1	2	1	2	1	2	0

Key Moments - 3 Insights

Why does the pool have connections available even after multiple requests?

Because connections are returned to the pool after use (see steps 5, 9, 13), so they can be reused for new requests.

What happens if two requests come at the same time and pool size is 2?

Both requests get assigned one connection each (steps 3 and 7), using all available connections until returned.

Why is connection #1 reused in step 11 instead of creating a new one?

Because connection #1 was returned to the pool and is available, so the pool reuses existing connections to save resources.

Visual Quiz - 3 Questions

Look at the execution table, what is the pool state after step 7?

A 2 / 0

B 0 / 2

C 1 / 1

D 0 / 1

💡 Hint

Check the 'Pool State' column at step 7 in the execution table.

At which step does the connection #1 get returned to the pool after first use?

A Step 3

B Step 5

C Step 7

D Step 9

💡 Hint

Look for when 'Return connection #1 to pool' happens in the execution table.

If the pool size was 1, what would happen at step 7 when the second request arrives?

A It would wait until the connection is returned

B It would create a new connection beyond the pool size

C It would fail immediately

D It would reuse connection #1 immediately

💡 Hint

Think about how connection pools limit simultaneous connections; see steps 3 and 7.

Concept Snapshot

Connection pooling in Flask with SQLAlchemy:
- Create engine with pool_size (e.g., 2)
- Pool holds open DB connections
- Requests get connections from pool
- Connections returned after use
- Reuse saves time and resources
- Limits max simultaneous DB connections

Full Transcript

Connection pooling means keeping a set of database connections open and ready to use in a Flask app. When the app starts, it creates a pool with a fixed number of connections. Each incoming request takes one connection from the pool, uses it to run queries, then returns it back. This way, connections are reused instead of opening and closing each time, which is faster and more efficient. The execution table shows how connections are assigned and returned step-by-step. If the pool size is 2, two requests can use connections simultaneously. When a connection is returned, it becomes available for the next request. If more requests come than the pool size, they wait until a connection is free. This helps manage resources and improves app performance.