Python course ยท Module 4: Data, APIs and Databases
SQL - relational data warehouses
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
sqlite3module - 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 numberREAL- floating-point numberTEXT- textBLOB- binary dataNULL- no value
Constraints:
PRIMARY KEY- unique row identifierAUTOINCREMENT- automatic incrementNOT NULL- required fieldUNIQUE- unique valueDEFAULT- default valueFOREIGN 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 groupsUPDATE - 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() automaticallySafari 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. Which SQL query retrieves all data from the 'animals' table?
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'