DB-API 2.0 Specification & sqlite3 Native Module

1Concept

PEP 249 defines the Python Database API 2.0 standard across all database drivers. Parameterized queries (`?` or `:name`) separate SQL logic from data, completely neutralizing SQL Injection attacks.

2Architecture Diagram

Input: user_id = "1 OR 1=1" ---> Parameterized: WHERE id = ? ---> Safe from SQL Injection!

3Code Example

import sqlite3

# In-memory database testing
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, balance REAL);")
# Parameterized insertion (Safe against SQL Injection)
cursor.execute("INSERT INTO users (username, balance) VALUES (?, ?);", ("alex_dev", 1250.50))
conn.commit()

cursor.execute("SELECT id, username, balance FROM users WHERE id = ?;", (1,))
user_row = cursor.fetchone()
print(f"Fetched User Record: ID={user_row[0]}, Name={user_row[1]}, Balance=${user_row[2]}")
conn.close()

4Expected Output

Fetched User Record: ID=1, Name=alex_dev, Balance=$1250.5

5Key Takeaways

• ✓NEVER use f-strings or string concatenation (`%`) to format SQL queries.
• ✓Always use context managers (`with conn:`) to auto-commit transactions.
• ✓`cursor.rowcount` reports number of rows affected by INSERT/UPDATE/DELETE.