Tutoriel Python - sommaire

SGBD SQLite et DB browser

Les Bases De Données 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).

Les différents SGBD

Il existe de nombreux SGBD mais les plus connus sont :
• MySQL
• PostgresSQL
• Oracle
• MS SQL Server
• SQLite

Nous utiliserons SQLite car il est beaucoup plus simple à mettre en œuvre que les autres : pas nécessaire d'installer un serveur de bases de données sur votre PC (à la différence de mySQL).
Comme mySQL, SQLite est un SGBD de type relationnel ce qui signifie que les tables d'une même base peuvent être reliées entre-elles (jointures) et qu'au sein d'une table toutes les lignes ont la même strucure.

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 ("fields") et les lignes sont aussi appelées enregistrements ("records").
A l'intersection d'une colonne et d'une ligne se trouve une cellule contenant une donnée.

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

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

• table "especes" avec les colonnes : id, nom_espece, code
• table "taxons" avec les colonnes : code, nom_taxon

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 deca décapodes epon éponges osse poissons osseux ...

Une ligne (ou enregistrement) par taxon.

Il faut donc définir pour cette table deux colonnes :
• la colonne "code" : contient du texte, cellule vide non autorisée ; doublons non autorisés pour cette colonne qui servira d'identifiant de l'enregistement. Cette colonne sera clé primaire
• la colonne "nom_taxon" : contient du texte, cellule vide non autorisée

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 ou "record") par espèce.

Il faut donc définir pour cette table trois colonnes (ou champs) :
• la colonne "id" contient un entier, aucun doublon n'est autorisé puisque ce nombre sert d'identifiant de l'enregistement ; la cellule ne peut pas être vide. La colonne ID sera clé primaire.
• la colonne "nom_espece" contient du texte, la cellule ne peut pas être vide
• la colonne "code" contient du texte, la cellule ne peut pas être vide

Dans toute table une colonne (et une seule) doit faire fonction de clé primaire.
Il ne peut y avoir de doublons pour cette colonne. Il y aura automatiquement création d'un index sur cette colonne. Donc la recherche d'une ligne sera extrèment rapide : recherche indéxée.

Liaison entre 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 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".
On dit que "taxons" est mère par rapport à "espèces" OU que "espèces" est fille par rapport à "taxons".

L'utilitaire Dbbrowser for SQLite

Présentation

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. Ce programme est est l'équivalent de PHPMyAdmin pour MySQL.

Pourquoi ce nom ?
• • DB : comme "Data Base" (base de données)
• • Browser : comme navigateur.
• • for SQLite : pour une base SQLlite

En fait cette interface génère du SQL à partir de tous les renseignements que vous lui fournissez dans le cadre de grilles.
Disons qu'avec DB browser vous allez découvrir en douceur le SQL.

Installation de l'outil

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

Création d'une base

Nous allons employer cette interface pour créer la base de données "animaux.db" et les différentes tables.

La procédure :
• lancez DB browser for SQLite en double cliquant sur le raccourci créé sur le bureau
• cliquez sur l'onglet "Nouvelle base de données" 
• choisissez le répertoire qui contient déjà vos programmes Python (c:\python_prog.
• nommez la nouvelle base "animaux.db" ; la base est créée !

Suite

Il faut maintenant créer chacune des deux tables (définir leur structure) puis leur ajouter des données (insérer des enregistrements).
Il faut donc générer pour chaque table deux requêtes SQL : une de type CREATE TABLE ... et plusieurs de type INSERT INTO ...
Nous allons toujours faire appel à DB browser pour créer et remplir les deux tables.

Création et remplissage de la table "taxons"

Cliquez sur "Créer une table".
Vous remplissez la fenêtre qui s'affiche comme suit :

Je pense que vous avez compris les abréviations qui légendent les cases à cocher :
• NN : not null (aucune cellule vide autorisée dans la colonne)
• CP : clé primaire
• IA : autoincrement (numérotage automatique dans une colonne de type integer)
• U : unique (doublons interdits)

Remarque : différence et U et CP : dans les deux cas les doublons pour cette colonne sont impossible. Mais avec CP il y a création d'un index. Ce qui n'est pas le cas avec U.

Pour la table "taxons" 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 bien sûr impossible.

Observons attentivement le code SQL généré : CREATE TABLE "taxons" ( "code" TEXT NOT NULL, "nom_taxon" TEXT NOT NULL, PRIMARY KEY("code") );

Saisie dans la table "taxons"

La table "taxons" existe désormais (a une structure) mais est vide de toute données. Il faut désormais y ajouter des lignes (ou enregistrements).

Sélectionnez la table "taxons" à partir de la fenêtre "parcourir les donnees" puis cliquez sur l'icône "insérer un nouvel enregistrement dans la table" : vous pointez alors la fin de table. il suffit de remplir la ligne vierge du tableau.
Recommencez la procédure autant de fois que nécessaire.

Tri de la table "taxons"

Après avoir saisi plusieurs lignes dans la table "taxons" vous pouvez trier par valeurs croissantes/décroissantes les noms des taxons. Il suffit à partir du contenu sous forme d'un tableau (fenêtre "parcourir les données) de cliquer sur l'étiquette de colonne.

Création et remplissage de la table "especes"

Créer la table "especes"

Attention le code SQL de création de la table "especes" est plus complexe que pour la table "taxons".
Il y a effet une contrainte d'intégrité référentielle à poser. On ne doit pouvoir saisir dans la colonne "especes.code" qu'une valeur qui existe déjà dans la colonne "taxons.code". Il faut définir la colonne "especes.code" comme clé étrangère par rapport à "taxons.code".

Le code SQL qu'il faut générer est donc :

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

Analyse de cette syntaxe

"id" INTEGER NOT NULL : le champ nommé "id" doit contenir un entier, une cellule de cette colonne ne peut rester vide.
"nom_espece" TEXT NOT NULL : le champ "nom_espece" doit contenir obligatoirement du texte, pas de cellule vide
"code" TEXT NOT NULL : même remarque que pour le champ précédent
PRIMARY KEY("id" AUTOINCREMENT) : la colonne "id" sert de clé primaire (doublons interdits, indexation sur cette colonne. 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. C'est le SGBD qui affecte un entier à la cellule en fonction de l'enregistrement précédent : incrémentation.
FOREIGN KEY("code") REFERENCES "taxons"("code") : la colonne "code" de la table 'especes" est clé étrangère par rapport à la colonne "taxons.code". Ce qui veut dire qu'on ne peut saisir dans la première qu'une valeur qui existe déjà dans la seconde.

Insertion d'enregistrements dans la table "especes"

À partir de la fenêtre "parcourir les données" sélectionnez "especes" dans la liste déroulante "table" puis cliquez sur l'icône "insérer un nouvel enregistement".
Notez que c'est désormais une grille de saisie qui apparait (et non pas un tableau). Cette grille est légendée : "ajouter un enregistrement". Le mode de saisie d'une nouvelle ligne dans une table dépend de l'absence ou de la présence d'une contrainte d'intégrité.

En haut de la grille et pour chaque champ, il faut saisir la valeur (sauf pour le champ ID qui se remplie automatiquement).
Pour "nom_espece" saisissez "cristatelle" et pour "code" saisissez "bryo" (comme bryozoaires) : un message d'erreur apparait : "Foreign key constraint failed ..." . La requête échoue ! En effet vous n'avez pas pas respecté l'intégrité référentielle ; la valeur "bryo" n'existe pas encore dans "taxons.code". Dans un langage imagé on dirait : "pas d'enfant sans mère".

Saisissez alors "mérou" & "osse".
Puis observez le code SQL que vous avez généré (bas de la grille):

 INSERT INTO "main"."especes"
("nom_espece", "code")
VALUES ('mérou', 'osse');

Cette fois l'insertion est autorisée puisque la valeur "osse" (poissons osseux) existe dans la colonne "taxons.code".

Dons si vous voulez ajouter des espèces appartenant au taxon des "bryozoaires" il faudra d'abord créer ce groupe dans la table "taxons" puis saisir les espèces de ce groupe dans la table 'especes".

Tri de la table "especes"

Après avoir saisi plusieurs lignes dans la table "especes" vous pouvez trier par valeurs croissantes/décroissantes les codes et les noms d'espèces. Il suffit à partir du contenu sous forme d'un tableau (fenêtre "parcourir les données) de cliquer sur l'étiquette de colonne ad hoc.

Les requêtes sélection

Les requêtes sélection ne modifient pas les données, à la différence des requêtes action telles INSERT INTO ... Elles se contentent d'afficher des données extraites d'une ou plusieurs tables avec éventuellement tri et filtage.

Affichage d'une table (toutes les colonnes)

De la table "especes" : SELECT * FROM especes

De la table "taxons" : SELECT * FROM taxons
* veut dire "afficher toutes les colonnes" de la table
Attention il faut taper les requêtes donc utiliser la fenêtre "Exécutez le SQL".

Afficher seulement de certaines colonnes

Colonne "nom_espece" & "code" : SELECT nom_espece, code FROM especes
Donc la colonne ID n'est pas affichée.

Filtrages

On affiche seulement les lignes correspondant à la condition de filtrage.

Afficher seulement les décapodes (code ="deca") :
SELECT nom_espece, code FROM especes WHERE code ="deca"

Afficher seulement les poissons osseux (code ="osse") :
SELECT * FROM especes WHERE code ="osse"

Utiliser l'opérateur LIKE :

SELECT * FROM especes WHERE nom_espece LIKE "h%"
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.

Afficher un intervalle de lignes

SELECT * FROM especes 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 especes ORDER by code
Dans la liste retournée, les lignes sont triées par valeurs croissantes pour la colonne code.

Filtrage et tri

SELECT * FROM especes WHERE code ="osse" ORDER by nom_espece
Dans la liste retournée, seuls les poissons osseux figurent et ils sont ordonnées par nom.
Attention la clause WHERE doit précéder la clause ORDER.
L'ordre physique des enregistrements dans la table n'est pas modifié par les tris et filtrages des requêtes SELECT

Requêtes sélection sur plusieurs tables

Nous voulons associer à chaque ligne de "especes" un enregistement (et un seul) de "taxons".
Entre "especes" et "taxons" nous avons un champ commun qui porte le même nom dans les deux tables : "code". Nous pouvons donc poser une "jointure" entre ces deux tables.

Requete sur especes & taxons

SELECT * FROM especes, taxons WHERE especes.code = taxons.code
La requête retourne autant de lignes que d'enregistements dans la table "especes".
Pour lever toute ambiguïté il faut employer les noms complets des champs "code" (nomtable.nomColonne) : "especes.code" d'une part et "taxons.code" d'autre part.

Le rendu (extrait) :

 
1	homard	deca	deca	décapodes
4	crevette grise	deca	deca	décapodes
5	bouquet	deca	deca	décapodes
6	étrille	deca	deca	décapodes
7	tourteau	deca	deca	décapodes
8	araignée de mer	deca	deca	décapodes
9	bulot	gast	gast	gastéropodes
...

Pour chaque ligne on a deux fois la même info ...

Solution au bug

SELECT nom_espece, taxons.code, nom_taxon FROM especes, taxons WHERE especes.code = taxons.code
Cette fois on affiche que les champs précisés après le mot SELECT : le code n'est affiché qu'une fois !

Le rendu (extrait) :

homard	deca	décapodes
crevette grise	deca	décapodes
bouquet	deca	décapodes
étrille	deca	décapodes
tourteau	deca	décapodes
araignée de mer	deca	décapodes
bulot	gast	gastéropodes
...

Ne plus utiliser la clause WHERE pour la jointure

Nous avons utilisé le mot clé WHERE pour poser une jointure entre deux tables et si nous voulions aussi effectuer un filtrage en plus on serait bien ennuyé ...
Il est donc préférable d'utiliser l'expression INNER JOIN pour poser une jointure entre deux tables.

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

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

Nous allons donc la sauvegarder la requête sélection précédente 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 elle est complexe à recréer.

Une requête sélection multitables très complexe

SELECT nom_espece, taxons.code, nom_taxon FROM especes INNER JOIN taxons ON especes.code = taxons.code WHERE nom_espece BETWEEN "b%" AND "m%" ORDER BY nom_espece

Une requête multitables avec en plus filtrage et tri.
Le rendu (totalité) :

balane	cirr	cirripèdes
bouquet	deca	décapodes
bulot	gast	gastéropodes
calmar	ceph	céphalopodes
cigale de mer	deca	décapodes
cione	asci	ascidies
comatue	crin	crinoïdes
coque	biva	bivalves
crabe chinois	deca	décapodes
crevette grise	deca	décapodes
crépidule	gast	gastéropodes
dugong	sire	siréniens
fraise de mer	asci	ascidies
homard	deca	décapodes
huitre creuse	biva	bivalves
lamantin	sire	siréniens
langouste	deca	décapodes
langoustine	deca	décapodes
lion de mer	pinn	pinnipèdes

Affichage des espèces dont le nom commence par les lettres "b" à "m" classées par odre alphabétique.
Il est préférable, vu sa complexité, de la sauvegarder sous forme d'une vue : "vue2".

Requêtes statistiques

Parmi les requêtes SELECT il y a les requêtes statistiques.

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

Exemples : SELECT COUNT(*) FROM especes SELECT COUNT(*) FROM vue1 SELECT COUNT(*) FROM taxons

Ces requêtes ne retournent qu'une ligne affichant un entier (le nombre de lignes de la source).

Compter le nombre de ligne par groupe

Dans la table "espèces" et pour chaque valeur de code il y a plusieurs lignes (plusieurs espèces par taxon).

SELECT code, COUNT(*) FROM especes GROUP BY code

Le rendu (extrait) :

 
...
biva	6
cart	2
ceph	3
cirr	2
crin	1
deca	11
...

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.

Les requêtes select max et select min

Ces deux requêtes retournent respectivement la valeur maxi et mini dans la colonne ID.
Remarque : suite à des suppressions de lignes, il peut y avoir des trous dans la numérotation : un enregistrement ne changera jamais d'ID même si des lignes qui le précédaient ont été supprimées.
a titre d'exemple la valeur maxi de ID dans la table "especes" est de 56 alors que le nombre de lignes est 40. En effet de nombreuses lignes ont été supprimées dans cette table : les espèces de poissons osseux et les éponges (avant de rajouter le mérou).

Les suppressions

Ne confondez pas les suppressions de tables (commande DROP TABLE nomTable en SQL) avec les suppressions de lignes dans une table (DELETE FROM nomTable WHERE condition en SQL).

Suppression de certaines lignes dans une table

Mais attention dans le cadre de l'intégrité référentielle on ne peut pas supprimer "une mère tant qu'elle a des filles".

Essayez de supprimer dans la table "taxons" la ligne "epon - éponges" sachant que plusieurs lignes de "especes" ont la valeur "epon" dans leur champ "code" :
• via la fenêtre "parcourir les données" ; sélection de la ligne puis clic droit : un message d'erreur apparait !
• via la fenêtre "exécuter du SQL" en tapant delete from taxons where code="epon" : c'est également échec.

Par contre rien n'interdit de supprimer des lignes dans la table "fille".
Essayez la commande : delete from especes where code ='epon'.
C'est un succès

Suppression d'une table

Testez la commande DROP TABLE taxons
À cause (ou plutôt grâce à la contrainte d'intégrité référentielle) entre les deux tables, la requête a échoué !
On ne peut supprimer la table "mères" tant que la table "filles" existe.

Ce chapitre est terminé. Dans le chapitre suivant nous verrons comment manipuler une base sqlite dans le cadre de l'interpréteur ou d'un programme.