Intégration SQL dans des programmes | Exercices corrigés

Concepts & Exercices

Connexion → Requête → Résultat

Flux d'exécution SQL

Requête dynamique

INSERT INTO table VALUES (?, ?, ?)

Curseur

Exécute les requêtes et récupère les résultats

Transaction

BEGIN/COMMIT/ROLLBACK pour la cohérence

🔌

Exercice 1

Connexion à une base de données SQLite avec Python

Exercice 2

Exécuter une requête SELECT avec paramètres

Exercice 3

Insérer des données de manière sécurisée

Exercice 4

Gérer une transaction avec rollback

Exercice 5

Mettre à jour plusieurs enregistrements en boucle

Corrigé : Exercices 1 à 3

1 Connexion à SQLite

Définition :

Connexion à la base de données : Établissement d'une communication entre le programme et le SGBD pour exécuter des requêtes SQL.

Méthode de connexion :

Importer la bibliothèque appropriée (sqlite3, psycopg2, etc.)
Créer un objet de connexion avec les paramètres requis
Créer un curseur pour exécuter les requêtes
Utiliser try/except pour gérer les erreurs

import sqlite3

# Établir la connexion
conn = sqlite3.connect('ma_base.db')
cursor = conn.cursor()

print("Connexion réussie à la base de données")

# Fermer la connexion
conn.close()
              

Étape 1 : Importer le module

On importe le module sqlite3 pour interagir avec la base SQLite

Étape 2 : Créer la connexion

On utilise sqlite3.connect('ma_base.db') pour créer ou ouvrir la base

Étape 3 : Créer le curseur

On crée un curseur avec conn.cursor() pour exécuter les requêtes

Étape 4 : Fermer la connexion

On ferme la connexion avec conn.close() pour libérer les ressources

Réponse finale :

Connexion établie avec succès à la base SQLite

Règles appliquées :

• Connexion unique : Une seule connexion active par session est recommandée

• Fermeture obligatoire : Toujours fermer la connexion pour libérer les ressources

• Gestion des erreurs : Utiliser try/except pour capturer les exceptions

2 Requête SELECT avec paramètres

Définition :

Paramètres dans les requêtes : Utilisation de placeholders (?) pour éviter les injections SQL et sécuriser les requêtes dynamiques.

import sqlite3

conn = sqlite3.connect('ma_base.db')
cursor = conn.cursor()

# Requête avec paramètre
requete = "SELECT * FROM clients WHERE age > ?"
parametre = (18,)
cursor.execute(requete, parametre)

resultats = cursor.fetchall()
for resultat in resultats:
    print(resultat)

conn.close()
              

Étape 1 : Préparer la requête

On utilise un placeholder ? à la place de la valeur directe

Étape 2 : Préparer les paramètres

On crée un tuple (18,) avec la valeur du paramètre

Étape 3 : Exécuter la requête

On utilise cursor.execute(requete, parametre) pour exécuter

Étape 4 : Récupérer les résultats

On utilise cursor.fetchall() pour obtenir tous les résultats

Réponse finale :

Requête exécutée en toute sécurité avec paramètres

Règles appliquées :

• Sécurité : Utiliser toujours des placeholders pour éviter les injections SQL

• Type tuple : Les paramètres doivent être fournis sous forme de tuple

• Placeholders : Utiliser ? pour SQLite, %s pour PostgreSQL/MySQL

3 Insertion sécurisée

Définition :

Insertion sécurisée : Méthode d'insertion de données en utilisant des paramètres pour éviter les failles de sécurité.

import sqlite3

conn = sqlite3.connect('ma_base.db')
cursor = conn.cursor()

# Données à insérer
donnees = ('Dupont', 'Jean', 25, 'jean.dupont@email.com')

# Requête d'insertion avec paramètres
requete = "INSERT INTO clients (nom, prenom, age, email) VALUES (?, ?, ?, ?)"
cursor.execute(requete, donnees)

# Valider la transaction
conn.commit()

print(f"{cursor.rowcount} ligne insérée")

conn.close()
              

Étape 1 : Préparer les données

On crée un tuple donnees avec les valeurs à insérer

Étape 2 : Créer la requête

On utilise des placeholders ? pour chaque champ

Étape 3 : Exécuter l'insertion

On exécute la requête avec cursor.execute()

Étape 4 : Valider la transaction

On utilise conn.commit() pour valider les modifications

Réponse finale :

Données insérées en toute sécurité dans la base

Règles appliquées :

• Sécurité : Toujours utiliser des paramètres pour les insertions

• Commit obligatoire : Utiliser commit() pour valider les modifications

• Nombre de lignes : cursor.rowcount permet de vérifier le nombre de lignes affectées

Corrigé : Exercices 4 à 5

4 Gestion de transaction

Définition :

Transaction : Ensemble d'opérations SQL qui doivent être exécutées ensemble ou annulées ensemble pour maintenir l'intégrité des données.

import sqlite3

conn = sqlite3.connect('ma_base.db')
cursor = conn.cursor()

try:
    # Début de la transaction
    conn.execute("BEGIN")
    
    # Première opération
    cursor.execute("INSERT INTO clients (nom, prenom) VALUES (?, ?)", ('Martin', 'Sophie'))
    
    # Deuxième opération
    cursor.execute("UPDATE comptes SET solde = solde - 100 WHERE id_client = ?", (cursor.lastrowid,))
    
    # Validation de la transaction
    conn.commit()
    print("Transaction réussie")
    
except Exception as e:
    # Annulation en cas d'erreur
    conn.rollback()
    print(f"Erreur : {e}")
    
finally:
    conn.close()
              

Étape 1 : Début de la transaction

On utilise conn.execute("BEGIN") pour démarrer la transaction

Étape 2 : Opérations multiples

On exécute plusieurs requêtes dans le bloc try

Étape 3 : Validation ou annulation

En cas de succès, commit() valide toutes les opérations

En cas d'erreur, rollback() annule toutes les opérations

Réponse finale :

Transaction gérée avec succès et sécurité

Règles appliquées :

• ACID : Atomicité, Cohérence, Isolation, Durabilité

• Try/Except/Finally : Structure pour gérer les erreurs

• Rollback : Annule toutes les opérations si une erreur survient

5 Mise à jour en boucle

Définition :

Exécution en lot : Technique pour exécuter plusieurs requêtes similaires de manière efficace en utilisant executemany().

import sqlite3

conn = sqlite3.connect('ma_base.db')
cursor = conn.cursor()

# Données à mettre à jour
donnees = [
    (100, 1),
    (150, 2),
    (200, 3)
]

# Requête de mise à jour
requete = "UPDATE produits SET prix = ? WHERE id_produit = ?"

try:
    # Exécuter plusieurs mises à jour
    cursor.executemany(requete, donnees)
    
    # Valider la transaction
    conn.commit()
    
    print(f"{cursor.rowcount} lignes mises à jour")
    
except Exception as e:
    print(f"Erreur : {e}")
    conn.rollback()
    
finally:
    conn.close()
              

Étape 1 : Préparer les données

On crée une liste de tuples donnees avec les valeurs à mettre à jour

Étape 2 : Créer la requête

On utilise des placeholders ? pour les valeurs variables

Étape 3 : Exécuter en lot

On utilise cursor.executemany() pour exécuter plusieurs fois la même requête

Étape 4 : Gérer les erreurs

On utilise try/except pour gérer les erreurs et rollback en cas de problème

Réponse finale :

Plusieurs mises à jour exécutées efficacement

Règles appliquées :

• Efficacité : executemany() est plus rapide que des exécutions individuelles

• Transaction unique : Toutes les opérations sont dans la même transaction

• Gestion des erreurs : Rollback en cas d'échec d'une opération

Cours bien détaillé

connect() → cursor() → execute() → fetch() → close()

Cycle de vie SQL

🎯

Connexion : Établissement de la liaison avec la base de données.

📏

Curseur : Interface pour exécuter les requêtes SQL.

📐

Paramètres : Utilisation de placeholders pour la sécurité.

📝

Transaction : Ensemble d'opérations qui doivent réussir ou échouer ensemble.

💡

Conseil : Toujours utiliser des paramètres dans les requêtes pour éviter les injections SQL

🔍

Attention : Fermer la connexion après chaque utilisation pour libérer les ressources

⚡

Astuce : Utiliser executemany() pour les opérations en lot

📋

Méthode : Encadrer les opérations sensibles dans des transactions

✅

Vérification : Toujours tester les erreurs potentielles avec try/except

Bonnes pratiques :

Utilisation de context managers : with statement pour gérer automatiquement les connexions
Validation des entrées : Vérifier les données avant insertion
Journalisation : Enregistrer les opérations pour le débogage
Sécurité : Utiliser des requêtes préparées avec paramètres

Règles de sécurité :

Ne jamais concaténer directement des chaînes dans les requêtes SQL
Toujours utiliser des paramètres nommés ou positionnels
Valider et nettoyer les données avant insertion dans la base
Utiliser des transactions pour les opérations critiques

execute()

Exécute une requête SQL unique

executemany()

Exécute une requête avec plusieurs jeux de paramètres

fetchall()

Récupère tous les résultats d'une requête SELECT

Intégration SQL dans des programmesExercices corrigés

Intégration SQL dans des programmes
Exercices corrigés