Cours
Manipulation de données en SQL
4 h
335.1K
Qu’est-ce que SQLite ?
Si vous connaissez les systèmes de bases de données relationnelles, vous avez sans doute entendu parler de poids lourds comme MySQL, SQL Server ou PostgreSQL. SQLite est toutefois un autre SGBDR très utile, extrêmement simple à installer et à utiliser, qui offre de nombreux atouts par rapport à d’autres bases relationnelles. Parmi ses caractéristiques :
- Pas de serveur nécessaire : aucun processus serveur à démarrer, arrêter ou configurer. Pas besoin d’administrateurs de bases de données pour gérer des instances ou des permissions d’accès utilisateurs.
- Fichiers de base simples : une base SQLite est un unique fichier ordinaire sur disque, stockable dans n’importe quel répertoire. Le partage est également simple, car il suffit de copier le fichier sur une clé USB ou de l’envoyer par e‑mail.
- Typage manifeste : la plupart des moteurs SQL s’appuient sur un typage statique, c’est‑à‑dire que chaque colonne ne peut stocker que des valeurs du type associé. SQLite utilise un typage manifeste, qui permet de stocker n’importe quelle quantité de données de n’importe quel type dans n’importe quelle colonne, quel que soit le type déclaré de la colonne. Notez qu’il existe des exceptions, comme les colonnes de clé primaire entière, qui ne peuvent contenir que des entiers.
Comme souvent, SQLite présente aussi des limites par rapport à d’autres moteurs. Par exemple, SQLite ne prend pas en charge les RIGHT ou FULL OUTER JOIN, et l’absence de serveur peut être un inconvénient pour la sécurité et la protection contre les usages abusifs des données. Il n’en reste pas moins un outil très puissant, particulièrement utile dans certaines situations. Par exemple, j’ai utilisé une base SQLite dans un récent projet d’analyse des réseaux sociaux pour stocker une edge list d’un nombre considérable d’utilisateurs Twitter de manière très simple et efficace.
Dans ce tutoriel pour débutants, je vais illustrer comment travailler avec une base SQLite. Nous verrons l’installation de SQLite, la création et les opérations sur les tables, les commandes point de SQLite, ainsi que l’utilisation de requêtes SQL simples pour manipuler les données dans une base SQLite.
Une edge list (ou liste d’adjacence) est un ensemble de listes non ordonnées utilisé pour représenter un graphe en analyse de réseaux. Chaque liste décrit l’ensemble des voisins d’un nœud ou sommet donné du graphe.
Installer et configurer SQLite
Commençons par l’installation de SQLite sur votre ordinateur. C’est très simple. Comme le montre le tutoriel SQLite with Python de Sayak Paul, il existe un outil graphique appelé DB Browser for SQLite qui permet de démarrer facilement. Pour varier, je vais montrer l’utilisation de l’outil en ligne de commande, que je préfère parfois.
Si vous êtes sur Windows, rendez-vous d’abord sur ce site et téléchargez sqlite-tools-win32-x86-3270200.zip. Après avoir décompressé le dossier, vous devriez voir les trois fichiers suivants :
Ensuite, lancer la ligne de commande SQLite est aussi simple que de cliquer sur l’application sqlite3, ce qui ouvre une fenêtre de commande comme ceci :
Vous remarquerez que, par défaut, SQLite utilise une base en mémoire transitoire. Dans la grande majorité des cas, ce n’est pas ce que vous souhaitez. À la place, créez une nouvelle base à l’aide de la commande point .open suivie du nom que vous souhaitez donner à votre fichier de base (voir ci‑dessous) :
Et voilà, vous avez créé une nouvelle base. Notez qu’il est indispensable d’ajouter l’extension .db au nom choisi pour créer le fichier de base. Nous avons nommé la base Tweet_Data.db, car j’utiliserai des tweets pour alimenter les tables.
Créer des tables
La base étant créée, passons à la création de notre table de tweets. Deux approches sont possibles. Vous pouvez d’abord importer un fichier .csv et en faire une table. Pour cela, passez SQLite en mode csv avec la commande point .mode suivie de csv, puis utilisez .import avec le fichier (ici un csv situé dans le même dossier, mais cela peut être n’importe quel csv si vous indiquez le chemin complet) et le nom de table de votre choix. Vous pouvez aussi afficher les en‑têtes de colonnes avec la commande point .headers suivie de on. (Voir ci‑dessous)
Une fois l’import terminé, utilisez la commande .tables pour afficher toutes les tables disponibles dans la base, ainsi que .schema suivie d’un nom de table pour consulter sa structure. Vous pouvez également exécuter une requête simple, par exemple SELECT * FROM Table_Name, pour vérifier la présence des données.
Outre la création de tables par import de fichiers csv, vous pouvez aussi créer des tables avec CREATE TABLE puis insérer de nouvelles lignes avec INSERT INTO. Dans l’exemple suivant, vous verrez comment créer une table à partir de zéro et y insérer deux lignes.
Remarque : dans la ligne de commande SQLite, si vous souhaitez poursuivre une requête sur la ligne suivante comme dans l’exemple, appuyez simplement sur Entrée pour passer à la ligne. La requête n’est évaluée qu’une fois que vous validez une ligne se terminant par un point‑virgule.
Exporter depuis SQLite
Si vous souhaitez exporter le résultat d’une requête vers un fichier externe, par exemple un csv, SQLite le permet très facilement. Pour un export propre en csv, suivez une démarche similaire à l’import. D’abord, .header suivi de on et .mode suivi de csv. Puis, utilisez la commande point .output suivie du nom de fichier csv souhaité et, enfin, exécutez la requête SQL à exporter et quittez SQLite avec la commande .exit. Essayons d’exporter en csv la nouvelle table créée à l’étape précédente.
Voici le csv fraîchement généré contenant également les données de Sample_Tweets_2.
Mettre à jour des tables
Au‑delà de la création et de l’export, vous pouvez utiliser les instructions SQL UPDATE/SET et DROP TABLE pour mettre à jour des lignes ou supprimer une table. Dans l’exemple suivant, nous mettons à jour le deuxième tweet de la table factice Sample_Tweets_2.
Maintenant, disons adieu à la table factice Sample_Tweets_2 et supprimons‑la du fichier de base avec DROP TABLE :
Utiliser une base SQLite pour traiter des données Twitter
Vous connaissez désormais l’essentiel pour travailler avec une base SQLite et manipuler des tables ; il ne vous reste plus qu’à en tirer des informations utiles via des requêtes SQL. Voici deux requêtes relativement simples que j’ai utilisées lors de mon projet d’analyse de médias sociaux sur Twitter. Considérez cela comme une mini étude de cas illustrant une application concrète d’une base SQLite.
Commençons simplement : trouver tous les pseudos uniques des utilisateurs Twitter dans la table Sample_Tweets. Dans mon projet, il était essentiel d’identifier les utilisateurs uniques dont je collectais les tweets, car je devais récupérer leurs abonnés pour créer la edge list mentionnée au début de ce tutoriel.
26 860 utilisateurs uniques dans la base. Un excellent échantillon, sachant que les thèmes suivis étaient la localisation de sites web et la traduction. Examinons maintenant les 20 utilisateurs ayant tweeté le plus dans l’échantillon, avec une requête SQL un peu plus complexe (retweets exclus) :
On voit que le volume de tweets le plus élevé appartient à un bot ! Il s’avère toutefois que le dernier de cette liste, UweMuegge, est un véritable influenceur selon divers indicateurs de réseau calculés pour le projet. Cela ne voulait pas dire pour autant qu’il était le plus important.
Enfin, regardons quelques tweets publiés par UweMuegge à l’aide d’une autre requête SQL :
Deux grands sujets se dégagent : des offres d’emploi en traduction et des conférences de traduction. On comprend pourquoi des professionnels du secteur le suivent sur Twitter.
Autre point visible sur la capture d’écran : l’une des limites de l’interface en ligne de commande de SQLite. Avec de grands champs texte, l’affichage n’est pas idéal et la lecture devient difficile. Dans ce cas, il est pertinent d’utiliser DB Browser for SQLite.
Résumé
Dans ce tutoriel, vous avez appris à créer des bases SQLite et à manipuler des tables via des commandes point et des instructions SQL. Vous avez aussi vu une petite étude de cas réelle où j’ai utilisé une base SQLite pour gérer des données Twitter. C’est déjà beaucoup, bravo !
À noter : les bases SQLite sont particulièrement utiles combinées avec R et Python. Vous pouvez manipuler des bases SQLite en Python via le module sqlite3 (pour en savoir plus, voir le tutoriel SQLite with Python) ou avec R via RSQLite. D’ailleurs, la edge list des abonnés d’utilisateurs Twitter mentionnée plus haut est stockée dans une base SQLite que j’ai créée avec RSQLite en ajoutant, pour chaque utilisateur, le graphe de ses abonnés à une table. À cette étape du projet, SQLite a été crucial, car une coupure de courant ou une mise à jour automatique de Windows peut forcer l’arrêt de votre ordinateur et vous obliger à reprendre la collecte des réseaux d’abonnés depuis le début. Avec les limites de débit de l’API de Twitter, cela peut prendre environ 4 semaines : repartir de zéro serait très pénalisant. En sauvegardant au fil de l’eau dans une base SQLite, même en cas d’arrêt forcé, vous pouvez reprendre à l’utilisateur suivant le dernier enregistré dans la base, ce qui fait gagner un temps précieux.
Ce n’est qu’un exemple montrant en quoi les bases SQLite peuvent être un formidable atout dans l’arsenal d’un data scientist. Je vous encourage à approfondir SQLite et SQL au‑delà de ce qui est couvert ici. Continuez à apprendre : le ciel est la seule limite !
Si vous souhaitez progresser en SQL, suivez le cours Introduction to Relational Databases in SQL de DataCamp.