SQL - les entrepôts de données relationnels

Content de te revoir ! Ici Darwin, avec une leçon cruciale sur le stockage des données.

Jusqu'ici, tu as travaillé avec des données de façon temporaire - dans des variables, des fichiers JSON, des pages scrapées. Mais que faire si tu as des millions d'observations d'espèces ? Que faire si tu dois rechercher rapidement toutes les espèces menacées du Serengeti ?

Alors il te faut une base de données - un système spécialisé dans le stockage et la gestion de grands ensembles de données !

Analogie safari : Les fichiers JSON sont comme le carnet d'un biologiste - ça marche pour 10 observations. Une base de données est une grande bibliothèque à catalogue - des tiroirs organisés avec des fiches, des index, et une recherche rapide sur des millions d'enregistrements !

Qu'est-ce qu'une base de données ?

Une base de données est un ensemble organisé de données + un système de gestion (SGBD - Système de Gestion de Base de Données).

Pourquoi des bases de données ?

• Recherche rapide - index, optimisation des requêtes
• Sécurité - contrôle d'accès, permissions
• Intégrité - validation, relations, clés étrangères
• Scalabilité - des millions d'enregistrements sans problème
• Accès concurrent - plusieurs utilisateurs en même temps
• Transactions - opérations atomiques (tout ou rien)

Relationnel vs NoSQL

Bases de données relationnelles (SQL)

Structure : des tables avec des lignes et des colonnes (comme un tableur Excel)

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

Caractéristiques :

• Schéma rigide (colonnes définies à l'avance)
• Relations entre les tables (clés étrangères)
• ACID (Atomicité, Cohérence, Isolation, Durabilité)
• Langage SQL (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+----+-----------------+------------+----------+

Bases de données NoSQL

Structure : documents, paires clé-valeur, graphes, colonnes

Exemples : MongoDB, Redis, Cassandra, Neo4j

Caractéristiques :

• Schéma flexible (de type JSON)
• Scalabilité horizontale
• Rapide pour des cas d'usage spécifiques

Dans cette leçon : nous nous concentrons sur SQL (relationnel) - le fondement de la plupart des applications !

SQLite - une base de données dans un seul fichier

SQLite est la base de données SQL la plus populaire au monde :

• Pas de serveur - toute la base dans un seul fichier .db
• Zéro configuration - prête à l'emploi
• Intégrée à Python - le module sqlite3
• Idéale pour commencer - prototypes, petites/moyennes applications

Usage : applications mobiles, navigateurs, objets IoT, prototypes

SQL - le langage de requête

SQL (Structured Query Language) est le langage universel pour communiquer avec les bases de données relationnelles.

Opérations de base (CRUD)

• CREATE - créer des tables
• INSERT - ajouter des enregistrements
• SELECT - récupérer des données (Read)
• UPDATE - mettre à jour des enregistrements
• DELETE - supprimer des enregistrements

CREATE TABLE - créer une table

1-- Table des espèces
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-- Table des observations
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);

Types de données SQLite :

• INTEGER - nombre entier
• REAL - nombre à virgule flottante
• TEXT - texte
• BLOB - données binaires
• NULL - absence de valeur

Contraintes :

• PRIMARY KEY - identifiant unique de ligne
• AUTOINCREMENT - incrémentation automatique
• NOT NULL - champ requis
• UNIQUE - valeur unique
• DEFAULT - valeur par défaut
• FOREIGN KEY - relation vers une autre table

INSERT - ajouter des données

1-- Ajouter un seul enregistrement
2INSERT INTO species (scientific_name, common_name, population, habitat, endangered)
3VALUES ('Panthera leo', 'Lion', 120, 'savanna', 1);
4
5-- Ajouter plusieurs enregistrements
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-- Ajouter une observation
13INSERT INTO observations (species_id, observation_date, location, count, notes)
14VALUES (1, '2024-01-15', 'Serengeti North', 12, 'Pride with 2 cubs');

SELECT - récupérer des données

1-- Toutes les espèces
2SELECT * FROM species;
3
4-- Colonnes sélectionnées
5SELECT common_name, population FROM species;
6
7-- WHERE - filtrage
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 - tri
13SELECT * FROM species ORDER BY population DESC;  -- Décroissant
14SELECT * FROM species ORDER BY common_name ASC;   -- Croissant
15
16-- LIMIT - limiter les résultats
17SELECT * FROM species LIMIT 10;
18SELECT * FROM species LIMIT 10 OFFSET 20;  -- Page 3 (20-30)
19
20-- LIKE - correspondance de motif
21SELECT * FROM species WHERE scientific_name LIKE 'Panthera%';  -- Commence par Panthera
22SELECT * FROM species WHERE habitat LIKE '%forest%';  -- Contient "forest"
23
24-- COUNT, SUM, AVG, MIN, MAX - agrégations
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 - regroupement
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 pour les groupes

UPDATE - mettre à jour des données

1-- Mettre à jour un seul enregistrement
2UPDATE species
3SET population = 125
4WHERE id = 1;
5
6-- Mettre à jour plusieurs enregistrements
7UPDATE species
8SET endangered = 1
9WHERE population < 50;
10
11-- Mettre à jour plusieurs colonnes
12UPDATE species
13SET population = 130, habitat = 'savanna and steppe'
14WHERE scientific_name = 'Panthera leo';

DELETE - supprimer des données

1-- Supprimer un seul enregistrement
2DELETE FROM species WHERE id = 10;
3
4-- Supprimer plusieurs enregistrements
5DELETE FROM species WHERE population = 0;
6
7-- ATTENTION : Tout supprimer (sans WHERE !)
8DELETE FROM species;  -- Supprime TOUS les enregistrements !

JOIN - combiner des tables

1-- INNER JOIN - seulement les enregistrements correspondants
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 - tout de gauche + les correspondances de droite
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-- Alias de tables (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 - le module sqlite3

Python dispose d'un module sqlite3 intégré pour travailler avec SQLite.

Les bases

1import sqlite3
2
3# 1. Se connecter à la base (crée le fichier s'il n'existe pas)
4conn = sqlite3.connect("safari.db")
5
6# 2. Créer un curseur (objet pour exécuter les requêtes)
7cursor = conn.cursor()
8
9# 3. Exécuter une requête SQL
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. Valider les changements (IMPORTANT !)
21conn.commit()
22
23# 5. Fermer la connexion
24conn.close()

INSERT - ajouter des données

1import sqlite3
2
3conn = sqlite3.connect("safari.db")
4cursor = conn.cursor()
5
6# Méthode 1 : requête avec des valeurs (DANGEREUX - injection SQL !)
7# NE FAIS PAS ÇA :
8# name = "Lion'; DROP TABLE species; --"  # Injection SQL !
9# cursor.execute(f"INSERT INTO species (common_name) VALUES ('{name}')")
10
11# Méthode 2 : requêtes paramétrées (SÛR)
12cursor.execute("""
13    INSERT INTO species (scientific_name, common_name, population, endangered)
14    VALUES (?, ?, ?, ?)
15""", ("Panthera leo", "Lion", 120, 1))
16
17# Plusieurs enregistrements à la fois
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# Récupérer l'ID du dernier enregistrement ajouté
32print(f"ID de l'espèce ajoutée : {cursor.lastrowid}")
33
34conn.close()

SELECT - récupérer des données

1import sqlite3
2
3conn = sqlite3.connect("safari.db")
4cursor = conn.cursor()
5
6# fetchall() - tous les résultats sous forme de liste de 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() - un seul résultat
14cursor.execute("SELECT * FROM species WHERE id = ?", (1,))
15lion = cursor.fetchone()
16print(lion)  # (1, 'Panthera leo', 'Lion', 120, 1)
17
18# fetchmany(n) - n résultats
19cursor.execute("SELECT * FROM species")
20first_five = cursor.fetchmany(5)
21
22# Row factory - résultats sous forme de dictionnaires
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 et 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"{cursor.rowcount} enregistrements mis à jour")
14
15# DELETE
16cursor.execute("DELETE FROM species WHERE population < ?", (10,))
17print(f"{cursor.rowcount} enregistrements supprimés")
18
19conn.commit()
20conn.close()

Gestionnaire de contexte - gestion sûre de la connexion

1import sqlite3
2
3# Commit et fermeture automatiques
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() automatiquement à la sortie du bloc with
13# close() automatiquement

Exemple safari - système de base de données complet

1import sqlite3
2from typing import List, Dict, Optional
3from datetime import datetime
4
5class SafariDatabase:
6    """Système complet de gestion des données Safari"""
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        """Créer une connexion à la base"""
14        conn = sqlite3.connect(self.db_path)
15        conn.row_factory = sqlite3.Row  # Résultats sous forme de dictionnaires
16        return conn
17
18    def init_database(self):
19        """Initialiser le schéma de la base"""
20        with self.get_connection() as conn:
21            cursor = conn.cursor()
22
23            # Table des espèces
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            # Table des observations
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            # Index pour une recherche plus rapide
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    # === CRUD ESPÈCES ===
65
66    def create_species(self, scientific_name: str, common_name: str,
67                      population: int = 0, habitat: str = "",
68                      endangered: bool = False) -> int:
69        """Ajouter une nouvelle espèce"""
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        """Récupérer une espèce par 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        """Lister les espèces avec des filtres"""
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        """Mettre à jour une espèce"""
113        if not kwargs:
114            return False
115
116        # Construction dynamique du UPDATE
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        """Supprimer une espèce"""
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        """Ajouter une 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        """Récupérer les observations d'une espèce"""
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    # === STATISTIQUES ===
172
173    def get_statistics(self) -> Dict:
174        """Récupérer les statistiques de la base"""
175        with self.get_connection() as conn:
176            cursor = conn.cursor()
177
178            # Nombre total d'espèces
179            cursor.execute("SELECT COUNT(*) as count FROM species")
180            total_species = cursor.fetchone()["count"]
181
182            # Espèces menacées
183            cursor.execute("SELECT COUNT(*) as count FROM species WHERE endangered = 1")
184            endangered_count = cursor.fetchone()["count"]
185
186            # Population totale
187            cursor.execute("SELECT SUM(population) as total FROM species")
188            total_population = cursor.fetchone()["total"] or 0
189
190            # Espèces par 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# === DÉMONSTRATION ===
208
209print("=== SYSTÈME DE BASE DE DONNÉES SAFARI ===\n")
210
211db = SafariDatabase("safari_demo.db")
212
213# 1. Ajouter des espèces
214print("1. Ajout des espèces...")
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"   {4} espèces ajoutées")
221
222# 2. Récupérer une espèce
223print("\n2. Récupération d'une espèce...")
224lion = db.get_species(lion_id)
225print(f"   {lion['common_name']} ({lion['scientific_name']})")
226print(f"   Population : {lion['population']}, Menacée : {'Oui' if lion['endangered'] else 'Non'}")
227
228# 3. Lister les espèces avec un filtre
229print("\n3. Liste des espèces menacées...")
230endangered = db.list_species(endangered=True)
231for species in endangered:
232    print(f"   - {species['common_name']} : {species['population']} individus")
233
234# 4. Mise à jour
235print("\n4. Mise à jour de la population du lion...")
236db.update_species(lion_id, population=125)
237lion = db.get_species(lion_id)
238print(f"   Nouvelle population : {lion['population']}")
239
240# 5. Ajouter des observations
241print("\n5. Ajout des 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("   3 observations ajoutées")
247
248# 6. Récupérer les observations
249print("\n6. Observations du lion...")
250lion_obs = db.get_observations_for_species(lion_id)
251for obs in lion_obs:
252    print(f"   - {obs['observation_date']} : {obs['count']}x à {obs['location']}")
253
254# 7. Statistiques
255print("\n7. Statistiques de la base...")
256stats = db.get_statistics()
257print(f"   Nombre total d'espèces : {stats['total_species']}")
258print(f"   Menacées : {stats['endangered_count']}")
259print(f"   Population totale : {stats['total_population']}")
260print("   Par habitat :")
261for habitat_stat in stats['by_habitat']:
262    print(f"     - {habitat_stat['habitat']} : {habitat_stat['count']} espèces")
263
264print("\nDémonstration terminée")

Transactions - atomicité des opérations

1import sqlite3
2
3conn = sqlite3.connect("safari.db")
4
5try:
6    cursor = conn.cursor()
7
8    # Début de la transaction (par défaut)
9    cursor.execute("INSERT INTO species (...) VALUES (...)")
10    cursor.execute("UPDATE observations SET ...")
11
12    # Valider la transaction
13    conn.commit()
14    print("Transaction validée")
15
16except Exception as e:
17    # Annuler la transaction en cas d'erreur
18    conn.rollback()
19    print(f"Transaction annulée : {e}")
20
21finally:
22    conn.close()

Résumé

Dans cette leçon, tu as appris :

• Ce que sont les bases de données et à quoi elles servent
• Relationnel (SQL) vs NoSQL
• SQLite - une base de données dans un seul fichier
• SQL : CREATE TABLE, INSERT, SELECT, UPDATE, DELETE
• WHERE, ORDER BY, LIMIT, JOIN
• Python sqlite3 : connect, cursor, execute, fetchall
• Les requêtes paramétrées (prévention des injections SQL)
• Les transactions et le rollback
• Un système CRUD complet avec des relations

Point de contrôle

Avant de continuer :

• Tu sais créer une table (CREATE TABLE)
• Tu sais ajouter des données (INSERT)
• Tu sais récupérer des données avec des filtres (SELECT WHERE)
• Tu comprends JOIN (combiner des tables)
• Tu sais utiliser sqlite3 en Python

Analogie safari : Une base de données SQL est une grande bibliothèque à catalogue avec des millions de fiches d'espèces - recherche rapide, relations, intégrité des données. JSON est un carnet - parfait pour 10 entrées, insuffisant pour des millions !

Dans la prochaine leçon, Darwin te montrera SQLAlchemy ORM - comment travailler avec les bases de données en utilisant des classes Python plutôt que du SQL brut !