Langage SQL avec SQLite3 sous
Python
Créez, lisez, mettez à jour, supprimez
Transactions et commit
Exemples concrets
Objectifs
• Comprendre SQL avec SQLite
• Utiliser le module sqlite3 en Python
• Maîtriser CREATE, INSERT, SELECT, UPDATE, DELETE, COMMIT
Prérequis
• Python 3
• sqlite3 intégré à Python
• Éditeur de code
•
• python -V
• python -c "import sqlite3; print(sqlite3.sqlite_version)"
Créer une base de données
• SQLite stocke un fichier .db
• La base est créée à la connexion si absente
•
• python
• import sqlite3
• conn = [Link]("[Link]")
• cur = [Link]()
Schéma d'exemple
• Table clients id, nom, email
• Clé primaire sur id
CREATE TABLE – syntaxe
• CREATE TABLE nom_table (...)
• Types SQLite INTEGER, TEXT, REAL, BLOB
•
• sql
• CREATE TABLE clients (
• id INTEGER PRIMARY KEY,
• nom TEXT NOT NULL,
• email TEXT UNIQUE
• );
CREATE TABLE en Python
• Exécuter du SQL avec [Link]
• Valider avec commit
•
• python
• [Link]("""
• CREATE TABLE IF NOT EXISTS clients (
• id INTEGER PRIMARY KEY,
• nom TEXT NOT NULL,
• email TEXT UNIQUE
• )
• """)
• [Link]()
INSERT INTO – syntaxe
• INSERT INTO table (colonnes) VALUES (valeurs)
• Toujours paramétrer les valeurs
•
• sql
• INSERT INTO clients (nom, email) VALUES ('Ali',
'ali@[Link]');
INSERT paramétré en Python
• Utiliser ? pour liaisons
• Évite l'injection SQL
•
• python
• [Link]("INSERT INTO clients (nom, email) VALUES (?, ?)",
("Ali", "ali@[Link]"))
• [Link]()
INSERT multiple
• executemany pour lots
• Gain de temps
•
• python
• rows = [("Sara", "sara@[Link]"), ("Noah", "noah@[Link]")]
• [Link]("INSERT INTO clients (nom, email) VALUES (?,
?)", rows)
• [Link]()
SELECT – syntaxe
• SELECT colonnes FROM table
• Filtrer avec WHERE, trier avec ORDER BY
•
• sql
• SELECT id, nom, email FROM clients;
SELECT en Python
• fetchone, fetchall
• Itérer sur le curseur
•
• python
• [Link]("SELECT id, nom, email FROM clients")
• for row in [Link]():
• print(row)
WHERE – filtres
• Opérateurs =, <, >, LIKE, IN
• Combiner avec AND, OR
•
• sql
• SELECT * FROM clients WHERE email LIKE '%@[Link]';
ORDER BY et LIMIT
• ORDER BY colonne ASC|DESC
• Limiter le nombre de lignes
•
• sql
• SELECT * FROM clients ORDER BY nom ASC LIMIT 10;
Paramètres dans SELECT
• Toujours passer les valeurs séparément
• Placeholders ?
•
• python
• domaine = "%@[Link]"
• [Link]("SELECT * FROM clients WHERE email LIKE ?",
(domaine,))
• print([Link]())
UPDATE – syntaxe
• UPDATE table SET colonne = valeur WHERE condition
• Toujours filtrer
•
• sql
• UPDATE clients SET email = 'ali@[Link]' WHERE id = 1;
UPDATE en Python
• Paramétrer et valider
• rowcount donne le nombre de lignes
•
• python
• [Link]("UPDATE clients SET email = ? WHERE id = ?",
("ali@[Link]", 1))
• print([Link])
• [Link]()
DELETE – syntaxe
• DELETE FROM table WHERE condition
• Sans WHERE supprime tout
•
• sql
• DELETE FROM clients WHERE id = 2;
DELETE en Python
• Paramétrer la condition
• Valider avec commit
•
• python
• [Link]("DELETE FROM clients WHERE email = ?",
("noah@[Link]",))
• [Link]()
Transactions et COMMIT
• Une transaction groupe plusieurs opérations
• COMMIT enregistre, ROLLBACK annule
•
• python
• try:
• [Link]("INSERT INTO clients (nom) VALUES (?)",
("Test",))
• [Link]("UPDATE clients SET nom = ? WHERE id = ?",
("Test2", 1))
• [Link]()
• except Exception:
• [Link]()
• raise
Mode autocommit
• Isolation par défaut DEFERRED
• Utiliser [Link] ou context manager
•
• python
• with conn:
• [Link]("INSERT INTO clients (nom) VALUES (?)",
("Auto",))
Contraintes utiles
• PRIMARY KEY, UNIQUE, NOT NULL
• CHECK pour valider les données
•
• sql
• CREATE TABLE emails (
• id INTEGER PRIMARY KEY,
• adresse TEXT NOT NULL UNIQUE CHECK(instr(adresse, '@') > 1)
• );
Types SQLite
• Affinités: INTEGER, TEXT, REAL, BLOB, NUMERIC
• NULL autorisé sauf NOT NULL
Index
• Accélère SELECT avec WHERE
• Coût sur INSERT, UPDATE, DELETE
•
• sql
• CREATE INDEX idx_clients_email ON clients(email);
Jointures simples
• JOIN relie des tables par clés
• Exemple client-commandes
•
• sql
• SELECT [Link], [Link]
• FROM clients c
• JOIN commandes o ON o.client_id = [Link];
Agrégations
• COUNT, SUM, AVG, MIN, MAX
• GROUP BY pour regrouper
•
• sql
• SELECT COUNT(*) FROM clients;
• SELECT substr(email, instr(email,'@')+1) AS domaine, COUNT(*)
• FROM clients
• GROUP BY domaine;
Script de bout en bout
• Créer, insérer, lire, mettre à jour, supprimer
• Tout dans un seul fichier
•
• python
• import sqlite3
• with [Link]("[Link]") as conn:
• cur = [Link]()
• [Link]("CREATE TABLE IF NOT EXISTS clients (id INTEGER
PRIMARY KEY, nom TEXT, email TEXT)")
• [Link]("INSERT INTO clients (nom, email) VALUES
(?, ?)", [("Ana","ana@[Link]"),("Ben","ben@[Link]")])
• [Link]()
• for row in [Link]("SELECT * FROM clients"):
• print(row)
Erreurs courantes
• Oublier WHERE dans UPDATE ou DELETE
• Ne pas valider la transaction
• Ne pas paramétrer les valeurs
Bonnes pratiques
• Toujours paramétrer
• Utiliser with conn pour transactions
• Ajouter des index ciblés
• Écrire des tests
Ressources
• Doc Python sqlite3
• Doc SQLite
• Référence SQL
•
• Liens:
• [Link]
• [Link]
• [Link]