Bases de données et SQL

Des tables en Python aux bases de données

Enregistrements, attributs, domaine

Une table (ou relation) organise des données structurées en lignes et colonnes. Chaque ligne correspond à un enregistrement ; chaque colonne correspond à un attribut (ou champ), associé à un domaine : l'ensemble des valeurs qu'il peut prendre (entiers, texte, dates...).

Exemple. Une table Editeurs recensant des éditeurs de jeux vidéo :

En Python, une table se représente naturellement comme une liste de dictionnaires, chaque dictionnaire correspondant à une ligne, et ses clés aux noms des colonnes (voir aussi la page « Notion de liste ») :

Editeurs = [
    {"id_editeur": 1, "nom": "Nintendo", "pays": "Japon"},
    {"id_editeur": 2, "nom": "Ubisoft", "pays": "France"},
]

Clé primaire

Une clé primaire est un attribut (ou groupe d'attributs) qui identifie de façon unique chaque enregistrement d'une table : deux lignes distinctes ont toujours des valeurs différentes de clé primaire, et celle-ci ne doit jamais être vide. Ici, id_editeur est la clé primaire de Editeurs. Lorsqu'aucun attribut « naturel » ne convient (deux éditeurs pourraient partager un nom proche), on recourt à une clé primaire artificielle : le plus souvent, un identifiant entier auto-incrémenté.

Clé étrangère, schéma relationnel et intégrité référentielle

Le schéma relationnel d'une base de données décrit l'ensemble de ses tables et les relations qui les unissent. Pour relier deux tables, on utilise une clé étrangère : un attribut d'une table qui référence la clé primaire d'une autre table.

Exemple. Une table Jeux, où chaque jeu vidéo est publié par un éditeur de la table Editeurs :

Ici, id_editeur (dans Jeux) est une clé étrangère : elle référence id_editeur, clé primaire de Editeurs. Plutôt que de recopier le nom et le pays de l'éditeur sur chaque ligne de Jeux, on se contente d'y stocker une référence vers Editeurs — ce qui distingue la structure de la base (son schéma) de son contenu (les données qu'elle stocke à un instant donné). Le respect de cette correspondance, où toute valeur de clé étrangère doit exister comme clé primaire dans l'autre table, s'appelle l'intégrité référentielle.

Répartir les données en plusieurs tables reliées, plutôt que de tout regrouper dans une seule, permet d'éviter des anomalies : redondance d'information (le pays d'un éditeur recopié à chaque jeu), risque d'incohérence lors d'une mise à jour (le corriger sur une ligne et l'oublier ailleurs), ou impossibilité d'enregistrer une donnée isolée (un éditeur dont aucun jeu n'est encore paru).

Le système de gestion de bases de données (SGBD)

Un SGBD (Système de Gestion de Bases de Données) est le logiciel qui gère une base de données relationnelle — PostgreSQL, MySQL et SQLite en sont des exemples courants. Il rend en particulier les services suivants :

• la persistance des données : elles restent stockées durablement, même après l'arrêt du programme ou de la machine ;
• la gestion des accès concurrents : plusieurs utilisateurs ou programmes peuvent lire et modifier la base simultanément sans la corrompre ;
• l'efficacité du traitement des requêtes, y compris sur de très grands volumes de données ;
• la sécurisation des accès, en contrôlant qui a le droit de lire ou de modifier quelles données.

Le langage SQL

Créer une table et insérer des données

Le langage SQL (Structured Query Language) permet de créer, interroger et modifier une base de données relationnelle. CREATE TABLE définit une table en précisant le nom et le type de chaque colonne, ainsi que sa clé primaire ; INSERT INTO y ajoute des enregistrements.

CREATE TABLE Editeurs (
    id_editeur INTEGER PRIMARY KEY,
    nom TEXT,
    pays TEXT
);
 
CREATE TABLE Jeux (
    id_jeu INTEGER PRIMARY KEY,
    titre TEXT,
    annee INTEGER,
    id_editeur INTEGER
);
 
INSERT INTO Editeurs (id_editeur, nom, pays)
VALUES (1, 'Nintendo', 'Japon'), (2, 'Ubisoft', 'France');
 
INSERT INTO Jeux (id_jeu, titre, annee, id_editeur)
VALUES (1, 'Zelda', 2017, 1), (2, 'Assassin''s Creed', 2007, 2), (3, 'Mario Kart', 2017, 1);

Interroger une table : SELECT, FROM, WHERE, ORDER BY

SELECT choisit les colonnes à afficher ; FROM précise la table interrogée ; WHERE filtre les lignes selon une condition. ORDER BY trie ensuite le résultat.

-- titre et annee de tous les jeux, du plus recent au plus ancien
SELECT titre, annee FROM Jeux
ORDER BY annee DESC;
 
-- jeux parus apres 2010, publies par l'editeur d'identifiant 1
SELECT titre FROM Jeux
WHERE annee > 2010 AND id_editeur = 1;

Croiser plusieurs tables : JOIN

Une jointure (JOIN ... ON ...) combine les lignes de deux tables lorsqu'une condition est vérifiée — le plus souvent, l'égalité entre une clé étrangère et la clé primaire qu'elle référence.

SELECT Jeux.titre, Editeurs.nom
FROM Jeux
JOIN Editeurs ON Jeux.id_editeur = Editeurs.id_editeur;

Chaque ligne du résultat associe ainsi un jeu au nom de son éditeur, sans qu'il ait jamais été nécessaire de stocker ce nom directement dans la table Jeux.

Accélérer les requêtes : l'index

Sur une table de plusieurs millions de lignes, retrouver un enregistrement en parcourant toutes les lignes une par une devient très coûteux. Un SGBD peut alors construire un index sur un attribut : une structure de données annexe qui permet de retrouver directement les lignes correspondant à une valeur donnée, sans avoir à toutes les parcourir — un peu comme l'index alphabétique à la fin d'un livre évite de le feuilleter en entier pour retrouver une notion.

CREATE INDEX idx_jeux_annee ON Jeux (annee);

Une clé primaire est presque toujours indexée automatiquement par le SGBD, précisément pour que les jointures qui s'appuient sur elle restent rapides même sur de grandes tables.

Attention aux deux sens du mot « index ». En Python (voir la page « Notion de liste »), un index désigne la position d'un élément dans une liste (Table[0], Table[2]...). En base de données, un index désigne au contraire une structure technique interne au SGBD, invisible dans le texte des requêtes SQL, dont le seul rôle est d'accélérer la recherche. Les deux notions partagent un nom, mais pas le même rôle.

Mettre à jour une base : UPDATE, DELETE

UPDATE ... SET ... WHERE ... modifie des enregistrements déjà présents ; DELETE FROM ... WHERE ... en supprime.

-- corriger l'annee de sortie d'un jeu
UPDATE Jeux SET annee = 2018 WHERE id_jeu = 3;
 
-- supprimer les jeux parus avant 2010
DELETE FROM Jeux WHERE annee < 2010;

Sans clause WHERE, UPDATE et DELETE s'appliquent à toutes les lignes de la table : mieux vaut toujours relire sa condition — voire la tester d'abord avec un SELECT — avant de les exécuter.

Retrouver ces opérations sur une table Python

Les opérations SQL ci-dessus ont un équivalent direct lorsqu'une table est représentée, comme en classe de première, par une liste de dictionnaires.

Sélectionner des lignes

La clause WHERE correspond à un filtre construit par compréhension :

def select(table, critere):
    return [ligne for ligne in table if critere(ligne)]
 
# equivalent de : SELECT * FROM Jeux WHERE annee > 2010
select(Jeux, lambda ligne: ligne["annee"] > 2010)

Trier une table

ORDER BY correspond à un tri avec la fonction native sorted, en précisant un attribut comme clé de tri :

def tri(table, attribut, decroissant=False):
    return sorted(table, key=lambda ligne: ligne[attribut], reverse=decroissant)
 
# equivalent de : SELECT * FROM Jeux ORDER BY annee DESC
tri(Jeux, "annee", decroissant=True)

Joindre deux tables

JOIN correspond à une fusion de deux listes de dictionnaires suivant un attribut commun :

def jointure(table1, table2, cle1, cle2):
    resultat = []
    for ligne1 in table1:
        for ligne2 in table2:
            if ligne1[cle1] == ligne2[cle2]:
                nouvelle_ligne = dict(ligne1)
                for cle in ligne2:
                    if cle != cle2:
                        nouvelle_ligne[cle] = ligne2[cle]
                resultat.append(nouvelle_ligne)
    return resultat
 
# equivalent de : SELECT * FROM Jeux JOIN Editeurs ON Jeux.id_editeur = Editeurs.id_editeur
jointure(Jeux, Editeurs, "id_editeur", "id_editeur")

Cette correspondance se résume ainsi :

À noter. Cette correspondance explique pourquoi le vocabulaire des tables (ligne, colonne) se retrouve, sous des noms légèrement différents, dans celui des bases de données relationnelles (enregistrement, attribut) : ce sont deux façons de manipuler la même notion de donnée structurée — l'une en Python, directement dans un programme ; l'autre confiée à un SGBD, à travers le langage SQL.