🐍 SQLite with Python

Python's built-in `sqlite3` module provides a simple interface for working with SQLite databases. It handles connections, transactions, and queries with minimal setup.

📦 Getting Started with sqlite3

The `sqlite3` module is part of Python's standard library, so no installation is required.

Basic Components:

• **Connection** - Represents the database file

• **Cursor** - Executes SQL statements and fetches results

• **Transactions** - Groups multiple operations for atomicity

Example 1: Basic Connection and Query

import sqlite3

# Open or create a database file
conn = sqlite3.connect('my_database.db')

# Create a cursor to execute queries
cursor = conn.cursor()

# Execute a simple query
cursor.execute("SELECT sqlite_version()")
version = cursor.fetchone()
print(f"SQLite Version: {version[0]}")

# Close the connection
conn.close()

Example 2: Creating Tables

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Create tables
cursor.execute("""
    CREATE TABLE IF NOT EXISTS authors (
        author_id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        birth_year INTEGER,
        nationality TEXT
    )
""")

cursor.execute("""
    CREATE TABLE IF NOT EXISTS books (
        book_id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        author_id INTEGER NOT NULL,
        year_published INTEGER,
        price REAL,
        FOREIGN KEY (author_id) REFERENCES authors(author_id)
    )
""")

conn.commit()  # Save changes
print("Tables created successfully")

conn.close()

🔄 The Cursor Object

The cursor executes SQL and manages results.

Cursor Methods:

• `execute(sql)` - Execute a single SQL statement

• `executescript(sql)` - Execute multiple SQL statements

• `fetchone()` - Get the next row

• `fetchall()` - Get all remaining rows

• `fetchmany(size)` - Get specified number of rows

• `commit()` - Save changes

• `rollback()` - Undo changes

Example 3: Inserting Data

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Insert a single record
author_name = "George Orwell"
birth_year = 1903

cursor.execute("""
    INSERT INTO authors (name, birth_year, nationality)
    VALUES (?, ?, ?)
""", (author_name, birth_year, 'British'))

conn.commit()
print(f"Inserted author: {author_name}")

# Get the ID of the inserted record
author_id = cursor.lastrowid
print(f"New author ID: {author_id}")

conn.close()

🔐 Parameterized Queries (SQL Injection Prevention)

Always use parameterized queries to prevent SQL injection attacks.

Example 4: Safe Parameter Binding

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# SAFE: Using ? placeholders
user_id = 42
cursor.execute("SELECT * FROM customers WHERE customer_id = ?", (user_id,))
result = cursor.fetchone()
print(f"Found customer: {result}")

# SAFE: Multiple parameters
genre = "Science Fiction"
min_price = 15.00
cursor.execute("""
    SELECT title, price
    FROM books
    WHERE genre = ? AND price > ?
    ORDER BY price DESC
""", (genre, min_price))

books = cursor.fetchall()
for title, price in books:
    print(f"  {title}: ${price:.2f}")

# For named parameters (more readable)
cursor.execute("""
    SELECT * FROM books
    WHERE title LIKE :search_term
    AND price < :max_price
""", {'search_term': '%Python%', 'max_price': 50})

results = cursor.fetchall()

conn.close()

📋 Batch Operations

Insert or update multiple records efficiently.

Example 5: Batch Insert

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Prepare data
authors = [
    ("Jane Austen", 1775, "British"),
    ("Mark Twain", 1835, "American"),
    ("Leo Tolstoy", 1828, "Russian"),
    ("Charlotte Bronte", 1816, "British"),
]

# Insert multiple records at once
cursor.executemany("""
    INSERT INTO authors (name, birth_year, nationality)
    VALUES (?, ?, ?)
""", authors)

conn.commit()
print(f"Inserted {cursor.rowcount} authors")

conn.close()

🎯 Context Managers (Automatic Resource Management)

Use context managers to ensure connections are properly closed.

Example 6: Using Context Manager Pattern

import sqlite3

# Automatic connection management
with sqlite3.connect('bookstore.db') as conn:
    cursor = conn.cursor()
    
    cursor.execute("SELECT COUNT(*) FROM authors")
    count = cursor.fetchone()[0]
    print(f"Total authors: {count}")
    
    conn.commit()
    # Connection automatically closes here

Example 7: Error Handling with Context Manager

import sqlite3

try:
    with sqlite3.connect('bookstore.db') as conn:
        cursor = conn.cursor()
        
        # This might fail if constraint is violated
        cursor.execute("""
            INSERT INTO authors (name, birth_year)
            VALUES (?, ?)
        """, ("Duplicate Name", 1950))
        
        conn.commit()
        print("Author inserted successfully")
        
except sqlite3.IntegrityError as e:
    print(f"Integrity error: {e}")
except sqlite3.OperationalError as e:
    print(f"Operational error: {e}")
except Exception as e:
    print(f"Unexpected error: {e}")

🛡️ Transactions and Rollback

Ensure data consistency with transaction management.

Example 8: Transaction Management

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

try:
    # Start transaction (implicit)
    
    # Update inventory
    cursor.execute("""
        UPDATE books
        SET quantity_in_stock = quantity_in_stock - ?
        WHERE book_id = ? AND quantity_in_stock >= ?
    """, (3, 10, 3))
    
    if cursor.rowcount == 0:
        raise Exception("Insufficient stock")
    
    # Create order record
    cursor.execute("""
        INSERT INTO orders (book_id, quantity, customer_id, order_date)
        VALUES (?, ?, ?, CURRENT_TIMESTAMP)
    """, (10, 3, 5))
    
    # Everything succeeded, commit
    conn.commit()
    print("Order processed and inventory updated")

except Exception as e:
    # Error occurred, rollback all changes
    conn.rollback()
    print(f"Transaction failed: {e}")

finally:
    conn.close()

📊 Fetching Results

Different methods for retrieving query results.

Example 9: Various Fetch Methods

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Fetch all results
cursor.execute("SELECT * FROM books LIMIT 10")
all_books = cursor.fetchall()  # Returns list of tuples
print(f"Total books fetched: {len(all_books)}")

# Fetch one result at a time
cursor.execute("SELECT * FROM authors")
first_author = cursor.fetchone()
print(f"First author: {first_author}")

# Fetch specific number
cursor.execute("SELECT * FROM authors")
first_three = cursor.fetchmany(3)
for author in first_three:
    print(f"  {author}")

# Iterate over cursor directly
cursor.execute("SELECT title, price FROM books WHERE price > 20")
print("Expensive books:")
for title, price in cursor:
    print(f"  {title}: ${price:.2f}")

conn.close()

🔍 Row Factories for Easier Access

Convert rows to dictionaries or custom objects for more readable code.

Example 10: Custom Row Factory

import sqlite3

conn = sqlite3.connect('bookstore.db')

# Access columns by name instead of index
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute("SELECT * FROM authors LIMIT 5")
for row in cursor:
    # Access by column name
    print(f"{row['name']} (born {row['birth_year']})")

# Using a custom row class
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

conn.row_factory = dict_factory
cursor = conn.cursor()

cursor.execute("SELECT * FROM books WHERE price > ?", (25,))
for book in cursor:
    print(f"Title: {book['title']}, Price: ${book['price']:.2f}")

conn.close()

🔑 Key Takeaways

• **sqlite3** is Python's built-in database module (no installation needed)

• **Connection** and **Cursor** are the core objects for database interaction

• **Always use parameterized queries** (?) to prevent SQL injection

• **executemany()** efficiently inserts or updates multiple records

• **Context managers** (with statement) ensure proper resource cleanup

• **Transactions** and **rollback()** maintain data consistency

• **Row factories** make accessing columns more convenient

• **Always close connections** or use context managers

📚 Further Learning

Deepen your Python database skills:

• [Advanced Python SQLite Patterns](/courses/python/database_basics/advanced/sqlite_with_python) - Connection pooling and async patterns

• [Performance & Best Practices](/courses/python/database_basics/beginner/database_performance) - Optimization techniques

• [Challenges & Quizzes](/#) - Practice building database applications