SQLite Integration & Database Intro in Python

Welcome to Module 6! In this session, we transition from flat file storage (CSV, JSON) to structured relational storage. Python comes pre-installed with the sqlite3 library, allowing you to build transactional database applications without installing a standalone database engine.

1. The Python DB-API 2.0 Architecture

Python standardizes relational database access through PEP 249 (Python Database API Specification v2.0). Whether interacting with SQLite, PostgreSQL, MySQL, or Oracle, the underlying workflow follows four standard core components:

DB-API Component	Primary Role	Common SQLite Methods
1. Connection Object	Manages the active session, file locks, and transaction boundary	`sqlite3.connect()`, `commit()`, `close()`
2. Cursor Object	Executes SQL statements, retrieves results, tracks cursor state	`cursor()`, `execute()`, `executemany()`
3. Execution Methods	Passes raw SQL string queries and parameter tuples to engine	`cursor.execute(sql, params)`
4. Fetching Methods	Retrieves rows returned by a query	`fetchone()`, `fetchmany(n)`, `fetchall()`

2. Connecting to SQLite & Connection Management

You can store data either in a persistent file on disk or in volatile RAM using :memory:. Using a context manager guarantees that open transactions commit cleanly and connections shut down automatically.

import sqlite3 # Option A: In-memory temporary database conn_memory = sqlite3.connect(':memory:') # Option B: Disk-backed persistent database with sqlite3.connect('company_store.db') as conn: cursor = conn.cursor() # Create table schema cursor.execute(''' CREATE TABLE IF NOT EXISTS employees ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, department TEXT NOT NULL, salary REAL NOT NULL ) ''') print("Employees table initialized successfully.")

In-Memory Efficiency: sqlite3.connect(':memory:') creates a superfast, isolated relational database inside your program's RAM. It is ideal for rapid unit testing and fast prototyping.

3. Safely Inserting Data & Parameterized Queries

Never concatenate user inputs into standard SQL string formats using f"INSERT INTO ... VALUES ({user_input})". This exposes your application to catastrophic SQL Injection Attacks. Use positional placeholders (?) or named placeholders instead.

import sqlite3 conn = sqlite3.connect(':memory:') cursor = conn.cursor() cursor.execute(''' CREATE TABLE IF NOT EXISTS inventory ( sku TEXT PRIMARY KEY, item_name TEXT, stock_count INTEGER ) ''') # SAFE: Positional Parameterization item_tuple = ('SKU-101', 'Keyboard', 45) cursor.execute("INSERT INTO inventory VALUES (?, ?, ?)", item_tuple) # SAFE: Bulk Parameterized Insertion bulk_items = [ ('SKU-102', 'Mouse', 120), ('SKU-103', 'Monitor', 15), ('SKU-104', 'USB Dock', 60) ] cursor.executemany("INSERT INTO inventory VALUES (?, ?, ?)", bulk_items) conn.commit() print(f"Total inserted rows: {cursor.rowcount}")

Security Warning: Always pass dynamic values into cursor.execute() via a tuple or dictionary as the second argument. Let the DB driver escape values safely.

4. Querying and Fetching Results

After running a SELECT query using cursor.execute(), use fetch methods to consume the active result set.

cursor.execute("SELECT * FROM inventory WHERE stock_count > ?", (30,)) # Fetch single row first_row = cursor.fetchone() print("First matching row:", first_row) # Fetch remaining rows remaining_rows = cursor.fetchall() print("Remaining matching rows:", remaining_rows) # Using dict-like access with Row Factory conn.row_factory = sqlite3.Row cursor_dict = conn.cursor() cursor_dict.execute("SELECT sku, item_name FROM inventory WHERE sku = ?", ('SKU-101',)) row = cursor_dict.fetchone() print(f"Item: {row['item_name']} (SKU: {row['sku']})")

🏋️ Try It Yourself: Practice Challenges

Challenge 1: Create Books Schema

Write an in-memory SQLite schema for a books table with columns book_id, title, author, and price.

Challenge 2: Bulk Parameter Insertion

Insert 3 records into your books table using executemany() with parameterized tuples.

Challenge 3: Aggregate Query

Execute an SQL query using AVG(price) to return the average price of all books in the database.

⚡ Interactive Sandbox (SQLite Engine)

import sqlite3

# 1. Connect to In-Memory Database
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# 2. Create Table
cursor.execute('''
    CREATE TABLE products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price REAL NOT NULL
    )
''')

# 3. Parameterized Insertions
items = [
    ('Wireless Headset', 89.99),
    ('Mechanical Keyboard', 129.50),
    ('Ergonomic Mouse', 45.00)
]

cursor.executemany("INSERT INTO products (name, price) VALUES (?, ?)", items)
conn.commit()

# 4. Query Data
cursor.execute("SELECT * FROM products WHERE price > ?", (50.0,))
rows = cursor.fetchall()

print("=== High-Value Products (Price > $50) ===")
for r in rows:
    print(f"ID: {r[0]} | Item: {r[1]} | Cost: ${r[2]:.2f}")

conn.close()

Console Output:

Click "Run Code" above to execute SQLite database script...

📝 Knowledge Check Quiz

1. Which special connection string creates a temporary, volatile database stored entirely in RAM?

sqlite3.connect(':ram:') sqlite3.connect(':memory:') sqlite3.connect('temp.db')

2. Why should you avoid string formatting (e.g. f-strings) when constructing dynamic SQL queries?

It causes Python syntax errors It exposes the database to security vulnerabilities like SQL Injection It reduces query execution speeds

3. Which method is used to execute a single parameterized SQL query across a list of multiple data tuples?

cursor.execute_all() cursor.executemany() cursor.run_batch()

Horizontal Ad Banner Slot (728x90 / Responsive)