Tutoriel Python - sommaire

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

Dans ce chapitre je vous montre comment manipuler une base SQLite via Python (en mode commmande & en mode programmé).

SQLite est le SGBDR privilégié dans l'univers Python. Ainsi le module sqlite3 est inclus dans la bibliothèque standard de Python (donc pas d'installation par PIP) alors qu'avant de manipuler une base MYSQL via Python il faudra au préalable installer la bibliothèque mysql-connector-python.

La base de données utilisée

C'est la même que dans le chapitre précédent : "animaux.db".

Je rappelle que cette base comprend deux tables : "taxons" (table mère) et "especes" (table fille).
• champs de la table "especes" : id, nom_espece, code
• champs de la table "taxons" : code,nom_taxon

La colonne "especes.code" est clé étrangère par rapport à "taxons.code" qui est clé primaire.
On ne peut donc saisir dans la colonne "especes.code" qu'une valeur qui n'existe déjà dans la colonne "taxons.code" ; "une fille doit avoir une mère" ou contrainte d'intégrité référentielle.

Manipuler la base à partir de la console

Commençons par des manipulations de cette base dans la console.

Lire la table "taxons"

>>> import os
>>> os.getcwd()
'C:\\Users\\darch'
>>> 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')
>>>

La base de données "animaux.db" est stockée dans le dossier "c:\python_prog".
Or le répertoire par défaut pour Python Python est celui où ce langage est installé.
os.getcwd() retourne le répertoire courant.
os.chdir("c:/python_prog") : le répertoire courant devient "c:/python_prog".
Pour manipuler une base de données SQLite via Python, il faut d'abord importer le module "sqlite3".

Les méthodes sont appliquées à un objet de type "connexion" OU à un objet de type "cursor" (sous objet de "connexion").
Il faut ensuite se connecter à une base de données avec la méthode connect() de ce module et donc créer un objet de type connection.
Ensuite il faut créer un objet de type cursor() (nommé ici "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" toutes les données produites par la requête.
for resultat in curseur : l'objet de type "cursor" est itérable.
Chaque enregistrement est retourné sous forme d'un tuple.

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

On peut appliquer à un objet de type "cursor" différentes méthodes :
• fetchone() : retourne un enregistrement
• fetchall() : retourne tous les enregistrements
• fetchmany(n) : retourne N enregistrements

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 as tcode, nom_taxon FROM especes INNER JOIN taxons ON especes.code = tcode
Donc les colonnes affichées sont : nom_espece, tcode, nom_taxon qui ont respectivement les indices 0,1 et 2.

La méthode fetchall()

Cette méthodé appliquée au curseur retourne toutes les lignes de la requête sous forme de tuples.
Pour se débarrasser des parenthèses, virgules, guillemets découlant du tuple, il suffit de manipuler directement les champs de la source.

...
>>> 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 retourne un nombre de lignes correspondant à la valeur passée en paramètre.

...
>>> 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 sous forme d'une liste de tuples.

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

Il y a 6 espèces de biva(lves) et 2 espèces de cart(ilagineux).

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

Manipuler une BD SQLite dans le cadre d'un programme

Manipuler une base de données en mode console, c'est pas forcément très pratique ...
Il faut mieux envisager un programme surtout si le traitement est régulier.

Programme pour parcourir une vue

Je vous propose tout d'abord un programme qui parcourt la vue "vue1".

Rappel de "vue1"

CREATE VIEW "vue1" AS SELECT nom_espece, taxons.code as tcode, nom_taxon FROM especes 
INNER JOIN taxons ON especes.code = tcode

J'ai donné un pseudo à la colonne "taxons.code".

Le programme

#nom programme : lire_vue1.py
import sqlite3
import os
courant = os.getcwd()
print("Répertoire courant : ", courant)
print("------------")
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() 

En mode programmé le répertoire courant est celui qui contient le programme donc pour moi : C:\python_prog. Or la base de données "animaux.db" est aussi stockée dans ce dossier d'où l'instruction connexion = sqlite3.connect("animaux.db")
Le chemin vers la base se résume au nom de celle-ci.

Le rendu

Répertoire courant :  C:\python_prog
----------------
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
...

Les espèces apparaissent dans l'ordre de saisie dans la table.
Pour une liste triée par ordre alphabétique il faudrait modifier la vue : rajouter un tri : ORDER BY nom_espece.

Programme pour parcourir une table

Je vous propose maintenant un programme qui lit non pas une table virtuelle (ou vue) mais une table réelle et en l'occurence 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 permet d'utiliser les noms de champs à la place de leur indice.
Ainsi nous pouvons écrire plus loin dans le programme :
print(ligne['id'], ligne['nom_espece'], ligne['code'])
Pratique !

Le rendu

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

Cette fois l'affichage des données est propre.
Si vous voulez un affichage par ordre alphabétique, il suffit de modifier le code de l'instruction SQL intégrée dans ce script.

Programmes de recherche dans une table

Une table peut contenir des milliers de lignes. La recherche séquentielle d'une ligne n'est alors plus envisageable.

Il faut envisager un programme qui permet d'effectuer des recherches dans la table "especes" sur la colonne "nom_espece" et retourne les enregistrements correspondants.

Première version

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.upper() =='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.

La version ci-dessus 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" (avec un C majuscule).

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' : Fin 

Je dois m'y reprendre à trois fois avant d'accéder au bon enregistrement ...
Donc ce script n'est pas ergonomique (délicat à utiliser).

Version plus ergonomique

Oubliez le script précédent ; une requête paramétrée basée sur l'opérateur LIKE offre 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.

Code de la version ergonomique

#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.upper() =='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 avec le programme précédent est l'instruction : curseur.execute(sql, (choix+'%',))
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' : Fin

J'ai saisi "crab" et j'obtiens toutes les lignes dont le contenu du champ "nom_espece" commence par "crab" en minuscules ou majuscules.
J'ai saisi "Fin" et pourtant je sors quand même de la boucle ...

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' ")
sqlite3.Cursor object at 0x0000023687E4E3B0>
>>> connexion.commit()

Toute requête qui modifie le contenu de la base doit être confirmée par la commande 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()

Ici je vous montre comment intégrer des requêtes INSERT dans un programme Python.
La variable un_poisson référence 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 référence 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".
Le rendu :

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

Six enregistrements ont bien été ajoutés.

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

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

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

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

Il serait préférable :
• que les saisies se fasse au travers de grilles
• que la saisie dans le champ "especes.code" se fasse via une liste déroulante ; liste ayant pour source la table "taxons"
• qu'il n'y ait plus d'affichage dans le shell mais dans des états

Sachez que tout cela est possible à condition d'utiliser le module tkinter qui permet de produire une belle interface graphique.
Vous pouvez aussi créer une application web (avec le framework Flask).

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