Retour au sommaire  

L'objectif de cette activité est de découvrir rapidement l'utilisation du module sqlite3 de Python afin d'accéder à une base de données et de la modifier en écrivant des requêtes SQL.
Exemples de programmes en Python
Application : enregistrer une liste de liste
La base de données bibliotheque
La base de données famille

 

Exemples de programmes en Python

Voici les programmes de base montrant comment utiliser le module sqlite3 en Python. Chaque programme peut être largement amélioré en fonction de vos besoins et de vos objectifs :

Création d'une table dans une nouvelle base de données
Lecture d'une table dans une base de données

Remarque : le fichier exemple.db créé en Python est parfaitement lisible et modifiable avec l'outils Sqlite du logiciel UwAmp, mais aussi avec l'outils SQLite d'EduPython disponible dans la menu Outils -> Outils -> SQLite Data Browser :

Voici la structure de la table users dans la base de données exemple.db :

Et voici les données qui y sont enregistrées :

La méthode execute de sqlite3 exécute une seule ligne de commande SQL.

La méthode executescript de sqlite3 permet d'exécuter un script SQL, c'est à dire plusieurs lignes de commande séparées par des points-virgule. Exemple d'utilisation :

Exécution d'un script SQL complet avec executescript()

 

Application : enregistrer une liste de liste

Problèmatique : nous voulons enregistrer l'état du jeu de puissance 4 en cours de partie afin de terminer la partie un autre jour. La grille du jeu Puissance 4 contenant 7 colonnes et 6 lignes est enregistrée dans Python sous forme d'une liste de liste 7x6 :

Dans cette structure de données, un 0 représente une case vide, un 1 représente un pion rouge et un 2 représente un pion bleu.

Nous allons voir 2 solutions différentes pour enregistrer une telle structure de données dans une table en SQL.

 

Solution 1 : on enregistre chaque case dans un champ de la table, avec 1 ligne de la grille par enregistrement.

La structure de la table est alors composées de 7 champs de type INTEGER correspondant aux 7 colonnes de la grille (champs nommés c1 à c7 dans le programme).

Comme chaque enregistrement de la table contient 1 ligne et qu'il y a 6 lignes dans la grille de Puissance 4, il faudra 6 enregistrements pour sauvegarder la grille entière dans la base de données.

Voici le programme (téléchargeable ci-dessous) qui enregistre 1 grille dans une table :

Écriture d'une grille dans une table

Ce programme crée un fichier de base de données nommé grille_1.bd

En ouvrant ce fichier grille_1.db dans le logiciel SQLite, on retrouve la structure de la table composée de 7 champs de type entier :

Et voici le contenu des données : 6 enregistrements dans la table users, ce qui correspond à la grille entière :

Une fois le fichier grille_1.db créé, il faut un autre programme pour le lire et reconstituer la grille dans Python.

Voici le programme (téléchargeable ci-dessous) qui lit 1 grille dans une table :

Lecture d'une grille dans une table

Chaque enregistrement de la table (il y en a 6 ici) est renvoyé sous forme d'un tuple. Aprés conversion des 6 tuples en liste, et ajout de ces 6 listes dans une liste grille[] initialement vide, on retrouve bien notre grille 7x6 sous forme d'une liste de liste :

Avantage de cette solution 1 : en consultant les données dans la table on retourve immédiatement la structure de la grille (6 lignes et 7 colonnes en clair)

Inconvénient de cette solution 1 : on ne peut enregistrer qu'une seule grille dans la base de données

 

Voyons maintenant la solution 2 qui permet d'enregistrer plusieurs grilles dans la même table.

 

Solution 2 : on enregistre une grille complète dans un seul champ de la table. Pour sauvegarder plusieurs grilles il suffit d'ajouter plusieurs enregistrements dans la table.

Voici le programme (téléchargeable ci-dessous) qui enregistre une grille par enregistrement dans une table :

Écriture de plusieurs grilles dans une table

Ce programme crée un fichier de base de données nommé grille_2.bd

En ouvrant ce fichier grille_2.db dans le logiciel SQLite, on retrouve la structure de la table composée de 3 champs :

Il est possible d'enregistrer plusieurs grilles dans la tables users. Voici par exemple 3 grilles différentes enregistrées :

Une fois le fichier grille_2.db créé, il faut un autre programme pour le lire et reconstituer dans Python toutes les grilles enregistrées.

Voici le programme (téléchargeable ci-dessous) qui lit toutes les grilles enregistrées dans la table users :

Lecture de plusieurs grilles dans une table

Chaque enregistrement de la table (accessible ici dans le tuple ligne) contient 3 champs :

Après conversion des chaînes de caractères ligne[2] en liste de liste grâce à la fonction eval() de Python, on retrouve bien toutes les grilles enregistrés (3 grilles dans cet exemple) sous forme d'une liste de liste de dimensions 7x6 :

Avantage de cette solution 2 : il est possible d'enregistrer plusieurs grilles dans la même table

Inconvénient de cette solution 2 : la lecture directe de la base de données ne redonne pas les grilles en clair. De plus, les grilles étant enregistrées sous forme de chaînes de caractères (et non sous forme d'entiers comme dans la solution 1, ni directement sous forme de listes) il faut utiliser la fonction eval() de Python pour convertir les chaînes de caractères en liste de liste directement exploitable par Pyhton.

 

La base de données bibliotheque

La base de données bibliotheque est disponible sur le site fxjollois.github.io et possède 4 tables :


Schéma de la base de données bibliothèque

IMPORTANT : pour pouvoir importer un fichier CSV dans SQLite la dernière ligne du fichier CSV doit finir par un retour à la ligne

Travail demandé :

  1. Allez sur le site fxjollois.github.io puis cliquez sur Requêtage direct puis sur bibliotheque
  2. À l'aide de requêtes SQL affichez l'intégralité de chacune des 4 tables, et enregistrez les tables sous forme de 4 fichiers CSV en cliquant sur le bouton CSV
  3. À l'aide de l'outil SQLite de Python, reconstituez l'intégralité de la base de données bibliotheque en y important les 4 fichiers CSV et enregistrez-la dans un fichier bibliotheque.db. Vous veillerez à ce que les attributs des champs aient exectament les types indiqués en cliquant sur le bouton Tables sur le site.
  4. Faites un programme en Python qui affiche la table Auteur dans la console de Python. Vous formaterez l'affichage afin de représenter une structure de données par table.
  5. Améliorez votre programme pour qu'il affiche la table Livre dans la console.
  6. Améliorez votre programme pour qu'il affiche la table Emprunte dans la console.
  7. Améliorez votre programme pour qu'il affiche la table Lecteur dans la console.
  8. Testez dans Python quelques requêtes de jointure utilisant le caractère relationnel de la base de données bibliotheque, indiqué par les clés primaires et étrangères sur le schéma ci-dessus (par exemple pour afficher tous les livres d'un auteur donné, ou pour afficher tous les livres empruntés par un lecteur donné). Le résultat de la jointure doit être afficher clairement dans la console, toujours sous forme de table formatée.

Remarque :

 

 

La base de données famille

Nous allons ici créer dans Python une nouvelle base de données qui sera enregistrée dans le fichier nommé famille.db

Cette base de données contiendra des informations simples (nom, prénom et sexe) des différentes personnes concernant plusiuers familles.

Pour obtenir un grand nombres d'enregistrements sur plusieurs personnes différentes vous utiliserez le générateur de données.

Comme dans l'activité précédente de la bibliothèque, la base de données de départ sera créée rapidement en important des fichiers CSV dans SQLite, mais c'est ensuite à Python de se connecter à la base de données en utilisant le module sqlite3 et de l'interroger grâce à des requêtes SQL appropriées afin de la consulter ou de la modifier.

 

Travail demandé :

Voici les différentes tâches à effectuer dans Python ou dans le logiciel SQLite d'EduPython. N'hésitez pas à automotiser certaines d'entre elles dans Python, sans faire les tâches répétitives ou laborieuses à la main dans SQLite ...

  1. Créez une nouvelle base de données nommée famille.db
  2. Ajoutez une nouvelle table enfant dans la base de données famille.db. Cette table devra avoir 4 attributs :
  3. En prenant les enregistrements grâce au générateur d'informations, insérez 20 personnes dans la table enfant en utilisant la méthode de votre choix
  4. Ajoutez une nouvelle table pere dans la base de données famille.db. Cette table possède les mêmes attributs que la table enfant mais doit contenir seulement des hommes
  5. En utilisant le générateur d'informations, insérez 20 personnes de sexe masculin dans la table pere en utilisant la méthode de votre choix
  6. Ajoutez une nouvelle table mere dans la base de données famille.db. Cette table possède les mêmes attributs que la table enfant mais doit contenir seulement des femmes
  7. En utilisant le générateur d'informations, insérez 20 personnes de sexe féminin dans la table mere en utilisant la méthode de votre choix
  8. Dans la table enfant, chaque personne doit avoir un père et une mère. Pour cela nous allons lier chaque enfant à ses deux parents en ajoutant des clés étrangères pointant vers les tables pere et mere.
  9. Ajoutez à la table enfant une nouvelle colonne (un nouvel attribut) nommé id_pere de de type INT (cette nouvelle colonne reste vide pour l'instant) : l'attribut id_pere de la table enfant est une clé étrangère qui pointera vers l'attribut id de la table pere
  10. Ajoutez à la table enfant une nouvelle colonne (un nouvel attribut) nommé id_mere de de type INT (cette nouvelle colonne reste vide pour l'instant) : l'attribut id_mere de la table enfant est une clé étrangère qui pointera vers l'attribut id de la table mere
  11. Renseignez l'attribut id_pere de chacun des 20 enfants en y inscrivant l'identifiant d'un père afin de le relier à la clé primaire de la table pere (on pourra atrtibuer automatiquement un père au hasard dans Python)
  12. Renseignez l'attribut id_mere de chacun des 20 enfants en y inscrivant l'identifiant d'un père afin de le relier à la clé primaire de la table mere (on pourra atrtibuer automatiquement une mère au hasard dans Python)
  13. Chaque enfant possédant maintenant 2 parents, affichez pour chacun d'entre eux les nom et prénom de ses parents à l'aide d'une jointure (en plus des nom et prénom de l'enfant) dans des colonnes clairement renommées
  14. Affichez toutes les personnes de la table pere qui n'ont pas d'enfant (c'est à dire que leur identifiant n'est pas utilisé dans la table enfant)
  15. Affichez toutes les personnes de la table mere qui n'ont pas d'enfant (c'est à dire que leur identifiant n'est pas utilisé dans la table enfant)
  16. En utilisant le générateur de données, ajoutez 20 nouveaux enfants dans la table enfant et renseignez leur père et leur mère par les relation clé étrangère / clé primaire
  17. Modifiez la table enfant pour que chaque enfant porte le nom du père (en remplaçant le nom d'origine de l'enfant)
  18. Affichez tous les pères et toutes les mères qui n'ont pas d'enfant
  19. Affichez tous les pères et toutes les mères qui ont un seul enfant
  20. Affichez tous les pères et toutes les mères qui ont plusieurs enfants

 

Remarques concernant la création des 3 tables :

 

Que faire si l'importation d'une table par un fichier CSV dans SQLite ne fonctionne pas ?

Si l'importation d'un fichier CSV dans SQLite de Python est impossible (SQLite plante systèmatiquement à l'importation du fichier CSV), vérifiez les points suivants en éditant votre fichier CSV dans l'éditeur Notepad++ :

Remarques :

Si on créer une nouvelle table en important un fichier CSV dans SQLite les attributs de la table ne sont pas typés :


Après importation du CSV les attributs de la table ne sont pas typés

Pour typer les attributs il faut modifier les caractéristiques de la table manuellement dans SQLite :

  1. cliquez sur Modify table dans le menu Edit de SQLite
  2. sélectionnez la table concernée puis cliquez sur Edit
  3. sélectionnez l'attribut id, cliquez sur Edit field, choisissez le type INTEGER PRIMARY KEY, puis cliquez sur Apply Changes
  4. sélectionnez l'attribut nom, cliquez sur Edit field, choisissez le type TEXT, puis cliquez sur Apply Changes
  5. sélectionnez l'attribut prénom, cliquez sur Edit field, choisissez le type TEXT, puis cliquez sur Apply Changes
  6. sélectionnez l'attribut sexe, cliquez sur Edit field, choisissez le type TEXT, puis cliquez sur Apply Changes
  7. cliquez sur le bouton Close
  8. vérifiez les nouveaux types des attributs dans la structure de la table dans l'onglet Database Structure :


Maintenant les attributs de la table sont typés

 

Conclusion

Vous savez maintenant utiliser le module sqlite3 afin d'accéder à une base de données en Python grâce aux requêtes SQL.

Le logiciel SQLite directement accessible depuis Python permet d'explorer facilement une base de données (structure de la table et contenu des données).

La méthode execute de sqlite3 exécute une seule ligne de commande SQL.

La méthode executescript de sqlite3 permet d'exécuter un script SQL, c'est à dire plusieurs lignes de commande séparées par des points-virgule.

De plus, grâce à la sérialisation (fonction str(structure) en Python) et à la dé-sérialisation (fonction eval(chaine) en Python) il est possible d'enregistrer facilement sous forme de chaîne de caractères toutes sortes de structures de données dans un seul champ TEXT d'une table :

 

 

Réalisé par Jean-Christophe MICHEL

© Mars 2025