Database Access in Python: The DB-API

1. Introduction to DB-API

Python provides a standard interface called DB-API 2.0 (PEP 249) for interacting with databases. This means the code structure looks almost identical whether you use SQLite, MySQL, or PostgreSQL.

Common Database Modules:

2. The Connectivity Workflow (5-Step Lifecycle)

Every database interaction follows this pattern:

Study Deep: The Cursor Pattern

Think of the Connection object as the physical "pipe" leading to the database. The Cursor object is the "hand" inside that pipe.

• Why a Cursor? Without it, you'd have to read the entire database into your computer's memory at once (slow and memory-intensive).
• The Efficiency: The Cursor lets you pull one row at a time, or small batches, exactly when you're ready to process them. This is crucial for handling millions of records without crashing your application.

Important: Step 4 (Commit) is only needed for write operations. SELECT queries don't modify data, so no commit is needed.

3. Best Practices: Context Manager

Use Python's with statement to auto-close connections (even on error):

import sqlite3

with sqlite3.connect('my_database.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    rows = cursor.fetchall()
# Connection auto-closes here, even if an error occurred

4. SQL Basics (Refresher)

DDL vs. DML:

• DDL (Data Definition Language): Defines the structure (tables, columns). Changes are auto-committed.
• DML (Data Manipulation Language): Manipulates the data (rows). Requires explicit commit().