Qu'est-ce que SQL ?

Le langage de requête structuré (SQL) est le langage le plus couramment utilisé pour exécuter diverses tâches d'analyse de données. Il est également utilisé pour la maintenance d'une base de données relationnelle, par exemple : l'ajout de tableaux, la suppression de valeurs et l'optimisation de la base de données. Une base de données relationnelle simple se compose de plusieurs tableaux interconnectés, chaque tableau étant constitué de lignes et de colonnes.

En moyenne, une entreprise technologique génère des millions de points de données chaque jour. Une solution de stockage robuste et efficace est nécessaire pour pouvoir utiliser les données afin d'améliorer le système actuel ou de créer un nouveau produit. Les bases de données relationnelles telles que MySQL, PostgreSQL et SQLite résolvent ces problèmes en offrant une gestion robuste des bases de données, une sécurité et des performances élevées.

Fonctionnalités de base de SQL

• Créer de nouveaux tableaux dans une base de données
• Exécuter des requêtes sur une base de données
• Extraire des données d'une base de données
• Insérer des enregistrements dans une base de données
• Mettre à jour des enregistrements dans une base de données
• Supprimer des enregistrements d'une base de données
• Optimisez n'importe quelle base de données

Le langage SQL est une compétence très demandée qui vous aidera à décrocher n'importe quel emploi dans le secteur technologique. Des entreprises telles que Meta, Google et Netflix sont toujours à la recherche de professionnels des données capables d'extraire des informations des bases de données SQL et de proposer des solutions innovantes. Vous pouvez apprendre les bases de SQL en suivant le tutorielIntroduction à SQL sur DataCamp.

Pourquoi utiliser SQL avec Python et R ?

SQL peut nous aider à découvrir les performances de l'entreprise, à comprendre les comportements des clients et à contrôler les indicateurs de réussite des campagnes de marketing. La plupart des analystes de données peuvent effectuer la majorité des tâches de business intelligence en exécutant des requêtes SQL, alors pourquoi avons-nous besoin d'outils tels que Python, Python et R ? En utilisant des requêtes SQL, vous pouvez savoir ce qui s'est passé dans le passé, mais vous ne pouvez pas prédire les projections futures. Ces outils nous aident à mieux comprendre les performances actuelles et la croissance potentielle.

Python et R sont des langages polyvalents qui permettent aux professionnels d'exécuter des analyses statistiques avancées, de construire des modèles d'apprentissage automatique, de créer des API de données et, finalement, d'aider les entreprises à penser au-delà des KPI. Dans ce tutoriel, nous allons apprendre à connecter des bases de données SQL, à alimenter des bases de données et à exécuter des requêtes SQL à l'aide de Python et de R.

Note : Si vous êtes novice en SQL, suivez lecursus de compétences SQL pour comprendre les principes fondamentaux de l'écriture de requêtes SQL.

Tutoriel Python

Le tutoriel Python couvrira les bases de la connexion avec différentes bases de données (MySQL et SQLite), la création de tableaux, l'ajout d'enregistrements, l'exécution de requêtes et l'apprentissage de la fonctionPythonread_sql.

Mise en place

Nous pouvons connecter la base de données à l'aide deSQLAlchemy, mais dans ce tutoriel, nous allons utiliser le paquetage Python intégréSQLite3 pour exécuter des requêtes sur la base de données. SQLAlchemy prend en charge tous les types de bases de données grâce à une API unifiée. Si vous souhaitez en savoir plus sur SQLAlchemy et sur la façon dont il fonctionne avec d'autres bases de données, consultez le coursIntroduction aux bases de données en Python.

MySQL est le moteur de base de données le plus populaire au monde, et il est largement utilisé par des entreprises telles que Youtube, Paypal, LinkedIn et GitHub. Nous allons ici apprendre à connecter la base de données. Les autres étapes de l'utilisation de MySQL sont similaires à celles du paquetage SQLite3.

Tout d'abord, installez le paquetmysql en utilisant '!pip install mysql' et créez ensuite un moteur de base de données local en fournissant votre nom d'utilisateur, votre mot de passe et le nom de la base de données.

import mysql.connector as sqlconn = sql.connect(  host="localhost",  user="abid",  password="12345",  database="datacamp_python")

De même, nous pouvons créer ou charger une base de données SQLite en utilisant la fonctionsqlite3.connect.SQLite est une bibliothèque qui met en œuvre un moteur de base de données autonome, sans configuration et sans serveur. Il estcompatible avec DataLab, nous l'utiliserons donc dans notre projet pour éviter les erreurs liées à l'hôte local.

import sqlite3import pandas as pdconn= sqlite3.connect("datacamp_python.db")

Création d'une base de données

Dans cette partie, nous allons apprendre à charger le jeu de donnéesCOVID-19's impact on airport traffic, sous licenceCC BY-NC-SA 4.0, dans notre base de données SQLite. Nous apprendrons également à créer des tableaux à partir de zéro.

L'ensemble des données relatives au trafic aéroportuaire correspond à un pourcentage du volume de trafic pendant la période de référence allant du 1er février 2020 au 15 mars 2020. Nous chargerons un fichier CSV à l'aide de la fonctionPandasread_csv, puis nous utiliserons la fonctionto_sql pour transférer le cadre de données dans notre tableau SQLite. La fonctionto_sql nécessite un nom de tableau (String) et une connexion au moteur SQLite.

data = pd.read_csv("data/covid_impact_on_airport_traffic.csv")data.to_sql(            'airport',             # Name of the sql table            conn,                 # sqlite.Connection or sqlalchemy.engine.Engine            if_exists='replace'           )

Nous allons maintenant vérifier si nous avons réussi en exécutant une requête SQL rapide. Avant d'exécuter une requête, nous devons créer uncurseur qui nous aidera à exécuter les requêtes, comme le montre le bloc de code ci-dessous. Vous pouvez avoir plusieurs curseurs sur la même base de données au sein d'une même connexion.

Dans notre cas, la requête SQL renvoie trois colonnes et cinq tableaux du tableau desaéroports. Pour afficher la première ligne, nous utiliseronscursor.fetchone().

cursor = conn.cursor()cursor.execute("""SELECT Date, AirportName, PercentOfBaseline                    FROM airport                  LIMIT 5""")cursor.fetchone()>>> ('2020-04-03', 'Kingsford Smith', 64)

Pour afficher le reste des enregistrements, nous utiliseronscursor.fetchall(). Le jeu de données de l'aéroport est chargé avec succès dans la base de données en quelques lignes de code.

cursor.fetchall()>>> [('2020-04-13', 'Kingsford Smith', 29),     ('2020-07-10', 'Kingsford Smith', 54),     ('2020-09-02', 'Kingsford Smith', 18),     ('2020-10-31', 'Kingsford Smith', 22)]

Maintenant, apprenons à créer un tableau à partir de zéro et à l'alimenter en ajoutant des valeurs d'échantillonnage. Nous allons créer un tableaustudentinfo avec l'identifiant (entier, clé primaire, auto-incrément), lenom (texte) et lesujet (texte).

Note : La syntaxe de SQLite est un peu différente. Il est recommandé de consulter l'aide-mémoire SQLite pour comprendre les requêtes SQL mentionnées dans ce tutoriel.

cursor.execute("""                CREATE TABLE studentinfo                (                id INTEGER PRIMARY KEY AUTOINCREMENT,                name TEXT,                subject TEXT                )                 """)

Vérifions le nombre de tableaux que nous avons ajoutés à la base de données en exécutant une simple requête SQLite.

cursor.execute("""                SELECT name                FROM sqlite_master                WHERE type='table'                 """)cursor.fetchall()>>> [('airport',), ('studentinfo',)]

Requêtes en cours d'exécution

Dans cette section, nous allons ajouter des valeurs au tableau "Informations sur les étudiants" et exécuter des requêtes SQL simples. En utilisantINSERT INTO, nous pouvons ajouter une seule ligne au tableaustudentinfo.

Pour insérer des valeurs, nous devons fournir unerequête et des arguments devaleur à la fonctionexecute. La fonction remplit les entrées" ?" avec les valeurs que nous avons fournies.

query = """            INSERT INTO studentinfo            (name, subject)            VALUES (?, ?)        """value = ("Marry", "Math")cursor.execute(query,value)

Répétez la requête ci-dessus en ajoutant plusieurs enregistrements.

query = """           INSERT INTO studentinfo           (name, subject)           VALUES (?, ?)        """values = [("Abid", "Stat"),          ("Carry", "Math"),          ("Ali","Data Science"),          ("Nisha","Data Science"),          ("Matthew","Math"),          ("Henry","Data Science")]cursor.executemany(query,values)

Il est temps de vérifier le dossier. Pour ce faire, nous allons exécuter une simple requête SQL qui renverra les lignes dont lesujet estData Science.

cursor.execute("""                SELECT *                FROM studentinfo                WHERE subject LIKE 'Data Science'                 """)cursor.fetchall()>>> [(4, 'Ali', 'Data Science'),     (5, 'Nisha', 'Data Science'),     (7, 'Henry', 'Data Science')]

La commandeDISTINCT subject est utilisée pour afficher les valeurs uniques présentes dans les colonnes sujet. Dans notre cas, il s'agit des mathématiques, des statistiques et de la science des données.

cursor.execute("SELECT DISTINCT subject from studentinfo")cursor.fetchall()>>> [('Math',), ('Stat',), ('Data Science',)]

Pour enregistrer toutes les modifications, nous utiliserons la fonctioncommit(). Sans validation, les données seront perdues après le redémarrage de la machine.

conn.commit()

SQL avec Pandas

Dans cette partie, nous allons apprendre à extraire les données de la base de données SQLite et à les convertir en un cadre de données Pandas en une seule ligne de code.read_sql offre bien plus que l'exécution de requêtes SQL. Nous pouvons l'utiliser pour définir des colonnes d'index, analyser la date et l'heure, ajouter des valeurs et filtrer les noms de colonnes. Apprenez-en plus sur l'importation de données en Python en suivant une courte formationDataCamp.

read_sql nécessite deux arguments : unerequête SQL et uneconnexion au moteur SQLite. Le résultat contient les cinq premiers tableaux du tableau desaéroports oùPercentOfBaseline est supérieur à 20.

data_sql_1 = pd.read_sql("""                        SELECT Date,City,PercentOfBaseline                        FROM airport                        WHERE PercentOfBaseline > 20                        ORDER BY Date DESC                        """,                        conn)print(data_sql_1.head())         Date          City  PercentOfBaseline0  2020-12-02        Sydney                 271  2020-12-02      Santiago                 482  2020-12-02       Calgary                 993  2020-12-02  Leduc County                1004  2020-12-02      Richmond                 86

L'exécution d'analyses de données sur des bases de données relationnelles est devenue plus facile grâce à l'intégration de Pandas. Nous pouvons également utiliser ces données pour prévoir les valeurs et effectuer des analyses statistiques complexes.

La fonctionplot est utilisée pour visualiser l'histogramme de la colonnePercentOfBaseline.

data_sql_1.plot(y="PercentOfBaseline",kind="hist");

De même, nous pouvons limiter les valeurs aux 20 premières et afficher un graphique linéaire de la série chronologique.

data_sql_2 = pd.read_sql("""                        SELECT Date,City,PercentOfBaseline                        FROM airport                        WHERE PercentOfBaseline > 20                        ORDER BY Date DESC                        LIMIT 20                        """,                        conn)data_sql_2.plot(x="Date",y="PercentOfBaseline",kind="line");

Enfin, nous fermerons la connexion pour libérer des ressources. La plupart des logiciels le font automatiquement, mais il est préférable de fermer les connexions après avoir finalisé les modifications.

conn.close()

R Tutoriel

Nous allons reproduire toutes les tâches du tutoriel Python en utilisant R. Le tutoriel comprend la création de connexions, l'écriture de tableaux, l'ajout de lignes, l'exécution de requêtes et l'analyse de données avecPython.

Mise en place

Le paquetDBI est utilisé pour se connecter aux bases de données les plus populaires telles queMariaDB,Postgres,Duckdb etSQLite. Par exemple, installez le paquetRMySQL et créez une base de données en fournissant un nom d'utilisateur, un mot de passe, un nom de base de données et une adresse hôte.

install.packages("RMySQL")library(RMySQL)conn = dbConnect(                MySQL(),                user = 'abid',                password = '1234',                dbname = 'datacamp_R',                host = 'localhost'                )

Dans ce tutoriel, nous allons créer une base de données SQLite en fournissant un nom et la fonction SQLite.

library(RSQLite)library(DBI)library(tidyverse)conn = dbConnect(SQLite(), dbname = 'datacamp_R.db')

Création d'une base de données

En important la bibliothèquetidyverse, nous aurons accès aux ensembles de données dplyr, ggplot et defaults.

dbWriteTable prenddata.frame et l'ajoute au tableau SQL. Elle prend trois arguments : la connexion à SQLite, le nom du tableau et le cadre de données. AvecdbReadTable, nous pouvons visualiser l'ensemble du tableau. Pour visualiser les 6 premières lignes, nous avons utiliséhead.

dbWriteTable(conn, "cars", mtcars)head(dbReadTable(conn, "cars"))    mpg    cyl    disp    hp    drat    wt    qsec    vs    am    gear    carb1    21.0    6    160    110    3.90    2.620    16.46    0    1    4    42    21.0    6    160    110    3.90    2.875    17.02    0    1    4    43    22.8    4    108    93    3.85    2.320    18.61    1    1    4    14    21.4    6    258    110    3.08    3.215    19.44    1    0    3    15    18.7    8    360    175    3.15    3.440    17.02    0    0    3    26    18.1    6    225    105    2.76    3.460    20.22    1    0    3    1

dbExecute nous permet d'exécuter n'importe quelle requête SQLite, nous allons donc l'utiliser pour créer un tableau appeléidcard.

Pour afficher les noms des tableaux de la base de données, nous utiliseronsdbListTables.

dbExecute(conn, 'CREATE TABLE idcard (id int, name text)')dbListTables(conn)>>> 'cars''idcard'

Ajoutons une seule ligne au tableauidcard et utilisonsdbGetQuery pour afficher les résultats.

Remarque : dbGetQuery exécute une requête et renvoie les enregistrements, tandis quedbExecute exécute une requête SQL mais ne renvoie aucun enregistrement.

dbExecute(conn, "INSERT INTO idcard (id,name)\                 VALUES(1,'love')")dbGetQuery(conn,"SELECT * FROM idcard")id    name1    love

Nous allons maintenant ajouter deux lignes supplémentaires et afficher les résultats en utilisantdbReadTable.

dbExecute(conn,"INSERT INTO idcard (id,name)\                VALUES(2,'Kill'),(3,'Game')               ")dbReadTable(conn,'idcard')id    name1    love2    Kill3    Game

dbCreateTable nous permet de créer un tableau sans tracas. Elle requiert trois arguments : la connexion, le nom du tableau et un vecteur de caractères ou un data.frame. Le vecteur de caractères se compose de noms (noms de colonnes) et de valeurs (types). Dans notre cas, nous allons fournir undata.frame de population par défaut pour créer la structure initiale.

dbCreateTable(conn,'population',population)dbReadTable(conn,'population')country    year    population

Ensuite, nous allons utiliserdbAppendTable pour ajouter des valeurs dans le tableau de lapopulation.

dbAppendTable(conn,'population',head(population))dbReadTable(conn,'population')country    year    populationAfghanistan    1995    17586073Afghanistan    1996    18415307Afghanistan    1997    19021226Afghanistan    1998    19496836Afghanistan    1999    19987071Afghanistan    2000    20595360

Requêtes en cours d'exécution

Nous utiliseronsdbGetQuery pour effectuer toutes nos tâches d'analyse de données. Essayons d'exécuter une requête simple avant d'en apprendre davantage sur les autres fonctions.

dbGetQuery(conn,"SELECT * FROM idcard")id    name1    love2    Kill3    Game

Vous pouvez également exécuter une requête SQL complexe pour filtrer la puissance et afficher un nombre limité de lignes et de colonnes.

dbGetQuery(conn, "SELECT mpg,hp,gear\                  FROM cars\                  WHERE hp > 50\                  LIMIT 5")mpg    hp    gear21.0    110    421.0    110    422.8    93    421.4    110    318.7    175    3

Pour supprimer des tableaux, utilisezdbRemoveTable. Comme nous pouvons le constater, nous avons réussi à supprimer le tableauidcard.

dbRemoveTable(conn,'idcard')dbListTables(conn)>>> 'cars''population'

Pour mieux comprendre les tableaux, nous utiliseronsdbListFields qui affichera les noms des colonnes d'un tableau particulier.

dbListFields(conn, "cars")>>> 'mpg''cyl''disp''hp''drat''wt''qsec''vs''am''gear''carb'

SQL avec dplyr

Dans cette section, nous utiliserons dplyr pour lire des tableaux, puis exécuter des requêtes à l'aide de filter, select et collect. Si vous ne voulez pasapprendre la syntaxe SQL et que vous souhaitez effectuer toutes les tâches en utilisant R, cette méthode est faite pour vous. Nous avons extrait le tableau desvoitures, l'avons filtré en fonction desvitesses et dukilométrage, puis avons sélectionné trois colonnes comme indiqué ci-dessous.

cars_results <-  tbl(conn, "cars") %>%  filter(gear %in% c(4, 3),         mpg >= 14,         mpg <= 21) %>%  select(mpg, hp, gear) %>%  collect()cars_resultsmpg    hp    gear21.0    110    421.0    110    418.7    175    318.1    105    314.3    245    3...   ...   ...

Nous pouvons utiliser la base de données filtrée pour afficher un graphique en boîte à l'aide deggplot.

ggplot(cars_results,aes(fill=as.character(gear), y=mpg)) +  geom_boxplot()

Nous pouvons également afficher un diagramme de points de facettes divisé par le nombre d'engrenages.

ggplot(cars_results,  aes(mpg, ..count.. ) ) +  geom_point(stat = "count", size = 4) +  coord_flip()+  facet_grid( as.character(gear) ~ . )

Conclusion

Dans ce tutoriel, nous avons appris l'importance de l'exécution de requêtes SQL avec Python et R, de la création de bases de données, de l'ajout de tableaux et de l'exécution d'analyses de données à l'aide de requêtes SQL. Nous avons également appris comment Pandas et dplyr nous aident à exécuter des requêtes avec une seule ligne de code.

Le langage SQL est une compétence indispensable pour tous les emplois liés à la technologie. Si vous commencez votre carrière en tant qu'analyste de données, nous vous recommandons de suivre le cursusData Analyst with SQL Server dans un délai de deux mois. Ce cursus professionnel vous apprendra tout sur les requêtes SQL, les serveurs et la gestion des ressources.

Vous pouvez exécuter gratuitement tous les scripts utilisés dans ce tutoriel :

• Le projetSQL avec Python est disponible dansce classeur DataLab.
• Le projetSQL with R est disponible sur leclasseur DataLab.