SGBDR SQLite et logiciel DB browser

La galaxie Python propose un SGBDR (Système de Gestion de Base de Données Relationnelles) de type SQL : SQLite.

Richard Hipp, le créateur de SQLite, commence la conception de ce moteur de SGBD en 2000 suite à une commande de la défense US ; SQLite devant équiper des missiles guidés.

Avantages et inconvénients de SQLite

Une base de données SQLite, c'est un simple fichier ! Ce qui élimine le besoin d’un serveur dédié et simplifie considérablement la migration vers un autre poste. Si vous connaissez MySQL vous savez que le déploiement d'une base de ce type est beaucoup plus compliqué.

SQLite présente cependant un inconvénient : il ne supporte qu’une seule connexion écriture à la fois. Les écritures concurrentes sont bloquées, ce qui rend le moteur inadapté aux applications multi‑utilisateurs.

En résumé, SQLite est le bon choix pour de petites applications où la simplicité, la faible consommation de ressources et la portabilité sont des paramètres essentielles.

De par son extrême légèreté (moins de 600 KO), il est également très populaire sur les systèmes embarqués, notamment sur la plupart des smartphones et tablettes modernes.

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, une commande, etc.

Dans un SGBDR SQL tous les individus sont donc décrits de la même façon. Toutes les lignes d'une table ont obligatoirement la même structure.

La thématique

Nous devons gérer les espèces animales d'un milieu marin.
Une espèce a un nom et appartient à un groupe (ou taxon). Je rappelle qu'un taxon est un groupe d'animaux "cousins" (qui ont un ancêtre commun).

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

la table "especes" avec les colonnes : id, nom_espece, code
la 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.

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.

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 indexé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". À contrario pour chaque ligne de "taxons" on peut associer 0 à N lignes de "especes".
Il s'agit donc ici d'une jointure "1 à N".
On dit que "taxons" est table mère par rapport à "espèces" ET que "espèces" est table fille par rapport à "taxons".
En effet une mère peut avoir plusieurs filles mais une fille n'a qu'une mère.

Vous verrez plus loin dans ce chapitre qu'il est possible (mais pas obligatoire) de créer un contrôle de saisie efficace et appelé contrainte d'intégrité référentielle afin d'éviter la création d'orpheline.

L'utilitaire DB Browser (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 une interface utilisateur intitulée : DB Brower (SQLliste).
Cette interface permet d'administrer une base SQLite sans connaitre SQL.
Ce logiciel est l'équivalent de PHPMyAdmin pour MySQL.
En fait cette interface génère du SQL à partir de tous les renseignements que vous lui fournissez dans le cadre de grilles de saisie.

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 DB Browser pour créer la base de données "animaux.db" et les différentes tables.

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 !

Il faut maintenant créer chacune des deux tables (définir leur structure) puis insérer les données (ajouter des lignes).
Les requêtes création de table et insertion de lignes peuvent être générées via des grilles de l'interface DB Browser.

Création des tables de la base

Création et remplissage de la table "taxons"

Cliquez sur "Créer une table".

renseignez la zone de saisie "table"
définissez chaque champ à raison d'une ligne par champ dans la grille

Aperçu de la grille dans l'exemple :

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)

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

Observons attentivement le code SQL généré et qui apparait sous la grille :

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

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 chaque ligne). Mais comme le type est TEXT, l'autoincrémentation est bien sûr impossible.

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" : une grille de saisie apparait
Recommencez la procédure autant de fois que nécessaire.

Tri de la table "especes"

cliquez sur l'onglet "parcourir les données"
sélectionnez la table "especes" dans la liste déroulante des objets de la base :
Le contenu de la table s'affiche en première ligne des étiquettes reprenant les noms des colonnes.
pour chaque étiquette cliquez une fois puis une deuxième fois :
tri sur cette colonne par ordre croissant puis décroissant

Création et remplissage de la table "especes"

Créer la table "especes"

Le code SQL de création de la table "especes" est plus complexe que pour la table "taxons".

On ne peut saisir dans la colonne especes.code qu'une valeur qui existe déjà dans la colonne taxons.code.
Il faut donc définir la colonne especes.code comme clé étrangère par rapport à la colonne taxons.code qui est clé primaire.
On dit que l'on pose une contrainte d'intégrité référentielle.
De façon imagée on peut dire : "une fille doit avoir une mère".

Le code SQL qu'il faut générer

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

Pour générer la clause FOREIGN KEY("code") REFERENCES "taxons"("code") vous devez (dans la grille) cliquer sur l'onglet "clé étrangère" puis renseigner les colonnes "colonnes" et "références".
Rappel : la colonne clé étrangère dans la table fille doit référencer une colonne de la table mère qui est clé primaire.

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 dans la table en cours" ; Une grille apparait !

Première insertion

L'insertion est autorisée.
La production de l'ID est automatique.
Le code SQL généré apparait en bas : requête de type INSERT

Deuxième tentative d'ajout

Cette fois échec. En effet la valeur "bryo" (comme "bryozoaires") n'existe pas encore dans la colonne "taxons.code".
Pour que les données restent cohérentes il faut d'abord créer ce nouveau taxon dans la table "taxons" puis saisir les espèces de ce groupe.

Tri de la table "especes"

cliquez sur l'onglet "parcourir les données"
sélectionnez la table "especes" dans la liste déroulante des objets de la base :
Le contenu de la table s'affiche en première ligne des étiquettes reprenant les noms des colonnes.
pour chaque étiquette cliquez une fois puis une deuxième fois :
tri sur cette colonne par ordre croissant puis décroissant

Les requêtes sélection

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

Dans l'interface DB Browser vous êtes obligé de saisir le code SQL des requêtes sélection (pas de formulaire disponible).

cliquer sur "exécutez le SQL"
saisir le code de la requête sélection
cliquer sur la flèche bleu orientée vers la droite

Afin de vous familiariser avec la syntaxe SQL, je vous invite à créer toutes les requêtes sélection ci-dessous.

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

Afficher seulement de certaines colonnes

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

Il est d'usage pour les mots réservés du langage SQL soient écrits en majuscules et que les noms des colonnes et des tables soit délimitées par des guillemets (simples ou doubles).
Mais le fait d'omettre les guillemets et d'écrire les mots réservées en minuscules ne constituent des erreurs de syntaxe.
Ainsi au lieu d'écrire SELECT 'nom_espece' FROM 'especes' vous pouvez écrire tout simplement select nom_espece from especes. Dans les deux cas la requête sera exécutée avec succès.

Limiter l'affichage à certaines lignes

Il s'agit d'une requête SELECT avec la clause LIMIT suivi de 1 ou 2 arguments.
SELECT * FROM especes LIMIT 10 : affichage des 10 premières lignes
SELECT * FROM especes LIMIT 10,30 : affichage de 22 lignes (de la 10ième à la 30ième incluses)

Filtrage avec la clause WHERE

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"

Afficher poissons osseux et décapodes :
SELECT * FROM especes WHERE code ="osse" or code="deca"
La condition de filtrage devient complexe.

Filtrage avec la clause LIKE : "commence par ..." ou "contient ..."

SELECT * FROM especes WHERE nom_espece LIKE "h%"
On affiche seulement les espèces dont le nom commence par la lettre "h" (minuscule ou majuscule car LIKE est insensible à la casse).
LIKE s'associe à "%" qui remplace n'importe qu'elle chaîne de caractères et "_" qui remplacement un seul et unique caractère.

Vous pouvez écrire : SELECT * FROM especes WHERE nom_espece LIKE "%de mer%".
Cette requête retourne les lignes dont le champ "nom_espece" contient l'occurence "de mer" .
Dans le chapitre suivant vous verrez que le critère de filtrage peut être saisi par l'utilisateur : requêtes paramétrées.

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

SELECT * FROM especes WHERE nom_espece BETWEEN 'a%' AND 'd%' order by nom_espece
On affiche les espèces dont la première lettre du nom est compris entre a et d.

Trier les lignes

SELECT nom_espece, code FROM especes ORDER BY code ASC
Dans la liste retournée, les lignes sont triées par valeurs croissantes pour la colonne code. L'argument ASC (ascending) est facultatif : valeur par défaut est ASC.

SELECT nom_espece, code FROM especes ORDER BY code DESC
Dans la liste retournée, les lignes sont triées par valeurs décroissantes pour la colonne code. L'argument DESC est obligatoire.

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" puisque chaque espèce appartient à un taxon et un seul.
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 de jointure (nomtable.nomColonne) : "especes.code" d'une part et "taxons.code" d'autre part.
D'ailleurs essayez a requête SELECT * FROM especes, taxons WHERE code = code : vous aurez une erreur !

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 mer	deca	deca	décapodes
9	bulot			gast	gast	gastéropodes
...

Pour chaque ligne on a deux fois la même info (deux fois le code du taxon).

Solution

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 du taxon n'est affiché qu'une fois !
Pour lever toute ambiguïté il faut écrire "taxons.code" OU "especes.code".

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

Remarque

Désigner des champs de jointure par le même nom (ici "code") est une maladresse. Mais cette maladresse est délibérée de ma part ; il faut parfois montrer ce qu'il ne faut pas faire.
Il aurait été préférable de désigner le champ "taxons.code" par "t_code" et le champ "especes.code" par "e_code" donc préfixer les champs de jointure.
Ainsi il n'est pas alors nécessaire dans les requêtes multi-tables d'indiquer les noms complets pour certains champs : nomTable.nomChamp.

Sauvegarder les requêtes sélection complexes : les vues

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é ... on ne peut pas avoir deux clauses WHERE dans la même requête.
Il faut alors utiliser la clause INNER JOIN pour poser une jointure entre deux tables.
Le code SQL devient alors :

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

Notez l'emploi d'un alias pour une colonne sélectionnée : taxons.code as tcode ainsi je peux écrire en fin de requête : ON especes.code = tcode

Nous allons maintenant sauvegarder cette requête sous forme d'une vue.
A partir de la fenêtre "Exécuter le SQL" cliquez sur l'icône "Enregistrer la vue des résultats". Nommons la vue "vue1".
Donc une vue c'est tout simplement une requête sélection sauvegardée ; c'est utile quand la requête sélection est complexe.

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

Je vous propose maintenant une requête avec un code SQL assez lourd ...
SELECT nom_espece, taxons.code as code, nom_taxon FROM especes INNER JOIN taxons ON especes.code = tcode WHERE nom_espece BETWEEN "b%" AND "m%" ORDER BY nom_espece
Le rendu (totalité) :

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

Cette requête affiche les espèces dont le nom commence par les lettres "b" à "m" ; espèces classées par odre alphabétique.
Cette requête ne tient pas compte de la casse ; la première lettre peut être en minuscule ou majuscule.

Il est préférable, vu sa complexité, de la sauvegarder sous forme d'une vue que je nomme "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 "especes" 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), 3 ceph(alopodes), 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

	select max(id) from especes
	select min(id) from especes

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

Autres types de requêtes

Pour supprimer tout ou partie des enregistrements dans une table il faut exécuter une requête "DELETE FROM nomtable".
Une requête DELETE FROM nomTable sans condition supprime toutes les lignes.
Par contre une requête DELETE FROM nomTable WHERE ... supprime seulement les lignes vérifiant la condition.

Cependant dès qu'une table est soumise à une contrainte d'intégrité référentielle ça devient un peu plus compliqué ... Lisez le paragraphe qui suit.

Suppression de lignes dans la table "taxons"

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

Le message indique que la contrainte d'intégrité référentielle "interdit de supprimer une mère tant qu'elle a des filles".

Par contre rien n'interdit de supprimer des lignes dans la table "fille" (table 'especes').
Essayez la commande : DELETE FROM especes WHERE code ='epon' : requête exécutée avec succès.

Structure actuelle de notre base

Compte tenu de toutes les modifications apportées la base "animaux.db" se présente comme suit :

Cet onglet affiche le code de création des tables mais aussi des vues.

La table "sqlite_sequence" est créée par le logiciel SQLite3. Elle est utilisée lorsqu'une colonne dans une table est AUTOINCREMENT .

Les dates & heures

Dans la base "animaux.db" aucun champ ne contient de date & heure. Or c'est une problèmatique fréquente dans le cadre d'un SGBD. Ainsi il faut stocker des dates de naissance, des dates d'entrée, des dates de création de document, etc.
Je ne peux terminer ce chapitre sur SQLite sans vous montrer comment manipuler les dates & heures avec ce SGBD.
Plutôt que de créer une nouvelle base je vais rajouter provisoirement deux tables : "posts" & "eleves" dans la base "animaux.db" ; tables qui n'ont rien à voir avec notre thème de biologie marine.

Concernant les types de champs, SQLite propose : integer, text, real, numérique mais pas vraiement de type date ...
Alors comment faire ???
Dans SQLite la problématique est différente selon qu'il s'agit de l'instant présent ou d'une date historique.

Stocker dans un champ l'instant de création de l'enregistrement

Par exemple dans le cadre d'un blog il faut afficher la date de création de chaque "post".

Structure de la table "posts"

CREATE TABLE "posts" ( "id" INTEGER, 
"date_creation" INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP, 
"contenu" TEXT NOT NULL, 
PRIMARY KEY("id") )

Notez la clause : "date_creation" INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP

Pour définir cette table et plus précisément le champ "date_creation" il faut remplir la colonne "défaut" dans la grille avec la valeur "CURRENT_TIMESTAMP".
Ce qui veut dire que ce champ sera rempli automatique lors de la création de l'enregistrement avec le timestamp à ce moment précis.
Le timestamp est le nombre de secondes écoulées depuis le 1er janvier 1970 et l'instant présent.

Contenu de la table "posts"

1	2023-12-01 18:40:17	Texte de la première ligne
2	2023-12-01 18:40:42	Texte de la deuxième ligne
3	2023-12-01 18:41:14	Texte de la troisième ligne

Heureusement un champ rempli avec des timestamps s'affiche sous forme d'une info lisible pour un francophone : date et heure (AAAA-MM-JJ HH:MM:SS)
Vous constatez que les lignes ont été créées le même jour (1er décembre 2023).

Requête sélection sur table "posts"

Je vous propose :
SELECT * FROM posts where date_creation < date('2024-01-01')
Requête qui retourne trois lignes, bien sûr.

Autre solution :
SELECT * FROM posts where year(date_creation) < 2024
Emploi de la fonction year() : pas d'erreur : affichage de trois lignes !

Saisir des dates historiques dans un champ ?

Dans la table "eleves" il faut saisir leur date de naissance.
Cette fois le remplissage automatique du champ n'est pas envisageable puisque la date ne correspond pas à l'instant présent.
Il faut créer un champ de type text et saisir toutes les dates au même format : aaaa-mm-jj (avec des tirets)

Structure de la table "eleves"

CREATE TABLE "eleves" (
	"id"	INTEGER,
	"nom"	TEXT NOT NULL,
	"prenom"	TEXT NOT NULL,
	"sexe" integer default 1,
	"date_naissance"	TEXT NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
)

Contenu de la table

Saisir quatre individus dans cette table : un né en 1979, un autre en 1980, un troisième né en 1981 et un quatrième en 1982. Tous sont nés le 1er janvier ; deux femmes et deux hommes.

1	Dupont	louis	1	1979-01-01
2	Dubois	hélène	2	1980-01-01
3	Durant	julien	1	1981-01-01
4	Dumond  pauline	2	1982-01-01

Quelques requêtes sélection sur la table "eleves"

SELECT * FROM eleves WHERE date_naissance < date('1981-01-01')
SELECT * FROM eleves WHERE year(date_naissance) = 1982
SELECT * FROM eleves WHERE strftime('%Y', date_naissance) = '1982'

La première requête fonctionne.
La deuxième requête plante car le champ date_naissance est de type text donc je ne peux employer la fonction year() ou les fonctions month(), day() très connues des utilisateurs de MySQL.

Mais rassurez vous, il y a une solution ; il faut utiliser la fonction strftime() (argumentée avec '%Y' ou '%M') comme dans la troisième requête. Attention la fonction strftime() retourne une chaine d'où les quotes autour de 1982.

Vidage de tables

Il ne faut pas confondre vidage de table avec suppression de table !

La commande "DELETE FROM nomTable" sans clause WHERE permet de vider une table de tout contenu ; par contre sa définition est sauvegardée. Donc vous videz la table mais ne la supprimer pas (la définition de sa structure subsiste).
Testez les commandes : DELETE FROM posts DELETE FROM eleves

Suppression de tables

On peut maintenant supprimer les tables provisoires "posts" & "eleves" de la base.
Exécutez les commandes : DROP TABLE eleves & DROP TABLE posts
Les deux tables sont bien supprimées ; elles ne sont plus dans la liste des tables.

Téléchargez la BD 'animaux.db'

Traduction

Tutoriel Python - sommaire

Débuter en Python

Python - approfondissements

Python et le développement web

Python et les jeux vidéos

Python et l'IA

Tutoriel Python - recherche

L'auteur : Patrick Darcheville

SGBDR SQLite et logiciel DB browser

Avantages et inconvénients de SQLite

Structure d'une base de données

Généralités

La thématique

Structure de la table "taxons"

Structure de la table "especes"

Liaison entre les deux tables

L'utilitaire DB Browser (for SQLite)

Présentation

Installation de l'outil

Création d'une base

Création des tables de la base

Création et remplissage de la table "taxons"

Saisie dans la table "taxons"

Tri de la table "especes"

Création et remplissage de la table "especes"

Créer la table "especes"

Le code SQL qu'il faut générer

Insertion d'enregistrements dans la table "especes"

Première insertion

Deuxième tentative d'ajout

Tri de la table "especes"

Les requêtes sélection

Affichage d'une table (toutes les colonnes)

Afficher seulement de certaines colonnes

Limiter l'affichage à certaines lignes

Filtrage avec la clause WHERE

Filtrage avec la clause LIKE : "commence par ..." ou "contient ..."

Afficher un intervalle de lignes

Trier les lignes

Filtrage et tri

Requêtes sélection sur plusieurs tables

Requete sur especes & taxons

Solution

Remarque

Sauvegarder les requêtes sélection complexes : les vues

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

Requêtes statistiques

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

Compter le nombre de ligne par groupe

Les requêtes select MAX et select MIN

Autres types de requêtes

Suppression de lignes dans la table "taxons"

Structure actuelle de notre base

Les dates & heures

Stocker dans un champ l'instant de création de l'enregistrement

Structure de la table "posts"

Contenu de la table "posts"

Requête sélection sur table "posts"

Saisir des dates historiques dans un champ ?

Structure de la table "eleves"

Contenu de la table

Quelques requêtes sélection sur la table "eleves"

Vidage de tables

Suppression de tables