Traduction
Tutoriel Python - sommaire
Tutoriel Python - recherche
L'auteur : Patrick Darcheville
Vous pouvez me contacter via Facebook pour questions & suggestions :
Page Facebook relative à mon site
Vous pouvez me contacter via Facebook pour questions & suggestions :
Page Facebook relative à mon site
Les Bases De Données (BDD) s’imposent comme une façon efficace de stockage de données. Il est alors plutôt aisé d’interagir avec celles-ci en utilisant un Système de Gestion de Base de Données (SGBD). Un SGBD est un logiciel spécialement conçu pour manipuler les bases de données à l’aide d’un langage normalisé : le SQL (Structured Query Language).
Nous utiliserons SQLite car il est beaucoup plus simple à mettre en œuvre que les autres : pas nécessaire d'installer un serveur sur votre PC.
Une base de données a un nom et comprend une ou plusieurs tables. Elle comprend aussi éventuellement des vues.
Un table c'est comme un tableau : des lignes et des colonnes. Les colonnes sont aussi appelée champs ("fields") et les lignes sont aussi appelées
enregistrements ("records").
A l'intersection d'une colonne et d'une ligne se trouve une cellule.
Chaque ligne décrit un individu (au sens statistique du terme) : un salarié, un client, un contact, un article du catalogue, un patient, etc.
Tous les individus sont donc décrits de la même façon.
Nous devons gérer les espèces animales d'un biotope marin.
Une espèce a un nom et appartient à un taxon (et un seul).
Je rappelle qu'un taxon est un groupe d'animaux "cousins" (qui ont un ancêtre commun). Les taxons peuvent avoir des niveaux hiérarchiques différents : embranchement, classe ou ordre. Ainsi "éponges & cténaires" sont des embranchements tandis que "bivalves, gastéropodes, céphalopodes" sont des classes de l'embranchement "mollusques". Mais quand on dit que telle espèce est un gastéropode on sous-entend bien sûr qu'il s'agit d'un mollusque.
La base de données se nommera "animaux.db". Elle comprendra donc deux tables :
Ce que doit contenir la table "especes" (extrait) :
Remarque : une ligne (ou enregistrement ou "record") par espèce.
Ce que doit contenir la table taxons (extrait) :
Une ligne (ou enregistrement) par taxon.
Il y a une colonne commune entre les deux tables ; elle porte d'ailleurs le même nom : "code" (mais ce n'est pas obligatoire).
Cette colonne commune aux deux tables est appelé "champ de jointure". En effet elle permet de mettre en relation les deux tables.
Ainsi à chaque enregistrement de "espèces" on pourra associer une ligne (et une seule) de "taxons".
A contrario pour chaque ligne de "taxons" on peut associer 0 à N lignes de "especes".
Le langage SQL peut paraitre difficile pour un débutant. Aussi nous allons créer et remplir les tables de notre base SQLite avec un utilitaire intitulé DB Brower for SQLliste.
En fait cette interface génère du SQL à partir de tous les renseignements que vous lui fournissez dans le cadre de fenêtres.
Disons qu'avec DB browser vous allez découvrir en douceur le SQL et par l'exemple.
Pour ceux qui connaissance MYSQL, DB Browser est l'équivalent de PHPMYAdmin.
Il suffit de se rendre dans la page de téléchargement du site sqlitebrower.org : https://sqlitebrowser.org/dl/
Vous allez utiliser cette interface pour créer la base de données "animaux.db" et les différentes tables.
Créons la table "especes" !
Commentons !
Vous devez donner un nom à la table : "especes"
Puis vous devez définir chacune des trois colonnes (champs) de la table.
En bas dans la fenêtre la requête SQL qui crée cette table est progressivement établie voir modifiée (suite au fait de cocher ou décocher d'une case).
Certains mots sont en majuscules, ce sont les mots réservés du langage SQL. Mais si ces mots sont écrits en minuscules, il n'y aura pas d'erreur de syntaxe. Écrire en majuscules ces mots, c'est simplement une convention ; mais que je conseille de respecter pour bien distinguer les mots clés des noms de tables, de champs.
Décortiquons cette phrase (car une requête SQL c'est une phrase avec des propositions) :
CREATE table "especes" : création d'une table nommée "especes"
puis pour chaque de colonne il y a une proposition à l'intérieur d'une paire de parenthèses. Entre chaque proposition une virgule
ici il y a trois colonnes mais quatre propositions car il y a aussi une proposition pour le champ faisant fonction de clé primaire
Il faut maintenant définir certains termes incontournables en bases de données.
Dans une table, une clé primaire est la colonne qui permet d'identifier de manière unique une enregistrement (ligne) de la table. C'est l'équivalent du numéro
de sécurité sociale pour un citoyen français. Donc si une colonne est définie comme "clé primaire" il ne peut y avoir de doubons ou de cellules vides.
Dans une table il ne peut y avoir qu'une clé primaire !
La commande AUTOINCREMENT est utilisée dans le langage SQL afin de spécifier qu'une colonne numérique sera remplie automatiquement à chaque ajout d'enregistrement dans celle-ci. C'est le SGBD qui affecte un entier à la cellule en fonction de l'enregistrement précédent (incrémentation).
Cliquez de nouveau sur "Créer une table".
Vous la remplissez la fenêtre qui s'affiche comme suit :
Dans une colonne "unique" les doublons sont interdits mais, à la différence d'une clé primaire, il n'y a pas d'index créé sur la colonne.
Pour cette deuxième table il n'y a que deux colonnes à définir.
Les deux colonnes sont de type "text". La première est clé primaire (pour identifier de manière unique un taxon).
Mais comme le type est TEXT, l'autoincrémentation est impossible.
Observons le code SQL généré :
Certes nous avons défini la structure de nos deux tables mais elles sont vides : pas d'enregistrements !
Il faut maintenant ajouter des lignes (ou enregistrements). Et la saisie sera rigoureusement contrôlée par la définition de la table.
Ainsi on ne peut modifier le contenu d'une cellule "id" , on ne peut vider une cellule "nom_espece" ou "code", etc.
Cliquez sur l'onglet "parcourir les données" puis dans la zone de liste déroulante et sélectionnez "taxons"
Puis cliquez sur l'icône légendé "insérer un nouvel enregistrement dans la table en cours".
La saisie se fait de façon tabulaire.
Ci-dessous capture d'écran de la fenêtre "insérer un nouvel enregistrement".
Nous allons maintenant remplir la table "especes".
Toujours dans l'onglet "parcourir les données" , sélectionnez "especes" dans la liste déroulante et effectuez les saisies suivantes.
Il y a un problème !!!
Dans une cellule de la colonne "code" j'arrive à saisir "gas".
Or dans la table "taxons" le code correspondant est "gast" (d'ailleurs tous les codes font quatre lettres).
Il faudrait que lorsque je saisis dans "especes.code" (colonne "code" de la table "especes")
il y ait vérification que cette valeur existe bien dans "taxons.code" (colonne "code" de la table "taxons").
C'est ce qu'on appelle dans le jargon des bases de données : un contrainte d'intégrité référentielle.
Dans la table "taxons" le champ "code" (commun aux deux tables) est clé primaire ; on dit que la table "taxons" est mère par rapport à "espèces".
On peut dire aussi que la table "espèces" est "fille" par rapport à taxons.
Pour poser l'intégrité référentielle il faut définir le champ "especes.code" comme clé étrangère par
rapport à la clé primaire "taxons.code". Toute valeur de clé étrangère peut être multiple mais doit impérativement exister en tant que clé primaire.
Chez tous les grands SGBD il est possible de rajouter une définition d'une clé étrangère à une table déjà créée. Il suffit alors de créer une
requête ALTER TABLE (qui modifie la définition initiale de la table.
SQLite a beaucoup de qualités mais là il présente un défaut : il n'est pas possible de rajouter une clé étrangère à une table déjà existante.
Heureusement notre table "especes" comprend encore très peu de lignes.
Donc nous allons "laisser tomber" la table "especes" et créer une nouvelle table nommée "especes2".
Pour définir une clé étrangère sur un champ il faut sélectionner dans la fenêtre "Créer une table" l'onglet "contraintes" (après avoir défini les trois champs) comme le montre la capture d'écran ci-dessus. Il faut générer la cinquième proposition suivante :
FOREIGN KEY("code") REFERENCES "taxons"("code")
Ce qui veut dire que la saisie dans la colonne "especes2.code" est dépendante de "taxons.code".
Vous pouvez sélectionner la nouvelle table dans la liste puis un clic droit et sélectionnez "Parcouir la table".
Regardez la capture d'écran ci-dessus.
D'abord notez que dès qu'il y a une relation de dépendance d'une table par rapport à une autre la grille de saisie change ;
elle ne se fait plus via un tableau mais dans une fenêtre intitulé "ajouter un enregistrement".
Dans la grille du haut et en face de chaque nom de champ il faut saisir la valeur, sauf pour le champ ID qui se remplie automatiquement.
Par étourderie j'ai tenté de saisir dans une cellule "especes2.code" la valeur "biv" (valeur qui n'existe pas la colonne dans "taxons.code").
Cette saisie est refusée ! Un message apparait, il est en anglais : "constraint failed" : non respect des contraintes.
En bas de la fenêtre le SQL, correspondant à l'ajout d'un ligne, apparait !
INSERT INTO "main"."especes2"
("nom_espece", "code")
VALUES ('palourde', 'biva');
Il s'agit d'une requête de type "insert" qui permet d'ajouter une ligne à une table.
"main" : veut dire table courante donc ici "especes2"
dans la première paire de () : les champs à remplir
dans la deuxième paire de () : les valeurs correspondantes
Notez qu'il y a dans cette requête aucune mention du champ "id" puisque ce dernier se remplit automatiquement par incrémentation.
Les requête DELETE permettent de supprimer des lignes dans une table. Nous avons créé la table "especes2" et avons saisi les espèces de bivalves ! Nous voulons ajouter à "especes2" tout le contenu de "especes" mais sans qu'il y ai en définite des doublons dans "especes2".
Contenu actuel de "espèces2" :
Or ces espèces existent déjà dans la table "espèces".
Avant d'ajouter tout le contenu de "especes" à "especes2" et pour éviter des doublon, il faut au préalable supprimer dans "espèces" les bivalves.
Cette fois on ne dispose pas de grille (pour générer du SQL), on doit tapez du SQL !
Attention l'interpréteur de SQL de DB browser n'est pas très tolérant : es mots clés peuvent être écrits en minuscules mais une requête
doit impérativement se terminer ";"
Cliquez sur l'onglet "Exécutez du SQL" et tapez dans cette fenêtre :
DELETE FROM speces WHERE code ="biva" ;
Cliquez sur l'icône légendée : "Exécutez la ligne courante".
Une message apparait en bas de la fenêtre avec le message :
"Résultat : Requête exécutée avec succès. Elle a pris 0 ms , 4 enregistrements affectés"
Cette requête a donc supprimé 5 lignes dans la table "especes".
Nous pouvons désormais transférer le contenu de "espèces" vers "especes2".
Le code SQL :
INSERT INTO especes2 (nom_espece, code) SELECT nom_espece, code FROM especes;
Un message indique que l'exécution de la requête est un succès.
L'ajout a pu se réaliser avec succès car les contenus de "especes.code" existaient déjà dans "taxons.code".
Nous n'avons plus besoin de la table "especes" , nous pouvons la supprimer :
DROP table especes ;
Attention avant de fermer la base n'oubliez pas d'enregistrer les modifications. Onglet : "Enregistrer les modifications"
Les requêtes sélection ne modifient pas le contenu des tables à la différence des requêtes delete, insert et drop.
Elles se contentent d'afficher les lignes d'une ou plusieurs tables avec éventuellement un filtrage de lignes et un ordonnancement.
De la table "especes2" :
SELECT * FROM especes2;
De la table "taxons" :
SELECT * FROM taxons;
* veut dire "afficher toutes les colonnes" de la table
Attention il faut taper les requêtes de type SELECT, donc utiliser la fenêtre "Exécutez le SQL".
Colone "nom_espece" & "code" :
SELECT nom_espece, code FROM especes2;
Remarque : colonne "id" masquée.
On affiche seulement les lignes correspondant à la condition de filtrage.
Afficher seulement les décapodes (code ="deca") :
SELECT nom_espece, code FROM especes2 WHERE code ="deca";
Afficher seulement les poissons osseux (code ="osse") :
SELECT * FROM especes2 WHERE code ="osse";
Utiliser l'opérateur LIKE :
SELECT * FROM especes2 WHERE nom_espece LIKE "h%";
Remarque : on affiche seulement les espèces dont le nom commence par la lettre "h".
LIKE s'associe à "%" qui remplace n'importe qu'elle chaîne de caractères et "_" qui remplacement un seul et unique caractère.
SELECT * FROM especes2 WHERE id BETWEEN '1' AND '10';
On affiche uniquement les enregistrements dont l'id est compris entre 1 et 10, donc le début de table
Exemple :
SELECT nom_espece, code FROM especes2 ORDER by code;
Dans la liste retournée, les lignes sont triées par valeurs croissantes pour la colonne code : les biva(lves ) puis les cart(cartilagineux )
puis les deca(podes), ...
L'ordre des enregistrements dans la table n'est pas modifié !
SELECT * FROM especes2 WHERE code ="osse" ORDER by nom_espece;
Dans la liste retournée seuls les poissons osseux figurent et ils sont ordonnées par nom.
La clause WHERE doit précéder la clause ORDER.
Entre "especes2" et "taxons" nous avons un champ commun : "code" qui est clé étrangère dans "especes2" et clé primaire dans "taxons".
Nous voulons associer à chaque ligne de "especes2" une ligne (et une seule") de "taxons".
SELECT * FROM especes2, taxons WHERE especes2.code = taxons.code;
Le tableau retourné comprend 24 lignes : le nombre d'enregistrements de la table "especes2".
Tous les champs des deux tables sont affichés donc les mêmes infos apparaissent dans deux colonnes.
SELECT nom_espece, taxons.code, nom_taxon FROM especes2, taxons WHERE especes2.code = taxons.code ORDER by nom_espece;
Cette fois on affiche que les champs précisés. Attention comme le champ "code" existe dans les deux tables, il faut utiliser le nom complet du champ : nomTable.nomChamp sinon il y a ambiguïté.
Il faut respecter un ordre sinon erreur de syntaxe :
Donc la syntaxe est : SELECT liste de champs FROM liste de tables/vues WHERE condition de filtrage/de jointure
ORDER BY argument de tri.
Notez que le mot clé WHERE peut tantôt être suivi d'une condion de filtrage ou d'une condition de jointure.
C'est pour cette raison qu'il faut mieux écrire les requêtes sélection multi-tables avec une autre syntaxe ; syntaxe que nous verrons plus tard.
La clause WHERE peut comprendre l'opérateur LIKE ("commence par") ou BETWEEN ... AND ("entre ... et").
La dernière requête sélection devient complexe avec une jointure entre deux tables et un tri. Nous allons donc la sauvegarder sous forme d'une vue.
A partir de la fenêtre "Exécuter le SQL" cliquez sur l'icône légendé "Enregistrer la vue des résultats". Nommons la vue "vue1".
Donc une vue c'est tout simplement une requête sélection sauvegardée (car complexe).
Ci-dessous extrait de "vue1" lorsqu'on l'ouvre en mode "Parcourir la table" :
État définitif de notre base de données :
Remarque : la table "sqlite_sequence" est une table créée d'office lorsque vous créez une nouvelle base ; elle ne peut être supprimée car elle permet de gérer les véritables tables.
Il y a beaucoup de choses à dire sur les requêtes SELECT.
SELECT nom_espece, taxons.code, nom_taxon FROM especes2 INNER JOIN taxons ON especes2.code = taxons.code;
Notez l'expression clé "INNER JOIN".
Cette requête sauvegardée sous en vue sous le nom "vue2".
SELECT nom_espece, taxons.code, nom_taxon FROM especes2 INNER JOIN taxons ON especes2.code = taxons.code WHERE nom_espece BETWEEN "b%" AND "m%" ORDER BY nom_espece;
Remarque : requête sélection multitables avec aussi un filtrage et tri.
Le rendu :
Affichage des espèces dont le nom commence par les lettres "b" et "m" par odre alphabétique.
Requête sauvegardée en tant que "vue3".
Parmi les requêtes SELECT il y a les requêtes statistiques.
Exemples :
Les deux premières requêtes retourne 24 et la troisième retoure 26.
Ces requêtes ne retournent qu'une ligne puisqu'il s'agit d'une statistique pour l'ensemble de la source.
Dans la table "espèces2" et pour chaque valeur de code il y a plusieurs lignes (plusieurs espèces par taxon).
SELECT code, COUNT(*) FROM especes2 GROUP BY code
Le rendu :
Nous pouvons dire qu'il y a 4 biva(lves), 2 cart(ilagineux), etc.
Cette requête statistique comprend autant de lignes que de groupes ; requête statistique par groupe.
La première requête retourne 24 et la deuxième retourne 1.
Donc dans la colonne "especes2.id", les valeurs sont de 1 à 24. Ce qui est logique puisqu'il s'agit d'un champ "autoincrement".
Ce chapitre est terminé. Dans le chapitre suivant nous verrons comment manipuler une base sqlite dans le cadre de l'interpréteur et d'un programme C'est à dire comment intégrer des requêtes SQL dans des commandes ou programmes Python.