Connection Management: Context Managers and Close
You'll learn: How to properly manage database connections using Python's with statement to ensure resources are always cleaned up, even when errors occur.
Why Connection Management Matters
Think of a database connection like checking out a library book. If you forget to return it, the library has one fewer book available for others. Similarly, if you open database connections without closing them, you'll eventually run out of available connections, causing your application to fail.
The Manual Way (Not Recommended)
After establishing a connection with sqlite3 or psycopg2, you could manually close it:
import sqlite3
conn = sqlite3.connect('mydata.db')
# do work...
conn.close() # Easy to forget!
The problem? If an error occurs before conn.close(), the connection never closes—it leaks.
The Better Way: Context Managers
Python's with statement automatically handles cleanup, even if exceptions occur:
import sqlite3
with sqlite3.connect('mydata.db') as conn:
cursor = conn.execute("SELECT * FROM users")
results = cursor.fetchall()
# Connection automatically closed here
The with block ensures the connection closes when you exit—whether normally or due to an error. It's like a library book with an auto-return feature!
Best Practices
- Always use
withfor connections to prevent resource leaks - The connection closes automatically at the end of the
withblock - Works with both
sqlite3andpsycopg2connections - Especially critical in long-running applications and web servers
Key Takeaway: Use with statements when working with database connections to guarantee automatic cleanup and prevent resource leaks—your application's reliability depends on proper connection management.