Flask’s built-in request context is your best friend for managing database connections, ensuring you have one, and only one, connection active for the duration of each incoming HTTP request.
Let’s see this in action. Imagine a simple Flask app with a route that fetches user data:
from flask import Flask, g, request
import sqlite3
DATABASE = 'mydatabase.db'
app = Flask(__name__)
def get_db():
db = getattr(g, '_database', None)
if db is None:
db = g._database = sqlite3.connect(DATABASE)
# Optional: Set row_factory for dictionary-like access
db.row_factory = sqlite3.Row
return db
@app.teardown_appcontext
def close_connection(exception):
db = getattr(g, '_database', None)
if db is not None:
db.close()
print("Database connection closed.") # For demonstration
@app.route('/user/<int:user_id>')
def get_user(user_id):
db = get_db()
cursor = db.execute('SELECT id, username, email FROM users WHERE id = ?', (user_id,))
user = cursor.fetchone()
if user:
return {
"id": user["id"],
"username": user["username"],
"email": user["email"]
}
return {"error": "User not found"}, 404
if __name__ == '__main__':
# Create a dummy database for demonstration
with app.app_context():
db = sqlite3.connect(DATABASE)
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS users")
cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, email TEXT NOT NULL)")
cursor.execute("INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com')")
cursor.execute("INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com')")
db.commit()
db.close()
app.run(debug=True)
When you send a request to /user/1, Flask does a few things behind the scenes. The request object is created, and with it, the g object. g is a special global object that is unique to each request. It’s the perfect place to store resources that should live for the duration of a single request.
The get_db() function checks if a database connection already exists on g._database. If not, it creates a new sqlite3.connect('mydatabase.db') connection and attaches it to g._database. Crucially, if another call to get_db() happens within the same request, it will find the existing connection on g._database and return that same instance. This guarantees only one connection per request.
Once the request is finished processing, whether it succeeds or raises an error, Flask calls the function registered with app.teardown_appcontext. This is where close_connection comes in. It checks g._database again. If a connection exists, it’s safely closed. This cleanup ensures no lingering connections tie up database resources.
This pattern is incredibly robust. If you have multiple functions called within a single request that all need database access (e.g., a controller calling several service functions, each of which needs the DB), they can all call get_db(), and they will all receive the exact same connection object. This avoids the overhead and potential race conditions of opening multiple connections and ensures that transactions, if managed, operate on a single, consistent connection.
The g object is not just for database connections; it’s a general-purpose request-local storage. You could store a user object, a logger instance, or any other per-request data there. The key is that g is automatically managed by Flask’s context system and cleaned up at the end of the request.
One subtle point: if you were to manually close the connection inside your route handler before the request finishes, and then later call get_db() again, you’d get a new connection. The teardown_appcontext would then close the new connection, leaving the old, now-closed connection object still attached to g. This is why you should only rely on teardown_appcontext for closing.
The next logical step is handling database transactions across multiple operations within a single request.