Base de données avec Python

5. Base de données avec Python#

5.1. Sqlite#

On utilisera essentiellement Sqlite dans le cadre du cours de NSI pour les raisons suivantes :

  • intégré à Python

  • stocke les informations dans un fichier texte

  • s’intère également à Jupyter

  • dispose de la plus part des mécanismes des SGBD

  • ne nécessite pas de serveur supplémentaire

Mais l’essentiel de ce que nous allons voir au sujet de sqlite est transférable à d’autres SGDB comme MariaDB[1] ou PostgreSQL pour ne citer que les SGDB libres [2].

Ce cours a pour obectif de présenter les bases de la manipulation de base de données. Un autre cours permettra d’avoir quelques éléments de compréhension de la conception de base de données.

5.2. Extrait du programme#

Contenu

Capacité

Commentaires

Langage SQL : requêtes d’interrogation et de mise à jour d’une base de données.

Identifier les composants d’une requête.

Construire des requêtes d’interrogation à l’aide des clauses du langage SQL : SELECT, FROM, WHERE, JOIN.

Construire des requêtes d’insertion et de mise à jour à l’aide de : UPDATE, INSERT, DELETE.

On peut utiliser DISTINCT, ORDER BY ou les fonctions d’agrégation sans utiliser les clauses GROUP BY et HAVING.

Pour utiliser sqlite avec Jupyter, il est nécessaire d’installer ipython-sql

#!pip install --user ipython-sql

%reload_ext sql

5.3. Utilisation#

L’extension sql ajoutée à Jupyter permet d’activer les commandes en un ligne du type %sql <REQUETE> où <REQUETE> correspond à une commande SQL valide.

Note

Pour disposer d’un mode multiligne, on utilisera %%sql.

5.3.1. connexion à la base de donnée#

On crée la connexion à la base de données.

%sql sqlite:///livres.sqlite

5.3.2. Création d’une table#

Pour créer une table Langues contenant un identifiant numérique et le nom de la langue, on utilise une requête CREATE TABLE.

CREATE TABLE Langues (
    IdLangue INTEGER PRIMARY KEY,
    Langue TEXT
);

 * sqlite:///livres.sqlite

Done.

[]

La création d’une table correspond à une création de structure. C’est une vision assez proche de la définition d’une classe en programmation orientée objet. Cependant, cette classe ne permettrait que d’instancier des objets avec des attributs, sans aucune méthode[3].

5.3.3. Insertion de données#

Une fois la table créée, on peut insérer des données dedans, avec la requête INSERT INTO <Table>

INSERT INTO Langues 
    (Langue)
    VALUES 
    ("Français"),
    ("Anglais")
    ;

 * sqlite:///livres.sqlite
2 rows affected.

[]

On peut noter ici qu’il n’a pas été nécessaire d’écrire la valeur de IdLangue.

5.3.4. Lire les données insérées#

On peut désormais lire les données de la table.

SELECT * FROM Langues ;

 * sqlite:///livres.sqlite
Done.
IdLangue Langue
1 Français
2 Anglais

Astuce

On peut ne sélectionner que certaines colonnes en remplaçant * par le nom des colonnes.

5.4. Créer une table Auteurs#

Cette table doit disposer de

  • un identifiant

  • un nom d’auteur

  • un prénom d’auteur

  • une langue maternelle de l’auteur

  • une année de naissance

Hide code cell content 
%%sql
CREATE TABLE Auteurs (
    IdAuteur       INTEGER  PRIMARY KEY,
    NomAuteur      TEXT,
    PrenomAuteur   TEXT,
    IdLangue       INTEGER,
    AnneeNaissance INTEGER,
    FOREIGN KEY(IdLangue) REFERENCES Langues(IdLangue)
);

 * sqlite:///livres.sqlite
Done.

[]

5.5. Alimenter la table Auteurs#

Alimenter ensuite cette table avec les données suivantes :

Nom

Prenom

annee naissance

langue

Orwell

George

1903

Anglais

Herbert

Frank

1920

Anglais

Asimov

Isaac

1920

Anglais

Huxley

Aldous

1894

Anglais

Bradbury

Ray

1920

Anglais

K. Dick

Philip

1928

Anglais

Barjavel

René

1911

Français

Boulle

Pierre

1912

Français

Van Vogt

Alfred Elton

1912

Anglais

Verne

Jules

1828

Français
Hide code cell content 
%%sql
INSERT INTO Auteurs   (NomAuteur,PrenomAuteur,AnneeNaissance,IdLangue)
VALUES
("Orwell","George",1903,2),
("Herbert", 	"Frank", 	1920 	,2),
("Asimov", 	"Isaac", 	1920 	,2),
("Huxley", 	"Aldous", 	1894 	,2),
("Bradbury", 	"Ray", 	1920 	,2),
("K. Dick", 	"Philip", 	1928 	,2),
("Barjavel", 	"René", 	1911 	,1),
("Boulle", 	"Pierre", 	1912 	,1),
("Van Vogt", 	"Alfred Elton", 	1912, 	2),
("Verne", 	"Jules", 	1828, 	1)
;

 * sqlite:///livres.sqlite
10 rows affected.

[]

5.6. Opérations sur les tables#

On peut ensuite réaliser une copie de la base de données pour expérimenter les actions suivantes.

5.6.1. Sélectionner que certaines lignes#

%sql SELECT NomAuteur, PrenomAuteur FROM Auteurs WHERE AnneeNaissance = 1920 ;

 * sqlite:///livres.sqlite
Done.
NomAuteur PrenomAuteur
Herbert Frank
Asimov Isaac
Bradbury Ray

5.6.2. Supprimer des informations#

%sql SELECT IdAuteur FROM Auteurs ;

 * sqlite:///livres.sqlite
Done.
IdAuteur
1
2
3
4
5
6
7
8
9
10 
%sql DELETE FROM Auteurs WHERE IdAuteur = 1 ;

%sql SELECT IdAuteur FROM Auteurs ;

 * sqlite:///livres.sqlite
Done.
IdAuteur
2
3
4
5
6
7
8
9
10

Pour supprimer toute une table :

%sql DROP TABLE Auteurs ;

5.6.3. Modifier une ligne#

UPDATE Auteurs SET PrenomAuteur = "Georges" WHERE PrenomAuteur = "George" ;

 * sqlite:///livres.sqlite

1 rows affected.

[]

%sql SELECT NomAuteur, PrenomAuteur FROM Auteurs ;

 * sqlite:///livres.sqlite
Done.
NomAuteur PrenomAuteur
Orwell Georges
Herbert Frank
Asimov Isaac
Huxley Aldous
Bradbury Ray
K. Dick Philip
Barjavel René
Boulle Pierre
Van Vogt Alfred Elton
Verne Jules

5.7. Jointure de tables#

La table Auteurs ne contient pas la Langue à proprement parler, mais un identifiant pointant vers cette Langue. Pour cumuler ces deux informations, on utilise JOIN

SELECT NomAuteur, PrenomAuteur, Langue, AnneeNaissance  
    FROM Auteurs 
    JOIN Langues 
    ON Auteurs.IdLangue = Langues.IdLangue

 * sqlite:///livres.sqlite
Done.
NomAuteur PrenomAuteur Langue AnneeNaissance
Orwell Georges Anglais 1903
Herbert Frank Anglais 1920
Asimov Isaac Anglais 1920
Huxley Aldous Anglais 1894
Bradbury Ray Anglais 1920
K. Dick Philip Anglais 1928
Barjavel René Français 1911
Boulle Pierre Français 1912
Van Vogt Alfred Elton Anglais 1912
Verne Jules Français 1828

5.8. Autres opérations#

5.8.1. Compter les auteurs#

Pour compter les éléments d’une table :

SELECT COUNT(*) FROM Auteurs ;

 * sqlite:///livres.sqlite
Done.
COUNT(*)
10

Compter les auteurs du XXème siècle

SELECT COUNT(*) FROM Auteurs WHERE AnneeNaissance > 1900 ;

 * sqlite:///livres.sqlite
Done.
COUNT(*)
8

5.8.2. Recherche dans une table#

Trouver le nom d’un auteur à partir de son prénom

SELECT NomAuteur FROM Auteurs WHERE PrenomAuteur = 'Jules';

  • Attention, la recherche est sensible aux majuscules

%sql SELECT NomAuteur FROM Auteurs WHERE PrenomAuteur = 'jules';

 * sqlite:///livres.sqlite
Done.
NomAuteur
Verne

Pour une recherche avec un motif :

SELECT NomAuteur,PrenomAuteur FROM Auteurs WHERE PrenomAuteur LIKE '%u%';

 * sqlite:///livres.sqlite

Done.
NomAuteur PrenomAuteur
Huxley Aldous
Verne Jules

5.8.3. Ordonner les résultats#

On peut ordonner les résultats :

SELECT * from Auteurs ORDER BY AnneeNaissance;

 * sqlite:///livres.sqlite
Done.
IdAuteur NomAuteur PrenomAuteur IdLangue AnneeNaissance
10 Verne Jules 1 1828
4 Huxley Aldous 2 1894
1 Orwell Georges 2 1903
7 Barjavel René 1 1911
8 Boulle Pierre 1 1912
9 Van Vogt Alfred Elton 2 1912
2 Herbert Frank 2 1920
3 Asimov Isaac 2 1920
5 Bradbury Ray 2 1920
6 K. Dick Philip 2 1928

On peut aussi ordonner les résultats en ordre inverse

SELECT * from Auteurs ORDER BY AnneeNaissance DESC;

%sql SELECT * from Auteurs ORDER BY AnneeNaissance DESC;

 * sqlite:///livres.sqlite
Done.
IdAuteur NomAuteur PrenomAuteur IdLangue AnneeNaissance
6 K. Dick Philip 2 1928
2 Herbert Frank 2 1920
3 Asimov Isaac 2 1920
5 Bradbury Ray 2 1920
8 Boulle Pierre 1 1912
9 Van Vogt Alfred Elton 2 1912
7 Barjavel René 1 1911
1 Orwell Georges 2 1903
4 Huxley Aldous 2 1894
10 Verne Jules 1 1828
Contenu