Prérequis : bases de données relationnelles (contraintes d’intégrité), requêtes de sélection SQL
À l’issue de ce chapitre, vous saurez :
INSERT INTO ... VALUES ;UPDATE ... SET ... WHERE ;DELETE FROM ... WHERE ;CREATE TABLE, en y déclarant les contraintes d’intégrité, et la supprimer avec DROP TABLE ;sqlite3.L’ordre INSERT INTO ajoute un nouveau n-uplet (une nouvelle ligne) dans une table.
Syntaxe générale :
INSERT INTO nom_table (attribut1, attribut2, ...)
VALUES (valeur1, valeur2, ...);
Exemple. Ajouter un nouveau produit dans la table Produits :
INSERT INTO Produits (id, nom, categorie, prix, stock)
VALUES (9, 'Règle 30 cm', 'Papeterie', 1.80, 120);
Les valeurs doivent respecter les types des attributs et les contraintes d’intégrité. Si l’id existe déjà ou si le prix est négatif (avec un CHECK), le SGBD refuse l’insertion.
L’ordre UPDATE modifie les valeurs d’un ou plusieurs attributs pour les lignes qui vérifient une condition.
Syntaxe générale :
UPDATE nom_table
SET attribut1 = nouvelle_valeur1, attribut2 = nouvelle_valeur2
WHERE condition;
Exemple. Le prix du Cahier A4 passe à 2.80 :
UPDATE Produits
SET prix = 2.80
WHERE nom = 'Cahier A4';
Attention. Sans clause WHERE, toutes les lignes de la table sont modifiées :
UPDATE Produits
SET stock = 0;
-- Met TOUT le stock à 0 ! Probablement pas souhaitable.
L’ordre DELETE supprime les lignes qui vérifient une condition.
Syntaxe générale :
DELETE FROM nom_table
WHERE condition;
Exemple. Supprimer les produits dont le stock est nul :
DELETE FROM Produits
WHERE stock = 0;
Attention. Sans clause WHERE, toutes les lignes sont supprimées (la table reste, mais vide).
La suppression peut être refusée si des lignes d’une autre table référencent la ligne à supprimer via une clé étrangère (contrainte de référence).
L’ordre CREATE TABLE définit la structure d’une nouvelle table : le nom de chaque attribut, son type et les contraintes associées.
CREATE TABLE Eleves (
id_eleve INTEGER PRIMARY KEY,
nom TEXT NOT NULL,
prenom TEXT NOT NULL,
classe TEXT,
annee_naissance INT CHECK (annee_naissance >= 2000)
);
| Type | Description |
|---|---|
INT ou INTEGER |
Entier |
REAL |
Nombre à virgule flottante |
TEXT |
Chaîne de caractères |
DATE |
Date (stockée comme texte au format 'AAAA-MM-JJ' en SQLite) |
Les contraintes d’intégrité
s’écrivent dans CREATE TABLE, à la suite d’un attribut ou en fin de définition :
| Mot-clé | Contrainte | Effet |
|---|---|---|
NOT NULL |
Domaine | Interdit les valeurs vides |
CHECK (condition) |
Domaine | Restreint les valeurs acceptées |
UNIQUE |
Relation | Interdit les doublons sur un attribut qui n’est pas la clé primaire |
PRIMARY KEY |
Relation | Désigne la clé primaire : unicité et non-nullité |
FOREIGN KEY (attribut) REFERENCES Table(attribut) |
Référence | Impose que la valeur désigne une ligne existante de l’autre table |
La table Eleves ci-dessus déclare déjà une clé primaire, deux attributs NOT NULL et un CHECK. La table Emprunts déclare en plus deux clés étrangères :
CREATE TABLE Emprunts (
id_emprunt INTEGER PRIMARY KEY,
id_livre INT NOT NULL,
id_adherent INT NOT NULL,
date_emprunt TEXT NOT NULL,
date_retour TEXT,
FOREIGN KEY (id_livre) REFERENCES Livres(id_livre),
FOREIGN KEY (id_adherent) REFERENCES Adherents(id_adherent)
);
Trois particularités de SQLite, le SGBD utilisé en TP :
PRAGMA foreign_keys = ON;, à exécuter au début de chaque connexion ;INTEGER PRIMARY KEY ou NOT NULL : c’est pourquoi les exemples écrivent INTEGER, et non INT, devant PRIMARY KEY ;'vingt-cinq' est rangée telle quelle dans un attribut INT, sauf si la table est déclarée STRICT.DROP TABLE nom_table;
Cette opération est irréversible : la table et toutes ses données sont supprimées.
Python intègre le module sqlite3 qui permet de manipuler une base de données SQLite sans installer de logiciel supplémentaire.
import sqlite3
conn = sqlite3.connect("ma_base.db")
cursor = conn.cursor()
Si le fichier ma_base.db n’existe pas, il est créé automatiquement.
# Créer une table
cursor.execute("""
CREATE TABLE IF NOT EXISTS Eleves (
id_eleve INTEGER PRIMARY KEY,
nom TEXT NOT NULL,
prenom TEXT NOT NULL,
classe TEXT
)
""")
# Insérer des données
cursor.execute("""
INSERT INTO Eleves (id_eleve, nom, prenom, classe)
VALUES (1, 'Dupont', 'Marie', 'Terminale A')
""")
# Valider les modifications
conn.commit()
L’appel à conn.commit() est indispensable pour enregistrer les modifications dans le fichier. Sans cet appel, les données sont perdues à la fermeture.
cursor.execute("SELECT nom, prenom FROM Eleves WHERE classe = 'Terminale A'")
resultats = cursor.fetchall()
for ligne in resultats:
print(ligne)
# ('Dupont', 'Marie')
cursor.fetchone() renvoie un seul tuple (la ligne suivante) ;cursor.fetchall() renvoie une liste de tous les tuples.conn.close()
Cela libère les ressources. Attention : close() n’enregistre pas les modifications ; il faut appeler conn.commit() avant de fermer la connexion.
INSERT et UPDATE ?
INSERT ajoute une nouvelle ligne dans la table. UPDATE modifie les valeurs d'une ou plusieurs lignes existantes. INSERT crée un nouveau n-uplet, UPDATE change un n-uplet déjà présent.DELETE FROM Produits; sans clause WHERE ?
WHERE avant d'exécuter un DELETE.conn.commit() en Python après un INSERT ?
conn.commit(), les modifications restent en mémoire mais ne sont pas écrites dans le fichier de la base de données. Si on ferme la connexion sans commit, les données insérées sont perdues.INSERT INTO ... VALUES ajoute de nouvelles lignes dans une table.UPDATE ... SET ... WHERE modifie des valeurs existantes (attention sans WHERE : toutes les lignes sont modifiées).DELETE FROM ... WHERE supprime des lignes (attention sans WHERE : toutes les lignes sont supprimées).CREATE TABLE définit la structure d’une table avec ses types et contraintes ; DROP TABLE la supprime définitivement.sqlite3 permet de se connecter à une base, d’exécuter des requêtes avec cursor.execute(), de récupérer les résultats avec fetchall(), et d’enregistrer les modifications avec conn.commit().