Accueil

Tutoriel Python - sommaire

Vous pouvez me contacter via Facebook (questions, critiques constructives) : page Facebook relative à mon site

Les bases de données SQLite

Les Bases De Données (BDD) s’imposent comme une forme efficace de stockage. 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).

Les différents SGBD

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.

Structure d'une base de données

Généralités

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 et les lignes sont aussi appelées enregistrements.
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.

Notre thématique

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 qu'il s'agit d'un mollusque.

La base de données se nommera "animaux.db". Elle comprendra donc deux tables :

Structure de la table "especes"

Ce que doit contenir la table "especes" (extrait) :

1	homard	deca
2	orange de mer	epon
3	fesses d'éléphant	epon
4	crevette grise	deca
5	bouquet	deca
6	étrille	deca

Remarque : une ligne (ou enregistrement) par espèce.

Structure de la table "taxons"

Ce que doit contenir la table taxons (extrait) :

hydr	hydraires
medu	méduses
acti	actinies
cten	cténaires
plat	vers plats
anne	annélidés
biva	bivalves

Une ligne (ou enregistrement) par taxon.

Remarque sur les deux tables

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 enregistreement de " espèces "on pourra associer une ligne (et une seule) de "taxons". Donc pour chaque espèce on pourra afficher le taxon d'appartenance de façon claire (et non pas sous forme d'un code).

Le logiciel Dbbrowser for SQLite

Présentation

Le langage SQL peut paraitre difficile pour un débutant. Aussi nous allons créer et remplirles tables de notre base SQLite avec un logiciel 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.
Mais disons qu'avec DB browser vous allez apprendre en douceur le SQL et par l'exemple.

Installation de l'outil

Il suffit de se rendre dans la page de téléchargement du site sqlitebrower.org : https://sqlitebrowser.org/dl/

Utilisation de DB browser for SQLite

Création de la base

Vous allez utiliser ce logiciel pour créer la base de données "animaux.db" et les différentes tables.

Création des 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).

Le code SQL de la requête de création de la table "especes"

CREATE TABLE "especes" (
	"id"	INTEGER NOT NULL,
	"nom_espece"	TEXT NOT NULL,
	"code"	TEXT NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);

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 table, 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

Terminologie relative aux bases de données

Il faut maintenant définir certains termes incontournables en bases de données.

Clé primaire

Dans une table, une clé primaire est la colonne qui permet d'identifier de manière unique un enregistrement 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 !

Autoincrement

La commande AUTO_INCREMENT est utilisée dans le langage SQL afin de spécifier qu'une colonne numérique sera incrémentée 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.

Création de la table "taxons"

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 de créé sur la colonne.

Pour cette deuxième table il n'y a que deux colonnes à définir.
Les deux colonnes sont de type texte. La première est clé primaire (pour identifier de manière unique un taxon). Mais comme le type est TEXTE, l'autoincrémentation est impossible.

Observons le code SQL généré :

CREATE TABLE "taxons" (
	"code"	TEXT NOT NULL,
	"nom_taxon"	TEXT NOT NULL,
	PRIMARY KEY("code")
);

Remplir les tables de données

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.

Saisie dans la table "taxons"

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".

Saisie dansla table "especes"

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 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.

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 comme 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 petit défaut : c'est pas possible de rajouter une clé étrangère à une table déjà existante.

La solution

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 "especes 2".

Création de la table "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 "code" dans "especes2") est dépendante de "taxons.code".

Saisie dans la table "especes2'

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êtes DELETE

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" :

1 huitre plate		biva
2	huitre creuse	biva
3	moule commune	biva
4	coque			biva
5	palourde		biva

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 especes 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".

Ajout de tout le contenu de "especes" à "especes2"

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".

Requête drop

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

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.

Affichage d'une table avec toutes les colonnes

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".

Afficher seulement de certaines colonnes

Exemple :

	SELECT nom_espece, code FROM especes2;

Affichage de deux colonnes sur 3, colonne "id" masquée.

Filtrages

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"; 
Cette fois on affiche toutes les colonnes mais pas toutes les lignes ; il y a filtrage avec une clause WHERE condition

Utiliser l'opérateur LIKE :

	SELECT * FROM especes2 WHERE nom_espece LIKE "h%";

Affichez 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.

Afficher un intervalle de lignes

	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

Trier les lignes

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é !

Filtrage et tri

	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.

Requêtes sélection sur plusieurs tables

Conditions préalables

Entre "especes2" et "taxons" nous avons un champ commun : "code" qui est clé étrangère dans "especes2" et clé primaire dans "taxons".
Nous pouvons donc associer à chaque ligne de "especes2" une ligne (et une seule") de "taxons".

Associer à chaque ligne de especes2 un enregistrement de taxons

Le code SQL :

	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.

Essayons une variante :

	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é.

Ordre des mots clés dans une requête SQL

Il faut respecter un ordre sinon erreur de syntaxe :

SELECT
FROM
WHERE
ORDER BY 

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").

Sauvegarder les requêtes sélection sous forme de vues

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 :

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.

Davantage sur les requêtes sélection

Il y a beaucoup de choses à dire sur les requêtes SELECT.

Autre façon d'écrire une requête sélection multitables

Nous pouvons écrire avec une autre syntaxe la requête sauvegardée dans "vue1"

	SELECT nom_espece,  taxons.code, nom_taxon FROM especes2 
		INNER JOIN taxons ON especes2.code = taxons.code; 

Notez l'expression clé "INNER JOIN".
Requête sauvegardée sous en vue sous le nom "vue2".

Une requête très complexe

	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;

La "totale" : requête sélection multitables avec aussi un filtrage et un ordonnancement.

Le rendu :

bouquet					deca	décapodes
bulot					gast	gastéropodes
congre					osse	osseux
coque					biva	bivalves
crevette grise			deca	décapodes
fesses d'éléphant		epon	éponges
fletan					osse	osseux
hareng					osse	osseux
homard					deca	décapodes
huitre creuse			biva	bivalves

Affiche les espèces dont le nom commence par les lettres "b" et "m" par odre alphabétique.
Requête sauvegardée en tant que "vue3".

Requêtes statistiques

Compter le nombre d'enregistrements d'une table / vue

Exemples :

	SELECT COUNT(*) FROM especes2
	SELECT COUNT(*) FROM vue4
	SELECT COUNT(*) FROM taxons

Les deux premières requêtes retourne 24 et la troisième retoure 26.

Compter le nombre de ligne par groupe d'enregistrements

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 :

 
biva	4
cart	2
deca	6
epon	2
gast	2
osse	8

Nous pouvons dire qu'il y a 4 biva(lves), 2 cart(ilagineux), etc.

Les requêtes select max et select min

	select max(id) from especes2
	select min(id) from especes2

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 Python.