Skip to main content Brad's PyNotes

Sqlite3 Module

TL;DR

The sqlite3 module provides a lightweight, disk-based database interface that supports SQL queries without requiring a separate server process.

Interesting!

SQLite is the most widely deployed database engine in the world - it’s built into every smartphone, browser, and operating system!

Basic Database Operations

python code snippet start

import sqlite3

# Connect to database (creates if doesn't exist)
con = sqlite3.connect("example.db")
cur = con.cursor()

# Create table
cur.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )
""")

# Insert data
cur.execute("INSERT INTO users (name, email) VALUES (?, ?)", 
           ("Alice", "alice@example.com"))

# Commit changes
con.commit()

python code snippet end

Safe Parameter Binding

python code snippet start

# NEVER do this (SQL injection risk):
# cur.execute(f"SELECT * FROM users WHERE name = '{user_input}'")

# Always use placeholders:
name = "Bob"
cur.execute("SELECT * FROM users WHERE name = ?", (name,))

# Named placeholders
cur.execute("""
    INSERT INTO users (name, email) 
    VALUES (:name, :email)
""", {"name": "Charlie", "email": "charlie@example.com"})

python code snippet end

Fetching Results

python code snippet start

# Fetch one row
cur.execute("SELECT * FROM users WHERE id = ?", (1,))
user = cur.fetchone()
print(user)  # (1, 'Alice', 'alice@example.com')

# Fetch multiple rows
cur.execute("SELECT name, email FROM users")
for row in cur.fetchall():
    print(f"Name: {row[0]}, Email: {row[1]}")

# Iterate directly
cur.execute("SELECT * FROM users")
for row in cur:
    print(row)

python code snippet end

Transaction Management

python code snippet start

try:
    con.execute("BEGIN")
    
    # Multiple operations
    con.execute("INSERT INTO users (name, email) VALUES (?, ?)", 
               ("David", "david@example.com"))
    con.execute("UPDATE users SET email = ? WHERE name = ?", 
               ("newemail@example.com", "Alice"))
    
    con.commit()  # Save changes
    print("Transaction completed")
    
except Exception as e:
    con.rollback()  # Undo changes
    print(f"Transaction failed: {e}")

python code snippet end

Context Manager Pattern

python code snippet start

# Automatic transaction handling
with sqlite3.connect("example.db") as con:
    cur = con.cursor()
    
    # These operations are automatically committed
    cur.execute("CREATE TABLE IF NOT EXISTS products (name, price)")
    cur.execute("INSERT INTO products VALUES (?, ?)", ("Apple", 0.50))
    
    # If an exception occurs, changes are automatically rolled back

python code snippet end

Row Factories

python code snippet start

# Access columns by name
con.row_factory = sqlite3.Row
cur = con.cursor()

cur.execute("SELECT * FROM users WHERE id = 1")
row = cur.fetchone()

print(row['name'])   # Alice
print(row['email'])  # alice@example.com

# Convert to dictionary
user_dict = dict(row)

python code snippet end

Bulk Operations

python code snippet start

# Insert multiple records efficiently
users_data = [
    ("Eve", "eve@example.com"),
    ("Frank", "frank@example.com"),
    ("Grace", "grace@example.com")
]

cur.executemany("INSERT INTO users (name, email) VALUES (?, ?)", users_data)
con.commit()

print(f"Inserted {cur.rowcount} users")

python code snippet end

SQLite provides a full-featured SQL database with zero configuration - perfect for applications that need persistent storage without database server complexity! Use sqlite3 with pathlib for database file management and JSON for data serialization . For robust database applications, combine with exception handling and logging for database operations . Perfect for storing precise decimal values and integrates with timestamp management for data tracking.

Reference: sqlite3 — DB-API 2.0 interface for SQLite databases