SQL - relational data warehouses

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)

1TABLE: species
2+----+-----------------+------------+----------+
3| id | scientific_name | population | habitat  |
4+----+-----------------+------------+----------+
5| 1  | Panthera leo    | 120        | savanna  |
6| 2  | Gorilla gorilla | 230        | forest   |
7+----+-----------------+------------+----------+

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

1-- Species table
2CREATE TABLE species (
3    id INTEGER PRIMARY KEY AUTOINCREMENT,
4    scientific_name TEXT NOT NULL UNIQUE,
5    common_name TEXT NOT NULL,
6    population INTEGER DEFAULT 0,
7    habitat TEXT,
8    endangered BOOLEAN DEFAULT 0,
9    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
10);
11
12-- Observations table
13CREATE TABLE observations (
14    id INTEGER PRIMARY KEY AUTOINCREMENT,
15    species_id INTEGER NOT NULL,
16    observation_date DATE NOT NULL,
17    location TEXT NOT NULL,
18    count INTEGER DEFAULT 0,
19    notes TEXT,
20    FOREIGN KEY (species_id) REFERENCES species(id)
21);

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

1-- Add a single record
2INSERT INTO species (scientific_name, common_name, population, habitat, endangered)
3VALUES ('Panthera leo', 'Lion', 120, 'savanna', 1);
4
5-- Add multiple records
6INSERT INTO species (scientific_name, common_name, population, habitat, endangered)
7VALUES
8    ('Loxodonta africana', 'African Elephant', 450, 'savanna', 1),
9    ('Gorilla gorilla', 'Gorilla', 230, 'tropical forest', 1),
10    ('Python regius', 'Ball Python', 85, 'jungle', 0);
11
12-- Add an observation
13INSERT INTO observations (species_id, observation_date, location, count, notes)
14VALUES (1, '2024-01-15', 'Serengeti North', 12, 'Pride with 2 cubs');

SELECT - retrieving data

1-- All species
2SELECT * FROM species;
3
4-- Selected columns
5SELECT common_name, population FROM species;
6
7-- WHERE - filtering
8SELECT * FROM species WHERE endangered = 1;
9SELECT * FROM species WHERE population > 100;
10SELECT * FROM species WHERE habitat = 'savanna' AND endangered = 1;
11
12-- ORDER BY - sorting
13SELECT * FROM species ORDER BY population DESC;  -- Descending
14SELECT * FROM species ORDER BY common_name ASC;   -- Ascending
15
16-- LIMIT - limiting results
17SELECT * FROM species LIMIT 10;
18SELECT * FROM species LIMIT 10 OFFSET 20;  -- Page 3 (20-30)
19
20-- LIKE - pattern matching
21SELECT * FROM species WHERE scientific_name LIKE 'Panthera%';  -- Starts with Panthera
22SELECT * FROM species WHERE habitat LIKE '%forest%';  -- Contains "forest"
23
24-- COUNT, SUM, AVG, MIN, MAX - aggregations
25SELECT COUNT(*) FROM species;
26SELECT AVG(population) FROM species;
27SELECT SUM(population) FROM species WHERE endangered = 1;
28SELECT MIN(population), MAX(population) FROM species;
29
30-- GROUP BY - grouping
31SELECT habitat, COUNT(*) as species_count
32FROM species
33GROUP BY habitat;
34
35SELECT habitat, AVG(population) as avg_population
36FROM species
37GROUP BY habitat
38HAVING AVG(population) > 100;  -- HAVING = WHERE for groups

UPDATE - updating data

1-- Update a single record
2UPDATE species
3SET population = 125
4WHERE id = 1;
5
6-- Update multiple records
7UPDATE species
8SET endangered = 1
9WHERE population < 50;
10
11-- Update multiple columns
12UPDATE species
13SET population = 130, habitat = 'savanna and steppe'
14WHERE scientific_name = 'Panthera leo';

DELETE - deleting data

1-- Delete a single record
2DELETE FROM species WHERE id = 10;
3
4-- Delete multiple records
5DELETE FROM species WHERE population = 0;
6
7-- WARNING: Delete everything (no WHERE!)
8DELETE FROM species;  -- Deletes ALL records!

JOIN - combining tables

1-- INNER JOIN - only matching records
2SELECT
3    species.common_name,
4    observations.observation_date,
5    observations.location,
6    observations.count
7FROM observations
8INNER JOIN species ON observations.species_id = species.id;
9
10-- LEFT JOIN - all from left + matching from right
11SELECT
12    species.common_name,
13    COUNT(observations.id) as observation_count
14FROM species
15LEFT JOIN observations ON species.id = observations.species_id
16GROUP BY species.id, species.common_name;
17
18-- Table aliases (s, o)
19SELECT
20    s.common_name,
21    o.location,
22    o.count
23FROM observations o
24INNER JOIN species s ON o.species_id = s.id
25WHERE s.endangered = 1;

Python + SQLite - the sqlite3 module

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

Basics

1import sqlite3
2
3# 1. Connect to database (creates file if it doesn't exist)
4conn = sqlite3.connect("safari.db")
5
6# 2. Create cursor (object for executing queries)
7cursor = conn.cursor()
8
9# 3. Execute SQL query
10cursor.execute("""
11    CREATE TABLE IF NOT EXISTS species (
12        id INTEGER PRIMARY KEY AUTOINCREMENT,
13        scientific_name TEXT NOT NULL UNIQUE,
14        common_name TEXT NOT NULL,
15        population INTEGER DEFAULT 0,
16        endangered BOOLEAN DEFAULT 0
17    )
18""")
19
20# 4. Commit changes (IMPORTANT!)
21conn.commit()
22
23# 5. Close connection
24conn.close()

INSERT - adding data

1import sqlite3
2
3conn = sqlite3.connect("safari.db")
4cursor = conn.cursor()
5
6# Method 1: Query with values (DANGEROUS - SQL injection!)
7# DON'T DO THIS:
8# name = "Lion'; DROP TABLE species; --"  # SQL injection!
9# cursor.execute(f"INSERT INTO species (common_name) VALUES ('{name}')")
10
11# Method 2: Parameterized queries (SAFE)
12cursor.execute("""
13    INSERT INTO species (scientific_name, common_name, population, endangered)
14    VALUES (?, ?, ?, ?)
15""", ("Panthera leo", "Lion", 120, 1))
16
17# Multiple records at once
18species_data = [
19    ("Loxodonta africana", "African Elephant", 450, 1),
20    ("Gorilla gorilla", "Gorilla", 230, 1),
21    ("Python regius", "Ball Python", 85, 0)
22]
23
24cursor.executemany("""
25    INSERT INTO species (scientific_name, common_name, population, endangered)
26    VALUES (?, ?, ?, ?)
27""", species_data)
28
29conn.commit()
30
31# Get the ID of the last added record
32print(f"Added species ID: {cursor.lastrowid}")
33
34conn.close()

SELECT - retrieving data

1import sqlite3
2
3conn = sqlite3.connect("safari.db")
4cursor = conn.cursor()
5
6# fetchall() - all results as a list of tuples
7cursor.execute("SELECT * FROM species")
8all_species = cursor.fetchall()
9
10for species in all_species:
11    print(species)  # (1, 'Panthera leo', 'Lion', 120, 1)
12
13# fetchone() - one result
14cursor.execute("SELECT * FROM species WHERE id = ?", (1,))
15lion = cursor.fetchone()
16print(lion)  # (1, 'Panthera leo', 'Lion', 120, 1)
17
18# fetchmany(n) - n results
19cursor.execute("SELECT * FROM species")
20first_five = cursor.fetchmany(5)
21
22# Row factory - results as dictionaries
23conn.row_factory = sqlite3.Row
24cursor = conn.cursor()
25
26cursor.execute("SELECT * FROM species")
27for row in cursor.fetchall():
28    print(row["common_name"], row["population"])
29
30conn.close()

UPDATE and DELETE

1import sqlite3
2
3conn = sqlite3.connect("safari.db")
4cursor = conn.cursor()
5
6# UPDATE
7cursor.execute("""
8    UPDATE species
9    SET population = ?
10    WHERE id = ?
11""", (125, 1))
12
13print(f"Updated {cursor.rowcount} records")
14
15# DELETE
16cursor.execute("DELETE FROM species WHERE population < ?", (10,))
17print(f"Deleted {cursor.rowcount} records")
18
19conn.commit()
20conn.close()

Context manager - safe connection management

1import sqlite3
2
3# Automatic commit and close
4with sqlite3.connect("safari.db") as conn:
5    cursor = conn.cursor()
6
7    cursor.execute("""
8        INSERT INTO species (scientific_name, common_name, population)
9        VALUES (?, ?, ?)
10    """, ("Acinonyx jubatus", "Cheetah", 7100))
11
12    # commit() automatically on exiting the with block
13# close() automatically

Safari example - complete database system

1import sqlite3
2from typing import List, Dict, Optional
3from datetime import datetime
4
5class SafariDatabase:
6    """Complete Safari data management system"""
7
8    def __init__(self, db_path: str = "safari.db"):
9        self.db_path = db_path
10        self.init_database()
11
12    def get_connection(self):
13        """Create database connection"""
14        conn = sqlite3.connect(self.db_path)
15        conn.row_factory = sqlite3.Row  # Results as dictionaries
16        return conn
17
18    def init_database(self):
19        """Initialize database schema"""
20        with self.get_connection() as conn:
21            cursor = conn.cursor()
22
23            # Species table
24            cursor.execute("""
25                CREATE TABLE IF NOT EXISTS species (
26                    id INTEGER PRIMARY KEY AUTOINCREMENT,
27                    scientific_name TEXT NOT NULL UNIQUE,
28                    common_name TEXT NOT NULL,
29                    population INTEGER DEFAULT 0,
30                    habitat TEXT,
31                    endangered BOOLEAN DEFAULT 0,
32                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
33                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
34                )
35            """)
36
37            # Observations table
38            cursor.execute("""
39                CREATE TABLE IF NOT EXISTS observations (
40                    id INTEGER PRIMARY KEY AUTOINCREMENT,
41                    species_id INTEGER NOT NULL,
42                    observation_date DATE NOT NULL,
43                    location TEXT NOT NULL,
44                    count INTEGER DEFAULT 0,
45                    notes TEXT,
46                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
47                    FOREIGN KEY (species_id) REFERENCES species(id) ON DELETE CASCADE
48                )
49            """)
50
51            # Indexes for faster searching
52            cursor.execute("""
53                CREATE INDEX IF NOT EXISTS idx_species_endangered
54                ON species(endangered)
55            """)
56
57            cursor.execute("""
58                CREATE INDEX IF NOT EXISTS idx_observations_species
59                ON observations(species_id)
60            """)
61
62            conn.commit()
63
64    # === SPECIES CRUD ===
65
66    def create_species(self, scientific_name: str, common_name: str,
67                      population: int = 0, habitat: str = "",
68                      endangered: bool = False) -> int:
69        """Add a new species"""
70        with self.get_connection() as conn:
71            cursor = conn.cursor()
72            cursor.execute("""
73                INSERT INTO species (scientific_name, common_name, population, habitat, endangered)
74                VALUES (?, ?, ?, ?, ?)
75            """, (scientific_name, common_name, population, habitat, int(endangered)))
76
77            conn.commit()
78            return cursor.lastrowid
79
80    def get_species(self, species_id: int) -> Optional[Dict]:
81        """Get species by ID"""
82        with self.get_connection() as conn:
83            cursor = conn.cursor()
84            cursor.execute("SELECT * FROM species WHERE id = ?", (species_id,))
85            row = cursor.fetchone()
86            return dict(row) if row else None
87
88    def list_species(self, endangered: Optional[bool] = None,
89                    habitat: Optional[str] = None,
90                    min_population: int = 0) -> List[Dict]:
91        """List species with filters"""
92        with self.get_connection() as conn:
93            cursor = conn.cursor()
94
95            query = "SELECT * FROM species WHERE population >= ?"
96            params = [min_population]
97
98            if endangered is not None:
99                query += " AND endangered = ?"
100                params.append(int(endangered))
101
102            if habitat:
103                query += " AND habitat = ?"
104                params.append(habitat)
105
106            query += " ORDER BY common_name"
107
108            cursor.execute(query, params)
109            return [dict(row) for row in cursor.fetchall()]
110
111    def update_species(self, species_id: int, **kwargs) -> bool:
112        """Update a species"""
113        if not kwargs:
114            return False
115
116        # Dynamic UPDATE building
117        fields = ", ".join([f"{key} = ?" for key in kwargs.keys()])
118        values = list(kwargs.values())
119        values.append(species_id)
120
121        with self.get_connection() as conn:
122            cursor = conn.cursor()
123            cursor.execute(f"""
124                UPDATE species
125                SET {fields}, updated_at = CURRENT_TIMESTAMP
126                WHERE id = ?
127            """, values)
128
129            conn.commit()
130            return cursor.rowcount > 0
131
132    def delete_species(self, species_id: int) -> bool:
133        """Delete a species"""
134        with self.get_connection() as conn:
135            cursor = conn.cursor()
136            cursor.execute("DELETE FROM species WHERE id = ?", (species_id,))
137            conn.commit()
138            return cursor.rowcount > 0
139
140    # === OBSERVATIONS ===
141
142    def create_observation(self, species_id: int, observation_date: str,
143                          location: str, count: int, notes: str = "") -> int:
144        """Add an observation"""
145        with self.get_connection() as conn:
146            cursor = conn.cursor()
147            cursor.execute("""
148                INSERT INTO observations (species_id, observation_date, location, count, notes)
149                VALUES (?, ?, ?, ?, ?)
150            """, (species_id, observation_date, location, count, notes))
151
152            conn.commit()
153            return cursor.lastrowid
154
155    def get_observations_for_species(self, species_id: int) -> List[Dict]:
156        """Get observations for a species"""
157        with self.get_connection() as conn:
158            cursor = conn.cursor()
159            cursor.execute("""
160                SELECT
161                    o.*,
162                    s.common_name as species_name
163                FROM observations o
164                JOIN species s ON o.species_id = s.id
165                WHERE o.species_id = ?
166                ORDER BY o.observation_date DESC
167            """, (species_id,))
168
169            return [dict(row) for row in cursor.fetchall()]
170
171    # === STATISTICS ===
172
173    def get_statistics(self) -> Dict:
174        """Get database statistics"""
175        with self.get_connection() as conn:
176            cursor = conn.cursor()
177
178            # Total species count
179            cursor.execute("SELECT COUNT(*) as count FROM species")
180            total_species = cursor.fetchone()["count"]
181
182            # Endangered species
183            cursor.execute("SELECT COUNT(*) as count FROM species WHERE endangered = 1")
184            endangered_count = cursor.fetchone()["count"]
185
186            # Total population
187            cursor.execute("SELECT SUM(population) as total FROM species")
188            total_population = cursor.fetchone()["total"] or 0
189
190            # Species by habitat
191            cursor.execute("""
192                SELECT habitat, COUNT(*) as count
193                FROM species
194                GROUP BY habitat
195                ORDER BY count DESC
196            """)
197            by_habitat = [dict(row) for row in cursor.fetchall()]
198
199            return {
200                "total_species": total_species,
201                "endangered_count": endangered_count,
202                "total_population": total_population,
203                "by_habitat": by_habitat
204            }
205
206
207# === DEMONSTRATION ===
208
209print("=== SAFARI DATABASE SYSTEM ===\n")
210
211db = SafariDatabase("safari_demo.db")
212
213# 1. Add species
214print("1. Adding species...")
215lion_id = db.create_species("Panthera leo", "Lion", 120, "savanna", True)
216elephant_id = db.create_species("Loxodonta africana", "African Elephant", 450, "savanna", True)
217gorilla_id = db.create_species("Gorilla gorilla", "Gorilla", 230, "tropical forest", True)
218python_id = db.create_species("Python regius", "Ball Python", 85, "jungle", False)
219
220print(f"   Added {4} species")
221
222# 2. Fetch species
223print("\n2. Fetching species...")
224lion = db.get_species(lion_id)
225print(f"   {lion['common_name']} ({lion['scientific_name']})")
226print(f"   Population: {lion['population']}, Endangered: {'Yes' if lion['endangered'] else 'No'}")
227
228# 3. List species with filter
229print("\n3. List of endangered species...")
230endangered = db.list_species(endangered=True)
231for species in endangered:
232    print(f"   - {species['common_name']}: {species['population']} individuals")
233
234# 4. Update
235print("\n4. Updating lion population...")
236db.update_species(lion_id, population=125)
237lion = db.get_species(lion_id)
238print(f"   New population: {lion['population']}")
239
240# 5. Add observations
241print("\n5. Adding observations...")
242db.create_observation(lion_id, "2024-01-15", "Serengeti North", 12, "Pride with 2 cubs")
243db.create_observation(lion_id, "2024-01-20", "Masai Mara", 8, "Male coalition")
244db.create_observation(elephant_id, "2024-01-16", "Amboseli", 35, "Large herd")
245
246print("   Added 3 observations")
247
248# 6. Fetch observations
249print("\n6. Lion observations...")
250lion_obs = db.get_observations_for_species(lion_id)
251for obs in lion_obs:
252    print(f"   - {obs['observation_date']}: {obs['count']}x in {obs['location']}")
253
254# 7. Statistics
255print("\n7. Database statistics...")
256stats = db.get_statistics()
257print(f"   Total species: {stats['total_species']}")
258print(f"   Endangered: {stats['endangered_count']}")
259print(f"   Total population: {stats['total_population']}")
260print("   By habitat:")
261for habitat_stat in stats['by_habitat']:
262    print(f"     - {habitat_stat['habitat']}: {habitat_stat['count']} species")
263
264print("\nDemonstration complete")

Transactions - operation atomicity

1import sqlite3
2
3conn = sqlite3.connect("safari.db")
4
5try:
6    cursor = conn.cursor()
7
8    # Begin transaction (default)
9    cursor.execute("INSERT INTO species (...) VALUES (...)")
10    cursor.execute("UPDATE observations SET ...")
11
12    # Commit transaction
13    conn.commit()
14    print("Transaction committed")
15
16except Exception as e:
17    # Roll back transaction on error
18    conn.rollback()
19    print(f"Transaction rolled back: {e}")
20
21finally:
22    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!