SQLite avec Python : Analyse de bases de données

Dans cet article, nous verrons comment utiliser sqlite3 pour créer, interroger et mettre à jour des bases de données SQLite Python. Nous verrons également comment simplifier le travail avec les bases de données SQLite en utilisant la librairie Pandas. J’utiliserai Python 3.7, mais cette même approche devrait fonctionner avec Python 2.

SQLite est un système de base de données qui simplifie le stockage et l’utilisation de données relationnelles.

Tout comme le format csv, SQLite stocke les données dans un seul fichier qui peut être facilement partagé. La plupart des langages de programmation et leurs environnements respectifs ont un bon support pour travailler avec des bases de données SQLite.
Python ne fait pas exception, une librairie pour accéder aux bases de données SQLite, appelée sqlite3, a été incluse avec Python depuis la version 2.5.

Exploration des données

Avant de commencer, jetons un coup d’œil aux données sur lesquelles nous allons travailler. Nous examinerons les données de vol des compagnies aériennes. Ces données contiennent des informations sur les compagnies aériennes, les aéroports et les routes entre les aéroports. Chaque itinéraire représente un vol répété qu’une compagnie aérienne effectue entre un aéroport de départ et un aéroport d’arrivée.

Toutes les données sont dans une base de données SQLite appelée flights.db, qui contient trois tables – airports, airlines et routes. Vous pouvez télécharger cette base de données ici.

airlines

Voici 2 lignes de la table airlines :

Comme vous pouvez le voir ci-dessus, chaque ligne représente une compagnie aérienne différente. Et chaque colonne est une propriété de cette compagnie aérienne, comme par exemple son nom et son pays. Chaque compagnie aérienne a aussi un identifiant unique (colonne id), ce qui nous permet de la consulter facilement lorsque nous en avons besoin.

airports

Voici 2 lignes de la table airports :

Comme vous pouvez le voir, chaque ligne correspond à un aéroport et contient des informations sur l’emplacement de l’aéroport. Chaque aéroport possède également un identifiant unique (id), ce qui nous permet de le consulter facilement.

routes

Voici 2 lignes de la table routes :

Chaque route contient un airline_id qui est l’ID de la compagnie aérienne desservant l’itinéraire, ainsi qu’un source_id qui correspond à l’ID de l’aéroport d’origine de l’itinéraire et enfin dest_id qui correspond à l’ID de l’aéroport de destination du vol.

Maintenant que nous connaissons le type de données avec lequel nous travaillons, commençons par nous connecter à la base de données et exécuter une requête.

Exécuter des requêtes sur une base de données SQLite avec Python

Pour travailler avec une base de données SQLite avec Python, nous devons d’abord nous y connecter. Nous pouvons le faire en utilisant la fonction connect, qui renvoie un objet Connection :

Une fois que nous avons un objet Connection, nous pouvons ensuite créer un objet Cursor . Les curseurs nous permettent d’exécuter des requêtes SQL sur une base de données:

Une fois que nous avons un objet Cursor, nous pouvons l’utiliser pour exécuter une requête sur la base de données avec la méthode execute. Le code ci-dessous va chercher les 5 premières lignes de la table airlines:

Vous avez peut-être remarqué que nous n’avons pas affecté le résultat de la requête ci-dessus à une variable. C’est parce que nous devons exécuter une autre commande pour aller chercher les résultats. Nous pouvons utiliser la méthode fetchall pour récupérer tous les résultats d’une requête:

Comme vous pouvez le voir, les résultats sont formatés comme une liste de tuples. Chaque ligne correspond à une ligne de la base de données flights.db. Traiter les données de cette façon est peu intuitive. Nous aurions besoin d’ajouter manuellement des en-têtes pour chaque colonne et d’analyser manuellement les données. Heureusement, la librairie Pandas a un moyen plus simple, que nous verrons dans la section suivante.

Avant de poursuivre, il est recommandé de fermer les objets Connection et Cursor ouverts. Cela empêche la base de données SQLite d’être verrouillée. Lorsqu’une base de données SQLite est verrouillée, il devient impossible de mettre à jour la base de données et parfois cela retourne des erreurs. Nous pouvons fermer le curseur et la connexion comme ceci:

Cartographie des aéroports

En utilisant nos nouvelles connaissances sur les requêtes, nous pouvons créer un graphique qui montrera où se trouvent tous les aéroports dans le monde. Tout d’abord, nous allons extraire les latitudes et les longitudes:

La requête ci-dessus va récupérer les colonnes latitude et longitude des aéroports de la table airports et les convertir en nombres flottants. Nous appelons ensuite la méthode fetchall pour les récupérer.

Nous devons ensuite configurer notre traçage en important matplotlib (la librairie principale de python pour tracer des graphiques). Combiné avec la librairie basemap, cela nous permet de créer des cartes uniquement en utilisant Python.

Nous devons d’abord importer les librairies:

Ensuite, nous configurons notre carte et dessinons les continents et les côtes qui formeront l’arrière-plan:

Enfin, nous tracerons les coordonnées de chaque aéroport sur la carte. Nous avons récupéré une liste de tuples à partir de la base de données SQLite. Le premier élément de chaque tuple est la longitude de l’aéroport et le second est sa latitude. Nous allons convertir les longitudes et les latitudes dans leurs propres listes puis les tracer sur la carte:

Nous obtenons une carte qui montre chaque aéroport dans le monde:

Comme vous l’avez peut-être remarqué, travailler avec des données de notre base SQLite est un peu pénible. Nous devions nous souvenir quelle position dans chaque ligne correspondait à quelle colonne de la base de données puis analyser manuellement les listes individuelles pour chaque colonne. Heureusement, la librairie Pandas nous donne un moyen plus facile de travailler avec les résultats des requêtes SQL.

Lecture des résultats dans un DataFrame Pandas

Nous pouvons utiliser la fonction read_sql_query (de la libraire Pandas) pour lire les résultats d’une requête SQL directement dans un DataFrame. Le code ci-dessous va exécuter la même requête que nous venons de faire, mais il retournera un DataFrame. Il a plusieurs avantages par rapport à la requête que nous avons faite ci-dessus:

• Cela ne nous oblige pas à créer un objet Cursor ou à appeler fetchall à la fin.
• Il lit automatiquement les noms des en-têtes de la table.
• Il crée un DataFrame, afin que nous puissions rapidement explorer les données.

Comme vous pouvez le voir, nous obtenons un DataFrame bien formaté en résultat. Nous pourrions facilement manipuler les colonnes comme ceci:

Je vous recommande fortement d’utiliser la fonction read_sql_query dès que possible.

Cartographie des itinéraires

Maintenant que nous savons comment lire les requêtes dans des DataFrames, nous pouvons créer une carte de chaque itinéraire aérien dans le monde. Nous commençons par interroger la base de données. La requête ci-dessous va:

• Obtenir la latitude et la longitude de l’aéroport source pour chaque itinéraire.
• Obtenir la latitude et la longitude de l’aéroport de destination pour chaque itinéraire.
• Convertir toutes les valeurs de coordonnées en nombres flottants.
• Lire les résultats dans un DataFrame et les stocker dans la variable routes.

Nous configurons ensuite notre carte:

Nous parcourons les 3000 premières lignes et les dessinons. Le code ci-dessous va:

• Parcourir les 3000 premières lignes de la variable routes.
• Déterminer si l’itinéraire est trop long.
• Si l’itinéraire n’est pas trop long:
  • Il va dessiner un cercle entre l’origine et la destination.

Nous obtenons avec la carte suivante:

Ce qui précède est beaucoup plus efficace lorsque nous utilisons la librairie Pandas pour transformer les résultats de la requête SQL en un DataFrame (au lieu de travailler avec les résultats bruts de sqlite3).

Maintenant que nous savons comment interroger les lignes de la base de données, passons à leur modification.

Modification des lignes de la base de données

Nous pouvons utiliser la librairie sqlite3 pour modifier une base de données SQLite en insérant, en mettant à jour ou en supprimant des lignes. La création de la connexion est la même chose que précédemment, nous allons donc ignorer cette partie.

Insérer des lignes avec Python

Pour insérer une ligne, nous devons écrire une requête INSERT. Le code ci-dessous va ajouter une nouvelle ligne à la table airlines. Nous spécifions 9 valeurs à insérer, une pour chaque colonne de la table airlines. Cela va ajouter une nouvelle ligne à la table.

Si vous essayez d’interroger la table maintenant, vous ne verrez pas la nouvelle ligne pour le moment. Au lieu de cela, vous verrez qu’un fichier a été créé, appelé flights.db-journal. flights.db-journal stocke la nouvelle ligne jusqu’à ce que vous soyez prêt à la valider (commit) dans la base de données principale flights.db.

SQLite n’écrit pas dans la base de données tant que vous n’avez pas validé une transaction. Une transaction consiste en une ou plusieurs requêtes qui effectuent toutes les modifications de la base de données à la fois. Ceci est conçu pour faciliter la récupération suite à des modifications ou des erreurs accidentelles. Les transactions vous permettent d’exécuter plusieurs requêtes, puis modifient finalement la base de données avec les résultats de chacune d’entre elles. Cela garantit que si l’une des requêtes échoue, la base de données n’est pas partiellement mise à jour (tout ou rien).

Exemple

Si vous avez deux tables, dont l’une contient des frais sur les comptes bancaires des personnes (frais), et une autre qui contient le montant en euros dans les comptes bancaires (soldes). Disons qu’un client de la banque, Thomas, veut envoyer 50 € à sa soeur, Emilie. Pour que cela fonctionne, la banque devrait:

• Créer une ligne de frais qui dit que 50 € est prélevé sur le compte de Thomas et envoyé à Emilie.
• Mettre à jour la ligne de Thomas dans la table des soldes et retirer 50 €.
• Mettre à jour la ligne d’Emilie dans la table des soldes et ajouter 50 €.

Cela nécessitera trois requêtes SQL distinctes pour mettre à jour toutes les tables. Si une requête échoue, nous serons bloqués avec de mauvaises données dans notre base de données. Par exemple, si les deux premières requêtes fonctionnent, et la troisième échoue, Thomas perdra son argent, mais Emilie ne l’obtiendra pas… Les transactions signifient que la base de données principale n’est pas mise à jour sauf si toutes les requêtes aboutissent. Cela empêche le système d’entrer dans un mauvais état où les clients perdent leur argent (dans le cas de problématiques bancaires).

Par défaut, sqlite3 ouvre une transaction lorsque vous faites une requête qui modifie la base de données. Vous pouvez en lire plus à ce sujet ici. Nous pouvons valider la transaction et ajouter notre nouvelle ligne à la table airlines, en utilisant la méthode commit:

Maintenant, lorsque nous interrogeons flights.db, nous verrons la ligne supplémentaire qui contient notre vol d’essai:

Passer des paramètres dans une requête

Dans la dernière requête, nous avons codé en dur les valeurs que nous voulions insérer dans la base de données. La plupart du temps, lorsque vous insérez des données dans une base de données, elles ne sont pas codées en dur, ce sont des valeurs dynamiques que vous voulez transmettre. Ces valeurs dynamiques peuvent provenir de données téléchargées ou provenir d’une entrée utilisateur (provenant d’un site web par exemple).

Lorsque vous travaillez avec des données dynamiques, il peut être tentant d’insérer des valeurs à l’aide de la mise en forme de chaîne Python:

Il faut absolument éviter de faire comme cela, la bonne méthode se trouve ci-dessous! L’insertion de valeurs avec la mise en forme de chaîne Python rend votre programme vulnérable aux attaques par injection SQL. Heureusement, sqlite3 a un moyen simple d’injecter des valeurs dynamiques sans compter sur le formatage des chaînes:

Toute valeur ‘?’ dans la requête sera remplacée par une valeur de values. Le premier ‘?’ sera remplacé par le premier élément dans values, le second par le second, et ainsi de suite jusqu’au dernier. Cela fonctionne pour n’importe quel type de requête. Cela a créé une requête SQLite paramétrée, ce qui évite les problèmes d’injection SQL.

Mise à jour de lignes

Nous pouvons modifier les lignes dans une table SQLite en utilisant la méthode execute (avec update):

Nous pouvons ensuite vérifier que la mise à jour s’est produite:

Suppression de lignes

Enfin, nous pouvons supprimer les lignes dans une base de données en utilisant la méthode execute (avec delete):

Nous pouvons ensuite vérifier que la suppression s’est produite, en vérifiant qu’aucune ligne ne correspond à notre requête:

Créer des tables

Nous pouvons créer des tables en exécutant une requête SQL. Nous pouvons créer une table pour représenter chaque vol quotidien sur un itinéraire, avec les colonnes suivantes:

• id – entier
• departure – date, lorsque le vol a quitté l’aéroport
• arrival – date, lorsque le vol est arrivé à destination
• number – text, le numéro de vol
• route_id – entier, l’identifiant de l’itinéraire sur lequel volait le vol

Une fois que nous avons créé une table, nous pouvons y insérer des données de façon classique:

Lorsque nous interrogeons la table, nous allons maintenant voir la ligne:

Créer des tables avec Pandas

Le librairie Pandas nous donne un moyen beaucoup plus rapide de créer des tables. Nous devons d’abord créer un DataFrame, puis l’exporter vers une table SQL. D’abord, nous allons créer un DataFrame:

Ensuite, nous serons en mesure d’appeler la méthode to_sql pour convertir df en une table dans une base de données. Nous fixons le paramètre keep_exists à la valeur « replace » pour supprimer et remplacer toutes les tables existantes nommées daily_flights:

Nous pouvons ensuite vérifier que tout a fonctionné en interrogeant la base de données:

Modifier les tables

L’une des parties les plus difficiles concernant le travail avec des cas réels de data science, c’est que les données que vous avez par enregistrement (dans cette base de données) changent souvent. En utilisant notre exemple de compagnie aérienne, nous pouvons décider d’ajouter un champ d’avions à la table des compagnies aériennes qui indique combien d’avions chaque compagnie aérienne possède (nombre qui peut évoluer au cours des mois). Heureusement, il existe un moyen de modifier une table pour ajouter des colonnes dans SQLite:

Notez que nous n’avons pas besoin d’appeler commit – les requêtes alter table sont immédiatement exécutées et ne sont pas placées dans une transaction. Nous pouvons maintenant interroger et voir la colonne supplémentaire:

Notez que toutes les colonnes sont définies sur null dans SQLite (qui se traduit par None en Python) car il n’y a pas encore de valeurs pour la colonne.

Modifier les tables avec Pandas

Il est également possible d’utiliser Pandas pour modifier les tables en exportant la table vers un DataFrame, en apportant des modifications au DataFrame, puis en exportant le DataFrame dans une table:

Le code ci-dessus ajoutera une colonne appelée delay_minutes à la table daily_flights.

Pour aller plus loin…sur SQLite avec Python

Vous devriez maintenant avoir une bonne compréhension de la façon de travailler avec des données dans une base de données SQLite en utilisant Python et Pandas. Nous avons couvert les requêtes pour extraire des données depuis une base de données, la mise à jour de lignes, l’insertion de lignes, la suppression de lignes, la création de tables et la modification de tables. Cela couvre toutes les opérations SQL majeures et presque tout ce que vous pourriez faire au quotidien.

Si vous souhaitez en savoir plus sur la façon de travailler avec Python et SQL, vous pouvez consulter le cours SQL de A à Z: Analyse de data ici.

Voici quelques ressources supplémentaires si vous voulez approfondir:

• Documentation sqlite3
• Comparer Pandas et SQL
• Guide d’opérations sqlite3

Enfin, si vous voulez continuer à pratiquer, vous pouvez télécharger le fichier que j’ai utilisé utilisé dans cet article, flights.db, ici.