Tuto Python : manipuler une base de donnée MySQL

Table des matières

Introduction

Dans ce Tutoriel, vous apprendrez comment installer MySQL, établir une connexion Jupyter notebook (python)/MySQL et interagir avec les deux systèmes en utilisant python et le langage SQL. Commençons donc par l’acronyme SQL et ce qu’il représente. Au niveau de base, SQL est une méthode de communication entre vous et une base de données. C’est le langage standard pour de nombreux systèmes de gestion de bases de données relationnelles. SQL est souvent utilisé pour interroger, insérer, mettre à jour et modifier des données. Beaucoup de commandes utilisées dans SQL sont assez faciles à interpréter par rapport à de nombreux autres langages informatiques. Cela rend SQL, en tant que langage, vraiment facile à comprendre et à apprendre, mais aussi très puissant. SQL est utilisé généralement pour:

Lire et récupérer des données (les données sont souvent stockées dans une base de données).
Ecrire des données dans une base de données (écrire des données dans une table et insérer de nouvelles données).
Mettre à jour des données.

MySQL est un système de gestion de bases de données relationnelles (SGBDR). Il fait partie des logiciels de gestion de base de données les plus utilisés au monde, autant par le grand public que par des professionnels. Le logiciel reste cependant entièrement gratuit et libre. Il ne faut donc pas confondre une base de données qui est un conteneur et le SGBDR qui est un logiciel de gestion de bases de données.

Ce tutoriel sera une base solide pour les gens qui souhaitent utiliser Python pour écrire des instructions SQL et ainsi interagir avec les bases de données gérées par MySQL.

Il suffit de vous concentrer et de bien suivre les différentes sections. Nous pensons que maintenant vous pouvez commencer ce tutoriel ! Allons-y.

1. Prérequis

1.1. Installer MySQL

L’installation de MySQL sur votre machine est très simple. Il suffit de télécharger un package MSI et valider quelques options. Dans ce tutoriel, l'installation est faite sous Windows.

Téléchargez le package MSI à partir d’ici. Les deux options de téléchargement sont une version web-community et une version complète. La version web-community ne téléchargera que le serveur, mais vous pouvez après télécharger d’autres applications. La version complète téléchargera le serveur et toutes les applications supplémentaires recommandées (la version utilisée dans ce tutoriel).

Exécutez le package MSI que vous avez téléchargé à partir de son emplacement sur votre ordinateur et acceptez le contrat de licence, puis cliquez sur suivant.
Ensuite, il faut déterminer le type de configuration que vous souhaitez utiliser pour l’installation. Si vous voulez créer votre propre base de données à partir de zéro, il vaut mieux utiliser la configurationDeveloper Default.

Installez le serveur SQL, le Workbench et les applications supplémentaires que vous avez sélectionnés. Par la suite, vaut serez amené à saisir un mot de passe qui sera utile après pour accéder au serveur.

Une fois l'installation terminé, ouvrez votre Workbench et accédez a votre connexion MySQL. Assurez-vous que votre serveur SQL est activé.

1.2. Établir la connexion Python/ MySQL

Dans ce tutoriel, nous utiliserons Jupyter Notebook comme un IDE SQL. C'est-à-dire que vous serez en mesure d’exécuter n’importe quelle requête MySQL à travers le Jupyter notebook. Pour commencer, vous devez installer des bibliothèques pour exécuter SQL directement dans le Jupyter notebook:

Syntaxe :

!pip install ipython-sql
!pip install mysql-connector-python

Maintenant, nous allons utiliser la bibliothèque sqlalchemy pour créer une connexion à la base de données MySQL :

Syntaxe :

import sqlalchemy
sqlalchemy.create_engine('mysql+mysqlconnector://root:mdp@localhost :3306')

Ici, root représente le nom d’utilisateur, mdp c’est le mot de passe que vous avez saisis lors de l’installation et avec lequel vous avez accéder au serveur MySQL et localhost:3306 représente l’hôte local connecté au port 3306 (vous pouvez utiliser l’adresse IP local 127.0.0.1 à la place de localhost).

Nous pouvons maintenant charger le module SQL précédemment installé :

Syntaxe :

%load_ext sql

Finalement, vérifiez votre connexion avec la commande suivante :

%sql mysql + mysqlconnector://root:mdp@localhost:3306

Remarque : Si vous utilisez un Jupyter notebook non local comme l’environnement Colab de Google, la dernière commande produira une erreur :

Pour contourner cette erreur, il faut télécharger ngrok :

Une fois installé, accédez a l’invite de commandes de votre ordinateur et taper la commande ngrok tcp 3306, ce qui permet de générer une adresse IP accessible depuis internet :

Tout ce qui reste à faire est de remplacer localhost :3306 dans les lignes de codes précédentes par l’adresse générée par ngrok, ici, par exemple, c’est 2.tcp.ngrok.io :16826 (l’adresse change d’une connexion à une autre).

Syntaxe :

%sql mysql + mysqlconnector://root:[email protected]:16826

Si vous avez en sortie connected : root@None, cela signifie que tout s’est bien passé et vous êtes prêt pour la suite (pour ceux qui travaille sur un notebook local, vous n’aurez pas besoin de ngrok, utilisez directement localhost :3306 pour accéder à votre base de données).

2. SQL-syntaxe avec Python

Maintenant, après avoir établi la connexion avec le serveur MySQL, c’est juste une question de préfixe de code avec %%sql ou %sql pour pouvoir exécuter une requête SQL dans une cellule de Juypter notebook.

Commençons d’abord par vérifier si une base de données existe en montrant toutes les bases de données de votre système à l’aide de l’instruction > :

Syntaxe :

%%sql
SHOW DATABASES

Résultat d’exécution:

Remarquez le préfixe %%sql avant l’instruction SQL qui est nécessaire pour exécuter la commande. Maintenant, créons notre propre base de données « mydata ». Pour la créer, utilisez l’instruction > :

Syntaxe :

%%sql
CREATE SCHEMA mydata

Vérifions si notre base de données est bien créer avec l’instruction SHOW DATABASES >>.

Résultat d’exécution :

Vous pouvez aussi vérifier si votre base de données existe au niveau de votre Workbench (suffit de l’actualiser) :

Pour pouvoir créer des tables et les manipuler en utilisant différent SQL syntaxe, faut d’abord se connecter à une base de données (pour accéder à la base de données mydata en rajoute /mydata) :

Syntaxe :

sqlalchemy.create_engine('mysql+mysqlconnector://root:mdp@localhost:3306/mydata')
%sql mysql + mysqlconnector://root:mdp@localhost:3306/mydata

Si vous avez bien suivi toutes les instructions, en sortie vous deverez avoir connected : root@mydata>.

2.1. Créer une table de données MySQL

La possibilité de créer des tables et de stocker des données en eux est vraiment bénéfique pour chaque personne qui manipule les données. Il est donc important d’avoir une compréhension de base de la façon dont cela fonctionne. Pour créer une table de données, il y a une instruction appelée >. Essayons de créer une table que nous appellerons Livres (pour gérer les données d’une bibliothèque par exemple),et qui contient le nom du livre, nombre de pages, le stock (nombre de copies existant) et le prix. L’instruction SQL est la suivante :

Syntaxe :

%%sql
CREATE TABLE IF NOT EXISTS Livres(
Id int(6) PRIMARY KEY,
nom varchar(100) DEFAULT NULL,
pages int(10) DEFAULT NULL,
stock int(4) DEFAULT NULL,
prix float(8.2) DEFAULT NULL)

L’instruction > permet de créer la table de données seulement s’il n’existe pas dans la base de données. Après, nous rajoutons le nom de la table (Livres) et ensuite entre parenthèses, nous allons séparer la liste des colonnes que nous allons créer par une virgule. Dans cet exemple, nous avons les colonnes Id, nom, pages, stock et prix. Après le nom de la colonne, il faut rajouter le type de données utilisé (par exemple pour la colonne Id c’est un entier à 6 chiffres maximum). Ensuite, la définition de certaines spécifications autour de chaque colonne (par exemple Id de livre est la clé principale dans cet exemple).

Vous pouvez vérifiez si la table est bien créer avec l’instruction > :

Syntaxe :

%%sql
SHOW TABLES

Résultat d’exécution:

2.2. Insérer des données

Maintenant, après avoir créé notre table de données MySQL, nous pouvons insérer nos propres données. Pour remplir une table dans MySQL, utilisez l’instruction > :

Syntaxe :

%%sql
INSERT INTO Livres VALUES (1 , "Les miserables" , 300 , 100 , 100.23) ;
INSERT INTO Livres VALUES (2 , "Le petit prince" , 200 , 20 , 65.0) ;
INSERT INTO Livres VALUES (3 , "Le rouge et le noir" , 420 , 10 , 200.5) ;
INSERT INTO Livres VALUES (4 , "Vingt mille lieues sous les mers" , 1100 , 1 , 500.0) ;
INSERT INTO Livres VALUES (5 , "Le seigneur des anneaux", 2150 , 15 , 150.36) ;
INSERT INTO Livres VALUES (6 , "Les trois mousquetaires" , 360 , 26 , 186.2) ;
INSERT INTO Livres VALUES (7 , "Le petit prince", 200 , 20 , 65.0)

Vous remarquez que l’instruction SQL commence par INSERT INTO suivi par le nom de la table de données, après VALUES suivi des données que vous voulez insérez. Vous remarquerez aussi la présence d’un point virgule, qui est obligatoire pour séparer les instructions SQL.Essayez d’insérer par vous-même plusieurs données et remplissez votre table de données MySQL.

2.3. Manipuler les données

Maintenant que vous avez inséré vos propres données, il faut savoir comment les manipuler avec SQL. Y’en a plusieurs instructions pour manipuler les tables de données, nous allons voir quelques-unes.

2.3.1. SELECT

Pour sélectionner une table de données depuis MySQL et l’afficher, nous allons utilisez l’instruction > :

Syntaxe :

%% sql
SELECT * FROM Livres

Résultat d’exécution :

L’instruction SQL commence par SELECT suivi de *, puis FROM suivi du nom de la table. Pour sélectionner uniquement quelques colonnes d’une table MySQL, utilisez l’instruction « SELECT » suivie des noms des colonnes :

Syntaxe :

%%sql
SELECT nom , prix FROM Livres

Résultat d’exécution:

Vous remarquez que dans les noms des livres, le petit prince est répété deux fois. Si vous voulez afficher les livres existants sans répétions, vous devez précédez le nom de la colonne (ici nom) par l’instruction DISTINCT :

Syntaxe :

%%sql
SELECT DISTINCT nom FROM Livres

Résultat d’exécution:

Supposant que vous voulez afficher les deux colonnes nom et prix depuis la table livres comme celle d’avant, mais cette fois vous voulez affichez les noms des livres sans répétions. Utilisez l’instruction suivante et vérifiez par vous-même le résultat.

Syntaxe :

%%sql
SELECT DISTINCT nom, prix FROM Livres

2.3.2. WHERE

L’instruction WHERE >> est utilisée pour filtrer la sélection à partir d’une table de données. Elle est généralement suivie par une ou plusieurs conditions. À partir de la table Livres, nous désirons filtrer les noms des livres et afficher que les livres ayant un prix inférieur à 100 :

Syntaxe :

%%sql
SELECT DISTINCT nom FROM livres WHERE prix

Résultat d’exécution:

Pour éviter la répétions, vous pouvez utiliser l’instruction DISTINCT vu précédemment.

2.3.2. ORDER BY

ORDER BY >> est une instruction qui permet d’ordonner les données sélectionnées à partir d’une table MySQL selon la façon souhaitée. Dans notre exemple, pour sélectionner le nom et le prix des livres et les afficher du prix le plus bas au plus grand, nous utiliserons l’instruction suivante :

Syntaxe :

%%sql
SELECT DISTINCT nom, prix FROM livres ORDER BY prix

Résultat d’exécution:

Pour changer l’ordre (ordre décroissant), vous devez rajouter l’instruction DESC. Si vous voulez afficher par exemple, toute la table de données Livres mais cette fois avec un ordre de stock décroissant, utilisez l’instruction suivante :

Syntaxe :

%%sql
SELECT * FROM livres ORDER BY stock DESC

Résultat d’exécution:

2.3.4. UPDATE

Vous pouvez mettre à jour les informations existantes dans une table de données MySQL à l’aide de l’instruction UPDATE >. Dans notre table de données Livres, nous souhaitons changer le prix de quelques livres :

Syntaxe :

%%sql
UPDATE Livres SET prix=120.0 WHERE nom="Les miserables" OR nom="Le seigneur des anneaux"

Nous avons changé le prix de deux livres à 120. Vous pouvez affichez la table de données et voir le changement.

2.3.5. DELETE

Vous pouvez supprimer des données d’une table MySQL à l’aide de l’instruction DELETE >>. Supposant que dans la table de données Livres vous souhaitez supprimer un livre (exemple: Vingt mille lieux sous la mer, car il y’a un seul exemplaire et vous l’avez vendu) :

Syntaxe :

%%sql
DELETE FROM Livres WHERE nom='Vingt mille lieues sous les mers'

Vous pouvez toujours afficher la table de données et voir le changement effectué.

2.4. Stocker des variables

Pour gérer les instructions SQL avec Jupyter Notebook, vous n’êtes pas limité aux instructions multi lignes qui commencent avec %%sql. Par exemple, vous pouvez stocker le résultat d’une instruction SQL sur une variable en utilisant %sql. Voyons cela en action : supposant que vous voulez enregistrer les livres à commander (par exemple les livres avec un stock inférieur à 50) dans une variable Python pour l’utiliser après. L’instruction est la suivante :

Syntaxe :

commande=%sql SELECT DISTINCT nom FROM Livres WHERE stockprint(commande)

Résultat d’exécution:

Et vous pouvez maintenant accéder aux données. Par exemple, si vous voulez enregistrer les données dans une liste, l’instruction est la suivante :

Syntaxe :

Commande_list = [ commande[0][0] , commande[1][0] , commande[2][0] , commande[3][0] ]

2.5. Transformer une table MySQL en Pandas dataframe

Vous pouvez transformer votre table de données MySQL en pandas dataframe, pour faire l’analyse de données avec python, en deux lignes de codes (une fonctionnalité supplémentaire fournie par le module ipython-sql). Pour transformer la table de données Livres déjà créée, en pandas dataframe,il faut exécuter l’instruction suivante :

Syntaxe :

result = %sql SELECT * FROM livres
df = result.DataFrame()
df.head()

Résultat d’exécution:

3. Exercices

Pour les exercices, on va se baser sur la table de données précédente.

3.1. Exercice 1

Une nouvelle commande de livres vient d’arriver, elle contient :

200 exemplaires d’un nouveau livre : Le comte de Monte-Cristo qui contient 550 pages et qui vaut 200 Dh.
100 exemplaires du livre : Les Trois mousquetaires.

Question : Mettre à jour la base de données.

3.2. Exercice 2

Question 1 : Supprimer les répétitions de la table de données Livres.

Question 2 : Calculer le nombre total (stock total) de livres qui existe dans la bibliothèque et stocker le résultat dans une variable python. (Utiliser SUM() qui retourne la somme totale d’une colonne numérique)

4. Solution des exercices:

4.1. Exercice 1

Syntaxe :

%%sql
INSERT INTO Livres VALUES ( 8 , "Le comte de Monte-Cristo" , 550 , 200 , 200 ) ;
UPDATE Livres SET stock=126 WHERE nom="Les trois mousquetaires"
%%sql
SELECT * FROM Livres

Résultat d’exécution:

4.2. Exercice 2

Solution :

Question 1 :

%%sql
DELETE FROM Livres WHERE Id=7

Question 2 :

Stock total :

%%sql
SELECT SUM(stock) FROM Livres

Résultat d’exécution:

Stocker le résultat dans une variable python:

stock_total = %sql SELECT SUM(stock) FROM Livres
stock_total = stock_total[0][0]

Conclusion

Nous somme arrivé à la fin de ce tutoriel. J’espère que vous avez réussi à obtenir quelque chose d’utile et que vous avez apprécié la simplicité d’effectuer des instructions SQL dans ce format.

Cependant, il y a encore trop des chose à apprendre, mais vous êtes sur la bonne voie.

Merci d’avoir lu et bon courage pour la suite.

Article publié le 08 Novembre 2020par Sami Nadif