Day 21: Introduction to Databases — SQLite with Python

-

🐍 Day 21: Introduction to Databases — SQLite with Python

1. Learning Objectives

By the end of Day 21, you will be able to:

  • Understand what a relational database is and why it's used over files
  • Create and connect to a SQLite database file using Python
  • Execute SQL commands from Python: CREATE TABLE, INSERT, SELECT, UPDATE, DELETE
  • Use parameterized queries to prevent SQL injection
  • Work with cursors and transactions
  • Apply best practices for database connections (using context managers)
  • Build a simple data‑driven application with persistent storage

2. Concept Explanation

2.1 Why Databases? — Beyond Simple Files

So far we stored data in JSON, CSV, or plain text files. Those work for small data, but as your app grows, you need:

NeedFile SolutionDatabase Solution
Fast searchLoad everything, loop manuallySQL queries with indexes
Multiple usersFile locks, corruption riskACID transactions
Complex relationshipsNested JSON, manual linkingJOINs, foreign keys
Data integrityManual validationConstraints (NOT NULL, UNIQUE)

A database is a structured collection of data managed by a DBMS (Database Management System). Today we'll use SQLite — the world's most deployed database, built right into Python.

2.2 What is SQLite?

  • Lightweight: Serverless — it's just a single file (.db or .sqlite).
  • Zero configuration: No setup, no daemons, no passwords.
  • Built‑in: The sqlite3 module is part of Python's standard library.
  • Ideal for: desktop apps, mobile apps, embedded systems, prototyping, testing.

SQLite uses full SQL (Structured Query Language) — the same language used by PostgreSQL, MySQL, etc. The skills you learn today transfer directly to those systems.

2.3 Core SQL Concepts (Quick Reference)

A relational database stores data in tables (like spreadsheets). Each table has columns (fields) and rows (records).

-- A simple users table
CREATE TABLE users (
    id INTEGER PRIMARY KEY,   -- auto-incrementing unique ID
    name TEXT NOT NULL,        -- required text
    email TEXT UNIQUE,         -- must be unique
    age INTEGER
);

Common SQL statements:

StatementPurpose
CREATE TABLEDefine a new table
INSERT INTOAdd rows
SELECTQuery rows
UPDATEModify existing rows
DELETERemove rows
DROP TABLEDelete entire table

2.4 Connecting to SQLite with Python

import sqlite3

# Connect (creates the file if it doesn't exist)
conn = sqlite3.connect("my_database.db")

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

# Execute a statement
cursor.execute("CREATE TABLE IF NOT EXISTS people (name TEXT, age INTEGER)")

# Commit changes (save)
conn.commit()

# Close connection
conn.close()

2.5 CRUD Operations in Python

Create (Insert):

import sqlite3

conn = sqlite3.connect("example.db")
cursor = conn.cursor()

# Insert a single row using parameterized query (safe!)
cursor.execute("INSERT INTO people (name, age) VALUES (?, ?)", ("Alice", 28))

# Insert multiple rows
users = [("Bob", 35), ("Charlie", 22), ("Diana", 30)]
cursor.executemany("INSERT INTO people (name, age) VALUES (?, ?)", users)

conn.commit()
conn.close()

⚠️ Never use string formatting for SQL! f"INSERT INTO ... VALUES ('{name}', {age})" invites SQL injection attacks. Always use ? placeholders (or :name named placeholders).

Read (Select):

conn = sqlite3.connect("example.db")
cursor = conn.cursor()

cursor.execute("SELECT * FROM people")
all_rows = cursor.fetchall()
for row in all_rows:
    print(row)         # ('Alice', 28), ('Bob', 35), ...

# Fetch one row
cursor.execute("SELECT name, age FROM people WHERE age > ?", (25,))
row = cursor.fetchone()
while row:
    print(row)
    row = cursor.fetchone()

conn.close()

Update:

conn = sqlite3.connect("example.db")
cursor = conn.cursor()

cursor.execute("UPDATE people SET age = ? WHERE name = ?", (29, "Alice"))
conn.commit()
conn.close()

Delete:

conn = sqlite3.connect("example.db")
cursor = conn.cursor()

cursor.execute("DELETE FROM people WHERE name = ?", ("Charlie",))
conn.commit()
conn.close()

2.6 Using the Connection as a Context Manager

Python's sqlite3 connections support the with statement. They auto‑commit on success and rollback on exception.

with sqlite3.connect("example.db") as conn:
    cursor = conn.cursor()
    cursor.execute("INSERT INTO people (name, age) VALUES (?, ?)", ("Eve", 42))
    # If an exception occurs, the INSERT is automatically rolled back
# Connection is automatically committed if no exception occurred

This is the recommended pattern — it prevents partial writes and reduces bugs.

2.7 Practical Example — A Simple Task Database

import sqlite3

def init_db(db_name="tasks.db"):
    with sqlite3.connect(db_name) as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                completed INTEGER DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)

def add_task(title, db_name="tasks.db"):
    with sqlite3.connect(db_name) as conn:
        conn.execute("INSERT INTO tasks (title) VALUES (?)", (title,))

def list_tasks(db_name="tasks.db"):
    with sqlite3.connect(db_name) as conn:
        cursor = conn.execute("SELECT id, title, completed FROM tasks")
        for row in cursor.fetchall():
            status = "✓" if row[2] else "○"
            print(f"{row[0]}. [{status}] {row[1]}")

def complete_task(task_id, db_name="tasks.db"):
    with sqlite3.connect(db_name) as conn:
        conn.execute("UPDATE tasks SET completed = 1 WHERE id = ?", (task_id,))

# Usage
init_db()
add_task("Learn SQLite")
add_task("Build a database app")
list_tasks()
complete_task(1)
list_tasks()

3. Code Examples

Example 1: Bookstore Database

import sqlite3

DB = "bookstore.db"

def setup():
    with sqlite3.connect(DB) as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS books (
                isbn TEXT PRIMARY KEY,
                title TEXT NOT NULL,
                author TEXT NOT NULL,
                price REAL
            )
        """)

def add_book(isbn, title, author, price):
    with sqlite3.connect(DB) as conn:
        conn.execute(
            "INSERT OR IGNORE INTO books VALUES (?, ?, ?, ?)",
            (isbn, title, author, price)
        )

def search_by_author(author):
    with sqlite3.connect(DB) as conn:
        cursor = conn.execute(
            "SELECT title, price FROM books WHERE author LIKE ?",
            (f"%{author}%",)  # % for partial match
        )
        return cursor.fetchall()

# Test
setup()
add_book("978-0132350884", "Clean Code", "Robert C. Martin", 42.99)
add_book("978-0201616224", "The Pragmatic Programmer", "David Thomas", 49.99)
print(search_by_author("Martin"))

Example 2: Using Named Parameters

with sqlite3.connect("example.db") as conn:
    conn.execute(
        "INSERT INTO people (name, age) VALUES (:name, :age)",
        {"name": "Frank", "age": 45}
    )

Example 3: Handling None / NULL Values

# Inserting NULL
conn.execute("INSERT INTO people (name, age) VALUES (?, ?)", ("Grace", None))

# Selecting and handling None
cursor.execute("SELECT name, age FROM people WHERE age IS NULL")
for row in cursor.fetchall():
    print(row)  # ('Grace', None)

4. Hands-On Exercises

Exercise 1: Create a Movie Database

Create a table movies with columns: id (auto-increment), title, year, rating. Insert 5 movies. Query and print all movies.

Exercise 2: Filter Queries

Expand Exercise 1: write a function get_movies_by_year(year) that returns all movies from a given year. Test with different years.

Exercise 3: Update and Delete

Add functions to update a movie's rating given its id, and to delete a movie by id. Test both.

Exercise 4: User Registration System

Create a users table with id, username (unique), email, password_hash. Write functions to register a user and to check if a username already exists. Use parameterized queries.

Exercise 5: Transaction with Rollback

Write a script that starts a transaction, inserts two rows, then intentionally raises an exception between them. Observe that neither row is inserted because the transaction is rolled back. Use a with block.

5. Applied Challenge Task 🏗️

Personal Library Manager with SQLite

Build a complete console application to manage your personal book library, backed by SQLite.

Requirements:

  1. Database schema:

    • Table books: id (INTEGER PRIMARY KEY), title (TEXT NOT NULL), author (TEXT), isbn (TEXT UNIQUE), status (TEXT DEFAULT 'unread'), rating (INTEGER).
    • The status can be "unread", "reading", or "finished".

  2. Menu options:

    === LIBRARY MANAGER ===
[1] Add Book
[2] List All Books
[3] Update Status
[4] Rate Book (1-5)
[5] Search by Author
[6] Delete Book
[7] Stats (total, read, unread)
[8] Quit

  3. Features:

    • Add book with validation: if ISBN already exists, show error.
    • Search by author using partial matching (LIKE).
    • Stats show total books, number read, unread, currently reading.

  4. Technical requirements:

    • Use with sqlite3.connect(...) for all operations.
    • Use parameterized queries exclusively.
    • Handle sqlite3.IntegrityError for duplicate ISBNs.

Stretch goals:

  • Export the library to a JSON file.
  • Add a date_added column with default current timestamp.
  • Provide an option to sort by title, author, or rating.
  • Write a simple command-line argument parser (using argparse) for non‑interactive queries.

6. Brief Review Summary

ConceptKey Points
SQLiteServerless, file‑based, built‑in sqlite3 module
TableA collection of rows with defined columns
CRUDCreate (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE)
Parameterized queriesUse ? or :name — never string interpolation
with connectionAuto‑commit on success, rollback on exception
CursorExecutes SQL and fetches results (fetchall, fetchone)
TransactionsGroup operations; all succeed or all fail

7. Preview of Next Topic — Day 22

Tomorrow we shift to ensuring our code actually works:

  • Software Testing — why we test and types of tests
  • Writing unit tests with pytest
  • Assertions, fixtures, and test discovery
  • Testing functions, classes, and database operations
  • Test‑Driven Development (TDD) introduction

🎯 Your Action Items for Day 21:

  1. ✅ Complete all 5 exercises
  2. ✅ Build the Personal Library Manager with SQLite
  3. ✅ Experiment with raw SQL in the sqlite3 shell (sqlite3.exe or sqlite3 command)
  4. ✅ Try breaking your queries — see what errors SQLite raises
Location: Manila, Metro Manila, Philippines

Comments

Post a Comment

Leave us your comments here...

Popular posts from this blog

Day 1: Welcome to Python — Your Journey Begins

-
🐍 Day 1: Welcome to Python — Your Journey Begins 1. Learning Objectives By the end of Day 1, you will be able to: Understand what Python is and why it's one of the most popular programming languages in the world Set up Python on your computer and write your first program Use variables to store data Identify and work with Python's four basic data types: integers, floats, strings, and booleans Use the type() and print() functions to inspect your code 2. Concept Explanation 2.1 What is Python? Python is a high-level, interpreted, general-purpose programming language created by Guido van Rossum in 1991. Its design philosophy emphasizes code readability — you can often read Python code like plain English. Why Python? ...
Read more

Python: Your Gateway to Coding Adventures

-
  Welcome to the world of Python! If you've ever been curious about coding, but felt intimidated by complex languages and technical jargon, you're in the right place. Python is a programming language renowned for its simplicity and readability, making it the perfect starting point for beginners and hobbyists alike. Whether you're a student, a professional looking to upskill, or simply someone with a passion for problem-solving, Python offers a vast array of possibilities.  Why Python? Python's popularity has skyrocketed in recent years for good reason. Its clean syntax and emphasis on readability make it easy to learn and understand. Unlike many other programming languages, Python focuses on solving problems rather than adhering to rigid rules, allowing you to concentrate on the logic and creativity behind your code. Moreover, Python's versatility is unparalleled. It's used in web development, data science, machine learning, automation, and countless other dom...
Read more

Day 11: List Comprehensions & Lambda Functions

-
🐍 Day 11: List Comprehensions & Lambda Functions 1. Learning Objectives By the end of Day 11, you will be able to: Write list comprehensions to create lists in a single, readable line Apply dictionary and set comprehensions for other collection types Use lambda functions for quick, throwaway operations Understand when to use comprehensions vs. traditional loops Recognize the map() , filter() , and reduce() functional tools Know that Python's way is often more readable than classic functional programming 2. Concept Explanation 2.1 Why List Comprehensions? — Power in One Line Python's list comprehensions let you transform, filter, and create lists in a single, elegant expression. They often replace several lines of loop code, making your intent crystal clear. Traditional loop vs. comprehension: # Traditional loop: create a list of squares squares = [] for x in range(10): squares.append(x ** 2) # List comprehension: same result squares = [x ** 2 fo...
Read more

Earn From the Comfort of Your Home

Build the skills to work comfortably from home, on your own terms.

Get Started Today