Python course ยท Module 4: Data, APIs and Databases

SQL - relational data warehouses

13 min read ยท
In this lesson16

Welcome back! Darwin here with a crucial lesson about data storage.

So far you've worked with data temporarily - in variables, JSON files, scraped pages. But what if you have millions of species observations? What if you need to quickly search for all endangered species from Serengeti?

Then you need a database - a specialized system for storing and managing large datasets!

Safari Analogy: JSON files are like a biologist's notebook - works for 10 observations. A database is a large catalog library - organized drawers with index cards, indexes, and fast searching for millions of records!

What are databases?

A database is an organized collection of data + a management system (DBMS - Database Management System).

Why databases?

  • Fast searching - indexes, query optimization
  • Security - access control, permissions
  • Integrity - validation, relationships, foreign keys
  • Scalability - millions of records without issues
  • Concurrent access - multiple users simultaneously
  • Transactions - atomic operations (all or nothing)

Relational vs NoSQL

Relational databases (SQL)

Structure: Tables with rows and columns (like an Excel spreadsheet)

Examples: PostgreSQL, MySQL, SQLite, Oracle, SQL Server

Characteristics:

  • Rigid schema (columns defined upfront)
  • Relationships between tables (foreign keys)
  • ACID (Atomicity, Consistency, Isolation, Durability)
  • SQL language (Structured Query Language)
TABLE: species
+----+-----------------+------------+----------+
| id | scientific_name | population | habitat  |
+----+-----------------+------------+----------+
| 1  | Panthera leo    | 120        | savanna  |
| 2  | Gorilla gorilla | 230        | forest   |
+----+-----------------+------------+----------+

NoSQL databases

Structure: Documents, key-value pairs, graphs, columns

Examples: MongoDB, Redis, Cassandra, Neo4j

Characteristics:

  • Flexible schema (JSON-like)
  • Horizontal scaling
  • Fast for specific use cases

In this lesson: We focus on SQL (relational) - the foundation of most applications!

SQLite - a database in a single file

SQLite is the most popular SQL database in the world:

  • No server - entire database in a single .db file
  • Zero configuration - ready out of the box
  • Built into Python - the sqlite3 module
  • Ideal to start with - prototypes, small/medium applications

Usage: Mobile apps, browsers, IoT devices, prototypes

SQL - the query language

SQL (Structured Query Language) is the universal language for communicating with relational databases.

Basic operations (CRUD)

  • CREATE - creating tables
  • INSERT - adding records
  • SELECT - retrieving data (Read)
  • UPDATE - updating records
  • DELETE - deleting records

CREATE TABLE - creating a table

-- Species table
CREATE TABLE species (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    scientific_name TEXT NOT NULL UNIQUE,
    common_name TEXT NOT NULL,
    population INTEGER DEFAULT 0,
    habitat TEXT,
    endangered BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Observations table
CREATE TABLE observations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    species_id INTEGER NOT NULL,
    observation_date DATE NOT NULL,
    location TEXT NOT NULL,
    count INTEGER DEFAULT 0,
    notes TEXT,
    FOREIGN KEY (species_id) REFERENCES species(id)
);

SQLite data types:

  • INTEGER - integer number
  • REAL - floating-point number
  • TEXT - text
  • BLOB - binary data
  • NULL - no value

Constraints:

  • PRIMARY KEY - unique row identifier
  • AUTOINCREMENT - automatic increment
  • NOT NULL - required field
  • UNIQUE - unique value
  • DEFAULT - default value
  • FOREIGN KEY - relationship to another table

INSERT - adding data

-- Add a single record
INSERT INTO species (scientific_name, common_name, population, habitat, endangered)
VALUES ('Panthera leo', 'Lion', 120, 'savanna', 1);

-- Add multiple records
INSERT INTO species (scientific_name, common_name, population, habitat, endangered)
VALUES
    ('Loxodonta africana', 'African Elephant', 450, 'savanna', 1),
    ('Gorilla gorilla', 'Gorilla', 230, 'tropical forest', 1),
    ('Python regius', 'Ball Python', 85, 'jungle', 0);

-- Add an observation
INSERT INTO observations (species_id, observation_date, location, count, notes)
VALUES (1, '2024-01-15', 'Serengeti North', 12, 'Pride with 2 cubs');

SELECT - retrieving data

-- All species
SELECT * FROM species;

-- Selected columns
SELECT common_name, population FROM species;

-- WHERE - filtering
SELECT * FROM species WHERE endangered = 1;
SELECT * FROM species WHERE population > 100;
SELECT * FROM species WHERE habitat = 'savanna' AND endangered = 1;

-- ORDER BY - sorting
SELECT * FROM species ORDER BY population DESC;  -- Descending
SELECT * FROM species ORDER BY common_name ASC;   -- Ascending

-- LIMIT - limiting results
SELECT * FROM species LIMIT 10;
SELECT * FROM species LIMIT 10 OFFSET 20;  -- Page 3 (20-30)

-- LIKE - pattern matching
SELECT * FROM species WHERE scientific_name LIKE 'Panthera%';  -- Starts with Panthera
SELECT * FROM species WHERE habitat LIKE '%forest%';  -- Contains "forest"

-- COUNT, SUM, AVG, MIN, MAX - aggregations
SELECT COUNT(*) FROM species;
SELECT AVG(population) FROM species;
SELECT SUM(population) FROM species WHERE endangered = 1;
SELECT MIN(population), MAX(population) FROM species;

-- GROUP BY - grouping
SELECT habitat, COUNT(*) as species_count
FROM species
GROUP BY habitat;

SELECT habitat, AVG(population) as avg_population
FROM species
GROUP BY habitat
HAVING AVG(population) > 100;  -- HAVING = WHERE for groups

UPDATE - updating data

-- Update a single record
UPDATE species
SET population = 125
WHERE id = 1;

-- Update multiple records
UPDATE species
SET endangered = 1
WHERE population < 50;

-- Update multiple columns
UPDATE species
SET population = 130, habitat = 'savanna and steppe'
WHERE scientific_name = 'Panthera leo';

DELETE - deleting data

-- Delete a single record
DELETE FROM species WHERE id = 10;

-- Delete multiple records
DELETE FROM species WHERE population = 0;

-- WARNING: Delete everything (no WHERE!)
DELETE FROM species;  -- Deletes ALL records!

JOIN - combining tables

-- INNER JOIN - only matching records
SELECT
    species.common_name,
    observations.observation_date,
    observations.location,
    observations.count
FROM observations
INNER JOIN species ON observations.species_id = species.id;

-- LEFT JOIN - all from left + matching from right
SELECT
    species.common_name,
    COUNT(observations.id) as observation_count
FROM species
LEFT JOIN observations ON species.id = observations.species_id
GROUP BY species.id, species.common_name;

-- Table aliases (s, o)
SELECT
    s.common_name,
    o.location,
    o.count
FROM observations o
INNER JOIN species s ON o.species_id = s.id
WHERE s.endangered = 1;

Python + SQLite - the sqlite3 module

Python has a built-in sqlite3 module for working with SQLite.

Basics

import sqlite3

# 1. Connect to database (creates file if it doesn't exist)
conn = sqlite3.connect("safari.db")

# 2. Create cursor (object for executing queries)
cursor = conn.cursor()

# 3. Execute SQL query
cursor.execute("""
    CREATE TABLE IF NOT EXISTS species (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        scientific_name TEXT NOT NULL UNIQUE,
        common_name TEXT NOT NULL,
        population INTEGER DEFAULT 0,
        endangered BOOLEAN DEFAULT 0
    )
""")

# 4. Commit changes (IMPORTANT!)
conn.commit()

# 5. Close connection
conn.close()

INSERT - adding data

import sqlite3

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

# Method 1: Query with values (DANGEROUS - SQL injection!)
# DON'T DO THIS:
# name = "Lion'; DROP TABLE species; --"  # SQL injection!
# cursor.execute(f"INSERT INTO species (common_name) VALUES ('{name}')")

# Method 2: Parameterized queries (SAFE)
cursor.execute("""
    INSERT INTO species (scientific_name, common_name, population, endangered)
    VALUES (?, ?, ?, ?)
""", ("Panthera leo", "Lion", 120, 1))

# Multiple records at once
species_data = [
    ("Loxodonta africana", "African Elephant", 450, 1),
    ("Gorilla gorilla", "Gorilla", 230, 1),
    ("Python regius", "Ball Python", 85, 0)
]

cursor.executemany("""
    INSERT INTO species (scientific_name, common_name, population, endangered)
    VALUES (?, ?, ?, ?)
""", species_data)

conn.commit()

# Get the ID of the last added record
print(f"Added species ID: {cursor.lastrowid}")

conn.close()

SELECT - retrieving data

import sqlite3

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

# fetchall() - all results as a list of tuples
cursor.execute("SELECT * FROM species")
all_species = cursor.fetchall()

for species in all_species:
    print(species)  # (1, 'Panthera leo', 'Lion', 120, 1)

# fetchone() - one result
cursor.execute("SELECT * FROM species WHERE id = ?", (1,))
lion = cursor.fetchone()
print(lion)  # (1, 'Panthera leo', 'Lion', 120, 1)

# fetchmany(n) - n results
cursor.execute("SELECT * FROM species")
first_five = cursor.fetchmany(5)

# Row factory - results as dictionaries
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute("SELECT * FROM species")
for row in cursor.fetchall():
    print(row["common_name"], row["population"])

conn.close()

UPDATE and DELETE

import sqlite3

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

# UPDATE
cursor.execute("""
    UPDATE species
    SET population = ?
    WHERE id = ?
""", (125, 1))

print(f"Updated {cursor.rowcount} records")

# DELETE
cursor.execute("DELETE FROM species WHERE population < ?", (10,))
print(f"Deleted {cursor.rowcount} records")

conn.commit()
conn.close()

Context manager - safe connection management

import sqlite3

# Automatic commit and close
with sqlite3.connect("safari.db") as conn:
    cursor = conn.cursor()

    cursor.execute("""
        INSERT INTO species (scientific_name, common_name, population)
        VALUES (?, ?, ?)
    """, ("Acinonyx jubatus", "Cheetah", 7100))

    # commit() automatically on exiting the with block
# close() automatically

Safari example - complete database system

import sqlite3
from typing import List, Dict, Optional
from datetime import datetime

class SafariDatabase:
    """Complete Safari data management system"""

    def __init__(self, db_path: str = "safari.db"):
        self.db_path = db_path
        self.init_database()

    def get_connection(self):
        """Create database connection"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row  # Results as dictionaries
        return conn

    def init_database(self):
        """Initialize database schema"""
        with self.get_connection() as conn:
            cursor = conn.cursor()

            # Species table
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS species (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    scientific_name TEXT NOT NULL UNIQUE,
                    common_name TEXT NOT NULL,
                    population INTEGER DEFAULT 0,
                    habitat TEXT,
                    endangered BOOLEAN DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)

            # Observations table
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS observations (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    species_id INTEGER NOT NULL,
                    observation_date DATE NOT NULL,
                    location TEXT NOT NULL,
                    count INTEGER DEFAULT 0,
                    notes TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (species_id) REFERENCES species(id) ON DELETE CASCADE
                )
            """)

            # Indexes for faster searching
            cursor.execute("""
                CREATE INDEX IF NOT EXISTS idx_species_endangered
                ON species(endangered)
            """)

            cursor.execute("""
                CREATE INDEX IF NOT EXISTS idx_observations_species
                ON observations(species_id)
            """)

            conn.commit()

    # === SPECIES CRUD ===

    def create_species(self, scientific_name: str, common_name: str,
                      population: int = 0, habitat: str = "",
                      endangered: bool = False) -> int:
        """Add a new species"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                INSERT INTO species (scientific_name, common_name, population, habitat, endangered)
                VALUES (?, ?, ?, ?, ?)
            """, (scientific_name, common_name, population, habitat, int(endangered)))

            conn.commit()
            return cursor.lastrowid

    def get_species(self, species_id: int) -> Optional[Dict]:
        """Get species by ID"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM species WHERE id = ?", (species_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def list_species(self, endangered: Optional[bool] = None,
                    habitat: Optional[str] = None,
                    min_population: int = 0) -> List[Dict]:
        """List species with filters"""
        with self.get_connection() as conn:
            cursor = conn.cursor()

            query = "SELECT * FROM species WHERE population >= ?"
            params = [min_population]

            if endangered is not None:
                query += " AND endangered = ?"
                params.append(int(endangered))

            if habitat:
                query += " AND habitat = ?"
                params.append(habitat)

            query += " ORDER BY common_name"

            cursor.execute(query, params)
            return [dict(row) for row in cursor.fetchall()]

    def update_species(self, species_id: int, **kwargs) -> bool:
        """Update a species"""
        if not kwargs:
            return False

        # Dynamic UPDATE building
        fields = ", ".join([f"{key} = ?" for key in kwargs.keys()])
        values = list(kwargs.values())
        values.append(species_id)

        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(f"""
                UPDATE species
                SET {fields}, updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            """, values)

            conn.commit()
            return cursor.rowcount > 0

    def delete_species(self, species_id: int) -> bool:
        """Delete a species"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM species WHERE id = ?", (species_id,))
            conn.commit()
            return cursor.rowcount > 0

    # === OBSERVATIONS ===

    def create_observation(self, species_id: int, observation_date: str,
                          location: str, count: int, notes: str = "") -> int:
        """Add an observation"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                INSERT INTO observations (species_id, observation_date, location, count, notes)
                VALUES (?, ?, ?, ?, ?)
            """, (species_id, observation_date, location, count, notes))

            conn.commit()
            return cursor.lastrowid

    def get_observations_for_species(self, species_id: int) -> List[Dict]:
        """Get observations for a species"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT
                    o.*,
                    s.common_name as species_name
                FROM observations o
                JOIN species s ON o.species_id = s.id
                WHERE o.species_id = ?
                ORDER BY o.observation_date DESC
            """, (species_id,))

            return [dict(row) for row in cursor.fetchall()]

    # === STATISTICS ===

    def get_statistics(self) -> Dict:
        """Get database statistics"""
        with self.get_connection() as conn:
            cursor = conn.cursor()

            # Total species count
            cursor.execute("SELECT COUNT(*) as count FROM species")
            total_species = cursor.fetchone()["count"]

            # Endangered species
            cursor.execute("SELECT COUNT(*) as count FROM species WHERE endangered = 1")
            endangered_count = cursor.fetchone()["count"]

            # Total population
            cursor.execute("SELECT SUM(population) as total FROM species")
            total_population = cursor.fetchone()["total"] or 0

            # Species by habitat
            cursor.execute("""
                SELECT habitat, COUNT(*) as count
                FROM species
                GROUP BY habitat
                ORDER BY count DESC
            """)
            by_habitat = [dict(row) for row in cursor.fetchall()]

            return {
                "total_species": total_species,
                "endangered_count": endangered_count,
                "total_population": total_population,
                "by_habitat": by_habitat
            }


# === DEMONSTRATION ===

print("=== SAFARI DATABASE SYSTEM ===\n")

db = SafariDatabase("safari_demo.db")

# 1. Add species
print("1. Adding species...")
lion_id = db.create_species("Panthera leo", "Lion", 120, "savanna", True)
elephant_id = db.create_species("Loxodonta africana", "African Elephant", 450, "savanna", True)
gorilla_id = db.create_species("Gorilla gorilla", "Gorilla", 230, "tropical forest", True)
python_id = db.create_species("Python regius", "Ball Python", 85, "jungle", False)

print(f"   Added {4} species")

# 2. Fetch species
print("\n2. Fetching species...")
lion = db.get_species(lion_id)
print(f"   {lion['common_name']} ({lion['scientific_name']})")
print(f"   Population: {lion['population']}, Endangered: {'Yes' if lion['endangered'] else 'No'}")

# 3. List species with filter
print("\n3. List of endangered species...")
endangered = db.list_species(endangered=True)
for species in endangered:
    print(f"   - {species['common_name']}: {species['population']} individuals")

# 4. Update
print("\n4. Updating lion population...")
db.update_species(lion_id, population=125)
lion = db.get_species(lion_id)
print(f"   New population: {lion['population']}")

# 5. Add observations
print("\n5. Adding observations...")
db.create_observation(lion_id, "2024-01-15", "Serengeti North", 12, "Pride with 2 cubs")
db.create_observation(lion_id, "2024-01-20", "Masai Mara", 8, "Male coalition")
db.create_observation(elephant_id, "2024-01-16", "Amboseli", 35, "Large herd")

print("   Added 3 observations")

# 6. Fetch observations
print("\n6. Lion observations...")
lion_obs = db.get_observations_for_species(lion_id)
for obs in lion_obs:
    print(f"   - {obs['observation_date']}: {obs['count']}x in {obs['location']}")

# 7. Statistics
print("\n7. Database statistics...")
stats = db.get_statistics()
print(f"   Total species: {stats['total_species']}")
print(f"   Endangered: {stats['endangered_count']}")
print(f"   Total population: {stats['total_population']}")
print("   By habitat:")
for habitat_stat in stats['by_habitat']:
    print(f"     - {habitat_stat['habitat']}: {habitat_stat['count']} species")

print("\nDemonstration complete")

Transactions - operation atomicity

import sqlite3

conn = sqlite3.connect("safari.db")

try:
    cursor = conn.cursor()

    # Begin transaction (default)
    cursor.execute("INSERT INTO species (...) VALUES (...)")
    cursor.execute("UPDATE observations SET ...")

    # Commit transaction
    conn.commit()
    print("Transaction committed")

except Exception as e:
    # Roll back transaction on error
    conn.rollback()
    print(f"Transaction rolled back: {e}")

finally:
    conn.close()

Summary

In this lesson you learned:

  • What databases are and what they're for
  • Relational (SQL) vs NoSQL
  • SQLite - a database in a single file
  • SQL: CREATE TABLE, INSERT, SELECT, UPDATE, DELETE
  • WHERE, ORDER BY, LIMIT, JOIN
  • Python sqlite3: connect, cursor, execute, fetchall
  • Parameterized queries (SQL injection prevention)
  • Transactions and rollback
  • A complete CRUD system with relationships

Checkpoint

Before moving on:

  • You can create a table (CREATE TABLE)
  • You can add data (INSERT)
  • You can retrieve data with filters (SELECT WHERE)
  • You understand JOIN (combining tables)
  • You can use sqlite3 in Python

Safari Analogy: An SQL database is a large catalog library with millions of species cards - fast searching, relationships, data integrity. JSON is a notebook - great for 10 entries, insufficient for millions!

In the next lesson, Darwin will show you SQLAlchemy ORM - how to work with databases using Python classes instead of raw SQL!

Docs entries used in this lesson

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. Which SQL query retrieves all data from the 'animals' table?

  2. 2. Which statement adds a new record to a table?

These are 2 of 3 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Click in order

    Click the elements in the correct order:

  • Horizontal ordering

    Arrange the elements in the correct order:

  • Code editor

    Create a connection to the 'safari.db' database and create an 'animals' table

  • Code editor

    Insert a record {'name': 'Tygrys', 'species': 'Panthera tigris'} into the 'animals' table

  • Vertical ordering

    Arrange the steps for working with SQLite:

  • Horizontal ordering

    Arrange: SELECT * FROM animals WHERE species = 'Tiger'

  1. QuestionSELECT
  2. QuestionINSERT
  3. QuestionWHERE
  4. Click in orderBuilding cursor.execute()
  5. Put in orderResponse JSON
  6. Coding taskSQLite in Python
  7. Coding taskINSERT Query
  8. Put in orderDatabase Operations
  9. Put in orderSQL SELECT

Useful articles