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, champ qui porte le même nom dans les deux tables : 'code'.

colonnes de la table "especes" sont : id, nom_espece, code
champs de la table "taxons" sont : code,nom_taxon

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 et donc créer un objet de type connection.

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 la variable "identifiant" et nous voulons qu'ensuite ce soit l'enregistrement dont l'id correspond au contenu de cette variable qui s'affiche.
Comme vous voyez ci-dessus la syntaxe est particulière : select ... id =?", (nomVariable,)
N'oubliez pas la virgule avant de fermer la parenthèse pour préciser que le tuple ne contient qu'une valeur sinon plantage !

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

Extrait de la trace :

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

Je vous propose 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

Extrait de la trace :

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

Programme de recherche dans une table

Les programmes ci-dessous permettent d'effectuer des recherches dans la table "especes" sur la colonne "nom_espece".
Ils sont basés sur des requêtes paramétrées.

Mauvaise solution

Le programme ci-dessous exige de la part de son utilisateur une grande rigueur de saisie. Ainsi s'il recherche la fiche sur le "Crabe vert" il doit saisir "Crabe vert" (avec un C majuscule) car le contenu de "nom_espece" pour cette ligne est "Crabe vert".

Le code

#nom programme : recherche_espece_bug.py
import sqlite3
connexion = sqlite3.connect("animaux.db")
curseur = connexion.cursor()
while True:
    choix = input("Tapez une partie du nom de l'espèce recherchée ou 'fin' : ")
    if choix =='fin' :
            break
    sql ="select code, nom_espece  from especes where nom_espece = ? "
    curseur.execute(sql, (choix,))
    lignes =curseur.fetchall()
    for ligne in lignes:
            print(ligne[1], ligne[0])
connexion.close()

Dans (choix,) n'oubliez pas la "," avant de fermer la parenthèse pour préciser que le tuple ne contient qu'une valeur sinon plantage !

Le rendu

Tapez une partie du nom de l'espèce recherchée ou 'fin' : crabe vert
Tapez une partie du nom de l'espèce recherchée ou 'fin' : Crabe
Tapez une partie du nom de l'espèce recherchée ou 'fin' : Crabe vert
Crabe vert deca
Tapez une partie du nom de l'espèce recherchée ou 'fin' :

J'ai du m'y prendre à trois fois pour enfin obtenir les infos sur l'espèce "crabe vert".

Solution correcte

Une requête paramétrée basée sur l'opérateur LIKE (plutôt que sur ==) offre à l'inverse une grande souplesse. En effet non seulement il n'est pas nécessaire de saisir le nom complet (mais simplement les premières lettres mais en plus l'opérateur LIKE est insensible à la casse.

Le code du programme "recherche_espece_ok.py"

#nom programme : recherche_espece_ok.py
import sqlite3
connexion = sqlite3.connect("animaux.db")
curseur = connexion.cursor()
while True:
    choix = input("Tapez une partie du nom de l'espèce recherchée ou 'fin' : ")
    if choix =='fin' :
            break
    sql ="select code, nom_espece  from especes where nom_espece like ? "
    curseur.execute(sql, (choix+'%',))
    lignes =curseur.fetchall()
    for ligne in lignes:
            print(ligne[1], ligne[0])
connexion.close()

La seule différence entre les deux programmes est la requête paramétrée.
Dans (choix+'%',) n'oubliez la virgule avant de fermer la parenthèse.

Le rendu

Tapez une partie du nom de l'espèce recherchée ou 'fin' : crab
crabe chinois deca
Crabe vert deca
Crabe japonais deca
Crabe marbré deca
Tapez une partie du nom de l'espèce recherchée ou 'fin' :

Je saisis "crab" et j'obtiens toutes les lignes dont le contenu du champ "nom_espece" commence par "crab" que la lettre C soit en minuscule ou majuscule.

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

En dehors de DB Browser toute requête (requête INSERT ou DELETE) qui modifie les données de la base doit être confirmée par la commit() de l'objet référant la connexion.

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

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

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.
Vous trouverez dans le chapitre suivant un programme de saisie dans la table "especes" dans le cadre d'un formulaire.

Récupération de la base "animaux.db"

La base de données SQLite 'animaux.db'

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

Programme de recherche dans une table

Mauvaise solution

Le code

Le rendu

Solution correcte

Le code du programme "recherche_espece_ok.py"

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

Récupération de la base "animaux.db"