Manipuler une base de données SQLite avec Python

Dans le chapitre précédent nous avons manipulé une base de donnée SQLite à partir du logiciel DB browser(for SQLite).
Mais on peut manipuler cette base via l'interpréteur Python ou dans le cadre d'un programme Python.
Je vous rappelle que la base "animaux.db" comprend deux tables : 'especes' & 'taxons' avec un champ de jointure entre ces deux tables : 'code'.

Lire les tables de la base

Nous alons tout d'abord utiliser la console Python.

Lire la table "taxons"

>>> import os
>>> os.chdir("c:/python_prog")
>>> import sqlite3
>>> connexion = sqlite3.connect("animaux.db")
>>> curseur = connexion.cursor()
>>> curseur.execute("SELECT * from taxons;")
<sqlite3.Cursor object at 0x00000147BF43E3B0>
>>> for resultat in curseur:
...     print(resultat)
...
('hydr', 'hydraires')
('medu', 'méduses')
('acti', 'actinies')
...
('pinn', 'pinnipèdes')
('sire', 'siréniens')
('epon', 'éponges')
>>>

Attention, la base de données "animaux.db" est stockée dans le dossier "c:\python_prog". Or le répertoire par défaut pour l'interpréteur est "c:\python39". Il faut donc changer de répertoire courant et donc importer le module "os" pour disposer de la fonction chdir().
Pour manipuler une base de données SQLite, il faut donc importer le module "sqlite3" .
Il faut ensuite se connecter à une base de données avec la méthode connect() du module sqlite3.

Ensuite il faut créer un objet de type cursor(). Ici cet objet est nommé "curseur".
On peut appliquer à cet objet "cursor" la méthode execute() qui permet d'appeler n'importe quel type de requête SQL.

Éxaminons quelques commandes :
curseur.execute("SELECT * from taxons;") : pour récupérer dans l'objet "cursor" tout le contenu de "taxons".
for resultat in curseur : l'objet de type "cursor" est itérable.
Les contenus sont retournés sous forme de tuples. Ce n'est donc pas très lisible à cause des parenthèses, des guillemets simples qui parasitent l'affichage.

Autres méthodes applicables à l'objet "cursor()"

fetchone()
fetchall()
fetchmany(n)

La méthode fetchone()

La méthode fetchone(), appliquée à l'objet de type cursor(), permet de récupérer l'enregistrement courant sous forme d'un tuple. ... >>> curseur.execute("SELECT * FROM vue1") <sqlite3.Cursor object at 0x0000029DB927E340> >>> >>> ligne = curseur.fetchone() >>> print(ligne) ('homard', 'deca','décapodes') >>>

Rappel de la syntaxe de "vue1" : CREATE VIEW "vue1" AS SELECT nom_espece, taxons.code, nom_taxon FROM especes INNER JOIN taxons ON especes.code = taxons.code

La méthode fetchall()

Cette méthodé appliquée au curseur permet de récupérer toutes les lignes correspondant à la requête sous forme de tuples.
Pour se débarrasser des parenthèses, crochets, guillemets il suffit de pratiquer le "slicing" (ou tranchage). Voir l'exemple qui suit. ... >>> curseur.execute("SELECT * FROM vue1") >>> lignes = curseur.fetchall() >>> for ligne in lignes: ... print(ligne[0], ligne[1], ligne[2]) ... homard deca décapodes orange de mer epon éponges ... étrille deca décapodes tourteau deca décapodes ...

La méthode fetchmany(n)

Cette méthode permet de récupérer un nombre de lignes correspondant à la valeur passée en paramètre.
En supposant que vous êtes connecté à la base "animaux.db", Les commandes qui suivent sont : ... >>> curseur.execute("select * from especes order by code") <sqlite3.Cursor object at 0x0000023687E4E3B0> >>> resultat = curseur.fetchmany(10) >>> print(resultat) [(27, 'talitre', 'amph'), (25, 'fraise de mer', 'asci'), (26, 'cione', 'asci'), (36, 'soleil de mer', 'aste'), (11, 'moule commune', 'biva'), (12, 'huitre creuse', 'biva'), (13, 'coque', 'biva'), (14, 'palourde', 'biva'), (20, 'roussette', 'cart'), (21, 'raie bouclée', 'cart')]

On affiche bien 10 lignes.

Requête sélection avec filtrage

Le critère de filtrage est une constante

...
>>> curseur.execute("select * from vue1 where id = 4")
>>> curseur.fetchone()
(4, 'crevette grise', 'deca', 'décapodes')
>>>

La requête sélectionne l'enregistrement dont l'id vaut 4.
curseur.fetchone() : affiche cette ligne sous la forme d'un tuple.

Le critère de filtrage est une variable

...
>>> identifiant = 20
>>> curseur.execute("select * from especes where id =?",(identifiant,))
<sqlite3.Cursor object at 0x000001B05EBCE340>
>>> curseur.fetchone()
(20, 'roussette', 'cart')
>>>

Nous affectons 20 dans une variable et nous voulons qu'ensuite ce soit l'enregistrement dont l'id correspond au contenu de la variable qui s'affiche.
Comme vous voyez ci-dessus la syntaxe est particulière : select ... id =?", (nomVariable)

Requête sélection statistique avec regroupements

>>> curseur.execute("select code, count(*) from especes group by code")
<sqlite3.Cursor object at 0x000001B05EBCE340>
>>> contenu = curseur.fetchall()
>>> for ligne in contenu:
...     print(ligne[0], ligne[1])
...
biva	6
cart	2
...

contenu = curseur.fetchall() : retourne 14 tuples car il y a 14 valeurs distinctes dans la colonne "especes.code"
Avec le "slicing", on se débarrasse des parenthèses et des guillemets.

Se déconnecter de la base de données

N'oubliez pas de fermer la connexion avec la base.
Il faut appliquer la méthode close() à l'objet de type "connect" et si cet objet se nomme "connexion", la commande est alors : connexion.close()

Programme pour parcourir une vue

Nous allons maintenant passer au mode programmé avec l'IDLE comme environnement de travail.
Je vous propose tout d'abord un programme qui parcoure la vue "vue1".

Rappel de "vue1"

CREATE VIEW "vue1" AS SELECT nom_espece, taxons.code, nom_taxon FROM especes INNER JOIN taxons ON especes.code = taxons.code
Cette vue mémorise une requête sélection multitables : associer à chaque enregistrement de "especes" une ligne de "taxons".

Le programme

#nom programme : lire_vue1.py
import sqlite3
connexion = sqlite3.connect("animaux.db")
curseur = connexion.cursor()
curseur.execute("SELECT * FROM vue1 ORDER BY code")
lignes = curseur.fetchall()
for ligne in lignes:
    print(ligne[0], ligne[1], ligne[2])
connexion.close()

Le programme et la BD étant stockés dans le même répertoire (c:\python_prog), l'instruction "chdir ... " est ici inutile.
Notez qu'en SQL on manipule une vue comme une table.

Le rendu

Trace d'éxécution dans l'IDLE (extrait) :

talitre amph amphipodes
ver bobbit anne annélidés
fraise de mer asci ascidies
cione asci ascidies
soleil de mer aste astérides
moule commune biva bivalves
...

Programme pour parcourir une table

Maintenant un programme qui lit la table "especes".
Pour afficher les champs nous allons utiliser leur nom plutôt que leur indice.
Le code : #nom programme : lire_especes.py import sqlite3 connexion = sqlite3.connect("animaux.db") connexion.row_factory =sqlite3.Row curseur = connexion.cursor() curseur.execute("SELECT id , nom_espece, code FROM especes ORDER BY id") lignes = curseur.fetchall() for ligne in lignes: print(ligne['id'], ligne['nom_espece'], ligne['code']) connexion.close()

Notez l'instruction connexion.row_factory =sqlite3.Row qui va nous permettre d'utiliser les noms de champs à la place de leur indice : print(ligne['id'], ligne['nom_espece'], ligne['code'])

Le rendu

Trace d'exécution dans l'IDLE (extrait) :

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

Insertion de lignes dans une table "especes"

Supprimons au préalable les poissons osseux de la table "especes" via la console Python :

...
>>> curseur.execute("delete from especes where code ='osse' ")

>>> connexion.commit()

Il faut toujours confirmer la modification des données dans une base avec la méthode commit().

Programme qui ajoute des lignes à partir de données fournies par le script

Le code

#nom programme : saisie_especes_6.py
import sqlite3
connexion = sqlite3.connect("animaux.db")
curseur = connexion.cursor()

#insertion d'une ligne
un_poisson = ('osse','murène')
curseur.execute("insert into especes(code,nom_espece) values (?, ?) ", un_poisson)
connexion.commit()

#insertion de 5 lignes
autres_poissons = [
    ('osse','hareng'),
    ('osse','maquereau'),
    ('osse', 'morue'),
    ('osse','sardine'),
     ('osse','baudroie')
]
curseur.executemany("insert into especes(code,nom_espece) values(?,?)", autres_poissons)
connexion.commit()
connexion.close()

Ce programme et la BD étant stockés dans le même répertoire (c:\python_prog), l'instruction "chdir ... " est ici inutile.

Ici je vous montre comment intégrer des requêtes SQL INSERT dans un programme Python.
La variable un_poisson contient un tuple décrivant une seule espèce. La syntaxe pour insérer une seule ligne est : curseur.execute(requête insert, nomTuple)
la variable autres_poissons est une liste de 5 tuples. La syntaxe pour insérer 5 nouvelles lignes est : curseur.executemany(requête insert, nomListedeTuples). Oui, vous avez bien lu executemany()
Il faut valider la modification de la table avec la commande commit() puis il faut se déconnecter de la base.

Contrôle

Pour vérifier que 6 lignes (1 + 5) ont bien été ajoutées à la table "especes" il suffit de relancer le programme "lire_vue1.py" :

Trace d'exécution du programme :

...
murène osse poissons osseux
hareng osse poissons osseux
maquereau osse poissons osseux
morue osse poissons osseux
sardine osse poissons osseux
baudroie osse poissons osseux
...

Donc six enregistrements ont bien été ajoutés.

Programma qui insère des lignes à partir de données communiquées par l'utilisateur

C'est quand même plus pratique lorsque c'est l'utilisateur qui saisit les données.

Le code du programme

#nom programme : saisie_especes_plus.py
import sqlite3
connexion = sqlite3.connect("animaux.db")
#curseur = connexion.cursor()
print("SAISIR DE NOUVELLES ESPECES")
while True:
    nom = input("nom de l'espèce OU 'fin' : ")
    if nom =="fin":
        break
    code =input("code du taxon corrrespondant : ")
    requete = "insert into especes(nom_espece, code) values(?, ?) "
    connexion.execute(requete, (nom,code))
    connexion.commit()
connexion.close()

La méthode execute() comprend deux paramètres : code SQL, tuple de variables.

Le rendu

Trace d'exécution du programme :

SAISIR DE NOUVELLES ESPECES
nom de l'espèce : truite 
code du taxon corrrespondant : osse
nom de l'espèce : saumon
code du taxon corrrespondant : osse
nom de l'espèce : fin
>>>

Critique constructive de ce programme

Ce programme fonctionne mais n'est pas très ergonomique ... L'utilisateur doit connaitre par coeur les codes des différents taxons.

que les saisies se fasse au travers d'un formulaire
que la saisie du code se fasse via une liste déroulante ; liste ayant pour source la table "taxons"

Sachez que tout cela est possible à condition d'utiliser le module tkinter ; voir le chapitre suivant.

Traduction

Tutoriel Python - sommaire

Débuter en Python

Python - approfondissements

Tutoriel Python - recherche

L'auteur : Patrick Darcheville

Manipuler une base de données SQLite avec Python

Lire les tables de la base

Lire la table "taxons"

Autres méthodes applicables à l'objet "cursor()"

La méthode fetchone()

La méthode fetchall()

La méthode fetchmany(n)

Requête sélection avec filtrage

Le critère de filtrage est une constante

Le critère de filtrage est une variable

Requête sélection statistique avec regroupements

Se déconnecter de la base de données

Programme pour parcourir une vue

Rappel de "vue1"

Le programme

Le rendu

Programme pour parcourir une table

Le rendu

Insertion de lignes dans une table "especes"

Programme qui ajoute des lignes à partir de données fournies par le script

Le code

Contrôle

Programma qui insère des lignes à partir de données communiquées par l'utilisateur

Le code du programme

Le rendu

Critique constructive de ce programme