Interagir avec une base de données SQLite3 depuis Python
I. Présentation générale
Pour interagir avec une base de données au format SQLite3 depuis Python, il est nécessaire d'utiliser le module sqlite3. Ensuite, il faut effectuer un certain nombre d'étapes :
- Se connecter à la base de données ;
- Exécuter la requête ;
- Éventuellement, exploiter le résultat de la requête ;
- Si la base a été modifiée, valider les changements ;
- Se déconnecter de la base
Nous allons maintenant étudier ces différentes parties dans le désordre.
II. Connexion et déconnexion
Pour une base SQLite3, ces 2 étapes sont très simples :
- Connexion
connexion = sqlite3.connect('NomFichierBase.db')La base sera en effet stockée dans un fichier dont le nom est suffixé par ".db"
- Déconnexion
connexion.close()
III. Valider les changements
Les bases de données sont des systèmes transactionnels. Cela signifie qu'un programme qui interagit avec une base de données fait ses modifications en mémoire, jusqu'à ce que celles-ci soient validées. Lors de cette validation, si les modifications sont toujours possibles (ce n'est pas forcément le cas, si un autre utilisateur a modifié la base entre temps), elles sont toutes transférées dans la base de données sur le disque. Sinon, aucune n'est effectuée. C'est cette validation par paquet qui constitue une transaction. En Python, les transactions sont validées en appelant la méthode commit() sur l'objet connexion. Chaque commit termine la transaction précédente, puis en commence une autre. La première transaction est initiée lors de la connexion à la base de données.
Bien sûr, si aucune modification n'a été apportée à la base, la phase de validation n'est pas nécessaire. Dans le cas contraire, on valide nos modifications ainsi :
connexion.commit()
IV. Exécuter la requête et exploiter ses résultats
A. Exécuter une requête
Pour exécuter une requête, il faut la passer en paramètre de la méthode execute de l'objet connexion.
1. Création d'une table
Voici par exemple le code d'un programme Python permettant de créer la table Editeur :
import sqlite3
#connexion à la base
connexion = sqlite3.connect('ludotheque.db')
#activation de la vérification des contraintes de clé étrangère
connexion.execute('PRAGMA foreign_keys = ON')
#suppression éventuelle de l'ancienne table
connexion.execute('DROP TABLE IF EXISTS editeur')
#creation de la table Editeur
connexion.execute('''CREATE TABLE editeur (
idEditeur int primary key not null,
nomEditeur text not null,
nationaliteEditeur text)''')
#validation
connexion.commit()
#déconnexion
connexion.close()
2. Ajout de données dans une table
Pour ajouter des données dans une table, on peut utiliser la méthode execute() présentée précédemment :
import sqlite3
connexion = sqlite3.connect('ludotheque.db')
connexion.execute('PRAGMA foreign_keys = ON')
connexion.execute('''INSERT INTO editeur
VALUES(1, 'Days of wonder', 'Française')''')
connexion.commit()
connexion.close()
Commencez par exécuter le programme précédent en mettant en commentaire le commit et vérifiez le contenu de la table (avec le programme sqlite3 par exemple) : vous constaterez que le tuple n'a pas été ajouté. Recommencez ensuite en décommentant le commit
Lorsqu'on désire insérer plusieurs données dans une table, on peut utiliser avantageusement la méthode executemany : Celle-ci prend 2 paramètres : une requête paramétrée (des '?' signalent les zone paramétrées) et une liste de tuples : la requête est exécutée pour chaque tuple de la liste, en remplaçant le i-ème point d'interrogation par la i-ème composante du tuple. Exemple :
import sqlite3
connexion = sqlite3.connect('ludotheque.db')
connexion.execute('PRAGMA foreign_keys = ON')
listeEditeurs = [
(2, 'EggertSiele', 'Allemande'),
(3, 'Iello', 'Française')
]
connexion.executemany("INSERT INTO editeur VALUES(?, ?, ?)", listeEditeurs)
connexion.commit()
connexion.close()
B. Exploiter les résultats
Lorsqu'on effectue une requête d'interrogation, c'est a priori pour récupérer les résultats ! Il se trouve que la méthode execute présentée précédemment renvoie un objet cursor qui est itérable. On peut donc aisément parcourir les résutats d'une requête ainsi :
import sqlite3
connexion = sqlite3.connect('ludotheque.db')
connexion.execute('PRAGMA foreign_keys = ON')
curseur = connexion.execute("SELECT * FROM Editeur")
for tuple in curseur:
print(tuple)
connexion.close()
Pour récupérer les valeurs pour les différents champs de chaque tuple, on peut les convertir en liste ainsi :
import sqlite3
connexion = sqlite3.connect('ludotheque.db')
connexion.execute('PRAGMA foreign_keys = ON')
curseur = connexion.execute("SELECT * FROM Editeur")
for tuple in curseur:
donnee = list(tuple)
print(donnee[1])
connexion.close()
Bien sûr, dans un tel cas, comme seuls les noms des éditeur importent, il es préférable d'avoir une requête avec une projection plus sélective :
import sqlite3
connexion = sqlite3.connect('ludotheque.db')
connexion.execute('PRAGMA foreign_keys = ON')
curseur = connexion.execute("SELECT nomEditeur FROM Editeur")
for tuple in curseur:
donnee = list(tuple)
print(donnee[0])
connexion.close()
C. Risque d'injection et requêtes paramétrées
Une requête simple peut également être paramétrée, comme une requête multiple. Voici un exemple d'un programme permettant de faire une recherche des éditeurs par nationalité :
import sqlite3
connexion = sqlite3.connect('ludotheque.db')
connexion.execute('PRAGMA foreign_keys = ON')
nationalite = input("Nationalité recherchée ? ")
curseur = connexion.execute('''SELECT nomEditeur
FROM editeur
WHERE upper(nationaliteEditeur) = upper(?)
ORDER BY nomEditeur''',
(nationalite,))
for tuple in curseur:
print(list(tuple)[0])
connexion.close()
Lorqu'une requête est paramétrée par une information issue (directement ou indirectement) de l'utilisateur, il est hautement préférable d'utiliser les requêtes paramétrées plutôt que la concaténation de chaînes de Python pour éviter des attaques par Injection SQL (voir la page wikipedia sur le sujet).