Kurs Python · Moduł 4: Dane, API i bazy danych
SQL - relacyjne magazyny danych
W tej lekcji16
Witaj ponownie! Darwin tutaj z kluczową lekcją o przechowywaniu danych.
Do tej pory pracowałeś/aś z danymi tymczasowo - w zmiennych, plikach JSON, scrapowanych stronach. Ale co gdy masz miliony obserwacji gatunków? Co gdy potrzebujesz szybko wyszukać wszystkie zagrożone gatunki z Serengeti?
Wtedy potrzebujesz bazy danych - wyspecjalizowanego systemu do przechowywania i zarządzania dużymi zbiorami danych!
Analogia Safari: Pliki JSON to jak notatnik biologa - działa dla 10 obserwacji. Baza danych to wielka biblioteka katalogowa - zorganizowane szuflady z kartotekami, indeksami, szybkim wyszukiwaniem dla milionów rekordów!
Czym są bazy danych?
Baza danych to uporządkowany zbiór danych + system zarządzania (DBMS - Database Management System).
Po co bazy danych?
- Szybkie wyszukiwanie - indeksy, optymalizacja zapytań
- Bezpieczeństwo - kontrola dostępu, uprawnienia
- Integralność - walidacja, relacje, klucze obce
- Skalowalność - miliony rekordów bez problemu
- Wielodostęp - wielu użytkowników jednocześnie
- Transakcje - atomowe operacje (albo wszystko, albo nic)
Relacyjne vs NoSQL
Relacyjne bazy danych (SQL)
Struktura: Tabele z wierszami i kolumnami (jak arkusz Excel)
Przykłady: PostgreSQL, MySQL, SQLite, Oracle, SQL Server
Cechy:
- Sztywny schemat (kolumny zdefiniowane z góry)
- Relacje między tabelami (klucze obce)
- ACID (Atomicity, Consistency, Isolation, Durability)
- Język SQL (Structured Query Language)
TABELA: species
+----+-----------------+------------+----------+
| id | scientific_name | population | habitat |
+----+-----------------+------------+----------+
| 1 | Panthera leo | 120 | sawanna |
| 2 | Gorilla gorilla | 230 | las |
+----+-----------------+------------+----------+NoSQL bazy danych
Struktura: Dokumenty, pary klucz-wartość, grafy, kolumny
Przykłady: MongoDB, Redis, Cassandra, Neo4j
Cechy:
- Elastyczny schemat (JSON-like)
- Skalowanie horyzontalne
- Szybkie dla specyficznych zastosowań
W tej lekcji: Skupiamy się na SQL (relacyjne) - fundament większości aplikacji!
SQLite - baza danych w jednym pliku
SQLite to najpopularniejsza baza SQL na świecie:
- Brak serwera - cała baza w jednym pliku .db
- Zero konfiguracji - gotowe od razu
- Wbudowane w Python - moduł
sqlite3 - Idealne na start - prototypy, małe/średnie aplikacje
Użycie: Aplikacje mobilne, przeglądarki, urządzenia IoT, prototypy
SQL - język zapytań
SQL (Structured Query Language) to uniwersalny język do komunikacji z bazami relacyjnymi.
Podstawowe operacje (CRUD)
- CREATE - tworzenie tabel
- INSERT - dodawanie rekordów
- SELECT - pobieranie danych (Read)
- UPDATE - aktualizacja rekordów
- DELETE - usuwanie rekordów
CREATE TABLE - tworzenie tabeli
-- Tabela gatunków
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
);
-- Tabela obserwacji
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)
);Typy danych SQLite:
INTEGER- liczba całkowitaREAL- liczba zmiennoprzecinkowaTEXT- tekstBLOB- dane binarneNULL- brak wartości
Ograniczenia (constraints):
PRIMARY KEY- unikalny identyfikator wierszaAUTOINCREMENT- automatyczny wzrostNOT NULL- pole wymaganeUNIQUE- wartość unikalnaDEFAULT- wartość domyślnaFOREIGN KEY- relacja do innej tabeli
INSERT - dodawanie danych
-- Dodaj jeden rekord
INSERT INTO species (scientific_name, common_name, population, habitat, endangered)
VALUES ('Panthera leo', 'Lew', 120, 'sawanna', 1);
-- Dodaj wiele rekordów
INSERT INTO species (scientific_name, common_name, population, habitat, endangered)
VALUES
('Loxodonta africana', 'Słoń afrykański', 450, 'sawanna', 1),
('Gorilla gorilla', 'Goryl', 230, 'las tropikalny', 1),
('Python regius', 'Pyton królewski', 85, 'dżungla', 0);
-- Dodaj obserwację
INSERT INTO observations (species_id, observation_date, location, count, notes)
VALUES (1, '2024-01-15', 'Serengeti North', 12, 'Pride with 2 cubs');SELECT - pobieranie danych
-- Wszystkie gatunki
SELECT * FROM species;
-- Wybrane kolumny
SELECT common_name, population FROM species;
-- WHERE - filtrowanie
SELECT * FROM species WHERE endangered = 1;
SELECT * FROM species WHERE population > 100;
SELECT * FROM species WHERE habitat = 'sawanna' AND endangered = 1;
-- ORDER BY - sortowanie
SELECT * FROM species ORDER BY population DESC; -- Malejąco
SELECT * FROM species ORDER BY common_name ASC; -- Rosnąco
-- LIMIT - ograniczenie wyników
SELECT * FROM species LIMIT 10;
SELECT * FROM species LIMIT 10 OFFSET 20; -- Strona 3 (20-30)
-- LIKE - wyszukiwanie wzorca
SELECT * FROM species WHERE scientific_name LIKE 'Panthera%'; -- Zaczyna się od Panthera
SELECT * FROM species WHERE habitat LIKE '%las%'; -- Zawiera "las"
-- COUNT, SUM, AVG, MIN, MAX - agregacje
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 - grupowanie
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 dla grupUPDATE - aktualizacja danych
-- Zaktualizuj jeden rekord
UPDATE species
SET population = 125
WHERE id = 1;
-- Zaktualizuj wiele rekordów
UPDATE species
SET endangered = 1
WHERE population < 50;
-- Zaktualizuj wiele kolumn
UPDATE species
SET population = 130, habitat = 'sawanna i step'
WHERE scientific_name = 'Panthera leo';DELETE - usuwanie danych
-- Usuń jeden rekord
DELETE FROM species WHERE id = 10;
-- Usuń wiele rekordów
DELETE FROM species WHERE population = 0;
-- UWAGA: Usuń wszystko (bez WHERE!)
DELETE FROM species; -- Usuwa WSZYSTKIE rekordy!JOIN - łączenie tabel
-- INNER JOIN - tylko dopasowane rekordy
SELECT
species.common_name,
observations.observation_date,
observations.location,
observations.count
FROM observations
INNER JOIN species ON observations.species_id = species.id;
-- LEFT JOIN - wszystkie z lewej + dopasowane z prawej
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;
-- Aliasy tabel (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 - moduł sqlite3
Python ma wbudowany moduł sqlite3 do pracy z SQLite.
Podstawy
import sqlite3
# 1. Połącz się z bazą (tworzy plik jeśli nie istnieje)
conn = sqlite3.connect("safari.db")
# 2. Utwórz kursor (obiekt do wykonywania zapytań)
cursor = conn.cursor()
# 3. Wykonaj zapytanie SQL
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. Zatwierdź zmiany (WAŻNE!)
conn.commit()
# 5. Zamknij połączenie
conn.close()INSERT - dodawanie danych
import sqlite3
conn = sqlite3.connect("safari.db")
cursor = conn.cursor()
# Metoda 1: Zapytanie z wartościami (NIEBEZPIECZNE - SQL injection!)
# NIE RÓB TAK:
# name = "Lew'; DROP TABLE species; --" # SQL injection!
# cursor.execute(f"INSERT INTO species (common_name) VALUES ('{name}')")
# Metoda 2: Parameteryzowane zapytania (BEZPIECZNE)
cursor.execute("""
INSERT INTO species (scientific_name, common_name, population, endangered)
VALUES (?, ?, ?, ?)
""", ("Panthera leo", "Lew", 120, 1))
# Wiele rekordów naraz
species_data = [
("Loxodonta africana", "Słoń afrykański", 450, 1),
("Gorilla gorilla", "Goryl", 230, 1),
("Python regius", "Pyton królewski", 85, 0)
]
cursor.executemany("""
INSERT INTO species (scientific_name, common_name, population, endangered)
VALUES (?, ?, ?, ?)
""", species_data)
conn.commit()
# Pobierz ID ostatnio dodanego rekordu
print(f"Dodano gatunek ID: {cursor.lastrowid}")
conn.close()SELECT - pobieranie danych
import sqlite3
conn = sqlite3.connect("safari.db")
cursor = conn.cursor()
# fetchall() - wszystkie wyniki jako lista tupli
cursor.execute("SELECT * FROM species")
all_species = cursor.fetchall()
for species in all_species:
print(species) # (1, 'Panthera leo', 'Lew', 120, 1)
# fetchone() - jeden wynik
cursor.execute("SELECT * FROM species WHERE id = ?", (1,))
lion = cursor.fetchone()
print(lion) # (1, 'Panthera leo', 'Lew', 120, 1)
# fetchmany(n) - n wyników
cursor.execute("SELECT * FROM species")
first_five = cursor.fetchmany(5)
# Row factory - wyniki jako słowniki
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 i DELETE
import sqlite3
conn = sqlite3.connect("safari.db")
cursor = conn.cursor()
# UPDATE
cursor.execute("""
UPDATE species
SET population = ?
WHERE id = ?
""", (125, 1))
print(f"Zaktualizowano {cursor.rowcount} rekordów")
# DELETE
cursor.execute("DELETE FROM species WHERE population < ?", (10,))
print(f"Usunięto {cursor.rowcount} rekordów")
conn.commit()
conn.close()Context manager - bezpieczne zarządzanie połączeniem
import sqlite3
# Automatyczne commit i close
with sqlite3.connect("safari.db") as conn:
cursor = conn.cursor()
cursor.execute("""
INSERT INTO species (scientific_name, common_name, population)
VALUES (?, ?, ?)
""", ("Acinonyx jubatus", "Gepard", 7100))
# commit() automatycznie przy wyjściu z bloku with
# close() automatycznieSafari przykład - kompletny system bazy danych
import sqlite3
from typing import List, Dict, Optional
from datetime import datetime
class SafariDatabase:
"""Kompletny system zarządzania danymi Safari"""
def __init__(self, db_path: str = "safari.db"):
self.db_path = db_path
self.init_database()
def get_connection(self):
"""Utwórz połączenie z bazą"""
conn = sqlite3.connect(self.db_path)
conn.row_factory = sqlite3.Row # Wyniki jako słowniki
return conn
def init_database(self):
"""Inicjalizuj schemat bazy danych"""
with self.get_connection() as conn:
cursor = conn.cursor()
# Tabela gatunków
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
)
""")
# Tabela obserwacji
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
)
""")
# Indeksy dla szybszego wyszukiwania
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:
"""Dodaj nowy gatunek"""
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]:
"""Pobierz gatunek po 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]:
"""Lista gatunków z filtrami"""
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:
"""Zaktualizuj gatunek"""
if not kwargs:
return False
# Dynamiczne budowanie UPDATE
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:
"""Usuń gatunek"""
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:
"""Dodaj obserwację"""
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]:
"""Pobierz obserwacje dla gatunku"""
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:
"""Pobierz statystyki bazy"""
with self.get_connection() as conn:
cursor = conn.cursor()
# Łączna liczba gatunków
cursor.execute("SELECT COUNT(*) as count FROM species")
total_species = cursor.fetchone()["count"]
# Zagrożone gatunki
cursor.execute("SELECT COUNT(*) as count FROM species WHERE endangered = 1")
endangered_count = cursor.fetchone()["count"]
# Łączna populacja
cursor.execute("SELECT SUM(population) as total FROM species")
total_population = cursor.fetchone()["total"] or 0
# Gatunki po siedlisku
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
}
# === DEMONSTRACJA ===
print("=== SAFARI DATABASE SYSTEM ===\n")
db = SafariDatabase("safari_demo.db")
# 1. Dodaj gatunki
print("1. Dodawanie gatunków...")
lion_id = db.create_species("Panthera leo", "Lew", 120, "sawanna", True)
elephant_id = db.create_species("Loxodonta africana", "Słoń afrykański", 450, "sawanna", True)
gorilla_id = db.create_species("Gorilla gorilla", "Goryl", 230, "las tropikalny", True)
python_id = db.create_species("Python regius", "Pyton królewski", 85, "dżungla", False)
print(f" Dodano {4} gatunki")
# 2. Pobierz gatunek
print("\n2. Pobieranie gatunku...")
lion = db.get_species(lion_id)
print(f" {lion['common_name']} ({lion['scientific_name']})")
print(f" Populacja: {lion['population']}, Zagrożony: {'Tak' if lion['endangered'] else 'Nie'}")
# 3. Lista gatunków z filtrem
print("\n3. Lista zagrożonych gatunków...")
endangered = db.list_species(endangered=True)
for species in endangered:
print(f" - {species['common_name']}: {species['population']} osobników")
# 4. Aktualizacja
print("\n4. Aktualizacja populacji lwa...")
db.update_species(lion_id, population=125)
lion = db.get_species(lion_id)
print(f" Nowa populacja: {lion['population']}")
# 5. Dodaj obserwacje
print("\n5. Dodawanie obserwacji...")
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(" Dodano 3 obserwacje")
# 6. Pobierz obserwacje
print("\n6. Obserwacje lwa...")
lion_obs = db.get_observations_for_species(lion_id)
for obs in lion_obs:
print(f" - {obs['observation_date']}: {obs['count']}x w {obs['location']}")
# 7. Statystyki
print("\n7. Statystyki bazy danych...")
stats = db.get_statistics()
print(f" Łącznie gatunków: {stats['total_species']}")
print(f" Zagrożone: {stats['endangered_count']}")
print(f" Łączna populacja: {stats['total_population']}")
print(" Według siedliska:")
for habitat_stat in stats['by_habitat']:
print(f" - {habitat_stat['habitat']}: {habitat_stat['count']} gatunków")
print("\nDemonstracja zakończona")Transakcje - atomowość operacji
import sqlite3
conn = sqlite3.connect("safari.db")
try:
cursor = conn.cursor()
# Rozpocznij transakcję (domyślnie)
cursor.execute("INSERT INTO species (...) VALUES (...)")
cursor.execute("UPDATE observations SET ...")
# Zatwierdź transakcję
conn.commit()
print("Transakcja zatwierdzona")
except Exception as e:
# Wycofaj transakcję przy błędzie
conn.rollback()
print(f"Transakcja wycofana: {e}")
finally:
conn.close()Podsumowanie
W tej lekcji nauczyłeś/aś się:
- Czym są bazy danych i po co służą
- Relacyjne (SQL) vs NoSQL
- SQLite - baza w jednym pliku
- SQL: CREATE TABLE, INSERT, SELECT, UPDATE, DELETE
- WHERE, ORDER BY, LIMIT, JOIN
- Python sqlite3: connect, cursor, execute, fetchall
- Parameteryzowane zapytania (SQL injection prevention)
- Transakcje i rollback
- Kompletny system CRUD z relacjami
Checkpoint
Przed przejściem dalej:
- Potrafisz stworzyć tabelę (CREATE TABLE)
- Potrafisz dodać dane (INSERT)
- Potrafisz pobrać dane z filtrem (SELECT WHERE)
- Rozumiesz JOIN (łączenie tabel)
- Potrafisz używać sqlite3 w Pythonie
Analogia Safari: Baza danych SQL to wielka biblioteka katalogowa z milionami kart gatunków - szybkie wyszukiwanie, relacje, integralność danych. JSON to notatnik - świetny na 10 wpisów, niewystarczający na miliony!
W następnej lekcji Darwin pokaże Ci SQLAlchemy ORM - jak pracować z bazami danych używając pythonowych klas zamiast surowego SQL!
Hasła z dokumentacji w tej lekcji
Widzisz błąd w tej lekcji?
Sprawdź się
Odpowiedz na pytania z tej lekcji. Wybierz odpowiedź, a od razu zobaczysz, czy jest poprawna.
1. Które zapytanie SQL pobiera wszystkie dane z tabeli 'animals'?
2. Które polecenie dodaje nowy rekord do tabeli?
To 2 z 3 pytań do tej lekcji. Pozostałe rozwiążesz w grze.
Zadania praktyczne w grze
- Klikanie w kolejności
Kliknij elementy w prawidłowej kolejności:
- Układanie w poziomie
Ułóż elementy w prawidłowej kolejności:
- Edytor kodu
Utwórz połączenie z bazą 'safari.db' i stwórz tabelę 'animals'
- Edytor kodu
Wstaw rekord {'name': 'Tygrys', 'species': 'Panthera tigris'} do tabeli 'animals'
- Układanie w pionie
Uporządkuj kroki pracy z SQLite:
- Układanie w poziomie
Uporządkuj: SELECT * FROM animals WHERE species = 'Tiger'