¶ Intégration du langage SQL dans un programme Python
Dans ce TP, vous allez découvrir comment interroger une base de données à l'aide d'un programme écrit en python.
Dans ce cas, le SGBD joue le rôle d'un serveur (qui répond à des demandes) et le programme python jour le rôle du client (qui effectue des demandes).
La plupart des langages (le Cobol, le langage C, le langage Java...) permettent d'utiliser des requêtes SQL des programme afin d'importer ou d'exporter des données et les enregistrer de façon persistante au sein d'une base de données.
¶ Connexion à la base de données depuis un programme
Pour intéragir avec une base de données, nous utiliserons le module python sqlite3.
import sqlite3
La méthode connect(nom_du_fichier.db) du module permet de se connecter à la base. Elle prend en paramètre le chemin vers la base de données et retourne un objet de type Connection.
nom_du_fichier = 'tp3.db'
connection = sqlite3.connect(nom_du_fichier)
Une fois la connexion établie, on peut créer un objet de type Cursor grâce à la méthode cursor() de notre objet Connection.
cursor = connection.cursor()
C’est à l’aide de cet objet cursor que l’on pourra exécuter des requêtes SQL. Pour terminer notre programme, il ne faut pas oublier de fermer la connexion grâce à la méthode close().
Voici ci-dessous le code minimal pour dialoguer avec une base de données avec le langage python.
import sqlite3
nom_du_fichier = 'tp3.db'
connection = sqlite3.connect(nom_du_fichier)
cursor = connection.cursor()
# Code pour effectuer des requêtes SQL
cursor.close()
connection.close()
¶ L’instruction SELECT
Un objet Cursor dispose d'une méthode execute(requete_sql) qui prend en paramètre une chaîne de caractères représentant une requête SQL.
Dans le cas d’une sélection de données, il faut récupérer le résultat de la requête pour pouvoir le traiter dans le programme python. Chaque ligne de la table ou de la combinaison de tables interrogée(s) est représentée sous la forme d'un tuple.
¶ Méthode 1
On peut récupérer la réponse de la requête ligne par ligne grâce à la méthode fetchone() de l’objet Cursor. Une fois qu'un premier tuple est extrait, cette méthode renvoie le tuple suivant.
Si toutes les lignes ont été lues ou que la requête ne renvoie pas de ligne, None est renvoyé.
cursor.execute("SELECT * FROM personne;")
first_personne = cursor.fetchone() # Récupère le 1er enregistrement
second_personne = cursor.fetchone() # récupère le 2e enregistrement
cursor.execute("SELECT * FROM personne WHERE prenom=?;", ('Bob',))
bob = cursor.fetchone() # Récupère le 1er enregistrement
¶ Méthode 2
On peut récupérer un nombre défini d’enregistrements grâce à la méthode fetchmany(). Le résultat est alors une liste de tuples de taille correspondant au paramètre de la fonction.
cursor.execute("SELECT * FROM personne;")
trois_personnes = cursor.fetchmany(3) # récupère les 3 premières lignes
for personne in trois_personnes:
print(personne[0], personne[1]) # Affiche le contenu de chaque tuple
¶ Méthode 3
On peut récupérer tous les résultats grâce la méthode fetchall(). Le résultat est une liste de tuples, qui peut être vide si aucun résultat n'est retourné.
cursor.execute("SELECT * FROM personne;")
personnes = cursor.fetchall() # Récupère toutes les lignes
for personne in personnes:
print(personne[0], personne[1]) #Affiche le contenu de chaque tuple
¶ Les instructions CREATE, INSERT, UPDATE, DELETE
Le fonctionnement est identique qu’avec l’instruction SELECT. Dans ce cas, aucune donnée ne va être renvoyée. L’utilisation des méthodes fetch() n’est pas nécessaire.
Néanmoins, à la fin de vos requêtes, il faut utiliser la méthode commit() de l’objet Connection qui permettra de mettre à jour l’état de la base après modification, suppression ou ajout.
Sans appel à cette méthode, une autre connexion à la base de données ne verra pas vos modifications.
¶ Insertion de données
cursor.execute("CREATE TABLE personne (nom TEXT, prenom TEXT, age INTEGER);")
nom = 'Dupont'
prenom='Paul'
age = 72
cursor.execute("INSERT INTO personne VALUES (?, ?, ?);", (nom, prenom, age))
connection.commit()
On peut aussi insérer plusieurs enregistrements.
liste_personnes = [('Durand', 'Sarah', 45),
('Cohen', 'Pauline', 13),
('Lisso', 'Marcel', 26) ]
cursor.executemany("INSERT INTO personne VALUES (?,?,?);", liste_personnes)
connection.commit()
La notation
VALUES(?,?,?)correspond à des requêtes préparés ou requêtes paramétrables. Elle protègent contre les injections SQL que vous pourrez découvrir dans l'activité 5.
¶ Modification de données
nom1='Henry'
cur.execute("UPDATE personne SET nom=?, prenom=?, age=? where nom=?;", (nom1,
prenom, age, nom))
connection.commit()
¶ Suppression de données
cur.execute("DELETE FROM personne WHERE nom=?;", (nom1,))
connection.commit()
¶ Travail à faire
Dans la suite de ce TP, nous allons utiliser la base de données bdd-python-sql.db utilisée dans le TP précédant avec quelques informations supplémentaires dans la table Festivals.
Question 1 - Dans un nouveau fichier Python, écrire un programme permettant de vous connecter à la base de données.
Question 2 - Essayer votre programme en affichant l'ensemble des informations du festival « Defqon 1».
Question 3 - Ajouter un nouveau festival selon les données ci-dessous :
nom : Igloofestdate_debut : 13/01/21date_fin : 5/02/2021pos_lat : 45.5016889pos_long : -73.567256ville : Montréalid_genre = Généraliste (id=1)id_pays : Canada (id=4)
Question 4 - Écrire une classe Festival ayant les mêmes attributs que la table Festival.
Question 5 - Écrire la fonction get_all_festivals. Cette fonction effectuera une requête SQL pour récupérer tous les festivals et retournera une liste d’objet Festival.
Question 6 - Écrire la fonction graphique_lieu_festival. Cette fonction récupéra le nombre de festivals pour chaque pays grâce à une requête SQL et affichera un diagramme « camembert » des résultats.
Pour générer des digrammes, vous pouvez utiliser le module pyplot du module matplotlib.
- La fonction
plt.piepermet de dessiner des graphiques circulaires.
Question 6 - Écrire la fonction graphique_age_participant. Cette fonction récupéra le nombre de participants selon leur âge grâce à une requête SQL et affichera un diagramme « en barre » des résultats.
Pour générer des digrammes, vous pouvez utiliser le module pyplot du module matplotlib.
- La fonction
plt.barpermet de dessiner des diagrammes en barres.
Question 7 - Écrire la fonction graphique_genre_festival. Cette fonction récupéra le nombre de festivals pour chaque genre de musique grâce à une requête SQL et affichera un diagramme « camembert » des résultats.
Pour générer des digrammes, vous pouvez utiliser le module pyplot du module matplotlib.
- La fonction
plt.piepermet de dessiner des graphiques circulaires.
Question 8 - Écrire la fonction carte_festival(). Cette fonction génère une carte qui affiche le lieu de chaque festival grâce à sa localisation. Vous pouvez utiliser le mode folium pour générer des cartes.
- Ajouter des couleurs au pointeur pour indiquer le genre de festival (rouge=généraliste, vert=électronique…)
