Working with SQLite

What is SQLite?

SQLite is like a lightweight, self-contained database that lives in a single file. Unlike larger database systems that require separate servers, SQLite is embedded directly into your application.

Think of it as a personal notebook that you carry everywhere - it contains all your information in one place and doesn't need any external setup.

Why Use SQLite with Python?

Easy Setup

No need to install or configure a separate database server. SQLite comes built into Python.

File-Based Storage

Your entire database is stored in a single file, making it easy to backup, move, or share.

Perfect for Development

Great for prototyping, testing, and small to medium-sized applications.

Cross-Platform

Works identically on Windows, macOS, Linux, and mobile devices.

Basic SQLite Operations in Python

Connecting to a Database

import sqlite3

# Create or connect to database
conn = sqlite3.connect('example.db')

Creating Tables

cursor = conn.cursor()
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        age INTEGER
    )
''')
conn.commit()

Inserting Data

cursor.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)", 
               ('Alice Johnson', 'alice@example.com', 28))
conn.commit()

Querying Data

cursor.execute("SELECT * FROM users WHERE age > ?", (25,))
results = cursor.fetchall()
for row in results:
    print(row)

Updating Data

cursor.execute("UPDATE users SET age = ? WHERE name = ?", (29, 'Alice Johnson'))
conn.commit()

Deleting Data

cursor.execute("DELETE FROM users WHERE age < ?", (18,))
conn.commit()

Best Practices

Always Close Connections

conn.close()

Use Parameterized Queries

Prevents SQL injection attacks and handles special characters automatically.

Handle Errors Gracefully

try:
    # database operations
except sqlite3.Error as e:
    print(f"Database error: {e}")

Creating a Task Manager Example

Imagine building a personal expense tracker. You could use SQLite to:

• Store expense categories
• Record each transaction with date, amount, and description
• Generate monthly spending reports
• Track budgets by category

SQLite provides the reliability of a database with the simplicity of a file-based approach.

When to Choose SQLite

• Prototyping and development
• Embedded applications
• Mobile apps
• Small to medium websites
• Applications that need to work offline
• Single-user applications

For multi-user applications or very large datasets, consider other database systems like PostgreSQL or MySQL.

In the next lesson, we'll explore Object-Relational Mapping with SQLAlchemy for more complex database interactions.